Database integration tests need realistic constraints, transaction behavior and queries. A substitute engine may differ in relevant semantics.
Before you start
You should understand inputs, expected outputs and the boundaries of the component under test. State the risk a test should detect before choosing a tool or mock. Distinguish controlled dependencies from real integrations so the result does not imply more coverage than the test actually provides.
The practical goal is to reason through this situation: A PostgreSQL-specific query is verified against an isolated PostgreSQL instance. Read the walkthrough first, then try the interview exercise before opening its answer. The important part is explaining the decision and its consequences, rather than remembering a definition alone.
Step-by-step walkthrough
Step 1: Use relevant engine behavior
Queries and constraints must match production semantics.
Step 2: Coordinate the competing writes
Create an actual uniqueness race rather than sequential inserts.
Step 3: Assert storage and error
One row plus a controlled conflict demonstrates both layers.
Worked scenario
A PostgreSQL-specific query is verified against an isolated PostgreSQL instance.
Two requests both pass an availability pre-check and then attempt the same unique value. A fake repository may accept both or serialize them unrealistically. An isolated representative database demonstrates whether the real constraint rejects one and whether the application translates the resulting failure correctly.
Common mistake
An in-memory fake cannot prove actual isolation or index behavior.
Verify the behavior
Test the race against the configured engine and inspect the final stored row count.
Interview exercise
Test a uniqueness race.
Answer and reasoning
Run competing writes and assert one valid record plus the documented conflict outcome under the real constraint.
Continue learning
Compare the scenario with the Software Testing interview questions and test your understanding with the Software Testing MCQs. For terminology and implementation details, consult the reference material.