Isolate database tests

Database tests become flaky when they share rows, depend on an earlier migration test, or leave transactions open.

Create a temporary SQLite database per test or suite, apply migrations from the same files used in production, seed only required data, and close and remove it during cleanup. A transaction rollback strategy is useful only when the code under test shares that transaction.

Isolation means each test can run first, last, alone, or in parallel and produce the same result. Generate a unique temporary path before opening SQLite, register cleanup immediately, and apply the real migration files. Creating tables with test-only SQL can hide a broken production migration.

A transaction around each test is fast, but it has a boundary. If application code opens another connection, commits independently, or runs work after the request returns, the outer rollback may not contain those writes. A fresh database is slower but gives stronger isolation and catches connection behavior. Choose based on what the test must prove.

Seed only the rows named by the scenario. Reusing a large fixture makes uniqueness and ordering bugs hard to diagnose. Avoid fixed IDs across parallel tests unless every test has its own database. Close statements and connections before deleting a temporary file, and make cleanup run even when setup or an assertion fails.

A CRUD sequence proves useful integration only if it checks persisted state. Reading the object returned by insert() can pass without a write. Create it through one call, then read it through a separate repository or HTTP call that queries the database.

Run the complete Books API CRUD sequence against a fresh temporary database. Repeat it twice in parallel with different titles and prove neither test can see the other test’s rows.