fastmigrateの紹介

Note

TLDR: This post introduces fastmigrate, a Python database migration tool. It focuses on sqlite, and it does not require any particular ORM library. It’s suitable if you want to work directly with sqlite and keep things simple. For instructions, check out the fastmigrate repo.

Let’s talk migrations!

not the migrations we’re talking about

Uh, no. Let’s talk about the database migration pattern.

Migrations represent a powerful architectural pattern for managing change in your database. They let you write your application code so that it only needs to know about the latest version of your database, and they simplify the code you use to update the database itself.

But it is easy to overlook this pattern because many database helper libraries do so many other things at the same time, in such a complex fashion, that they obscure the simplicity of this basic pattern.

So today, we’re releasing fastmigrate, a library and command line tool for database migrations. It embraces the simplicity of the underlying pattern by being a simple tool itself. It provides a small set of commands. It treats migrations as just a directory of your own scripts. It only requires understanding the essential idea, not a lot of extra jargon. We like it!

This article will explain what database migrations are in general and what problem they solve, and then illustrate how to do migrations in sqlite with fastmigrate.

The problem which migrations solve

The core problem which migrations solve is to make it easier to change your database schema (and other basic structures) without breaking your application. They do this by making database versions explicit and managed, just like the changes in your application code.

To see how complexity creeps in otherwise, consider a typical sequence of events in developing an app. The first time the app runs, it only needs to handle one situation, the case where there is no database yet and it needs to create one. At this point, your app’s startup code might look like this:

App v1

db.execute("CREATE TABLE documents (id INT, content TEXT);")

But wait… The second time a user runs that same app, the table will already exist. So in fact your code should handle two possible cases – the case where the table does not exist, and the case where it already exists.

So in the next version of your app, you update your initialization code to the following:

App v2

db.execute("CREATE TABLE IF NOT EXISTS documents (id INT, content TEXT);")

Later, you might decide to add a new column to the database. So in your app’s third version, you add a second line:

App v3

db.execute("CREATE TABLE IF NOT EXISTS documents (id INT, content TEXT);")

db.execute("ALTER TABLE documents ADD COLUMN title TEXT;")

But wait again… You don’t want to alter the table like this if the column already exists. So App v4 will need more complex logic to handle that case. And so on.

Even this trivial example would create bugs if not handled properly. In a real app, as you introduce and then modify table relationships, such issues become more subtle, numerous, and stressful since one wrong step can lose user data.

What happens is that, with every new version, your application’s code grows more complicated because it is required to handle not just one state of the database but every possible previous state.

To avoid this, you would need to force separate database updates so that your application code knew exactly what to expect from the database. This is often not feasible when the app manages the database and every user gets to decide when to run their own installation of the app, as is the case in a mobile app, a desktop app, or a webapp with one database per user. Even in systems with a single database, forcing separate database updates would introduce an important new kind of change to manage – that is, database changes, which would need to be delicately coupled with changes in your application code.

This gets to the heart of the problem, which is that by default these various database states are implicit and unmanaged.

With your application code, a git commit unambiguously specifies both a version of your code and the change which produced it. Then, your deployment system lets you control exactly which version of your application your users will see next. But with your database, without some system, all you know is that the database is in some unnamed state produced by previous code. The version control and deployment tools which so nicely manage your application code will not automatically control which version of the database your application sees next.

How migrations solve this problem

The database migration pattern solves this problem with two key measures:

First, defining database versions, based on migrations. Instead of reasoning about unnamed database state, we introduce explicit version management of your database.

How do we do this? With migration scripts. A migration script is an isolated, single-purpose script whose only job is to take the database from one version (e.g., 5) to the next version (e.g., 6).

Fastmigrate keeps this simple and names the scripts based on the database version they produce so that, for instance, the script named 0006-add_user.sql must be the one and only script which produces database version 6. In a fundamental sense, the version numbers in the migration scripts define the set of recognized database versions. Thus, you can see the past version of your database by listing the scripts which produced those versions, just like looking at a log of git commits:

$ ls -1 migrations/

0001-initialize.sql

0002-add-title-to-documents.sql

0003-add-users-table.sql

This structured approach enables the next key measure.

Second, writing the app to target one database version. Moving the database evolution code into these migration scripts means that the application code can forget about database changes and target only one version of the database, the latest version.

