Continuing my thought from yesterday, I think I realized what I was aiming for. I need a strategy to handle rollbacks for migrations. I woke up this morning and realized something that might work.

Database migrations should follow Semantic Versioning (SV).

SV describes versioning software using the MAJOR.MINOR.PATCH scheme:

  1. MAJOR version when you make incompatible API changes,
  2. MINOR version when you add functionality in a backwards-compatible manner, and
  3. PATCH version when you make backwards-compatible bug fixes.

If database migrations used semantic versioning, deployments and rollbacks have can following the versioning scheme to determine if migrations should roll back (migrate down).

... sips coffee ...

Let me play this idea out.

Let's assume I have an app. Bundled with the app code, I have several migrations that I magically versioned using SV. My database stores this version in a table as well (no different than what alembic does). Let's also assume I have a deployment pipeline that migrates (migrate up) my database before my code is deployed onto my web servers. Finally, let's assume I have a rollback pipeline that deploys a previous version of my web servers before migrating (migrate down) database. For the remainder of the examples, I focus only on the semantic version of the migrations and assume that I only have a currently deployed version of the app code and a to-be-deployed version of the app code. Also, this is my post, so I can do what I want. Hmph!

MAJOR Version Bump

Let's start with a MAJOR version bump of migrations. My database currently as v1.2.0 recorded as the current version of migrations. I trigger the pipeline, my migrations run and updates the recorded version to v2.0.0. Then my code is deployed to my app server.

At this point, life is good. ... sips coffee ...

Now let's see how migration rollbacks are handled with SV.

I notice that my app code has a major bug and causing an incident. My immediate reaction is to rollback the deployed code. The rollback pipeline uses Semantic Versioning to figure out if a rollback should be performed or not.

Therefore, the first action the rollback pipeline executes deploying the previous version of the codebase. The second action, rolling back a migration, isn't as simple. The second action checks the current version of the database – v2.0.0. The second action notices that v2.0.0 is the first major version of the v2 series of migrations. Therefore the rollback pipeline stops execution because major version bumps are not backwards compatible. Done!

MINOR Version Bump

Now I look at what happens when we look at minor version bumps. I use the same scenario above – the database has a recorded version of v1.2.0. This time, trigger my pipeline will bump the migration version to v1.3.0 – a minor version bump.

At this point, life is good. ... sips coffee ...

Now I break down the rollback pipeline.

Triggering the rollback pipeline starts the first action. The pipeline deploys the previous version of the app code. Now the second action is to rollback the migration. Since minor version bumps are backwards compatible with the previous version, the pipeline performs the down migration!


Now I have the previous version of the code with the previous version of the database.

PATCH Version Bump

Luckily, the deploy and rollback pipeline scenario for a PATCH version bump is identical to the MINOR Version Bump.

... sips coffee ...

This Might Work ...

I actually think this might work.

But what do MAJOR, MINOR, and PATCH migrations look like?

Fuuuuuuug... I'm out of coffee.