# Use a Real Database Test When SQL Behavior Is the Feature > Choose mocks, containers, or shared databases by the persistence risk: constraints, transactions, query plans, migrations, and extensions. Canonical URL: https://www.devobs.io/articles/real-database-integration-tests/ By: Samira Haddad Published: 2023-05-03T03:53:41.355Z Updated: 2026-09-05 Section: Comparisons Use a real instance of the production database whenever correctness depends on SQL semantics. Mocks remain useful for testing application branching and rare repository errors, but they cannot prove constraints, isolation, locking, query syntax, extensions, migration order, or planner behavior. Choose the smallest realistic boundary that can expose the risk under test. ## Match the test double to the question A fake repository is fast and deterministic. It can answer “does the service reject an empty title before saving?” or “does this error reach the caller?” It should implement an application interface, not pretend to be PostgreSQL. The more SQL behavior it imitates, the more likely it becomes a second, incorrect database implementation. An in-memory database can test its own dialect and transaction model. It is insufficient when production uses different null ordering, JSON operators, collations, generated columns, indexes, foreign-key timing, or isolation. A vendor emulator is stronger when the vendor specifies its compatibility, but verify every feature you depend on. Do not infer equivalence from a shared query language. PostgreSQL's [transaction isolation documentation](https://www.postgresql.org/docs/current/transaction-iso.html) details phenomena permitted at each level and PostgreSQL-specific behavior such as Read Committed snapshots per command. An in-memory map cannot expose a write skew, serialization failure, or lock wait caused by concurrent database transactions. Write integration tests with two independent connections and controlled barriers for those risks. ## Use disposable real instances for database contracts A containerized database gives each test suite or worker an isolated server with the production major version, extensions, configuration, and migrations. The official [Testcontainers for Java guide](https://testcontainers.com/guides/getting-started-with-testcontainers-for-java/) demonstrates starting a PostgreSQL container for tests and connecting application code to it. The same pattern exists in several language ecosystems. Pin the image deliberately and update it through normal dependency review. Run the actual migration chain from an empty database, then test an upgrade from a representative previous schema. Seed data that triggers uniqueness, foreign keys, checks, null handling, and the largest risky query shapes. A test that creates tables through ORM metadata can pass while the shipped migration fails. Real does not automatically mean representative. Tiny fixtures will not reveal a missing index or a plan that changes with skewed statistics. Use `EXPLAIN` assertions sparingly around essential access paths and test with realistic distributions. Avoid asserting volatile costs or exact plan text; check properties such as an index being usable and a query staying within a measured budget in a controlled environment. ## Keep isolation and speed deliberate Create one database per test worker or one schema per test when the engine and connection pool support it. Roll back a transaction after each test only if application code does not open independent connections or commit internally. Otherwise truncate in dependency order or recreate the disposable instance. Shared long-lived databases invite order dependence, leaked rows, and tests that pass only when run alone. Keep a small unit-test layer around business logic, a focused real-database suite around repository behavior, and fewer end-to-end flows. Do not move every test into containers. A pricing calculation with no persistence behavior gains nothing from a database startup. Conversely, a repository test that replaces the database with mocks has removed the subject it claims to test. ## Exercise failure semantics Test duplicate inserts, concurrent updates, deadlocks or serialization retries where expected, statement cancellation, connection loss, migration interruption, extension absence, and rollback after a partial application failure. Assert stored rows and constraints through an independent query. Capture server logs for failures without leaking credentials. The next step is to identify the persistence bug that would be most expensive to repeat and reproduce it against a disposable production-version database. Review by 2026-12-05 or when the database major version or migration tool changes. ## Source references - -