database-testing

name	database-testing
description	Expert guidance on testing persistence layers (Room, SQLite, SQLDelight, Core Data, Drift, WatermelonDB) using in-memory databases and migration tests. Use when asked to test a DAO, a query, or a schema migration.

Database Testing

Instructions

Database tests are the second-most-valuable layer after unit tests. They catch the bugs unit tests cannot see: wrong SQL, broken migrations, missing indices, and type-mapping errors. Keep them fast and hermetic by running against in-memory databases whenever the driver supports it.

1. Pick the Right Harness per Stack

• Android + Room — Room.inMemoryDatabaseBuilder(...) on the host via Robolectric, or on-device with androidx.test.ext.junit. Prefer JVM + Robolectric for iteration speed.
• Android + SQLDelight — JdbcSqliteDriver(JdbcSqliteDriver.IN_MEMORY) on JVM, no emulator required.
• Kotlin Multiplatform (SQLDelight) — use the JVM driver for commonTest; native driver only for target-specific edge cases.
• iOS + SQLite / GRDB — in-memory DatabaseQueue (DatabaseQueue() without a path).
• iOS + Core Data — NSPersistentStoreDescription with NSInMemoryStoreType in a dedicated test container.
• Flutter + Drift — NativeDatabase.memory() or DriftIsolate with a memory executor.
• Flutter + sqflite — databaseFactoryFfi with inMemoryDatabasePath and sqflite_common_ffi.
• React Native + WatermelonDB — LokiJSAdapter with useWebWorker: false and dbName unique per test.

2. DAO Test — Android Room (JVM via Robolectric)

@RunWith(AndroidJUnit4::class)
@Config(manifest = Config.NONE)
class OrderDaoTest {
    private lateinit var db: AppDatabase
    private lateinit var dao: OrderDao

    @Before fun setUp() {
        db  = Room.inMemoryDatabaseBuilder(
            ApplicationProvider.getApplicationContext(), AppDatabase::class.java
        ).allowMainThreadQueries().build()
        dao = db.orderDao()
    }

    @After fun tearDown() = db.close()

    @Test fun insert_and_load() = runTest {
        dao.insert(OrderEntity(id = 1, total = 1999))
        assertEquals(1999, dao.load(id = 1)!!.total)
    }
}

3. DAO Test — iOS + GRDB

final class OrderDaoTests: XCTestCase {
    var db: DatabaseQueue!

    override func setUp() async throws {
        db = try DatabaseQueue() // in-memory
        try await AppSchema.migrator.migrate(db)
    }

    func test_insertAndLoad() async throws {
        let dao = OrderDao(db: db)
        try await dao.insert(Order(id: 1, totalCents: 1999))
        let loaded = try await dao.load(id: 1)
        XCTAssertEqual(loaded?.totalCents, 1999)
    }
}

4. DAO Test — Flutter + Drift

void main() {
  late AppDb db;

  setUp(() => db = AppDb(NativeDatabase.memory()));
  tearDown(() async => db.close());

  test('insert and load order', () async {
    await db.into(db.orders).insert(OrdersCompanion.insert(id: 1, totalCents: 1999));
    final row = await (db.select(db.orders)..where((t) => t.id.equals(1))).getSingle();
    expect(row.totalCents, 1999);
  });
}

5. DAO Test — React Native + WatermelonDB

import { Database } from '@nozbe/watermelondb';
import LokiJSAdapter from '@nozbe/watermelondb/adapters/lokijs';

const makeDb = () => new Database({
  adapter: new LokiJSAdapter({ schema, useWebWorker: false, useIncrementalIndexedDB: false, dbName: `test_${Math.random()}` }),
  modelClasses: [Order],
});

test('insert and load order', async () => {
  const db = makeDb();
  await db.write(async () => {
    await db.get<Order>('orders').create((o) => { o.orderId = 1; o.totalCents = 1999; });
  });
  const loaded = await db.get<Order>('orders').query().fetch();
  expect(loaded[0].totalCents).toBe(1999);
});

6. Migration Tests

Migrations are where database tests earn their keep.

Room: MigrationTestHelper creates a DB at version N, runs the migration, and asserts the resulting schema is identical to the auto-generated N+1 schema (schemas/ directory with exportSchema = true).

Core Data: build a .sqlite seeded with old data, apply the mapping model, assert the new entities contain the expected data.

SQLDelight / Drift: ship N.sqm / migration strategies; write tests that open the DB at the old schema, insert representative rows, run schemaVersion-aware migration, and re-read.

Every destructive migration must have at least one test that seeds realistic old-shape rows and verifies no data loss where none is intended.

7. Transactions and Concurrency

• Test that a failed step rolls back: wrap two inserts in a transaction, have the second throw, assert the first is absent.
• Test that a query running concurrently with a write does not see partial state (where your driver guarantees it).

8. What to Avoid

• Shared singleton DB across tests — flakiness guaranteed. Always build and tear down per test.
• Writing to the user's real app directory from tests. Use memory or a per-test temp directory.
• Asserting on SQL strings. Assert on resulting rows.

9. Checklist

• DB is fresh per test (in-memory or temp file, closed in teardown).
• Every migration has a test with realistic seeded data.
• Schema is exported and diffed (exportSchema = true for Room; equivalent for SQLDelight / Drift).
• Transactions are covered: both commit and rollback paths.
• No test depends on the execution order of another.
• DAO tests run on the host JVM / host Swift / Dart VM / Node — not on emulator — when the driver allows.