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

> Explore the SQLite schema migration pattern in the Iris controller. Learn about versioned upgrades, meta tables, and sequential Python script execution for safe database updates.

- Repository: [The Marin Project/marin](https://github.com/marin-community/marin)
- Tags: how-to-guide
- Published: 2026-08-29

---

**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](https://github.com/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`](https://github.com/marin-community/marin/blob/main/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:

```python

# 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`](https://github.com/marin-community/marin/blob/main/test_migration_00XX.py), where `00XX` represents the target schema version (e.g., [`test_migration_0051.py`](https://github.com/marin-community/marin/blob/main/test_migration_0051.py)). Every script exposes a standardized `run` function that receives a raw SQLite connection:

```python

# 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`](https://github.com/marin-community/marin/blob/main/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`](https://github.com/marin-community/marin/blob/main/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

```python
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()`:

```python
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:

```python
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`](https://github.com/marin-community/marin/blob/main/lib/iris/src/iris/cluster/controller/writes.py), the implementation demonstrates this hybrid pattern using SQLAlchemy's `sqlite_insert` for upsert operations:

```python
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`](https://github.com/marin-community/marin/blob/main/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`](https://github.com/marin-community/marin/blob/main/db.py) and [`writes.py`](https://github.com/marin-community/marin/blob/main/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`](https://github.com/marin-community/marin/blob/main/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`](https://github.com/marin-community/marin/blob/main/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`](https://github.com/marin-community/marin/blob/main/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.