Mocking vs Real DB in Tests: AI Rules

The AI's Mocking Default and Its Consequences

When asked to generate a test for a function that queries the database, the AI defaults to: mocking the database. const mockDb = { select: vi.fn().mockResolvedValue([{ id: '1', name: 'Alice' }]) }. The test passes: always (the mock returns exactly what you told it to). The test verifies: that the function calls db.select correctly and processes the result. The test does NOT verify: that the SQL query is correct, that the WHERE clause filters correctly, that JOINs work, that indexes are used, that database constraints are satisfied, or that the schema matches the query.

The consequence: a mocked database test suite that passes 100% while the actual queries fail in production. The mock said: "here is the data you asked for." The real database said: "column not found" or "constraint violation" or "query timeout." The mocked tests: provided false confidence. The team deployed: untested SQL. The production bug was: a JOIN on a column that was renamed in a migration. The mock did not know about the migration. The real database did.

The rule this article provides: when to mock (the database is not what you are testing — you are testing logic that happens to call the database) vs when to use a real test database (the database interaction IS what you are testing — you are testing whether the SQL is correct). One clear decision framework, two AI rules, and practical patterns for test database setup.

When to Mock the Database: Logic Tests

Mock the database when: you are testing business logic that happens to use data from the database, not testing the query itself. Example: a function that takes a list of users and determines which ones need a reminder email. The function: receives users, applies business logic (last_login > 30 days ago, has_notification_preference = true), returns the filtered list. The test: should mock the database to provide test users, then verify the filtering logic. The database query is: not the subject under test. The business logic is.

Mock rule: "Mock the database for: business logic tests (the function receives data and applies logic), transformation tests (data in → transformed data out), and service layer tests where the repository is a dependency (mock the repository interface, test the service logic). Use vi.fn() or a fake repository that returns predetermined data. The test verifies: the logic applied to the data, not how the data was retrieved."

The mock test pattern: const fakeRepo = { getActiveUsers: vi.fn().mockResolvedValue([testUser1, testUser2]) }; const service = new ReminderService(fakeRepo); const result = await service.getUsersNeedingReminder(); expect(result).toHaveLength(1); expect(result[0].id).toBe(testUser1.id). This test: verifies the reminder logic, independent of the database. If the reminder logic is wrong: this test catches it. If the SQL query is wrong: this test does NOT catch it (by design — that is a different test).

• Mock when: testing business logic that USES data, not testing how data is RETRIEVED
• Subjects: filtering, transformation, business rules, service layer logic
• Mock pattern: fake repository returns predetermined data, test verifies logic on that data
• The mock test catches: logic bugs (wrong filter, wrong calculation, wrong condition)
• The mock test misses: SQL bugs, schema mismatches, constraint violations (by design)

When to Use a Real Test Database: Query Tests

Use a real test database when: you are testing the database interaction itself. Example: a repository function that queries users by role with pagination. The SQL: SELECT * FROM users WHERE role = $1 ORDER BY created_at DESC LIMIT $2 OFFSET $3. What can go wrong: the WHERE clause has a typo (role vs roles column), the ORDER BY uses a non-indexed column (slow at scale), the LIMIT/OFFSET calculation is off-by-one, or a migration changed the column name. A mock: cannot catch any of these. A real database: catches all of them.

Real DB rule: "Use a real test database for: repository tests (verifying SQL correctness), migration tests (verifying schema changes work), constraint tests (verifying unique, foreign key, and NOT NULL constraints), and query performance tests (verifying indexes are used). Seed the database with test data in beforeEach. Clean up in afterEach (truncate tables or use transactions that roll back). The test database should: match production schema exactly."

The real DB test pattern: beforeEach: await db.insert(users).values([testUser1, testUser2, testUser3]). Test: const result = await userRepo.findByRole('admin'); expect(result).toHaveLength(1); expect(result[0].email).toBe(testUser1.email). afterEach: await db.delete(users). This test: verifies that the SQL query returns the correct users by role. If the column name changes: this test fails (the real database does not have the old column). If the query logic is wrong: this test fails (the real database returns incorrect results).

• Real DB when: testing the database interaction itself (SQL correctness, constraints, migrations)
• Catches: column typos, wrong JOINs, constraint violations, missing indexes, migration issues
• Seed in beforeEach, clean in afterEach — each test starts with known state
• Test database: matches production schema exactly (same migrations applied)
• The real test catches: every database bug a mock cannot — the SQL, the schema, the constraints

