Why does an Alembic revision that changes a column's type fail on SQLite, and what does `op.batch_alter_table()` do instead?
answer
- SQLite's thin ALTER TABLE
- move and copy
- temporary table, copy, drop, rename
- recreate defaults to auto
- unnamed CHECK constraints
basics
~20 sSQLite's ALTER TABLE cannot change a column's type, and Alembic refuses constraint changes on SQLite, so plain operations fail there. op.batch_alter_table() collects the changes and rebuilds the table: create a new one, copy the rows, drop the old, rename.
solid answer
~40 s`op.alter_column(type_=...)` emits `ALTER TABLE invoices ALTER COLUMN amount TYPE ...`, which SQLite rejects with `near "TYPE": syntax error`, and constraint operations raise `NotImplementedError` ("No support for ALTER of constraints in SQLite dialect. Please refer to the batch mode feature..."). Inside `with op.batch_alter_table("invoices") as batch_op:` the operations are collected; when the block exits on SQLite, Alembic reflects the table, creates `_alembic_tmp_invoices` with the new shape, copies rows with `INSERT ... SELECT`, drops the original and renames the copy. With the default `recreate="auto"` this happens only on SQLite and only for operations other than `add_column()`, `create_index()` and `drop_index()`; other databases get ordinary `ALTER TABLE`. A rebuild drops unnamed CHECK constraints, with a warning since Alembic 1.20, and fails if foreign keys from other tables are enforced.
code
python · 31 linesimport sqlalchemy as sa
from alembic.migration import MigrationContext
from alembic.operations import Operations
engine = sa.create_engine("sqlite://")
with engine.begin() as conn:
conn.exec_driver_sql(
"CREATE TABLE invoices (id INTEGER PRIMARY KEY, amount INTEGER NOT NULL)"
)
op = Operations(MigrationContext.configure(conn))
try: # a plain op emits ALTER COLUMN ... TYPE, which SQLite rejects
op.alter_column("invoices", "amount", type_=sa.Numeric(10, 2))
except sa.exc.OperationalError as err:
print(err.orig)
try: # constraint changes are refused before any SQL is sent
op.create_check_constraint("ck_invoices_amount", "invoices", "amount >= 0")
except NotImplementedError as err:
print(err)
# batch mode: on SQLite the block rebuilds the table when it exits
with op.batch_alter_table("invoices") as batch_op:
batch_op.alter_column(
"amount", type_=sa.Numeric(10, 2), existing_type=sa.Integer()
)
batch_op.create_check_constraint("ck_invoices_amount", "amount >= 0")
print(conn.exec_driver_sql(
"SELECT sql FROM sqlite_master WHERE name = 'invoices'"
).scalar_one())go deeper
Recall that SQLite cannot change a column's type with ALTER TABLE and that op.batch_alter_table() rebuilds the table instead.
Explain the five steps of a rebuild, what recreate='auto' decides, and why the same revision emits plain ALTER TABLE on PostgreSQL.
Show you know what a rebuild can lose, unnamed CHECK constraints and enforced foreign keys, and how table_args or naming conventions prevent it.
Weigh running development on SQLite with batch revisions against using the production database everywhere, given what rebuilds hide.
## Why SQLite needs special handling SQLite's `ALTER TABLE` supports a far narrower set of forms than PostgreSQL's or MySQL's: renaming a table or a column and adding a column are the classic ones, and newer releases also drop columns. It has no form that changes a column's type, and Alembic does not attempt constraint changes on SQLite at all. Alembic's ordinary operations assume a database with a full `ALTER TABLE`, so on SQLite: - `op.alter_column("invoices", "amount", type_=sa.Numeric(10, 2))` emits `ALTER TABLE invoices ALTER COLUMN amount TYPE NUMERIC(10, 2)`, and SQLite answers `near "TYPE": syntax error`. - `op.create_check_constraint()`, `op.create_foreign_key()` and `op.drop_constraint()` raise `NotImplementedError` before any SQL is sent: "No support for ALTER of constraints in SQLite dialect. Please refer to the batch mode feature which allows for SQLite migrations using a copy-and-move strategy." ## What batch mode emits `op.batch_alter_table("invoices")` is a context manager that yields a `batch_op` with the same methods as `op`, minus the table name. The operations are recorded, not executed, until the block exits. Then, on SQLite: 1. Alembic reflects the existing table from the database, unless `copy_from` supplies a `Table` instead. 2. It applies the recorded changes to that copy and emits `CREATE TABLE _alembic_tmp_invoices` with the new shape. 3. It copies the data with `INSERT INTO _alembic_tmp_invoices (...) SELECT ... FROM invoices`, casting columns whose type changed. 4. It emits `DROP TABLE invoices`. 5. It renames the copy with `ALTER TABLE _alembic_tmp_invoices RENAME TO invoices`. ## The `recreate` parameter | `recreate=` | Behaviour | |---|---| | `"auto"` (default) | rebuild on SQLite when any operation other than `add_column()`, `create_index()` or `drop_index()` is present (or an added column carries a SQL-expression server default); plain `ALTER TABLE` elsewhere | | `"always"` | rebuild on every database, PostgreSQL and MySQL included | | `"never"` | never rebuild; each operation is emitted as ordinary `ALTER` | So the same revision runs on SQLite in development and on PostgreSQL in production: there, the block emits `ALTER TABLE invoices ALTER COLUMN amount TYPE NUMERIC(10, 2)` and `ALTER TABLE invoices ADD CONSTRAINT ck_invoices_amount CHECK (amount >= 0)`, one statement per operation. ## What a rebuild can lose - **Unnamed CHECK constraints.** Alembic cannot match a reflected CHECK constraint without a name to its columns, so it leaves it out of the new table. Up to Alembic 1.19 this was silent; 1.20 warns: "Unnamed CHECK constraint on reflected table 'invoices' is being omitted from the table recreate; pass the constraint explicitly using batch_alter_table.table_args." Restate it with `table_args=[CheckConstraint(...)]`, or name constraints through `MetaData(naming_convention=...)` so later rebuilds carry them. - **Type-generated constraints.** A `Boolean` or `Enum` that creates a CHECK constraint must have a name for that constraint to survive a rebuild. - **Named CHECK constraints on a dropped column.** A named CHECK is carried over, so dropping or renaming a column it mentions requires `batch_op.drop_constraint("ck1", type_="check")` first. - **Referencing foreign keys.** The original table is dropped, so if other tables reference it and SQLite's foreign key enforcement is on, the drop fails; that enforcement must be off while the rebuild runs. ## Controlling what is reflected The rebuilt table is only as good as the reflected one, so batch mode lets a revision correct it: - `reflect_args` overrides individual columns or constraints during reflection, for example to reflect a `Boolean` with `create_constraint=False`. - `table_args` adds constraints to the new table that reflection cannot see, such as an unnamed CHECK. - `copy_from` skips reflection entirely and uses a complete `Table` you supply, which is also what offline mode requires. ## Using batch in a revision - Group every change to one table in one block, so the table is copied once rather than once per operation. - Pass `existing_type` and the other `existing_*` arguments to `alter_column()`: a SQLite rebuild takes them from reflection, but the plain `ALTER` emitted on other databases, MySQL in particular, may need them. - Expect a full copy of the table: on a large table, a rebuild takes time proportional to the row count. - In offline `--sql` mode there is no database to reflect, so a rebuild needs `copy_from`. - Drafting revisions with batch blocks from the start is a separate autogenerate setting; this is about running and maintaining them.
- Does wrapping operations in `op.batch_alter_table()` change what PostgreSQL receives?Not with the default `recreate="auto"`. On databases other than SQLite the block emits one ordinary `ALTER TABLE` per operation, so a revision written with batch blocks runs unchanged in production. Only `recreate="always"` forces the copy-and-rename rebuild on PostgreSQL or MySQL.
- How do you keep an unnamed CHECK constraint through a batch rebuild?Pass it in `table_args`, for example `op.batch_alter_table("invoices", table_args=[CheckConstraint("amount >= 0")])`, which also silences Alembic 1.20's warning. It must be restated on every rebuild. The lasting fix is to name constraints, explicitly or with a `MetaData` naming convention, so reflection returns the name and later rebuilds carry them.
- batch_alter_table always rebuilds the table, on every database.
- SQLite supports ALTER COLUMN TYPE, so batch mode only speeds things up.
- A batch rebuild keeps every CHECK constraint automatically, named or not.
- A SQLite batch rebuild works in --sql mode with no extra arguments.
- Each operation inside the block runs immediately, one rebuild per call.
Assumes Alembic 1.20 with SQLAlchemy 2.1; Alembic 1.20 started warning when a batch rebuild omits an unnamed CHECK constraint, which earlier releases dropped silently.