The application can rely on a migration library, like fastmigrate, to run whatever migrations are needed. That might mean recapitulating all the migrations to create the latest version of the database from nothing when running a fresh instance in development. Or it might mean applying only the latest migration, to bring a recent database version up to date. Or it might mean something in between. The point is, the application does not need to care.

One way to measure the simplification is to count how many fewer cases different parts of your system need to handle.

Before migrations, your application code was in effect responsible for handling all possible previous database states, even when it would have required increasingly careful attention to remember and understand just what all those states were. After migrations, everything is explicit, legible, and factored. The application is responsible for working with just one database version. And every database version has exactly one script which produces it from one previous version. (So clean! Doesn’t it make you want to sigh? Ahhhh…)

Feature

Without migrations

With migrations

DB States

Uncounted, unnamed

image explicit versions

DB Management

None

image isolated migration scripts, one per version

App Requirements

App must support all DB states, and manage DB changes

App must support only one DB version, the latest

How to use fastmigrate

Let us follow the previous example again, and see how this works in fastmigrate.

Instead of embedding the evolving database schema logic into your app’s startup, you will define a series of migration scripts. These scripts are SQL, but you could also use Python or shell scripts. Your application will then use fastmigrate’s API to run those scripts as needed, bringing the database to the latest expected version automatically.

Your first migration script creates the table. Create a directory migrations/ and in that directory put the file 0001-initialize.sql.

-- migrations/0001-initialize.sql

CREATE TABLE documents (

id INTEGER PRIMARY KEY,

content TEXT

);

The 0001 prefix is key: it indicates this is the first script to run, and also that it produces version 1 of your database.

Run pip install fastmigrate to install it from PyPi, so your app can use it.

Now your application startup code can rely on fastmigrate to create and/or update the database. Create your app, in a file called app.py:

from fastmigrate.core import create_db, run_migrations, get_db_version

db_path = "./app.db"

migrations_dir = "./migrations/"

Ensures a versioned database exists.

If no db exists, it's created and set to version 0.

If a db exists, nothing happens

create_db(db_path)

Apply any pending migrations from migrations_dir.

success = run_migrations(db_path, migrations_dir)

if not success:

print("Database migration failed! Application cannot continue.")

exit(1) # Or your app's specific error handling

After this point, your application code can safely assume

the 'documents' table exists exactly as defined in 0001-initialize.sql.

The database is now at version 1.

version = get_db_version(db_path)

print(f"Database is at version {version}")

The first time this Python code runs, create_db() initializes your database, and inserts metadata to mark it as a managed database with version 0. This is done by adding a small _meta table, which stores the current version and indicates it is a managed database.

Then, the function run_migrations() sees 0001-initialize.sql. Since version 1 is greater than the database’s current version 0, the function executes it, and marks the database’s version to 1. On subsequent runs, if no new migration scripts have been added, run_migrations() sees the database is already at version 1 and does nothing further.

You can run your app now, with python3 app.py, and the app will report that the db is at version 1, no matter how many times you run it. You will also be able to see in your directory data.db, the database file it created.

But what about schema evolution?

When you decide your documents table needs a title column, you only need to add a migration script which adds the column.

This change defines version 2 of your database. In the migrations directory, add a file named 0002-add-title-to-documents.sql.

-- migrations/0002-add-title-to-documents.sql

ALTER TABLE documents ADD COLUMN title TEXT;

The key point is, your application startup code does not change: It remains the same Python snippet shown above.

When that code runs on a database which was previously at version 1 (i.e., where only 0001-initialize.sql had been applied), the following happens:

create_db(db_path) confirms the database exists and is at version 1.

run_migrations() scans the migrations/ directory. It finds 0002-add-title-to-documents.sql. Since the script’s version (2) is greater than the database’s current version (1), it executes this new script.

After successful execution, fastmigrate marks the database’s version to 2.

Your application code, which runs after these fastmigrate calls, can now assume the documents table has id, content, and the new title column.

Run your app again, with python3 app.py, and now it will report the database is at version 2.

If you are curious how this works under the hood, it is nothing occult. Fastmigrate marks a database by adding the _meta table, which you can see directly by using the sqlite3 executable:

$ sqlite3 app.db .tables

