SQLite Schema Migrations Without a Framework

You do not need Flyway, Alembic, or a ORM migrator to change a SQLite schema on a VPS. You need a numbered list of SQL files, a table that records which files have run, and a tiny runner that applies the rest inside a single transaction. The whole mechanism fits in one script and is easy to audit when something goes wrong at 2 a.m.

Frameworks earn their keep on teams that generate migrations from model diffs. On a single application with a handful of tables, generated diffs hide the actual ALTER TABLE you will have to live with. SQLite has a short list of schema changes it can do in place. Everything else is create-new-table, copy, drop, rename. Write that by hand once. It is clearer than a generated 400-line migration.

What SQLite can ALTER

SQLite can ADD COLUMN, RENAME COLUMN, DROP COLUMN (recent versions), RENAME TABLE, and create or drop indexes. It cannot add a FOREIGN KEY to an existing table via ALTER TABLE; you rebuild the table. It cannot change a column type in place in older versions; you rebuild. DEFAULT values on new columns must be constant. Adding a NOT NULL column without a default fails if rows already exist.

Plan additive changes first. New nullable columns, new tables, new indexes are cheap and reversible. Breaking changes get a rebuild. Test the rebuild on a copy of production, not on production.

A schema_migrations table and numbered files

Keep a directory of files named with a zero-padded integer and a slug, applied in sort order.

CREATE TABLE IF NOT EXISTS schema_migrations (
  id INTEGER PRIMARY KEY,
  filename TEXT NOT NULL UNIQUE,
  applied_at TEXT NOT NULL DEFAULT (datetime('now'))
);

Example files:

• 001_init.sql creates the first tables
• 002_accounts_verified.sql adds a column
• 003_events_kind_index.sql adds an index

Each file must be idempotent enough that a retry after a crash does not leave you stuck, or the runner must wrap the file in a transaction and record the filename only on success. SQLite DDL is transactional. That is the feature people forget: you can BEGIN, ALTER, INSERT into schema_migrations, COMMIT, and a crash rolls the whole file back.

import os
import sqlite3

MIGRATIONS = '/var/lib/myapp/migrations'

def apply(conn):
    conn.execute(
        'CREATE TABLE IF NOT EXISTS schema_migrations ('
        'id INTEGER PRIMARY KEY,'
        'filename TEXT NOT NULL UNIQUE,'
        'applied_at TEXT NOT NULL DEFAULT (datetime('now')))'
    )
    done = {row[0] for row in conn.execute(
        'SELECT filename FROM schema_migrations')}
    names = sorted(n for n in os.listdir(MIGRATIONS) if n.endswith('.sql'))
    for name in names:
        if name in done:
            continue
        path = os.path.join(MIGRATIONS, name)
        sql = open(path, 'r', encoding='utf-8').read()
        conn.execute('BEGIN IMMEDIATE')
        try:
            conn.executescript(sql)
            conn.execute(
                'INSERT INTO schema_migrations(filename) VALUES (?)',
                (name,))
            conn.execute('COMMIT')
        except Exception:
            conn.execute('ROLLBACK')
            raise

executescript issues an implicit COMMIT before running, which is a footgun inside an outer transaction. For files that are one or two statements, use execute or executescript only on a connection with isolation_level set so you understand when COMMIT happens. A simpler rule: one migration file, one statement, until you know the runner.

Rebuild a table when ALTER is not enough

To add a foreign key or change a column constraint, create a new table, copy data, drop the old table, rename. Foreign keys must be off during the rename dance or SQLite will check half-moved rows.

PRAGMA foreign_keys = OFF;
BEGIN;
CREATE TABLE events_new (
  id INTEGER PRIMARY KEY,
  account_id INTEGER NOT NULL REFERENCES accounts(id),
  kind TEXT NOT NULL,
  payload TEXT NOT NULL,
  created_at TEXT NOT NULL
);
INSERT INTO events_new(id, account_id, kind, payload, created_at)
  SELECT id, account_id, kind, payload, created_at FROM events;
DROP TABLE events;
ALTER TABLE events_new RENAME TO events;
CREATE INDEX events_account_created ON events(account_id, created_at);
INSERT INTO schema_migrations(filename) VALUES ('004_events_rebuild.sql');
COMMIT;
PRAGMA foreign_keys = ON;

Run PRAGMA foreign_key_check after you turn them back on. If it returns rows, you copied bad data and you should ROLLBACK before anyone notices. Keep the old file on disk; never edit 004 after it has been applied in production. A mistake is 005, not a rewrite of history.

Ship the runner as part of the deploy, before the app starts. systemd ExecStartPre that applies migrations and fails the unit if they fail is better than an app that boots against the wrong schema and writes garbage.

Always apply the next file against a copy of production first. VACUUM INTO a snapshot, point the runner at that snapshot, and inspect .schema plus a few row counts. If the rebuild dropped a trigger or an index you forgot to recreate, you want that on the copy, not on Lucknow DC01 live data. Keep the copy on High-Speed SSD next to the live file so the test is honest about duration, then delete it. A migration that takes twelve minutes on 8 GB is a maintenance window, not a silent ExecStartPre.

A directory of numbered SQL files and one table will carry a VPS app for a long time. You can spin up an Ubuntu 24.04 instance on Netbay in under 60 seconds and follow along — netbayhosts.in.