Database migrations

sqlite-utils includes a migration system for applying repeatable changes to SQLite database files.

A migration is a Python function that receives a sqlite_utils.Database instance and then executes Python code to modify that database - creating or transforming tables, adding indexes, inserting rows, or any other operation supported by SQLite.

Migrations are grouped into named sets using the sqlite_utils.Migrations class, and each applied migration is recorded in the _sqlite_migrations table in that database.

This means you can run the migrate operation multiple times and it will only apply migrations that have not previously been recorded.

Defining migrations

Ordered migration sets are defined by first creating a sqlite_utils.Migrations object.

Individual migrations are Python functions that are then registered with that migration set. Each migration function is passed a single argument that is a sqlite_utils.Database instance.

The name passed to Migrations("creatures") identifies that set of migrations. Use a name that is unique for your project, since multiple migration sets can be applied to the same database.

Here is a simple example of a migrations.py file which creates a table, then adds an extra column to that table in a second migration:

from sqlite_utils import Database, Migrations

migrations = Migrations("creatures")

@migrations()
def create_table(db):
    db["creatures"].create(
        {"id": int, "name": str, "species": str},
        pk="id",
    )

@migrations()
def add_weight(db):
    db["creatures"].add_column("weight", float)

Applying migrations in Python

Once you have a Migrations(name) collection with one or more migrations registered to it, you can execute them in Python code like this:

db = Database("creatures.db")
migrations.apply(db)

Running migrations.apply(db) repeatedly is safe. Migrations that already have a matching migration_set and name row in _sqlite_migrations will be skipped.

Migration functions are applied in the order that they were registered. The function name is used as the migration name unless you pass one explicitly:

@migrations(name="001_create_table")
def create_table(db):
    db["creatures"].create({"id": int, "name": str}, pk="id")

When you apply a set of migrations you can stop part way through by specifying a stop_before= migration name:

migrations.apply(db, stop_before="add_weight")

Migrations and transactions

Each migration runs inside a transaction, together with the _sqlite_migrations record of it having been applied. If a migration function raises an exception, everything it did is rolled back, no record is written and the migration stays pending - so fixing the error and re-applying will run that migration again from a clean state. Migrations that completed earlier in the same apply() run stay applied.

Some operations cannot run inside a transaction, for example VACUUM or changing the journal mode with db.enable_wal(). Register migrations like these with transactional=False:

@migrations(transactional=False)
def compact(db):
    db.execute("VACUUM")

A migration registered with transactional=False runs without a wrapping transaction, so if it fails part way through any changes it already made will not be rolled back, and re-applying will run the whole function again.

Avoid calling db.commit() or otherwise managing transactions manually inside a transactional migration - register the migration with transactional=False if it needs to control its own transactions. Using with db.atomic(): blocks inside a migration is fine: they nest as savepoints within the migration’s transaction, so the migration as a whole still commits or rolls back as a single unit. See Transactions and saving your changes.

Applying migrations using the CLI

Run migrations using the sqlite-utils migrate command:

sqlite-utils migrate creatures.db path/to/migrations.py

The first argument is the database file. The remaining arguments can be paths to migration files or directories containing migration files.

If you omit migration paths, sqlite-utils searches the current directory and subdirectories for files called migrations.py:

sqlite-utils migrate creatures.db

You can also pass a directory. Every migrations.py file in that directory tree will be considered:

sqlite-utils migrate creatures.db path/to/project/

Running the command repeatedly is safe. Migrations that already have a matching migration_set and name row in _sqlite_migrations will be skipped.

Listing migrations

Use --list to show applied and pending migrations without running them. This is a read-only operation - it will not create the database file or the _sqlite_migrations table:

sqlite-utils migrate creatures.db --list

Example output:

Migrations for: creatures

  Applied:
    create_table - 2026-06-09 17:23:12.048092+00:00
    add_weight - 2026-06-09 17:23:12.051249+00:00

  Pending:
    add_age

Stopping before a migration

When applying migrations using the CLI, you can stop before a named migration:

sqlite-utils migrate creatures.db path/to/migrations.py --stop-before add_weight

This applies any pending migrations before add_weight and leaves add_weight and later migrations pending. An unqualified migration name matches in any migration set.

You can also target a specific migration set using migration_set:migration_name. This is useful if a migrations file contains more than one migration set, or if multiple sets use the same migration name:

sqlite-utils migrate creatures.db path/to/migrations.py \
  --stop-before creatures:add_weight \
  --stop-before sales:drop_index

The --stop-before option can be passed more than once.

If a --stop-before value does not match any known migration the command exits with an error, rather than silently applying everything.

Verbose output

Use --verbose or -v to show the schema before and after migrations are applied, plus a unified diff when the schema changes:

sqlite-utils migrate creatures.db --verbose

Migrating from sqlite-migrate

This system uses the same migration table format as the older sqlite-migrate package. To use existing migration files directly with sqlite-utils, update their import from sqlite_migrate to sqlite_utils:

from sqlite_utils import Migrations

migration = Migrations("creatures")

@migration()
def create_table(db):
    db["creatures"].create({"id": int, "name": str}, pk="id")

Python API

class sqlite_utils.migrations.Migrations(name)

Parameters:

name (str)

migrations_table = '_sqlite_migrations'

pending(db)

Return a list of pending migrations.

This is a read-only operation - it does not write to the database.

Parameters:

db (Database)

Return type:

list[Migrations._Migration]

applied(db)

Return a list of applied migrations, in the order they were applied.

This is a read-only operation - it does not write to the database.

Parameters:

db (Database)

Return type:

list[Migrations._AppliedMigration]

apply(db, *, stop_before=None)

Apply any pending migrations to the database.

Each migration runs inside a transaction, together with the record of it having been applied - if the migration raises an exception its changes are rolled back, no record is written and the migration stays pending. Migrations registered with transactional=False run outside of a transaction.

Parameters:

• db (Database)

• stop_before (str | Iterable[str] | None)

ensure_migrations_table(db)

Ensure the _sqlite_migrations table exists and has the correct schema.

Parameters:

db (Database)