Learn how to test SQL migrations in any environment. This guide will point out all the considerations we at Ory make when implementing SQL migration tests for our open source products.
How Apple broke "Sign in with Apple" with an unannounced and silent redirect
How do you test your SQL migrations? I am pretty sure you don't. Unless one calls running the migration and checking if something broke a proper test. But how could you possibly test migrations?
Why are database migration tests necessary in the first place? They can be buggy like any other code you write and might introduce regression issues.
Our goal is to develop reliable, hardened, and lightweight services that integrate well with other systems. As such they come with database migrations to set up the database of your choice. The biggest challenge is ensuring people who skip versions when upgrading will still be able to migrate their database.
This guide explains how we at Ory test all the migrations of our products.
When applying a database migration you have two types of records:
Our tests now have to make sure data of any type will work accordingly after applying the migration. So the obvious way to verify that assumption is to insert both types of data and check if they work as expected.
The flow of such a basic test looks like this:
In a scenario where you just have to run one migration this would be possible manually. Our requirements mandate fully automated tests that ensure reliable schema migration across multiple steps.
The test from above can sequentially be run for every migration. Conveniently we can use the post migration record as the next pre migration record. It also makes sense to do all the assertions in the end and thus avoiding the need to keep old data models around. This does not affect the validity of our test because the latest migration always represents the schema a user upgrades to.
As it also is not possible to insert rows before the table is created we are rendered with the following flow:
Now let's have a look on some code examples written in GO (open gist):
func TestMigrations(t *testing.T) {
// start a test container and create the database connection
connection := dockertest.ConnectToTestPostgreSQLPop(t)
// The test migrator searches for testdata SQL files in the ./testdata directory.
// After applying each migration it runs its corresponding testdata file.
// This test migrator highly depends on the way you apply migrations.
testMigrator := NewTestMigrator(t, connection, "../migrations", "./testdata")
require.NoError(t, testMigrator.Up())
// there are 10 migrations
for i := 0; i < 10; i++ {
// get the expected data for the migration
expect := expectedData(i)
// find the actual data
var actual testData
connection.Find(&actual, expect.ID)
// check if the records are equal
assert.Equal(t, expect, actual)
}
}
An SQL migration might look like this:
ALTER TABLE hydra_oauth2_authentication_session ADD remember bool NOT NULL DEFAULT FALSE;
UPDATE hydra_oauth2_authentication_session SET remember=TRUE;
It's corresponding assertions could look like:
assert.True(t, preMigrationSession.Remember)
assert.False(t, postMigrationSession.Remember)
Our approach in testing migrations assures you can upgrade your database to any version. Even when implementing this pattern in a private project with known databases it will not only automate the testing of migrations but helps to prevent regression issues and ensures your migrations follow your data models.
To strengthen the trust in our migrations we might choose to run full stack integration tests using a database of an older schema. As this topic highly depends on your application there is not a single way to approach this. It will not be suitable in every case but is an interesting idea to consider.
You can have a look on Ory Hydra's migration tests to see a very verbose example. If you use github.com/gobuffalo/pop as well you might want to use their TestMigrator as we do. To learn more about how to manage database changes with CI/CD you can find some good advice on hackernoon.