simonw / sqlite-migrate

A simple database migration system for SQLite, based on sqlite-utils

sqlite-migrate

A simple database migration system for SQLite, based on sqlite-utils.

This project is an early alpha. Expect breaking changes.

Installation

This tool works as a plugin for sqlite-utils. First install that:

pip install sqlite-utils

Then install this plugin like so:

sqlite-utils install sqlite-migrate

Migration files

This tool works against migration files. A migration file looks like this:

from sqlite_migrate import Migrations

# Pick a unique name here - it must not clash with other migration sets that
# the user might run against the same database.

migration = Migrations("creatures")

# Use this decorator against functions that implement migrations
@migration()
def create_table(db):
    # db is a sqlite-utils Database instance
    db["creatures"].create(
        {"id": int, "name": str, "species": str},
        pk="id"
    )

@migration()
def add_weight(db):
    # db is a sqlite-utils Database instance
    db["creatures"].add_column("weight", float)

Here is documentation on the Database instance passed to each migration function.

Running migrations

Running this command will execute those migrations in sequence against the specified database file.

Call migrate with a path to your database and a path to the migrations file you want to apply:

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

Running this multiple times will have no additional affect, unless you add more migration functions to the file.

If you call it without arguments it will search for and apply any migrations.py files in the current directory or any of its subdirectories.

You can also pass the path to a directory, in which case all migrations.py files in that directory and its subdirectories will be applied:

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

When applying a single migrations file you can use the --stop-before option to apply all migrations up to but excluding the specified migration:

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

Listing migrations

Add --list to list migrations without running them, for example:

sqlite-utils migrate creatures.db --list

The output will look something like this:

Migrations for: creatures

  Applied:
    create_table - 2023-07-23 04:09:40.324002
    add_weight - 2023-07-23 04:09:40.324649
    add_age - 2023-07-23 04:09:44.441616
    cleanup_columns - 2023-07-23 04:09:44.443394

  Pending:
    drop_table

Verbose mode

Add -v or --verbose for verbose output, which will show the schema before and after the migrations were applied along with a diff:

sqlite-utils migrate creatures.db --verbose

Example output:

Migrating creatures.db

Schema before:

  CREATE TABLE [_sqlite_migrations] (
     [id] INTEGER PRIMARY KEY,
     [migration_set] TEXT,
     [name] TEXT,
     [applied_at] TEXT
  );
  CREATE UNIQUE INDEX [idx__sqlite_migrations_migration_set_name]
      ON [_sqlite_migrations] ([migration_set], [name]);
  CREATE TABLE [creatures] (
     [id] INTEGER PRIMARY KEY,
     [name] TEXT,
     [species] TEXT,
     [weight] FLOAT
  );

Schema after:

  CREATE TABLE [_sqlite_migrations] (
     [id] INTEGER PRIMARY KEY,
     [migration_set] TEXT,
     [name] TEXT,
     [applied_at] TEXT
  );
  CREATE UNIQUE INDEX [idx__sqlite_migrations_migration_set_name]
      ON [_sqlite_migrations] ([migration_set], [name]);
  CREATE TABLE "creatures" (
     [id] INTEGER PRIMARY KEY,
     [name] TEXT,
     [species] TEXT,
     [weight] FLOAT,
     [age] INTEGER,
     [shoe_size] INTEGER
  );

Schema diff:

 );
 CREATE UNIQUE INDEX [idx__sqlite_migrations_migration_set_name]
     ON [_sqlite_migrations] ([migration_set], [name]);
-CREATE TABLE [creatures] (
+CREATE TABLE "creatures" (
    [id] INTEGER PRIMARY KEY,
    [name] TEXT,
    [species] TEXT,
-   [weight] FLOAT
+   [weight] FLOAT,
+   [age] INTEGER,
+   [shoe_size] INTEGER
 );

sqlite-migrate's Issues

Ability to apply migrations up to a point

In writing a test for a migration for this issue:

• simonw/llm#217

I realized I wanted to apply migrations up to a specific point, then run some test code, then apply the rest.

I think .apply() should take an optional second argument, stop_before="name".

tests/test_sqlite_migrate.py:78:11: F841 Local variable `db` is assigned to but never used

Lint failure: https://github.com/simonw/sqlite-migrate/actions/runs/8270287510/job/22627467914

4 files would be left unchanged.
warning: `ruff <path>` is deprecated. Use `ruff check <path>` instead.
tests/test_sqlite_migrate.py:78:11: F841 Local variable `db` is assigned to but never used
Found 1 error.
No fixes available (1 hidden fix can be enabled with the `--unsafe-fixes` option).
Error: Process completed with exit code 1.

Initial design

Based on https://github.com/simonw/llm/blob/0.6/llm/migrations.py

Migration steps with same name are erroneously skipped

Discovered when I had two separate migrations with different migration_set names but the same step name.

The first migration:

from sqlite_migrate import Migrations

m1 = Migrations("m1")

@m1()
def m001_init(db):
    pass

The second:

from sqlite_migrate import Migrations
m2 = Migrations("m2")

@m2()
def m001_init(db):
    pass

The m1.m001_init() step runs successfully, but m2.m001_init() is skipped. This is because they share the same step name m001_init, but they should have a different namespace anyway.

Caused by this, I think:

sqlite-migrate/sqlite_migrate/__init__.py

Line 51 in 82f3459

if migration.name not in already_applied

Add py.typed file to indicate type hints

I had to add this to mypy.ini in LLM:

[mypy-sqlite_migrate.*]
ignore_missing_imports = True

--list should show all migrations, not just pending

Currently:

sqlite-utils migrate data.db --list
Pending migrations for data.db:

It shouldn't be necessary to name the migrations alphabetically

For LLM I got into a habit of doing this:

@migration()
def m001_create_table(db):
    # db is a sqlite-utils Database instance
    db["creatures"].create(
        {"id": int, "name": str, "species": str},
        pk="id"
    )

@migration()
def m002_add_weight(db):
    # db is a sqlite-utils Database instance
    db["creatures"].add_column("weight", float)

Because I was using the function names as the primary key in the database and I wanted them to naturally be displayed in the correct order in Datasette.

I can do better than that.

Ability to run against a specified migration file

Should be able to do this:

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

As an alternative to the current automatic discovery.

Document how to use as a Python library

README currently just talks about the CLI.

Before shipping non-alpha figure out if this is going to break deployed software in strange ways

I'm going to test this with a fresh install of llm to make sure it doesn't break.

Although... here's an interesting callenge with LLM: I frequently run many different copies of it against the same llm.db database stashed away in my ~/Library/Application Support folder.

It's perfectly possible I'll run an LLM upgraded instance which will upgrade the database... but then re-run an older version against the same DB in a way that will break things.

Originally posted by @simonw in #6 (comment)

Documentation should recommend testing with pragma legacy_alter_table=on

See here:

• simonw/llm#162 (comment)

More comprehensive documentation

Should provide more examples, and cover things like the --list command.