Towards a Better SQL Migration
Because the current tools don't cut it.
I’ve used a lot of SQL migration tools. They almost all work the same way:
Grab migration files in a list sorted by filename1
Either use some DSL to generate the “CREATE TABLE” command or run the raw SQL directly
Use hashes to verify integrity. If a file ever changes (or if the file order ever changes) break the entire migration pipeline
Rollbacks are optional (and often not present)
No form of database integrity checks - that’s left to your app’s integration tests
Assuming you have them, and they work, and they don’t use a mock/in-memory database alternative
Unfortunately, this is a terrible way of doing things. Here’s the problems I’ve ran into:
Files are sorted by filename. Which means that we all have to agree on a way to sort migration scripts by the order they should run instead of name them by what they do.
Keeping files in proper order to avoid altering a table that hasn’t been created yet sucks.
To keep files sorted, filenames are often prefixed with a timestamp of when the file was created - usually using the timestamp of the machine the file was generated on. This breaks down whenever you have a team working across timezones because the times on the machines running it is vastly different.
UTC timestamps are sometimes used, which helps since it removes the “local machine” conflict. However, UTC timestamps (especially in the common unix timestamp format) are long and hard to compare quickly when resolving merge conflicts.
Version numbers can be used instead. But this also sucks because now you have to manage an auto-incrementing number when multiple people are trying to create scripts simultaneously.
Also, people are creating scripts simultaneously, and PRs move through review at different speeds! This means that Fred could make a PR with version 50 on May 9th, Joe could make a PR with version 51 on May 10th, Joe’s PR gets merged on May 11th, a deploy happens on May 12th, and then Fred’s PR gets merged May 13th. This means that version 50 was migrated after version 51 on production servers (assuming your migration tool doesn’t freak out and crash), but whenver you rebuild a server (e.g. a new test environment, a dev box, etc.) then version 51 will run before version 50.
Some tools will just crash when this happens, which is very frustrating.
Also, if it does introduce a bug, you can’t “just fix it” because of all the hash verification. On production servers, version 50 will always come after version 51, and you can’t change that because it’s locked in.
This ends up meaning that you must check and double check that the migration file name is going to end up in the right order before merging a PR. And if you have to rename a migration file, then in almost every PR system I’ve seen, that means your approval is invalidated and you need to get another review.
Rollbacks often don’t work or don’t exist. Which sucks, because now roll-forwards have to be written defensively to be robust.
Which also, a lot of people don’t do. And fixing a bad script because production had a bad deploy breaks so much stuff locally. Often, when that happens, somebody just logs into prod and runs some magic commands that never get documented anywhere and don’t make it into the migration scripts because that would break everyone!
There’s no way for database tools to know if they did things right. Or if things work. Just hope and pray I guess?
There’s no way to easily bypass hashes, which makes so many things a pain
Tools don’t understand dependencies between files, which is why they rely so much on ordering. But, this also means that you can’t rebuild a section of your database or rollback a specific bad script without rolling back or rebuilding a lot of other things that you don’t want to touch! This is very problematic when an experimental, not yet publicly released feature causes database issues, but you can’t just fix it because rollbacks or rebuilding the table would require rebuilding/rolling back a lot of other scripts that impact tables which customers do use. Queue “do something super hacky in production database environments to try to bypass the limitations of really bad tooling to fix a problem that isn’t impacting customers but a proper fix would impact customers.”
Also, because everything is so flaky and finicky and there are no checks or easy ways of doing things, nobody rebuilds their local database - even after working on a migration script. So migrations aren’t well tested, and the developer almost certainly ran a command which didn’t make it in, so half the time running the migrations from the beginning doesn’t even work!
Also, there isn’t an audit log of migration changes ran, so if you ever lose commit history or an AI deletes a codebase, you’re more than a little screwed.
If you haven’t noticed, I’ve had to deal with a lot of migration scripts. And the tools I have used professionally all suck - even the paid ones.
Generally, the “premium features” or “selling points” are things that don’t matter. Like “generating SQL Server and PostgreSQL CREATE TABLE commands.” I’m sorry, that’s bloat. Sure, it’s cool. Some people who don’t bother learning SQL will like it. But I find those features to be infuriating and fickle2. Either they’re feature is severely lacking because it doesn’t support functionality that isn’t present in every SQL implementation (e.g. stored procedures, granting access, foreign keys, etc.), it has features which can’t be trivially undone which then breaks rollbacks because now there are “undoable chanes” (e.g. updating a stored procedure) or requires you to write some SQL, or it has a ton of SQL engine-specific options and flags which then means you can’t actually run it against multiple engines so you didn’t save anything!
Building Something Better
Okay, ranting is one thing. Fixing stuff is another. So, let me introduce “rove migrate” - my tool that tries to address a lot of these issues. This tool is pre-alpha. Which means this post is less of “here’s a premium product” and more “this is a developer who’s solo-making a tool to solve a problem they’re constantly facing.” Anyway, let’s talk about the tool.
My goals are simply as follows:
Never rely on file ordering for knowing how to run a migration. Instead, each migration declares a dependency list, and the tool figures out what has ran in production, what hasn’t, and then executes a smart plan.
Developers do need to accurately declare dependencies, but this should hopefully reduce merge issues because now the ordering is based on dependency chains and not file ordering on disk.
This also allows us to migrate (or rollback) the minimal changeset needed for a specific commit, rather than “everything that followed” - a huge win for working on non-public and/or experimental features.
Have the ability to run database tests written in SQL after a migration runs.
This is critical when ordering is based around dependencies - we want to assert developers didn’t miss something!
Also, tests should not leave any trace in the database (aka. automatic rollback)
This allows inserting/deleting/updating records as part of tests without breaking the final production code.
Have the ability to run migrations in different orders (or to skip some migrations) to check that migrations are actually robust. Including doing random rollbacks. And have this be something that can run in a CI/CD pipeline
Which is enhanced by the above point of automated SQL tests!
Have a plugin system to let the community extend the tooling
Also, try to have the plugin system be based around stdin/stdout protocols rather than shared libraries or scripting or something. That way, the community can write the plugins with whatever tools/languages they want.
Have an audit log of every migration command ran against the database, and every session that tracks which migration was ran and whether or not it was a rollback.
Tests may be an exception since they are automatically rolled back. However, any lasting change (create table, etc) should be saved in the database - that way you can lose your code and still recover the exact steps on how your database got to where it is.
While not a current goal, it would also be really cool to rebuild migration scripts from the database audit log.
Allow easily fixing the database state by modifying values in a single table, and have the tool tell you which values to modify and how.
This means that we can “invalidate” a hash if we did do an update, rerun a script by changing the status, etc.
It also means we can be really strict in our integrity checks and stop the system if anything looks out of place, prompt the user to go double check things are in good shape, and then let the user signal through the database that things are okay - which lets us handle the CI/CD use case.
Have migration generation commands which promote rollbacks and verifications.
Tools to view the current database state from the command line
A single executable is all I distribute for the base tool. Plugins should be easy to install as well (just unpack it in a specific folder)
Easy to use the CLI tool.
Do NOT have a “master file” with all the migrations. That causes merge conflicts.
Non-goals include the following:
Code generation of scripts. I only really want to support raw SQL3
Built-in support for every database driver under the sun4. That’s where I want plugins to come in
Compatibility with existing tools.
Current State
Well, those are my goals. That’s not where I’m at. I got a lot of progress made on the audit logs, I have dependencies be declared, I can generate script file templates, I can do assertions, and I have the starts of a database driver plugin system (but it’s not robust or complete). And it’s written in Go so I can cross-compile and distribute a single executable.
However, I have a long ways to go.
For one, I need to cleanup some features that I don’t like. I need to simplify a lot of things as well, like file options. Also, I just started adding in some interactive usage through a TUI. But, that’s only with one command. Most commands require a lot of flags/config files up-front, which is a bit of a pain since I don’t have things that well documented.
Also, I don’t have a generic plugin system either. My database state commands need some work as well, some of my error messages need rewording, and I need to put in a lot of work for in-tool discovery.
That said, things are in an okayish state. I’m using it for side projects pretty heavily, and so far it’s working great.
If you want to check it out, I don’t currently have “releases” or executables that I distribute. Everything is on Gitlab, and you just download it, build it with Go, and then add the executable to your path. Updates are sporadic. Again, this is pre-alpha.
I’m mostly posting this for myself since I had to use another migration tool the other week, and it drove me a little crazy. Getting back to my custom tool is really nice.
Though, there was one I used which instead had it sorted by the order it was written to an XML file. Which caused a lot of version control collisions since it was one XML file for the entire monolithic codebase used by the entire company.
I also rarely see those “code generation” features never get used, and most places I’ve worked at people just write raw SQL files instead. Great use of a “premium feature.”
My current code does have limited generation of tables, but I’m planning on removing those. I don’t want them
Or even most databases. I only want to support the engines I use - Postgres and some SQLite