> **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](https://github.com/AnswerDotAI/fastmigrate).
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](https://github.com/AnswerDotAI/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:
``` python
# 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:
``` python
# 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:
``` python
# 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:
``` bash
$ 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
n explicit versions
DB Management
None
n 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`.
``` 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`:
``` python
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`.
``` 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:
1. `create_db(db_path)` confirms the database exists and is at version
1.
2. `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.
3. After successful execution, `fastmigrate` marks the database’s
version to 2.
4. 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:
``` bash
$ sqlite3 app.db .tables
_meta documents
```
You can look in it to see the version is now 2:
``` bash
$ 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:
``` bash
$ 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:
``` bash
$ 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](https://github.com/AnswerDotAI/fastmigrate/blob/main/adding_migrations.md).
There is also guidance on taking a database which started outside of
`fastmigrate`, and [enrolling
it](https://github.com/AnswerDotAI/fastmigrate/blob/main/enrolling.md)
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!