_meta documents

You can look in it to see the version is now 2:

$ sqlite3 app.db "select * from _meta;"

1|2

But this an implementation detail. The crucial point is the shift in approach:

The complex conditional logic is entirely removed from your application’s main startup sequence.

Schema changes are isolated into small, clearly named, versioned SQL scripts.

Your application’s core startup routine (create_db(), run_migrations()) is stable, even as the database schema evolves.

The rest of your application code, the part that actually uses the database, can always be written to expect the single, latest schema version defined by the highest-numbered migration script. It doesn’t need conditional paths for older database structures.

This "append-only" approach to migrations, where you always add new, higher-numbered scripts for subsequent changes, makes your database evolution explicit, managed, and easy to integrate. The responsibility for reaching the target schema version is delegated to fastmigrate.

When you check your code into version control, you should take care to include the migration script which defines the new database version along with the application code which requires that new database version. Then, your application code will always see exactly the database version which it requires.

Testing on the command line

Before integrating a new migration script into your app, you will of course want to test it. This is straightforward since migration scripts are designed to run in isolation. To help run them interactively, fastmigrate also provides a command line interface (CLI).

If you want to inspect the database your app just created, you can run the check version command:

$ fastmigrate_check_version --db app.db

FastMigrate version: 0.3.0

Database version: 2

When the names of CLI commands match the API, they do exactly the same thing. fastmigrate_create_db behaves just like fastmigrate.create_db, fastmigrate_run_migrations like fastmigrate.run_migrations, and so on.

For instance, you can run these commands to create an empty managed db and run migrations on it:

$ fastmigrate_create_db --db data.db

Creating database at data.db

Created new versioned SQLite database with version=0 at: data.db

$ fastmigrate_run_migrations --db data.db --migrations migrations/

Applying migration 1: 0001-initialize.sql

✓ Database updated to version 1 (0.00s)

Applying migration 2: 0002-add-title-to-documents.sql

✓ Database updated to version 2 (0.00s)

Migration Complete

• 2 migrations applied

• Database now at version 2

• Total time: 0.00 seconds

Nothing new to learn!

For a more detailed walkthrough of the recommended workflow when introducing a new migration, please see our guide on safely adding migrations.

There is also guidance on taking a database which started outside of fastmigrate, and enrolling it as a managed database. Technically, this is nothing more than adding the private metadata which marks the database’s version. But the tool will gives you some help in getting started by generated a draft 0001-initialize.sql migration script, since you will need one which initializes a database equivalent to the database which you are enrolling. This generated script is only a draft since you should definitely verify manually that it is correct for your database.

Simple = Clear = Calm

Check out that map again and consider that our ancestors traveled thousands of miles, without even having air conditioning, podcasts, and AI chatbots to flatter them. It was rough and, yes, we don’t have it so bad.

But nevertheless, managing the evolution of a production database is stressful.

This is natural enough, since it’s the user’s data. The whole purpose of most software is to transform and store that data. So if you mess up your database, your software has failed at its main reason for existing.

The antidote to that stress is clarity. You want to know what you are doing.

Consider that warm feeling of comfort you get when someone refers to a git commit by its hash. (Mmmm.) That feeling is because a hash is unambiguous. If you ask git to compute which files changed between two commit hashes, you know exactly what the answer means. You want to have the same clarity regarding your database.

The migrations pattern brings that by ensuring your database has a simple version number which tells you what state it is in and, therefor, exactly what your application can expect.

And since it’s a simple idea, it needs only a simple tool.

That is why fastmigrate introduces only a few main commands – create_db, get_db_version, and run_migrations – and relies on things you already know, like how to list files and interpret an integer.

In contrast, many existing database tools are complex because they provide a lot of other things as well – object-relational mappers, templating systems, support for various backends, requirements for multiple config files with different syntaxes. If your system has grown in complexity to the point where it needs all that, then that is what you need.

But if you are able to keep your system simple, then a simple solution will serve you better. It will be easier to understand, easier to use, easier to hold in your head and in your hand. If you were chopping a carrot, would you want a good sharp knife? Or a food processor, with a special carrot-chopping attachment, which you need to read the manual of just to figure out how to attach it?

fastmigrate aims to be a good sharp knife. May you wield it with clarity and confidence!