SQLite Migrations: Versioning Schemas with user_version

Schemas Change. Plan for It.

The first version of your schema is never the last. Columns get added, tables get split, indexes get rethought. The question isn't whether your schema will change — it's whether the change will land cleanly on every laptop, server, and user device that already has an older copy of the database.

That's what migrations are for: a sequence of small, ordered scripts that take a database from version N to version N+1. Run them in order, and any database catches up to current. Skip the discipline, and you end up with "works on my machine" bugs that take all afternoon to track down.

SQLite gives you exactly one built-in tool for this: PRAGMA user_version. It's a 32-bit integer the database stores for you, untouched by SQLite itself. You decide what it means.

PRAGMA user_version;
PRAGMA user_version = 1;
PRAGMA user_version;

A fresh database starts at 0. Set it to whatever migration number you've just applied. Read it on startup to know where you are.

A Minimal Migration Loop

The mental model: each migration is a numbered SQL script. Your app reads the current user_version, runs every script with a higher number in order, and updates user_version after each one.

Here's migration 1 — creating the initial schema:

BEGIN;

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name  TEXT NOT NULL
);

PRAGMA user_version = 1;

COMMIT;

Two things to notice. The whole thing is wrapped in BEGIN; ... COMMIT; so it's atomic — if the CREATE TABLE fails, user_version doesn't get bumped and you can fix and retry. And PRAGMA user_version = 1 is the last statement before the commit, so the version flips only if everything else succeeded.

Now suppose you need to add a created_at column. That's migration 2:

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE
);

BEGIN;

ALTER TABLE users ADD COLUMN created_at TEXT
    NOT NULL DEFAULT (datetime('now'));

PRAGMA user_version = 2;

COMMIT;

SELECT sql FROM sqlite_master WHERE name = 'users';

A database at version 0 runs both. A database at version 1 runs only the second. A database at version 2 runs nothing. The order is the contract.

What ALTER TABLE Can and Can't Do

SQLite's ALTER TABLE is deliberately narrow. It supports:

• ADD COLUMN — append a new column with an optional default.
• DROP COLUMN — remove a column (since 3.35).
• RENAME COLUMN — rename a column (since 3.25).
• RENAME TO — rename the table itself.

That's it. You can't change a column's type, change NOT NULL, alter a CHECK constraint, or add a FOREIGN KEY to an existing column.

-- Not supported:
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(255);
ALTER TABLE users ADD CONSTRAINT users_email_check CHECK (email LIKE '%@%');

When you need a change SQLite can't do directly, the official recipe is "rebuild the table." It's more verbose but completely reliable.

Rebuilding a Table for Bigger Changes

The pattern is: create a new table with the shape you want, copy the data over, drop the old one, rename the new one into place. All inside a transaction.

BEGIN;

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name  TEXT NOT NULL
);
INSERT INTO users (email, name) VALUES ('a@x.com', 'Ada'), ('b@x.com', 'Boris');

-- Now we want a CHECK constraint on email. Rebuild:
CREATE TABLE users_new (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE CHECK (email LIKE '%@%'),
    name  TEXT NOT NULL
);

INSERT INTO users_new (id, email, name)
    SELECT id, email, name FROM users;

DROP TABLE users;
ALTER TABLE users_new RENAME TO users;

COMMIT;

SELECT * FROM users;

The full SQLite docs call this the 12-step recipe and add a few extra cautions for triggers, views, and foreign-key references — worth reading once before doing it on a production schema. For most cases the four-step version above is enough.

A heads-up: if you have foreign keys pointing at the table you're rebuilding, run PRAGMA foreign_keys = OFF before the migration and PRAGMA foreign_keys = ON after. Otherwise the DROP TABLE can break referential integrity mid-flight.

Driving Migrations from Your Application

The bookkeeping is simple enough that you can write it yourself. In Python with the standard library:

-- Conceptually, your app does this on startup:
--
--   current = SELECT user_version FROM PRAGMA;
--   for i in range(current + 1, len(MIGRATIONS) + 1):
--       run MIGRATIONS[i] in a transaction
--       PRAGMA user_version = i;
--
-- The SQL each step runs looks like the migrations above.

PRAGMA user_version;

The key invariants:

• Migrations are numbered consecutively starting at 1. No gaps, no reordering.
• Each migration is wrapped in a transaction along with the PRAGMA user_version = N bump.
• Once a migration is committed and shipped, you never edit it. New changes go in a new migration.

That last rule is the one teams break most often. If you edit migration 3 after a colleague's database has already applied it, their database is silently out of sync with yours forever.

Recording an Audit Trail

user_version tells you where a database is. It doesn't tell you when each step ran or what it did. A small bookkeeping table fixes that:

CREATE TABLE schema_migrations (
    version    INTEGER PRIMARY KEY,
    name       TEXT NOT NULL,
    applied_at TEXT NOT NULL DEFAULT (datetime('now'))
);

BEGIN;

CREATE TABLE posts (
    id     INTEGER PRIMARY KEY,
    title  TEXT NOT NULL,
    body   TEXT NOT NULL
);

INSERT INTO schema_migrations (version, name)
    VALUES (1, 'create_posts');
PRAGMA user_version = 1;

COMMIT;

SELECT * FROM schema_migrations;

Now you have a row per migration with a name and timestamp — handy when debugging "why does this database have a column the code doesn't expect?"

PRAGMA user_version is still the source of truth for the loop; the table is for humans.

Rollback: What Transactions Give You, and What They Don't

SQLite's DDL is transactional. If migration 5 starts creating a table, copying data, and bumping user_version, and the copy fails halfway through, ROLLBACK undoes everything — including the CREATE TABLE. The database is exactly as it was before BEGIN.

BEGIN;

CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance INTEGER NOT NULL);
INSERT INTO accounts (id, balance) VALUES (1, 100);

-- Pretend something goes wrong here:
ROLLBACK;

-- The table doesn't exist:
SELECT name FROM sqlite_master WHERE type='table' AND name='accounts';

That covers failed migrations. It doesn't cover migrations that committed successfully and that you now regret. For those, you write a separate down-migration — a script that undoes the change. SQLite has no automatic reverse. If migration 7 added a column, the down version drops it. If migration 7 dropped a column, the down version can't recover the data; the best it can do is recreate the column empty.

In practice, many small projects skip down-migrations entirely and rely on backups for "undo." That's a valid choice as long as you take the backups.

A Few Habits That Save Pain Later

• One migration per logical change. A migration that adds three unrelated columns is harder to review and harder to revert than three migrations.
• Test migrations against a copy of production. Schema changes can be slow on big tables; finding out in production is no fun.
• Never edit a shipped migration. Add a new one.
• Back up first. A quick .backup in the CLI or a file copy when the database is closed is cheap insurance before any non-trivial migration.
• Watch out for PRAGMA foreign_keys. Turn it off during table rebuilds, back on after.

For larger projects, reach for a dedicated tool — Alembic with SQLAlchemy, golang-migrate, Knex, Flyway. They handle ordering, concurrent runners, and team conventions you'd otherwise reinvent. The principles are the same as the loop above; the tool just removes the boilerplate.

Next: WAL Mode and Concurrency

Migrations usually run while the application is offline or holding an exclusive lock. The rest of the time, your database is serving reads and writes from multiple connections at once — and SQLite's default journal mode isn't always the best fit. The next page covers WAL mode, what it changes, and when to switch.