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.
The General Idea
When applying a database migration you have two types of records:
- ones that where already in the database complying to the old schema
- others that will be added in the future complying to the new schema
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:
- insert old schema record
- run the migration
- insert new schema record
- check if both records are present as expected
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:
- initialize the database with migration 1
- insert record 1
- apply migration 2
- insert record 2
- ...
- apply migration n
- insert record n
- check assertions on all records
Code example (GO)
Now let's have a look on some code examples written in GO
(open gist):
func TestMigrations(t *testing.T) {
connection := dockertest.ConnectToTestPostgreSQLPop(t)
testMigrator := NewTestMigrator(t, connection, "../migrations", "./testdata")
require.NoError(t, testMigrator.Up())
for i := 0; i < 10; i++ {
expect := expectedData(i)
var actual testData
connection.Find(&actual, expect.ID)
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)
Conclusion
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.
Outlook
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.
Continue reading
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.
