Use an in-memory database in tests

Open :memory: instead of a file path. The database exists only for that connection and disappears when it closes.

That gives a test fast setup and isolation without temporary files:

const db = openDatabase(':memory:')
runMigrations(db)
seedMinimumData(db)

Create a fresh database for each test, or reset it deliberately between tests. Sharing one mutable database across a suite lets test order affect the result: one test can leave a row that makes the next test pass or fail.

Run the application’s real migrations at setup rather than recreating tables with a test-only SQL string. This verifies that a new installation can reach the schema the application expects. If migration 7 forgets a column, an in-memory test should expose the mismatch.

An in-memory database belongs to a connection. Opening a second :memory: connection normally creates a different empty database. This surprises applications that use connection pools: the migration can run on one connection while the query runs on another. Use a single connection for the test, use SQLite’s shared-memory URI deliberately, or use a temporary file when the production code requires multiple connections.

In-memory tests are useful, but they are not a perfect production simulation. They do not reproduce file permissions, disk-full failures, backup behavior, or the exact locking interactions of several processes. Keep a smaller integration-test layer that opens a real temporary database file for those concerns.

Also enable the same important connection settings as production, such as foreign-key enforcement:

PRAGMA foreign_keys = ON;

Try it: write one test that inserts an invalid foreign key and assert it fails. If it passes, your test connection is not enforcing the same invariant as production.