Application and Database Integration Tests

Test through stable interfaces

Start an isolated database at the supported engine/version, apply the same migrations the application uses, and connect through the real driver and repository layer. At the API boundary, send a request and assert status, response body, and persisted database state.

1. Provision a clean test database and load deterministic fixtures.
2. Call the application service, repository, or API with realistic input.
3. Assert observable response and database state.
4. Test invalid input, missing rows, constraint errors, and duplicate/retried requests.
5. Clean up only records owned by the test.

Example test scenario

For an order API, a test can create an order, read it back, and verify that a second request with the same idempotency key does not create a duplicate. Assert database rows as well as the HTTP response; a successful response alone may hide an unintended write.

Given a test customer with no orders
When the API creates an order with idempotency key "test-run-42"
Then the response contains the new order ID
And exactly one order exists for that test key
When the same request is retried
Then the same order ID is returned
And the order count remains one

This is a test specification, not SQL or executable code. Implement it with your application's test framework and isolated test fixtures.

Replace external dependencies carefully

Payment, email, and message brokers may be stubbed for a focused database integration test, but the database itself should be real for behaviors that depend on its SQL dialect, constraints, transactions, or locking. Add separate end-to-end tests for critical external integrations.

Diagnose flaky integration tests

• Use unique records and stable keys; avoid shared mutable fixtures.
• Wait for explicit readiness rather than fixed sleeps.
• Pin the engine image/version and initialize schema deterministically.
• Ensure each test owns its transaction/connection lifecycle.
• Capture sanitized database and application logs for failed runs.
• Test concurrency deliberately instead of relying on test execution order.