Introducing Fire Migrate

A database tool to make migrations robust

I’ve written a few posts about ideas for creating a new database migration tool. One that uses declared dependencies rather than file ordering, and also has post-migration assertions to verify that things actually worked.

During that time, I also wrote a tool called Fire Migrate, documented it, and published it on a personal site. And now, I’m writing a post about it. You can see the tool’s site online at fire-migrate.matthewtolman.com.

Fire Migrate

Fire Migrate is a command-line tool. You call it with options - sometimes those options are stored in files or environment variables - it runs, reports the output, and quits. It’s not a continuous application.

That said, I also tried to make it so that it’s fairly straightforward to use. For instance, if you want to just run some migrations but forgot to provide a database host, then it will just prompt you to enter the database host. Same for any other required information for every command. This allows it to be a lot more forgiving since you can “discover” what is needed by using the tool rather than trying to read and remember the docs (though that is still a great option too).

I also tried to provide a Getting Started guide as well to take you through everything. That guide will go into way more detail than I do here, so give it a read if you like the tool.

Installing Fire Migrate

To get started, you can either build from source or download a binary. For some reason, the software I’m using to host the binaries decides to rename the files, and I haven’t figured out how to stop that yet, and I haven’t gotten setup with a proper file hosting service (once I do, I’ll get my links updated). Until then, you’ll just want to rename the binary you download to something like fire-migrate. After that, just put it on your path (and also make it executable, deal with your operating system’s “this is a program downloaded from the internet” warnings, etc.).

Once it’s setup, run it with fire-migrate --help to test it out.

Using Fire Migrate

To use the tool, we first need to tell it how to connect to a database. There are four options: environment variables, command line options, filling out the prompts each time a command is ran, and a config file. I’m going to use the config file option here. For the rest, just run the help command to get the docs.

To make a file, simply run fire-migrate gen -w conn. This will prompt you for the connection info and save it to a TOML file. After that, we just pass in the file path with --conf (e.g. --conf db.toml).

Now we need a migration to run. To create a migration, run fire-migrate --conf <conf>.toml gen -w mig --migs migrations. The connection file will be used to determine which dialect to use for the generated files (PostgreSQL or SQLite). This will then create a “migrations” directory. Inside that directory, you’ll find another TOML file, and three folders (up, down, assert). The TOML file is your migration’s settings file. It determines the name that the migration will be called, what migrations it depends on, and what scripts will be ran (it’s possible for a single migration to use multiple SQL files for each step! This is extremely useful when writing tests for prepared statements so each test can be focused on one edge case or code path).

By default, the up folder holds the up SQL files, the down folder holds the rollback SQL files, and the assert folder holds our database tests. Go ahead and open those folders, and you’ll see a single SQL file. We can edit that file to change the migration steps. For instance, try changing the up SQL file to the following (assuming you use PostgreSQL):

CREATE TABLE IF NOT EXISTS favorite_foods ( col TEXT );

Then, change the down SQL file to the following:

DROP TABLE IF EXISTS favorite_foods;

Finally, update the assert SQL file to the following:

DO $$
BEGIN
	INSERT INTO favorite_foods (col) VALUES ('test');

	IF NOT EXISTS (SELECT 1 FROM favorite_foods) THEN
		RAISE EXCEPTION 'INSERTION FAILED';
	END IF;
END;
$$

We now have a full migration with rollbacks and assertions!

Let’s give it a go! Run fire-migrate --conf <conf>.toml migrate --migs migrations.

Auditing the Database

We just migrated the database, which is great. But, we didn’t commit the file to version control. What if we lose the file, or it gets changed, or we had a bug so our rollback script didn’t properly clean up locally? Not to worry, fire actually keeps track of an audit log of every command it runs, and it stores that log inside the database rather than on a file system. That way, anyone who has database access rights can audit what happened. We can also see what migrations were ran when, session information (basically all migrations ran/rolled back with a single CLI run), whether the migration was rolled back, a list of current tables, when migration rans failed, and more. All of this can be seen with one command: fire-migrate --conf <conf>.toml info.

Take a minute to admire how much information about your database you just got. It’s a very detailed audit log.

Now run it with fire-migrate --conf <conf>.toml info --show-sessions. You only get the sessions data now. Every section of the full info display has a “show” flag, so you can limit the output to only what you care about. Multiple show flags are allowed as well.

Projects

I haven’t done a proper post on the projects idea (yet). Basically, it’s a way to manage migrations across multiple databases at once. It’s generally not a problem for most small projects. However, I’ve had to work with some pretty complex interactions where a single API essentially had multiple databases. Sometimes they were different swimlanes or tenant partitions, which meant they all had to be kept in sync with the same migrations folder. Other times the databases were separate for compliance or privacy reasons (e.g. one server with PII/encrypted data, another with analytics data filled with opaque ids). In those cases, we had separate migrations, but often changes needed to be rolled out to both systems.

Projects is meant for this very specific use-case. You create your migrations, database connection files, etcetera, and then you create a “project” file which wires those up together. You tell it which servers use which connection file and migrations folder, what target that server should migrate to (or leave it empty for migrating everything), any settings for different migration folders (which allows you to have servers with different database engines), etc.

When working with projects, anything you do (generate migrations, run migrations, etc.) goes through the project sub commend. So, to create a new server, you would run fire-migrate project gen -w server. To create a new migration, it would be fire-migrate project gen -w mig.

Running migrations is a little different too. When working across databases, it’s not strictly “up” or “down.” Some servers may be migrating forward, others may be rolling back. Instead, we “resolve” the server to the target described in the file. Fire will then inspect each server, figure out where it’s at, and then either do a rollback or migration depending on both that server’s state and it’s desired target state.

The automatic “rollback” or “migrate forward” is very useful for CI/CD systems. If a database migration introduced a problem, we can do a revert commit to move the project back to the old target, and then treat that revert commit the same as a normal commit. We don’t need a separate “revert” CI/CD path, or a separate “undeploy” pipeline. Reverts are treated as normal commits, and fire takes care of the rest. It resolves the database to the desired state. This is also why the project file allows specifying the target in the file. It’s for easy integration with a CI/CD pipeline.

One other benefit to this is when you have different release schedules for databases. For instance, tenants on shared database instances may get releases early and fast. While tenants on dedicated instances may get releases slower. Or perhaps a feature is rolling out to one region first before it rolls out elsewhere. By having the target for each database documented in version control and enforced by the migration tool, you can more easily manage these multi-version multiple-database environments.

To run a project resolution, simply run fire-migrate project resolve.

Wrap Up

I’m really excited to share fire migrate with you all! You can find the project hosted on GitLab. If you have questions, reach out to me in the comments or on substack.

At some point, I’ll probably take a look at what it takes to get published to a registry like Homebrew. I also know that some Go-based tools allow installing through the go CLI command, so maybe I’ll check that out.

Also, let me know if you have a better file hosting service that you use which doesn’t mangle file names. Or let me know if you know of some good resources about publishing to some of the popular package repositories, like Homebrew, Linux repositories, flatpak/snap, etc. I’d love to get something better going for distribution.