Database Tests and Representative Engine Behavior

A repository class is a thin layer of your code wrapped around a large amount of someone else’s: the database engine. The engine decides whether a value fits a column, whether a constraint holds, how LIKE compares strings, what order rows come back in and what error you get when something goes wrong. When you test data-access code, most of what you’re checking is that engine’s behavior combined with your SQL.

That’s why the choice of database in tests matters more than it first appears. Swapping PostgreSQL for SQLite or H2 “because it’s faster” doesn’t just change the speed of the tests. It changes the behavior being tested. Many of the bugs database tests should catch only exist in the gap between engines.

Same schema, two engines

Here’s one table, created in SQLite 3.51 (via Node 22’s built-in node:sqlite) and in PostgreSQL 18.3 (running in-process through PGlite, covered below). The same statements were run against both (output abbreviated, with each statement shown next to its label):

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  sku VARCHAR(12) NOT NULL UNIQUE,
  name TEXT NOT NULL,
  price_cents INTEGER NOT NULL,
  discontinued_at TEXT
);

text in integer column   INSERT ... (1, 'LAMP-01', 'Desk lamp', 'abc', NULL)
  postgres: ERROR 22P02: invalid input syntax for type integer: "abc"
  sqlite:   accepted
sku longer than 12       INSERT ... (2, 'LAMP-01-BRASS-XL', 'Desk lamp', 3999, NULL)
  postgres: ERROR 22001: value too long for type character varying(12)
  sqlite:   accepted
dup sku                  INSERT ... (4, 'CHAIR-01', 'Chair copy', 12900, NULL)
  postgres: ERROR 23505: duplicate key value violates unique constraint "products_sku_key"
  sqlite:   ERROR ERR_SQLITE_ERROR 2067: UNIQUE constraint failed: products.sku

Two rows that PostgreSQL rejects went straight into SQLite. Reading them back, SQLite reports typeof(price_cents) as text for the 'abc' row: column types in SQLite are affinities, preferences rather than rules, and VARCHAR(12) is accepted as syntax but the length is never enforced. Both behaviors are documented. They just aren’t PostgreSQL’s.

Then two queries, after adding a Desk lamp (discontinued) and a desk mat:

SELECT sku FROM products WHERE name LIKE 'desk%' ORDER BY sku
  postgres: [ 'DESK-02' ]
  sqlite:   [ 'DESK-01', 'DESK-02', 'LAMP-01', 'LAMP-01-BRASS-XL' ]

SELECT sku FROM products ORDER BY discontinued_at, sku
  postgres: [ 'DESK-01', 'CHAIR-01', 'DESK-02' ]
  sqlite:   [ 'CHAIR-01', 'DESK-02', 'LAMP-01', 'LAMP-01-BRASS-XL', 'DESK-01' ]

SQLite’s LIKE is case-insensitive for ASCII letters, PostgreSQL’s is case-sensitive. And when sorting ascending, PostgreSQL puts NULL last while SQLite puts it first. (SQLite also has more rows simply because it accepted the two bad inserts.) Finally, ILIKE, PostgreSQL’s case-insensitive operator, doesn’t exist in SQLite at all: near "ILIKE": syntax error.

How a substitute engine hides real bugs

Each difference above turns into a specific kind of escaped bug.

Validation you think the database is doing. If the API layer lets a 16-character SKU through because “the column is VARCHAR(12), the database will reject it”, tests on SQLite will never show the rejection. In production the request fails with an unhandled 22001 and the user sees a 500.

Queries written for the test engine. Search must ignore case. A developer writes WHERE name LIKE '%' || ? || '%', the test “searching desk finds Desk lamp” passes on SQLite, and production search silently returns nothing for lowercase terms. Writing SQL that “works on both” tends to mean writing SQL that’s correct on neither.

Error handling that never runs. Turning a unique violation into a domain error is common:

} catch (err) {
  if (err.code === '23505') throw new SkuTaken(sku); // unique_violation
  throw err;
}

On SQLite that error has code ERR_SQLITE_ERROR and extended code 2067, so the branch never executes in tests. The tempting “fix” is to match on the message text, 'UNIQUE constraint failed', which makes tests pass and production break.

Order you didn’t ask for. Any test that compares results to a fixed array, where the query lacks a complete ORDER BY, is relying on engine-specific behavior. The NULL-ordering difference is one case; row order without ORDER BY is another, and it can change between versions of the same engine as query plans change.

Testing against PostgreSQL itself

The direct fix is to run database tests on the engine and major version you deploy. There are two common ways to do that.

A real server in a container. Testcontainers (available for Java, Node, Python, Go and .NET) starts a PostgreSQL Docker container for the test run and gives you its connection string. You get the full server: multiple connections, extensions, the same configuration as production. The cost is Docker on every developer machine and CI runner, and a few seconds of startup.

PGlite. PGlite is PostgreSQL compiled to WebAssembly, packaged as an npm module. It runs inside the Node process, with no Docker and no separate server. Here’s a repository written for PostgreSQL, with its tests:

export const SCHEMA = `
CREATE TABLE products (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku VARCHAR(12) NOT NULL UNIQUE,
  name TEXT NOT NULL,
  price_cents INTEGER NOT NULL CHECK (price_cents >= 0)
)`;

export class ProductRepo {
  constructor(db) { this.db = db; }

  async add({ sku, name, priceCents }) {
    try {
      const { rows } = await this.db.query(
        'INSERT INTO products (sku, name, price_cents) VALUES ($1, $2, $3) RETURNING id',
        [sku, name, priceCents],
      );
      return rows[0].id;
    } catch (err) {
      if (err.code === '23505') throw new SkuTaken(sku);
      throw err;
    }
  }

  async search(term) {
    const { rows } = await this.db.query(
      "SELECT sku, name FROM products WHERE name ILIKE '%' || $1 || '%' ORDER BY name",
      [term],
    );
    return rows;
  }
}

let db;
let repo;

before(async () => {
  db = new PGlite();          // PostgreSQL compiled to WebAssembly, in this process
  await db.exec(SCHEMA);
  repo = new ProductRepo(db);
});

beforeEach(async () => {
  await db.exec('TRUNCATE products RESTART IDENTITY');
});

test('search ignores case', async () => {
  await repo.add({ sku: 'DESK-01', name: 'Desk lamp', priceCents: 3999 });
  await repo.add({ sku: 'CHAIR-01', name: 'Office chair', priceCents: 12900 });

  assert.deepEqual(await repo.search('desk'), [{ sku: 'DESK-01', name: 'Desk lamp' }]);
});

test('a duplicate SKU is reported as SkuTaken', async () => {
  await repo.add({ sku: 'DESK-01', name: 'Desk lamp', priceCents: 3999 });

  await assert.rejects(
    repo.add({ sku: 'DESK-01', name: 'Brass desk lamp', priceCents: 5999 }),
    SkuTaken,
  );
});

test('a SKU longer than 12 characters is rejected by the database', async () => {
  await assert.rejects(
    repo.add({ sku: 'DESK-01-BRASS-XL', name: 'Desk lamp', priceCents: 3999 }),
    { code: '22001', message: 'value too long for type character varying(12)' },
  );
});

engine ready in 1125 ms
✔ search ignores case (1127.868083ms)
✔ a duplicate SKU is reported as SkuTaken (3.83625ms)
✔ a SKU longer than 12 characters is rejected by the database (1.596584ms)
✔ a negative price violates the check constraint (1.706584ms)

The fourth test, not shown, checks that a negative price fails the CHECK constraint with code 23514. Starting the engine took about 1.1 seconds, which is why it happens once per file in before, not per test. After that each test takes a few milliseconds. Isolation comes from TRUNCATE ... RESTART IDENTITY before every test, so ids restart at 1 and no rows carry over. The SkuTaken branch now runs in tests for real.

Run the migrations your application uses rather than a hand-maintained test schema, so the tests also catch a migration that doesn’t match what the code expects.

What even the right engine won’t show

Using PostgreSQL in tests closes the semantic gap. It doesn’t make tests representative of everything:

• Concurrency. PGlite runs a single connection, so two transactions racing for the same row, lock waits, deadlocks and serialization failures under SERIALIZABLE can’t happen. Testing “two checkouts can’t both buy the last item” needs a real server with two connections, which is a job for the container approach.
• Query plans. With ten rows, every query is fast and PostgreSQL may scan the table instead of using an index. A missing index only shows up with realistic data volumes, in a separate performance check.
• Configuration and versions. Collations, time zones, max_connections and extensions should match production. A major-version mismatch (testing on 15, running 17) reintroduces small semantic differences.
• Migrations on existing data. Adding a NOT NULL column passes on an empty test database and fails on a production table with rows. Test migrations against a copy of realistic data, too.

Interview practice

A Spring Boot service uses H2 in its test profile and PostgreSQL in production. All tests pass, but after a release, an endpoint fails in production with ERROR: operator does not exist: jsonb @> character varying. The query works in tests. What happened, and how would you change the test setup?

Answer

The query uses PostgreSQL’s JSONB containment operator, and the tests never ran it against PostgreSQL. H2 doesn’t have JSONB; depending on configuration it either maps the column to another type, runs in a “PostgreSQL compatibility mode” that only emulates some syntax, or the query goes through a code path the tests stub. Either way the tests verified H2’s interpretation of the query, not PostgreSQL’s. The production error shows the real problem: the parameter was sent as character varying, and PostgreSQL has no @> between jsonb and text, so the query needed an explicit cast such as ?::jsonb.

I’d switch the repository and integration tests to PostgreSQL itself, using Testcontainers with the same major version and extensions as production, and run the real Flyway or Liquibase migrations at startup. To keep the suite fast, I’d start one container per test run (or per CI worker), isolate tests with transactions or truncation, and keep pure business logic in unit tests that don’t touch the database at all. Then I’d add a regression test for this endpoint’s query with a JSON filter, which would have failed with the same error before the cast was added.

Related reading

• Software Testing interview questions
• Software Testing MCQs
• Test Fixtures and Independent State
• Unit and Integration Test Balance
• PostgreSQL documentation: character types and length limits