{"id":9334,"library":"sqlite-migrate","title":"SQLite Migrate","description":"sqlite-migrate is a simple database migration system for SQLite, built upon the sqlite-utils library. It allows users to define database schema changes and data manipulations as Python functions within numbered files, which can then be applied incrementally. The current version is 0.1b0, with releases occurring periodically to address bugs and introduce new features during its beta phase.","status":"active","version":"0.1b0","language":"en","source_language":"en","source_url":"https://github.com/simonw/sqlite-migrate","tags":["database","sqlite","migrations","cli","sqlite-utils"],"install":[{"cmd":"pip install sqlite-migrate","lang":"bash","label":"Install sqlite-migrate"}],"dependencies":[{"reason":"Core functionality is built on sqlite-utils for database interactions.","package":"sqlite-utils","optional":false}],"imports":[{"symbol":"Migrations","correct":"from sqlite_migrate import Migrations"},{"symbol":"MigrationSet","correct":"from sqlite_migrate import MigrationSet"}],"quickstart":{"code":"import sqlite_utils\nfrom sqlite_migrate import Migrations\nimport os\n\n# 1. Prepare a temporary database file\ndb_path = \"quickstart.db\"\nif os.path.exists(db_path):\n    os.remove(db_path)\ndb = sqlite_utils.Database(db_path)\n\n# 2. Define a migration in a temporary directory\nmigrations_dir = \"./qs_migrations\"\nos.makedirs(migrations_dir, exist_ok=True)\nmigration_file_path = os.path.join(migrations_dir, \"001_create_users_table.py\")\n\nwith open(migration_file_path, \"w\") as f:\n    f.write(\"\"\"\ndef migrate(db):\n    db[\"users\"].create({\"id\": int, \"name\": str}, pk=\"id\", if_not_exists=True)\n    db[\"users\"].insert({\"id\": 1, \"name\": \"Alice\"})\n\"\"\")\n\n# 3. Initialize Migrations and apply them\nmigrations = Migrations(db, [migrations_dir])\nmigrations.apply()\n\nprint(f\"Database '{db_path}' migrated successfully.\")\nprint(f\"Tables: {db.table_names()}\")\nprint(f\"Users count: {db['users'].count()}\")\n\n# 4. Clean up temporary files\nos.remove(migration_file_path)\nos.rmdir(migrations_dir)\nos.remove(db_path)","lang":"python","description":"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."},"warnings":[{"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.","message":"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).","severity":"breaking","affected_versions":"0.1b0 and later"},{"fix":"Ensure every migration Python file includes `def migrate(db):` at the module level. Example: `def migrate(db):\n    db['mytable'].create({'id': int})`","message":"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.","severity":"gotcha","affected_versions":"All versions"},{"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`.","message":"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.","severity":"gotcha","affected_versions":"All versions"}],"env_vars":null,"last_verified":"2026-04-16T00:00:00.000Z","next_check":"2026-07-15T00:00:00.000Z","problems":[{"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.","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.","error":"sqlite3.IntegrityError: UNIQUE constraint failed: _sqlite_migrations.name"},{"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): ...`).","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.","error":"AttributeError: 'NoneType' object has no attribute 'migrate'"}]}