Skip to content
Tutorials Center
My learning

Topics

Lesson 27 of 37 ยท Testing

Test Databases and Fixtures

Choose a test database strategy for ORM code: in-memory SQLite or a real engine, rollback or fresh schema per test, and reliable fixtures.

Tests that touch a database are slower and more fragile than pure unit tests, but they are also the tests that catch the bugs ORMs are famous for: a wrong column name, a missing relation, a query that returns duplicates. The question is not whether to test against a database, but how to give every test a clean, predictable one without making the suite crawl.

This lesson compares the common strategies and shows the two most useful patterns in runnable form. The runnable blocks use node:sqlite; TypeORM and Sequelize setup code is read-only because the packages are not installed in the lesson runner.

Three places to run your tests

Strategy Speed Realism Watch out for
Mock the repository fastest lowest Proves your service calls the mock, not that the SQL works
In-memory SQLite fast medium Dialect differences: no JSONB, different types, looser typing, different locking
Real engine (PostgreSQL or MySQL in a container) slower highest Needs Docker or a service in CI; must be reset between tests

A sensible mix: unit-test business logic with fakes, and run a smaller set of integration tests against the same database engine you use in production. If production runs PostgreSQL, a test that passes on SQLite can still fail in production on a case-sensitive collation, a RETURNING clause or a jsonb operator.

Building the schema for tests

Both libraries can create tables from your models directly, which is acceptable for tests even though it is forbidden in production (see Why You Need Migrations).

TypeScript
// TypeORM: a throwaway data source for tests
export const testSource = new DataSource({
  type: 'better-sqlite3',
  database: ':memory:',
  entities: [User, Post],
  synchronize: true,   // OK here: the database is disposable
});
JavaScript
// Sequelize
const sequelize = new Sequelize('sqlite::memory:', { logging: false });
await sequelize.sync({ force: true }); // drop and recreate all tables

If your project has real migrations, run them in at least one CI job (migration:run or db:migrate against an empty test database). That is the only way to prove the migration history actually produces the schema your models expect.

Pattern 1: fresh database per test

An in-memory database is created in microseconds, so every test can have its own. Nothing leaks between tests, and tests may run in parallel.

Node.js A fresh in-memory database for every test

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Pattern 2: roll back after each test

With a real database that takes seconds to set up, build the schema once, then wrap every test in a transaction and roll it back at the end. The data written by the test disappears instantly.

Node.js Transaction per test, rolled back afterwards

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

The catch with this pattern: your code under test must use the same connection, so the ORM has to be told to reuse the test's transaction. In TypeORM you pass the test's QueryRunner/EntityManager into the service; in Sequelize you use CLS or pass { transaction }. Code that opens its own transaction will use savepoints, which works, but code that commits explicitly will break the pattern. If that is a problem, use truncation instead.

Pattern 3: truncate between tests

Slower than rollback but simple and engine-independent: after each test, delete all rows from all tables (children first, or disable foreign keys while you do it).

TypeScript
afterEach(async () => {
  for (const meta of dataSource.entityMetadatas) {
    await dataSource.query(`DELETE FROM "${meta.tableName}"`);
  }
});

On PostgreSQL, TRUNCATE ... RESTART IDENTITY CASCADE resets auto-increment counters as well, which keeps ids predictable.

Fixtures: loading known data

A fixture is data you load before a test so you know exactly what is in the database. Keep fixtures small and explicit: one function per scenario, called inside the test or beforeEach.

Node.js Small named fixtures

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Avoid one giant SQL dump shared by every test: nobody remembers which test depends on which row, and changing it breaks unrelated tests. The next lesson replaces hand-written inserts with factories: Factories and Mocks.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why should at least one test job run your real migrations?

    sync builds tables from models and would hide a broken or missing migration.
  2. 2. What is the main risk of testing only on in-memory SQLite when production uses PostgreSQL?

    Types, collations, JSON operators and locking behave differently between engines.
  3. 3. In the rollback-per-test pattern, when is the schema created?

    Only the data is discarded per test; building the schema repeatedly would be slow.
  4. 4. Which is a good fixture style?

    Explicit scenarios make each test readable and independent.

Summary

  • Mix fast unit tests with integration tests on the engine you run in production.
  • A fresh in-memory database per test is simplest; rollback-per-test is fastest on real engines; truncation is the fallback.
  • Use synchronize/sync for the disposable test schema, but run real migrations in CI at least once.
  • Keep fixtures small, named and local to the test.

Next: Testing Queries with node:test