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")
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:
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.
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)[source]¶
- Parameters:
name (str)
- pending(db)[source]¶
Return a list of pending migrations.
- Parameters:
db (Database)
- Return type:
list[Migrations._Migration]
- applied(db)[source]¶
Return a list of applied migrations.
- Parameters:
db (Database)
- Return type:
list[Migrations._AppliedMigration]