cli-recipes

# Alembic / Flask-Migrate CLI Recipes Ready-to-use commands for database schema migrations. Alembic 1.20 needs Python 3.10+ and SQLAlchemy 2.0+. Every autogenerated migration must be read before it is applied: autogenerate misses some changes and misreads others (see Manual Migration Edits). ## Flask-Migrate (Flask Projects) ### Initial Setup ```bash pip install Flask-Migrate ``` ```python # extensions.py from flask_migrate import Migrate from flask_sqlalchemy import SQLAlchemy db = SQLAlchemy(model_class=Base) # Base carries the naming convention (see model-patterns) migrate = Migrate() # app.py def create_app(): app = Flask(__name__) db.init_app(app) migrate.init_app(app, db) # add this line return app ``` ```bash # Initialize migrations directory (run once) flask db init ``` Put a `naming_convention` on `Base.metadata` before the first migration (see `model-patterns`): constraints and indexes then get stable, predictable names, and later `ALTER`/`DROP CONSTRAINT` statements can refer to them. Flask-Migrate 4.x already turns on `compare_type=True` (column type changes are detected) and `render_as_batch=True` (autogenerated migrations use `batch_alter_table`, which SQLite needs). The `env.py` that Flask-Migrate 4.1 generates calls `db.get_engine()` first, which Flask-SQLAlchemy 3.1 deprecates. It only shows as `DeprecationWarning: 'get_engine' is deprecated` when warnings are errors (pytest `filterwarnings`, `python -W error`). In `migrations/env.py` make the helper read the engine directly: ```python def get_engine(): return current_app.extensions['migrate'].db.engine ``` ### Migration Workflow ```bash # 1. Make model changes in models.py # 2. Generate migration script flask db migrate -m "Add phone column to clients" # 3. Read the generated file in migrations/versions/ and fix what autogenerate got wrong # 4. Apply migration flask db upgrade # Verify current state flask db current # CI gate: exits non-zero when the models and the migrations disagree flask db check ``` ### Common Operations ```bash # Apply all pending migrations flask db upgrade # Rollback last migration flask db downgrade # Show migration history flask db history # Show current revision (the database) and the newest revision(s) in the scripts flask db current flask db heads # Join two heads that appeared after merging branches flask db merge -m "Merge heads" <rev1> <rev2> # Generate empty migration (for manual edits) flask db revision -m "Custom migration" # Mark the database as being at a revision WITHOUT running any migration flask db stamp <revision> ``` `stamp` changes only the `alembic_version` table. Use it deliberately: to adopt a database whose schema already matches a known revision, or after repairing a half-applied migration by hand. `flask db stamp head` on a database that is behind marks it current and silently skips every missing change; the next `upgrade` then does nothing and the application fails on missing columns. ## Standalone Alembic ### Initial Setup ```bash pip install alembic alembic init alembic # or keep the source-layout settings in pyproject.toml instead of alembic.ini (Alembic 1.16+): alembic init --template pyproject alembic ``` Edit `alembic/env.py` to point to your models and set the database URL from the environment, not from a committed `alembic.ini`: ```python # alembic/env.py from myapp.models import Base # importing the models populates Base.metadata target_metadata = Base.metadata # inside run_migrations_online(): # context.configure( # connection=connection, # target_metadata=target_metadata, # render_as_batch=True, # SQLite: emit batch_alter_table in autogenerated migrations # compare_server_default=True, # also detect server_default changes # ) ``` `compare_type` is already `True` by default since Alembic 1.12. ### Migration Workflow ```bash # Auto-generate from model changes alembic revision --autogenerate -m "Add phone column" # Apply all pending alembic upgrade head # Print the SQL instead of running it alembic upgrade head --sql # Rollback one step alembic downgrade -1 # Rollback to specific revision alembic downgrade abc123 # View history, newest revisions, current revision alembic history alembic heads alembic current # CI gate: after "alembic upgrade head" on a clean database, fails if autogenerate would produce a migration alembic check ``` ## Manual Migration Edits Autogenerate compares models with the live schema; it cannot know intent. It reports a renamed column or table as a drop plus an add (which loses the data), and it does not see everything (some constraint, enum and server-default changes). Read every generated file and edit it: ```python # In migrations/versions/xxxx_add_phone_column.py import sqlalchemy as sa from alembic import op def upgrade() -> None: op.add_column("clients", sa.Column("phone", sa.String(20), nullable=True)) op.create_index(op.f("ix_clients_phone"), "clients", ["phone"]) def downgrade() -> None: op.drop_index(op.f("ix_clients_phone"), table_name="clients") op.drop_column("clients", "phone") ``` `op.f(...)` marks a name as final, so the naming convention is not applied to it a second time; autogenerate writes it for you. ### Common Migration Operations ```python import sqlalchemy as sa from alembic import op # Add column op.add_column("clients", sa.Column("phone", sa.String(20), nullable=True)) # Drop column op.drop_column("clients", "phone") # Rename column (keeps the data; autogenerate would drop and re-add) op.alter_column("clients", "name", new_column_name="full_name") # Change column type (PostgreSQL needs postgresql_using when there is no implicit cast) op.alter_column("clients", "age", type_=sa.Integer(), postgresql_using="age::integer") # Add index op.create_index(op.f("ix_clients_phone"), "clients", ["phone"]) # Add unique constraint op.create_unique_constraint(op.f("uq_clients_email"), "clients", ["email"]) # Add foreign key op.create_foreign_key( op.f("fk_appointments_client_id_clients"), "appointments", "clients", ["client_id"], ["id"] ) # Create table op.create_table( "new_table", sa.Column("id", sa.Integer, primary_key=True), sa.Column("name", sa.String(100)), ) # Drop table op.drop_table("old_table") ``` SQLite cannot alter or drop most constraints in place. Wrap such changes in batch mode, which rebuilds the table: ```python with op.batch_alter_table("clients") as batch_op: batch_op.add_column(sa.Column("nickname", sa.String(50))) batch_op.create_unique_constraint(op.f("uq_clients_nickname"), ["nickname"]) batch_op.alter_column("name", existing_type=sa.String(100), type_=sa.String(200)) ``` ### Data Migrations Do not import application models into a migration (the models change, the migration must not). Describe just the columns you touch: ```python clients = sa.table("clients", sa.column("id", sa.Integer), sa.column("status", sa.String)) op.execute(clients.update().where(clients.c.status == "regular").values(status="standard")) ``` ## Troubleshooting Migrations | Issue | Fix | |-------|-----| | "Target database is not up to date" | Run `flask db upgrade` first | | "Can't locate revision identified by 'x'" | The database's `alembic_version` names a revision that has no file in `migrations/versions/` (deleted file, another branch, stale checkout). Run `flask db heads` and `flask db history`, restore the missing file (merge or check out the branch that has it), then `flask db upgrade`. Re-stamp only to a revision whose schema you have verified: `flask db stamp <revision>` | | "Multiple head revisions are present" | Two branches both added a migration: `flask db heads`, then `flask db merge -m "Merge heads" <rev1> <rev2>`, then `flask db upgrade` | | Autogenerate misses changes | Edit the migration manually; check `compare_type` / `compare_server_default` in `env.py` | | Autogenerate drops and re-adds a renamed column | Replace the pair with `op.alter_column(..., new_column_name=...)` | | "No changes in schema detected" with a new model | The models module is not imported in `env.py` / the app, or `db.create_all()` already created the table | | Migration fails midway | PostgreSQL runs the migration's DDL in one transaction and rolls it back: fix the migration and rerun. For MySQL and SQLite Alembic assumes non-transactional DDL (its log says so), so the part that ran stays: undo it by hand, then rerun | | `DeprecationWarning: 'get_engine' is deprecated` | Edit `get_engine()` in `migrations/env.py` (see Initial Setup) | | Need to start fresh (development only) | Delete the database, delete `migrations/`, run `flask db init` and `flask db migrate`. Never on a shared or production database |