SQLDelight testing should verify the database behavior that generated types cannot guarantee on their own. The compiler can catch many invalid statements, but it cannot confirm that ordering matches product expectations, custom adapters preserve values or every released migration upgrades real user data safely.
Use a real temporary database
Prefer a temporary file or in-memory database provided by the target driver. Tests should execute the generated query API, not a mocked replacement, because SQL semantics, constraints and transactions are the behavior under test.
Build a query contract suite
- Insert representative rows, including boundary values.
- Verify sort order when values are equal.
- Cover nullability and default values.
- Test update and delete counts.
- Confirm unique, foreign-key and check constraints where enabled.
- Exercise queries inside and outside transactions.
Test custom adapters in both directions
A ColumnAdapter has an encode and decode path. Round-trip tests should insert a Kotlin value, read it back and compare the result. Add malformed stored values when the adapter must handle legacy data defensively.
val expected = true
queries.insertSetting("sync_enabled", expected)
val actual = queries.selectSetting("sync_enabled")
.executeAsOne()
check(actual == expected)Verify migrations from realistic starting points
A fresh schema test is not enough. Save schema snapshots for supported old versions, open each snapshot, apply every later .sqm file and compare the final structure and data. Include rows that exercise renamed columns, defaults and constraints.
Test interrupted and repeated upgrade logic
Application startup code should not reapply completed migrations. Test the schema version before and after an upgrade, then reopen the database to confirm normal queries still work.
Add platform-specific integration tests
Shared tests can validate most query behavior, but drivers may differ in file paths, locking, threading and storage APIs. Run a smaller integration suite on Android, iOS, JVM, desktop or web targets used by the product.
Keep fixtures readable
Use small data builders with names that explain the scenario. Large anonymous data dumps make SQL failures difficult to diagnose. One failing assertion should point reviewers toward the affected query or migration step.
Release gate
Block dependency or schema upgrades until query contracts, adapter round trips, old-version migrations and platform smoke tests pass in the same toolchain used for production builds.