SQLite Migrate

Common errors

• sqlite3.IntegrityError: UNIQUE constraint failed: _sqlite_migrations.name

  cause This typically occurs when applying `sqlite-migrate 0.1b0` or later to a database that was initially migrated with a version prior to `0.1b0`. The older schema of `_sqlite_migrations` table used `name` as the sole primary key, while the newer version attempts to use `(migration_set, name)`, leading to conflicts if existing 'name' entries are duplicated by design in the new system.
  fix If safe, drop and recreate the `_sqlite_migrations` table. For production databases, carefully manage the upgrade of this internal table's schema, potentially by manually adding the `migration_set` column and altering the primary key to `(migration_set, name)` before applying new migrations.

• AttributeError: 'NoneType' object has no attribute 'migrate'

  cause A migration file was found and imported, but it did not define the expected `migrate(db)` function, or the function was misspelled/not at the top level of the module.
  fix Review the migration file(s) that are intended to be executed and ensure each one contains a function named `migrate` that takes a single argument, `db` (e.g., `def migrate(db): ...`).

Warnings

• breaking The internal `_sqlite_migrations` table's primary key was changed from `name` to `(migration_set, name)`. If you migrate an existing database created with `sqlite-migrate < 0.1b0` to `0.1b0` or later, and try to apply new migrations, you may encounter `UNIQUE constraint failed` errors if migration names conflict across different sets (or if the internal table schema isn't properly updated).
  Fix: For existing databases from `0.1aX`, consider creating a backup, then manually altering the `_sqlite_migrations` table to include `migration_set` in its primary key, or use a fresh database instance with `0.1b0+` for new projects. If using migration sets, ensure older migrations (before 0.1b0) are associated with a single 'default' migration set or handled carefully during schema upgrade.
• gotcha Migration files must define a top-level function named `migrate` that accepts a `db` argument (an `sqlite-utils` Database object). Files without this function will be ignored.
  Fix: Ensure every migration Python file includes `def migrate(db):` at the module level. Example: `def migrate(db): db['mytable'].create({'id': int})`
• gotcha Migrations are applied in alphabetical (lexicographical) order based on their filenames. This means `10_migration.py` will run *before* `2_migration.py` if not zero-padded. Improper naming can lead to unexpected database states.
  Fix: Always use zero-padded, sequential numbering for migration files, e.g., `001_initial_schema.py`, `002_add_user_email.py`, `010_refactor_addresses.py`.

Install

• pip install sqlite-migrate Install sqlite-migrate

Imports

• Migrations

  from sqlite_migrate import Migrations

• MigrationSet

  from sqlite_migrate import MigrationSet

Quickstart

This quickstart demonstrates how to create a simple migration, apply it to an SQLite database, and verify the changes. It involves creating a temporary migration file and directory, then using the `Migrations` class to execute the migration.

import sqlite_utils
from sqlite_migrate import Migrations
import os

# 1. Prepare a temporary database file
db_path = "quickstart.db"
if os.path.exists(db_path):
    os.remove(db_path)
db = sqlite_utils.Database(db_path)

# 2. Define a migration in a temporary directory
migrations_dir = "./qs_migrations"
os.makedirs(migrations_dir, exist_ok=True)
migration_file_path = os.path.join(migrations_dir, "001_create_users_table.py")

with open(migration_file_path, "w") as f:
    f.write("""
def migrate(db):
    db["users"].create({"id": int, "name": str}, pk="id", if_not_exists=True)
    db["users"].insert({"id": 1, "name": "Alice"})
""")

# 3. Initialize Migrations and apply them
migrations = Migrations(db, [migrations_dir])
migrations.apply()

print(f"Database '{db_path}' migrated successfully.")
print(f"Tables: {db.table_names()}")
print(f"Users count: {db['users'].count()}")

# 4. Clean up temporary files
os.remove(migration_file_path)
os.rmdir(migrations_dir)
os.remove(db_path)