Set up your test database from migrations

No matter what language or framework you use, as long as your stack includes a featureful ORM, I’m willing to bet there is a call to db.create_all(), or an analogue, somewhere in test setup code.

Here I’m shaking hands of anyone who read featureful as fearful. You’re onto something.

In this article I’ll try to make the point that in software used in production, a call like that has no place anywhere in the codebase, including test setup.

Note

A counterpoint: some people claim that tools like Alembic are polished enough these days to be trusted with keeping the DB schema in sync with the models.

What do we mean by create_all?

Per Flask-SQLAlchemy documentation:

To create the initial database, just import the db object from an interactive Python shell and run the SQLAlchemy.create_all() method to create the tables and database:

Upon calling create_all, table information is taken from the ORM metadata, which describes all tables Flask-SQLAlchemy was able to discover. SQLAlchemy performs a topological sort on the table dependency graph and creates tables in order. This works well - well enough to create a full database schema matching the models exactly.

Analogues exist in other ORMs.

What’s the problem with create_all?

create_all works well. You get exactly what you asked for. Unlike… migrations. Migrations are incredibly error-prone and painful to test manually. It’s a safe bet that in any project that saw a couple of years of moderately frequent schema changes, the schema as created from the models will diverge from the schema present in the production database - in ways you won’t notice easily [1]. The sort of divergences that sneak in is a silent kind - missing constraints, missing or superfluous indexes, subtle type difference, or even indexes that are made use of in production but not present in the models.

Suffer early, suffer less

There is no perfect solution for writing correct migrations. However, there exists a perfect solution for the production schema diverging from the one declared in the code. You only have to stop making use of create_all and set up all development and testing databases from migrations - it’s as simple as that. How does it look like? Instead of invoking the ORM DDL magic, your test setup code has to call the migration machinery.

In Flask+SQLAlchemy and Alembic it’s going to look like:

from alembic.command import upgrade
from appname import app, get_alembic_config

config = get_alembic_config()  # Obtain Alembic config - this depends on your specific setup.
with app.app_context():
    upgrade(config, revision)

This way, your tests and your development environment will always see the same schema you’re about to ship to production. If that doesn’t sound like a good sell, I don’t know what does.

Is it slow? Not really. I’ve actually observed the opposite - db.create_all taking more time than running a number of migrations on an empty database. Moreover, if your migration history is too long, you should be able to regenerate it. To retain the last N migrations, you first go N+1 migrations back, dump the schema, and save it in the new init migration, removing everything before. You then stitch the N retained migrations to the new init migration by editing migration files. In Alembic, all it takes is setting the ID of the new init migration as the down_revision of the oldest retained migration.

If speed is still a problem, in Postgres you can create a template database, which you can later use to quickly create clones thereof. Also remember to set fsync = off in postgresql.conf - in testing only.

Problems remaining unsolved

Creating dev and test databases from migrations doesn’t guarantee the created schema conforms to the models. What’s guaranteed is that if it diverges, it diverges exactly the same way in development and testing as in production. Then, the better your tests are, the more issues you can catch.

No attention has been given so far to testing downwards migrations. These you can validate in a special test that upon creating the database, populates it, and applies downwards and upwards migrations. If you’re there, you may use this opportunity to add a test that verifies that the migration history doesn’t have branches - migrations are hard enough already when they are completely linear.

When is it OK to use create_all?

As usual, there are some exceptions. They could include:

  • For generating the initial schema the initial migration is generated from.

  • Pre-production software, where it doesn’t even make sense to write migrations at all.

  • Software without migrations (persistent log architecture).

Conclusion

In normal database-driven backends, you should use always migrations to set up a database, no matter what purpose.

There should be tests checking migrations themselves.