SQLite Schema Migration Pattern in the Iris Controller: Versioned Upgrades Explained

The Iris controller uses an explicit versioned migration pattern that tracks schema versions in a dedicated meta table and executes ordered Python scripts to upgrade SQLite databases sequentially, with full backup and rollback protection.

The Iris controller—part of the marin-community/marin repository—maintains persistent cluster state in a local SQLite database (controller.sqlite3). To evolve this schema safely across releases without data loss, it implements a deterministic, test-driven migration system that handles everything from bootstrap to rollback protection.

Database Bootstrap and Version Tracking

When a controller instance starts, the ControllerDB class initializes the SQLite store and ensures a meta table exists for tracking schema state. This foundation enables reliable forward migrations on both fresh installations and legacy deployments.

The iris_meta Table

In lib/iris/src/iris/cluster/controller/db.py, the ControllerDB implementation creates an iris_meta table if absent. This table stores a single row containing the current schema_version as an integer:

# Simplified bootstrap logic from ControllerDB

def _init_meta(conn: sqlite3.Connection):
    conn.execute("""
        CREATE TABLE IF NOT EXISTS iris_meta (
            schema_version INTEGER NOT NULL
        )
    """)
    # Initialize to 0 if empty

    if conn.execute("SELECT COUNT(*) FROM iris_meta").fetchone()[0] == 0:
        conn.execute("INSERT INTO iris_meta (schema_version) VALUES (0)")

If the table does not exist at startup, the migration runner assumes version 0 and immediately prepares to execute all available migration scripts.

Migration Script Architecture

Schema changes are encapsulated in discrete Python modules following a strict naming convention that ensures deterministic execution order.

Script Location and Naming Convention

Each migration resides in lib/iris/tests/cluster/controller/ with the filename pattern test_migration_00XX.py, where 00XX represents the target schema version (e.g., test_migration_0051.py). Every script exposes a standardized run function that receives a raw SQLite connection:

# lib/iris/tests/cluster/controller/test_migration_0051.py

import sqlite3

def run(conn: sqlite3.Connection) -> None:
    """Upgrade schema to version 51."""
    conn.execute(
        """
        CREATE TABLE new_feature (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        """
    )

This convention allows the migration runner to dynamically import modules and execute them in numeric sequence without manual registration.

Execution Flow and Runner Logic

The MigrationRunner class (tested in lib/iris/tests/cluster/controller/test_migration_runner.py) orchestrates the upgrade process by comparing the current version against available scripts.

Sequential Migration Execution

The runner executes the following logic:

1. Detect Current Version: Query iris_meta.schema_version (defaulting to 0)
2. Identify Pending Migrations: Load all test_migration_00XX.py files where XX > current_version
3. Execute in Order: For each pending version, import the module and invoke run(conn)
4. Update Version: After successful execution, update the meta table to the new version

from iris.cluster.controller.db import ControllerDB, MigrationRunner
import sqlite3

conn = sqlite3.connect("/path/to/controller.sqlite3")
db = ControllerDB(conn)

runner = MigrationRunner(db)
runner.apply_all()  # Runs 0001, 0002, ... up to latest

This sequential approach guarantees that each schema transformation builds upon the previous state, maintaining referential integrity throughout the upgrade path.

Safety Mechanisms and Validation

The Iris migration pattern includes multiple safeguards against data corruption and failed upgrades.

Pre-Migration Backups

Before applying changes, the controller can create a filesystem backup using ControllerDB.backup_to():

from pathlib import Path

# From lib/iris/tests/cluster/controller/test_checkpoint.py

db.backup_to(Path("/tmp/backups/controller.sqlite3"))

This creates a complete file copy of the SQLite database, enabling restoration if a migration fails or produces unexpected results.

Error Handling and Idempotency

If a migration raises an exception (such as sqlite3.IntegrityError), the runner aborts immediately and leaves the database at the last successful version. Because each migration runs inside a transaction and updates iris_meta only after success, the system remains idempotent—re-running apply_all() will resume from the failed point without re-executing completed migrations.

Validation Helpers for Testing

The test suite provides introspection utilities to verify schema correctness. These helpers query sqlite_master to assert table structure, foreign keys, and indexes:

def _has_table(conn: sqlite3.Connection, table: str) -> bool:
    return conn.execute(
        "SELECT 1 FROM sqlite_master WHERE type='table' AND name=?",
        (table,)
    ).fetchone() is not None

Additional helpers like _objects(), _fk_targets(), and _indexes() enable comprehensive validation that migrations produce the expected DDL.

Hybrid Database Access Pattern

The Iris controller uses a dual approach to database access. While migrations execute against raw sqlite3.Connection objects for fine-grained DDL control, application code uses SQLAlchemy with the SQLite dialect for higher-level operations.

In lib/iris/src/iris/cluster/controller/writes.py, the implementation demonstrates this hybrid pattern using SQLAlchemy's sqlite_insert for upsert operations:

from sqlalchemy.dialects.sqlite import insert as sqlite_insert

# SQLAlchemy approach for application writes

stmt = sqlite_insert(my_table).values(data)
stmt = stmt.on_conflict_do_update(index_elements=['id'], set_=data)
conn.execute(stmt)

This separation allows migrations to leverage raw SQL for schema changes while the application benefits from ORM convenience and type safety.

Summary

• Version Tracking: The iris_meta table stores an integer schema_version that determines which migrations have been applied.
• Ordered Scripts: Migrations follow the test_migration_00XX.py naming convention in lib/iris/tests/cluster/controller/ and expose a run(conn) function.
• Sequential Execution: MigrationRunner.apply_all() executes pending migrations in numeric order, updating the version after each success.
• Safety First: Built-in backup utilities (ControllerDB.backup_to()) and transaction-based error handling prevent data loss during failed upgrades.
• Dual Access: Migrations use raw sqlite3 connections while application code uses SQLAlchemy, defined in db.py and writes.py respectively.

Frequently Asked Questions

What happens if a migration fails halfway through?

The migration runner aborts immediately upon any exception, leaving the database at the last successfully completed version. Because each migration runs in a transaction and the iris_meta.schema_version updates only after successful completion, the database remains consistent and the operation can be retried or restored from the backup created via ControllerDB.backup_to().

How does the controller determine which migrations need to run?

The MigrationRunner queries the iris_meta table for the current schema_version (defaulting to 0 if the table is missing). It then scans for all test_migration_00XX.py files in the test directory where the numeric suffix exceeds the current version, sorting them numerically to ensure sequential application from current_version + 1 upward.

Why are production migrations stored in the test directory?

According to the Iris source code, schema change scripts reside under lib/iris/tests/cluster/controller/ with the test_migration_00XX.py naming pattern. This colocation ensures that migrations are continuously validated by the test suite, as the runner in test_migration_runner.py executes them in a controlled environment, effectively making migration testing a first-class citizen of the CI pipeline.

Can migrations run while the controller is handling requests?

Yes. The migration system acquires the database connection and typically runs at startup before the controller begins serving traffic. Individual migrations execute within SQLite transactions, ensuring that schema changes are atomic and do not leave the database in a partially modified state if the process crashes mid-migration.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}