Home › API › SIMPLE_SQL_MIGRATION_RUNNER

SIMPLE_SQL_MIGRATION_RUNNER

Version-controlled database migrations using SQLite's user_version PRAGMA. Apply schema changes in order and track migration state.

View Source

How It Works

Migrations are numbered starting from 1. The current version is stored in SQLite's user_version PRAGMA. Only migrations newer than the current version are applied.

Creation

make (a_db: SIMPLE_SQL_DATABASE)

Create migration runner attached to database.

Adding Migrations

add_migration (a_version: INTEGER; a_sql: READABLE_STRING_8)

Add SQL migration at version number.

add_migration_with_name (a_version: INTEGER; a_name: READABLE_STRING_8; a_sql: READABLE_STRING_8)

Add named migration for better logging.

Running Migrations

migrate

Run all pending migrations in order.

migrate_to (a_version: INTEGER)

Run migrations up to specific version.

Version Queries

current_version: INTEGER

Get current schema version.

latest_version: INTEGER

Get highest registered migration version.

pending_count: INTEGER

Number of migrations not yet applied.

Examples

Define and Run Migrations

local
    runner: SIMPLE_SQL_MIGRATION_RUNNER
do
    create runner.make (db)

    -- Version 1: Create users table
    runner.add_migration_with_name (1, "create_users",
        "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE)")

    -- Version 2: Add created_at column
    runner.add_migration_with_name (2, "add_created_at",
        "ALTER TABLE users ADD COLUMN created_at TEXT DEFAULT CURRENT_TIMESTAMP")

    -- Version 3: Create sessions table
    runner.add_migration_with_name (3, "create_sessions",
        "CREATE TABLE sessions (id INTEGER PRIMARY KEY, user_id INTEGER REFERENCES users(id), token TEXT NOT NULL, expires_at TEXT)")

    -- Run all pending migrations
    print ("Current version: " + runner.current_version.out + "%N")
    print ("Pending migrations: " + runner.pending_count.out + "%N")

    runner.migrate

    print ("Migrated to version: " + runner.current_version.out + "%N")
end

Multi-Statement Migration

-- Migrations can contain multiple SQL statements
runner.add_migration (4,
    "CREATE INDEX idx_sessions_user ON sessions(user_id); CREATE INDEX idx_sessions_token ON sessions(token)")

Check Version Before Operations

local
    runner: SIMPLE_SQL_MIGRATION_RUNNER
do
    create runner.make (db)

    -- Ensure database is at expected version
    if runner.current_version < 3 then
        print ("Database needs migration. Run migrate command first.%N")
    else
        -- Safe to proceed with operations that require v3 schema
        run_application
    end
end

Incremental Migration

-- Only migrate to version 2 (skip later migrations)
runner.migrate_to (2)

-- Check what's pending
print ("Still pending: " + runner.pending_count.out + "%N")

Migration Best Practices

Never modify an existing migration after it's been deployed
Each migration should be self-contained and idempotent where possible
Use IF NOT EXISTS / IF EXISTS clauses for safety
Back up database before running migrations in production

SQLite Limitations

SQLite has limited ALTER TABLE support. For complex changes (rename column, change type), create a new table and migrate data:

CREATE TABLE users_new (...);
INSERT INTO users_new SELECT ... FROM users;
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;