Grdb.swift: Unit testing creating tables and overall db migrations

[SebastianOsinski]

Hi @groue! Firstly, thank you for you hard work! GRDB is a marvelous library!
I have a question regarding unit testing creation of tables and migrations. I want to write tests for my migrations, to check if tables are created correctly, have correct names etc. to ensure they stay correct. Is there some recommended way of doing that? I found DB introspection methods: https://github.com/groue/GRDB.swift#database-schema-introspection. Are they intended for this purpose? I couldn't find anything else remotely related to testing migrations and table creation. Or maybe there is no point in testing that?

[groue]

Hello @SebastianOsinski,

Thanks for having looked at the available tooling before asking.

Testing is not quite an expertise area of mine, but as support for inspiration, there are sample tests in the demo app of RxGRDB:

• AppDatabaseTests.swift: tests for the schema (I'm not sure it is really useful)
• PlayerTests.swift: tests for a record type (tests for basic roundtrip and common requests)
• PlayersTests: tests for a "manager" of records (tests for high-level operations on the database of players)

Interesting bit: note how the demo app is organized so that application and tests can share the same database setup.

Tricky bit: the difficulty is not to test the framework. For example, this test does not test if GRDB knows how to insert records (this is already tested here). It tests that the application has properly defined the Player record so that the insertion method sets its id.

I want to write tests for my migrations

Migrations testing is very useful! This is the opportunity to test that users who upgrade from versions 1, 2, or 3 of your app get the desired behavior when they install version 4.

I export interesting states of the database at different versions of the schema, and embed them as resources of the test target. The tests run migrations on those databases, and check the desired outcomes. The difficult part here is to choose which are those "interesting" states. It can be:

• the barely empty database after the initial app run.
• a very big database that contains a lot of funny user data
• databases that contain known buggy situations that you want to make sure you support (for example, a missing unique index has allowed user databases to, unfortunately, contain duplicate values - how does the app handle this?)
• databases that contain missing values (v2 has introduced a new wantsKitten preference - does the app picks the right default when its value is missing?)
• etc, etc

Some tests that migrate to various versions of the schema deserve to be kept forever. For example, imagine a test that checks that the v1 to v2 migration sets the new preference wantsKitten to true. You don't really want to remove or rewrite it when you ship v3 and v4.

Such a test can be achieved this way:

class SchemaV2Tests: XCTestCase {
    func testWantsKittenIsDefaultedToTrue() throws {
        let dbQueue = try loadDatabase("v1.sqlite")
        try migrator.migrate(dbQueue, upTo: "v2")
        let preferences = try dbQueue.read { db in
            try Preferences.fetchOne(db)
        }
        XCTAssertTrue(preferences.wantsKitten)
    }
}

But there is a problem with the above test, though: it may break whenever the Preferences model becomes unable to load from the v2 schema. Remember that the Swift code that runs your application usually fits the latest database schema - it is likely that it eventually becomes unable to deal with older schemas.

To deal with this, and help your tests run forever, you may prefer to use raw SQL:

class SchemaV2Tests: XCTestCase {
    // Raw SQL never gets old
    func testWantsKittenIsDefaultedToTrue() throws {
        let dbQueue = try loadDatabase("v1.sqlite")
        try migrator.migrate(dbQueue, upTo: "v2")
        let wantsKitten = try dbQueue.read { db in
            try Bool.fetchOne(db, sql: "SELECT wantsKitten FROM preferences")
        }
        XCTAssertTrue(wantsKitten)
    }
}

For convenience, you may prefer to use ad-hoc records:

class SchemaV2Tests: XCTestCase {
    // This record is designed to always match V2 schema
    private struct PreferencesV2: FetchableRecord, TableRecord, Decodable {
        static let databaseTableName = "preferences"
        var wantsKitten: Bool
    }
    func testWantsKittenIsDefaultedToTrue() throws {
        let dbQueue = try loadDatabase("v1.sqlite")
        try migrator.migrate(dbQueue, upTo: "v2")
        let preferences = try dbQueue.read { db in
            try PreferencesV2.fetchOne(db)
        }
        XCTAssertTrue(preferences.wantsKitten)
    }
}

As you see, testing the database is a lot of fun ;-)

[groue]

BTW, thanks for asking about migrations testing! I'm not sure my answer covers the topic fully, but it was the opportunity to talk about techniques I think all GRDB users should know about. Hence the "best practices" label 👍

[SebastianOsinski]

Thank you for such quick response! I really like the idea of creating ad-hoc records for previous schema versions.

I was also a bit worried about my tests testing the framework itself. Thanks for pointing that out :)

[groue]

You're welcome, @SebastianOsinski! Testing the framework, at worst, is spilled energy: that's not very wrong. And sometimes it helps completing holes in the documentation, or making sure our interpretation of the doc is correct. GRDB "tests" many aspects of SQLite, just to make sure some assumptions do hold strong.

Feel free to reopen this issue or another one when you have another question. Happy GRDB!