Module 61: Database Integration
Testing with a real database
🏠The everyday picture
Rehearsing a play on the actual stage, with the real lights and props, before opening night, rather than only in a practice room.
📘What it really is
-
Unit tests with in-memory repositories (Module 46) stay fast. Add integration tests that run repositories and API routes against a real PostgreSQL, because SQL, constraints, transactions and RLS can only be checked there.
-
Ways to get a test database:
- a separate database on your local server (
crm_test), reset before each run; - Docker or Testcontainers, which starts a throwaway PostgreSQL for the tests automatically;
-
a branch database (Neon offers database branches).
-
Before tests: run migrations, insert known test data. Between tests: wrap each test in a transaction rolled back at the end, or truncate tables.
-
Test what only the database can enforce:
- constraints (duplicate email refused, negative amount refused);
- foreign-key delete rules;
- transactions rolling back on failure;
- RLS isolation between organisations;
-
that indexes exist for important queries (query plans in a test, for critical paths).
-
Never run tests against production. Use separate credentials so it's impossible by accident.
🖱️Try it
Set up a crm_test database and an npm script that runs migrations and integration tests against it. Write tests for: a
duplicate email being refused; deleting a customer with orders being refused; an order transaction rolling back when stock is
insufficient; and organisation isolation under RLS.
✅Quick check
Why aren't in-memory repository tests enough?
Show the answer
They can't check real SQL, constraints, transactions or RLS; only a real database can.
How do you keep tests from affecting each other's data?
Show the answer
Reset data before runs and roll back or truncate between tests.