Test against the same database engine

How to do it

Start the database in a container for the test run, for example with Testcontainers or a Compose file. Run the migrations before the tests. An in-memory stand-in such as SQLite for a PostgreSQL app hides bugs in queries, types and locking. Unit tests of logic that takes plain data need no database at all.

Example

import { PostgreSqlContainer } from '@testcontainers/postgresql';

const db = await new PostgreSqlContainer('postgres:18').start();
process.env.DATABASE_URL = db.getConnectionUri();
await runMigrations();

# Python with testcontainers
from testcontainers.postgres import PostgresContainer

with PostgresContainer("postgres:18", driver=None) as database:
    os.environ["DATABASE_URL"] = database.get_connection_url()
    run_migrations()

How to check

The test setup starts the production database engine at the same major version as production and runs every migration. A query that only works on the stand-in fails the suite.