Migrations and schema versioning for embedded apps

user_version as a migration counter, an idempotent runner, safe table rewrites, the limits of ALTER TABLE, and data migrations.

Versioning the schema

pragma user_version;      -- an integer you own, stored in the file header
pragma user_version = 7;  -- set after migrations have been applied

-- the runner, in outline
-- 1. read pragma user_version
-- 2. for each migration with index > version, in order:
--      begin immediate
--      apply the statements
--      pragma user_version = index
--      commit
-- 3. if anything throws, roll back and leave the version untouched

import sqlite3

MIGRATIONS = [
    # index 1
    """
    create table book (
      id integer primary key, title text not null, pages integer
    ) strict;
    create index book_title on book (title);
    """,
    # index 2
    "alter table book add column isbn text;",
    # index 3 - a table rewrite, because SQLite cannot drop a column in place
    """
    create table book_new (
      id integer primary key, title text not null, isbn text, pages integer
    ) strict;
    insert into book_new (id, title, isbn, pages) select id, title, isbn, pages from book;
    drop table book;
    alter table book_new rename to book;
    create index book_title on book (title);
    """,
]

def migrate(db_path):
    con = sqlite3.connect(db_path, isolation_level=None)
    con.execute("pragma foreign_keys = on")
    version = con.execute("pragma user_version").fetchone()[0]

    for i, statements in enumerate(MIGRATIONS, start=1):
        if i <= version:
            continue
        con.execute("begin immediate")
        try:
            con.executescript(statements)
            con.execute("pragma user_version = " + str(i))
            con.execute("commit")
        except Exception:
            con.execute("rollback")
            raise
    con.close()

• pragma user_version = ? does not accept a bound parameter in all builds - build the statement from an integer you control, never from user input.
• One transaction per migration keeps the file consistent: either the schema and the version both move, or neither does.
• foreign_keys must be off during a table rewrite, because dropping the old table would cascade or fail. Re-enable it and run pragma foreign_key_check afterwards.
• Application and schema version must be checked together: an old application opening a newer file should refuse to run rather than corrupt it.

What ALTER TABLE can and cannot do

-- the canonical safe rewrite
pragma foreign_keys = off;
begin immediate;

create table book_new (
  id    integer primary key,
  title text not null,
  isbn  text unique,
  pages integer not null default 0 check (pages >= 0)
) strict;

insert into book_new (id, title, isbn, pages)
  select id, title, isbn, coalesce(pages, 0) from book;

drop table book;
alter table book_new rename to book;

create index book_title on book (title);

pragma foreign_key_check;
commit;
pragma foreign_keys = on;
pragma user_version = 4;

A rewrite copies the whole table, so on a large database it needs free disk space and takes time. Do it during a maintenance window, or copy in batches into the new table and swap at the end if the file is large.

Data migrations

• Separate schema changes from data changes: the first is fast and atomic, the second can be long and should be resumable.
• Batch a large backfill by rowid and commit every few thousand rows, so progress survives a crash: where id > ? order by id limit 1000.
• VACUUM after a big delete or rewrite to return space to the filesystem and rebuild the file compactly.
• Never rely on the application to fix data on read. A migration that runs on every device, once, is far more predictable than code that repairs lazily.
• Keep a migration log table if you need to report which devices have upgraded, but keep user_version as the source of truth for the schema.

-- a resumable backfill driven by the application
update book
set slug = lower(replace(title, ' ', '-'))
where id in (select id from book where slug is null order by id limit 1000);

-- after the last batch
create index book_slug on book (slug);
vacuum;

FAQ

Related

Transactions, locking and concurrency Testing, tooling and benchmarks for SQLite apps

Last refreshed 2026-09-18.