SQLite Schema Versioning: Track and Apply Migrations (2026)

The most surprising thing about SQLite schema versioning is that it’s entirely a convention; SQLite itself doesn’t know or care about "versions" or "migrations."

Let’s see this in action. Imagine you have a simple SQLite database my_app.db that stores user information.

import sqlite3

# Initial database creation
conn = sqlite3.connect('my_app.db')
cursor = conn.cursor()
cursor.execute('''
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE
)
''')
conn.commit()
conn.close()

print("Database 'my_app.db' created with 'users' table.")

Now, you need to add an email column. This is your first "migration." You’ll write SQL for this and store it in a file, say 001_add_email_to_users.sql:

ALTER TABLE users ADD COLUMN email TEXT;

To manage this, you need a "migration table" within your SQLite database. This table will track which migrations have already been applied. Let’s call it schema_migrations.

import sqlite3

conn = sqlite3.connect('my_app.db')
cursor = conn.cursor()

# Create the migration tracking table if it doesn't exist
cursor.execute('''
CREATE TABLE IF NOT EXISTS schema_migrations (
    version INTEGER PRIMARY KEY,
    applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
''')
conn.commit()
conn.close()

print("Migration tracking table 'schema_migrations' is ready.")

Now, when you want to apply 001_add_email_to_users.sql, your application logic would:

1. Read the contents of 001_add_email_to_users.sql.
2. Connect to my_app.db.
3. Check if a migration with version 1 (derived from the filename 001_...) already exists in schema_migrations.
4. If not, execute the SQL.
5. Insert (1, CURRENT_TIMESTAMP) into schema_migrations.

Here’s a Python snippet demonstrating the application logic:

import sqlite3
import os

def apply_migration(db_path, migration_file):
    migration_name = os.path.basename(migration_file)
    try:
        version = int(migration_name.split('_')[0])
    except ValueError:
        print(f"Skipping invalid migration filename: {migration_name}")
        return

    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    # Check if migration already applied
    cursor.execute("SELECT 1 FROM schema_migrations WHERE version = ?", (version,))
    if cursor.fetchone():
        print(f"Migration {version} ({migration_name}) already applied.")
        conn.close()
        return

    print(f"Applying migration {version} ({migration_name})...")
    try:
        with open(migration_file, 'r') as f:
            sql = f.read()
            cursor.execute(sql)
            cursor.execute("INSERT INTO schema_migrations (version) VALUES (?)", (version,))
            conn.commit()
            print(f"Migration {version} applied successfully.")
    except sqlite3.Error as e:
        conn.rollback()
        print(f"Error applying migration {version}: {e}")
    finally:
        conn.close()

# --- Simulate applying the migration ---
# Create the migration file first (as shown above)
with open('001_add_email_to_users.sql', 'w') as f:
    f.write('ALTER TABLE users ADD COLUMN email TEXT;')

apply_migration('my_app.db', '001_add_email_to_users.sql')

# Verify the schema
conn = sqlite3.connect('my_app.db')
cursor = conn.cursor()
cursor.execute("PRAGMA table_info(users);")
print("\nUsers table schema after migration:")
for col in cursor.fetchall():
    print(col)
conn.close()

This system solves the problem of evolving your database schema over time without losing data or breaking existing applications. The core mental model is a two-part system: your application code is responsible for reading migration files and orchestrating their application, and a dedicated schema_migrations table within the SQLite database acts as the single source of truth for what’s been done. Each migration file represents a specific, atomic change to the schema, and the schema_migrations table ensures that each change is applied only once. The version number, typically derived from the filename (e.g., 001_, 002_), is crucial for ordering and identification.

The exact levers you control are the migration files themselves (their SQL content and naming convention) and the application logic that reads them, checks the schema_migrations table, executes the SQL, and records the successful application. You can also define custom logic for rollback if needed, though this is less common with SQLite due to its simplicity. The applied_at timestamp in schema_migrations is primarily for auditing and debugging, not for controlling the migration process itself.

A common point of confusion is how to handle complex schema changes that might involve multiple steps or data transformations. While you can put complex SQL into a single migration file, it’s generally better practice to break down significant changes into smaller, sequential migrations. For example, if you need to rename a column, add a new one, populate it, and then drop the old one, you’d typically do this across three separate migration files: one to add the new column, one to populate it, and a final one to drop the old column. This makes it easier to track progress, debug issues, and potentially revert specific steps if necessary.

The next logical step in managing your schema evolution is to explore tools that automate this process, such as Alembic for Python or other language-specific migration frameworks that can be configured to work with SQLite.