Test Database Setup Patterns

SQLite in-memory: the fastest option. sqlite.open(':memory:') creates a database in RAM. Apply migrations: run the same migration files against the in-memory DB. Limitations: SQLite SQL syntax differs from Postgres (no JSONB, different date functions, no array types). Use for: Drizzle/Prisma projects where the ORM abstracts SQL differences. Do not use for: projects with Postgres-specific features (JSONB queries, array operations, tsvector search).

Postgres test container: a real Postgres instance running in Docker. testcontainers library: starts a Postgres container before tests, applies migrations, provides the connection URL, and destroys the container after tests. No SQLite compatibility issues: the test database IS Postgres. Limitations: slower startup (2-5 seconds per test suite for container creation). Use for: projects with Postgres-specific features, CI pipelines (Docker is available), and when SQL accuracy matters more than test speed.

Postgres test database: a persistent Postgres instance (local or remote) dedicated to tests. Apply migrations: same as production. Between test suites: truncate all tables (fast cleanup). Between test runs: the database persists (no startup cost). Limitations: requires a running Postgres instance (add to docker-compose.yml for local dev). Use for: the fastest real-Postgres testing (no container startup per suite). The AI rule: "Test database: Postgres test container in CI, local Postgres test DB in development. Apply the same migrations as production. Never SQLite if the project uses Postgres-specific features."

• SQLite in-memory: fastest, no infrastructure. Limited: no JSONB, no arrays, different SQL syntax
• Postgres test container: real Postgres in Docker. Accurate SQL. 2-5s startup per suite
• Postgres persistent test DB: fastest real Postgres. No startup cost. Requires running instance
• Rule: Postgres container in CI, local Postgres in dev. Same migrations as production
• Never SQLite when: JSONB, arrays, tsvector, or any Postgres-specific feature is used

AI Rule Templates for Database Testing

Mock-appropriate rule: "For service layer tests: mock the repository. const mockRepo = { findByRole: vi.fn().mockResolvedValue(testData) }. Test the business logic, not the database. Mock tests: fast (milliseconds), no database setup, test logic in isolation. Use for: business rules, transformations, calculations, filtering logic."

Real-DB rule: "For repository and query tests: use the test database (Postgres). Seed test data in beforeEach: await db.insert(users).values(fixtures). Clean up in afterEach: await db.delete(users). Test: call the repository function, assert the returned data matches expectations. Real DB tests: verify SQL correctness, constraint enforcement, and schema compatibility. Run with: pnpm test:integration (separate from unit tests for speed)."

The combined rule: "Test pyramid: (1) Unit tests (mock DB, test logic): fast, many, run on every save. (2) Integration tests (real DB, test queries): slower, fewer, run in CI and before commit. (3) E2E tests (real everything): slowest, fewest, run in CI before deploy. Each layer: tests different concerns. Unit: logic correctness. Integration: database correctness. E2E: system correctness. Never use one layer for everything — each layer has a specific purpose."

• Mock rule: 'Service tests mock the repo. Test logic, not DB. Fast, no setup needed'
• Real DB rule: 'Repo tests use test Postgres. Seed, test, clean. Verify SQL and constraints'
• Pyramid: unit (mock, fast, many) → integration (real DB, slower, fewer) → E2E (slowest, fewest)
• Separate commands: pnpm test (unit, fast) and pnpm test:integration (real DB, CI)
• Each layer tests different concerns: logic (unit), database (integration), system (E2E)

Mock vs Real DB Summary

Summary of when to mock vs use a real test database.

• Mock: testing logic that uses data. Real DB: testing the database interaction itself
• Mock catches: logic bugs (wrong filter, calculation, condition). Misses: SQL bugs entirely
• Real DB catches: SQL bugs, schema mismatches, constraint violations, migration issues
• Mock: milliseconds, no setup. Real DB: seconds, requires Postgres (container or persistent)
• Never mock what you are testing: mocked DB in a query test = tests the mock, not the query
• SQLite: fast but inaccurate for Postgres features. Postgres container: accurate but slower startup
• Test pyramid: unit (mock, many) + integration (real DB, fewer) + E2E (everything, fewest)
• AI default (mock everything): produces tests that pass 100% while SQL bugs ship to production