Testing database schema migrations

[dawilliams-gpsw]

Hello!

I have run into an issue where dates greater than 9999 have entered my database. I've written a migration to fix the issue, and now I'm in the testing phase. I'm using Swift Package Manager with a testTarget to copy over a test .sqlite file that matches the state of the database prior to the migration. I have a test along the lines of the following:

// given a problem db
let problemDBFile = Bundle.module.url(...)!
let dbPool = try DatabasePool(path: problemDBFile.path)
let expectedDate = Date()
let dbClient = DatabaseClient(dbPool, dateProvider: { expectedDate }) // runs migration, uses date provider to replace the bad date with the date that it's run. dateProvider defaults to Date.init in production

// when we fetch some rows in the db
let foos = try await dbClient.fetchAllFoos()

// then ensure that the foos have the correct date
XCTAssert(foos[0].date.timeIntervalSince1970, expectedDate.timeIntervalSince1970, accuracy: 0.001)

This passes, but only once. The reason it fails after the first time is because the database file seems to be fixed after the first run. Running a clean in Xcode fixes the issue, but again, only for one run.

I'm not sure if this is an SPM bug, but I'm wondering if there's another way to provide an 'in-memory database based on an existing database file' in GRDB. Is that possible? Am I holding it wrong?

Thanks in advance!

[groue]

Hello @dawilliams-gpsw,

Migrations modify their input database. If you want to run your test several times, you need to leave the original database intact, and therefore to migrate a copy of the original.

One way to do it to copy the database file to some temporary location on disk, with FileManager, and run your test on a database connection to the new file.

Another way to do it is to open a connection to the original file, and backup this connection into a temporary in-memory connection:

let url = Bundle.module.url(...)!

// Make an in-memory copy
let dbQueue = try DatabaseQueue()
let originalDbQueue = try DatabaseQueue(path: url.path)
try originalDbQueue.backup(to: dbQueue)

// Proceed with the test
let dbClient = DatabaseClient(dbQueue, ...)

[dawilliams-gpsw]

@groue I thought about making a copy of the file, but the backup method is exactly what I was hoping for! GRDB is such a delight. Thank you for everything!