skip to content

On SQLite, why does an Alembic autogenerated op.alter_column() fail at upgrade, and what does render_as_batch=True change in the draft?

level: middleimportance: should knowfreq 35%

answer

  1. SQLite's very limited ALTER TABLE
  2. move and copy
  3. a context.configure() option, not a CLI flag
  4. with op.batch_alter_table(...) as batch_op

basics

~10 s

SQLite's ALTER TABLE cannot change a column's type, nullability or constraints, so a plain op.alter_column() fails. render_as_batch=True makes autogenerate wrap table changes in op.batch_alter_table(), which on SQLite rebuilds the table and copies the rows.

solid answer

~40 s

Autogenerate drafts `op.alter_column('invoice', 'amount', type_=sa.Numeric(precision=12, scale=2), ...)`, Alembic emits `ALTER TABLE invoice ALTER COLUMN amount TYPE NUMERIC(12, 2)`, and SQLite answers `near "TYPE": syntax error`, because its ALTER TABLE has no form that changes an existing column's type. Passing `render_as_batch=True` to `context.configure()` in env.py makes autogenerate render table-level changes inside `with op.batch_alter_table('invoice', schema=None) as batch_op:`. On SQLite the batch block runs "move and copy": create a new table with the target shape, copy the rows with INSERT ... SELECT, drop the old table and rename the new one. On other backends the same block emits ordinary ALTER statements, so one migration history serves a SQLite test database and a PostgreSQL production database.

code

plaintext · 2 lines
plaintext
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "TYPE": syntax error
[SQL: ALTER TABLE invoice ALTER COLUMN amount TYPE NUMERIC(12, 2)]

go deeper

for a junior

Remember that SQLite cannot alter a column's type or nullability, and that render_as_batch=True in env.py's context.configure() makes drafts that work there.

for a middle

Explain move and copy, why batch blocks emit plain ALTERs on other backends, and that only drafts made after the flag is set are affected.

for a senior

Show you would spot a half-applied revision on SQLite, check that constraints are named before a rebuild, and restate unnamed CHECKs in the batch's table_args.

for a principal

Weigh SQLite for local and test runs against the extra migration rendering and the risk that SQLite rebuilds hide problems the production database would show.

## The failure SQLite has deliberately limited support for `ALTER TABLE`. It can add a column and rename a table, and recent versions can rename or drop a column, but it cannot **change a column's type or nullability, or add or drop a constraint** on an existing table. Autogenerate does not know which database a revision will later run on, so by default it drafts the generic operation: ```python op.alter_column('invoice', 'amount', existing_type=sa.INTEGER(), type_=sa.Numeric(precision=12, scale=2), existing_nullable=False) ``` At `upgrade`, Alembic's SQLite implementation emits `ALTER TABLE invoice ALTER COLUMN amount TYPE NUMERIC(12, 2)` and the driver raises `sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "TYPE": syntax error`. There is a second trap. Alembic treats SQLite DDL as **non-transactional**; it says so at start-up with `Will assume non-transactional DDL.` Operations that ran before the failing one in the same revision stay applied, while the version table still names the previous revision. The database is now half-migrated, and the next autogenerate run drafts only what is left. ## What render_as_batch changes `render_as_batch` is an option of `context.configure()` in `env.py`, default `False`. Set to `True`, autogenerate renders every command that alters elements within a table inside a **batch block**: ```python with op.batch_alter_table('invoice', schema=None) as batch_op: batch_op.alter_column('amount', existing_type=sa.INTEGER(), type_=sa.Numeric(precision=12, scale=2), existing_nullable=False) ``` The operations inside the block lose their table-name argument; `op.batch_alter_table()` supplies it. Creating or dropping whole tables stays outside any batch block. ## What a batch block does at upgrade On SQLite, the batch performs **move and copy**: 1. reflect the existing table from the database; 2. `CREATE TABLE _alembic_batch_temp` with the new structure; 3. `INSERT INTO _alembic_batch_temp (...) SELECT ... FROM invoice`; 4. `DROP TABLE invoice`, then `ALTER TABLE _alembic_batch_temp RENAME TO invoice`. With the default `recreate="auto"`, SQLite skips the rebuild only when the block holds nothing beyond plain column additions and index changes; anything else rebuilds the table. On any other backend the same block emits the **ordinary ALTER statements**, as if there were no batch. That is why Alembic's documentation calls `render_as_batch=True` safe to use in all cases, and why a project whose tests run on SQLite and whose production runs on PostgreSQL can keep one migration history. ## Where the setting goes, and the alternatives | Setup | env.py setting | |---|---| | SQLite only | `render_as_batch=True` | | PostgreSQL or MySQL only | leave the default `False` | | SQLite in tests, another database in production | `True`, or `render_as_batch=connection.dialect.name == "sqlite"` | A test suite that builds its SQLite schema by running the migrations, rather than calling `create_all()`, is usually where a missing flag is discovered. The flag only affects how **new** drafts are rendered. Revisions drafted before it was set keep their plain `op.alter_column()` calls and have to be rewritten by hand to run on SQLite. ## Reading the rendered operations Inside the batch block, `batch_op.alter_column()` still carries `existing_type`, `existing_nullable` and, where relevant, `existing_server_default`. On SQLite the rebuild takes the full column definition from reflection; on MySQL those arguments are what lets Alembic restate the column correctly. When you hand-edit a batch draft, keep them. A batch block groups every change to one table, so a draft that touches three columns of `invoice` rebuilds the table once rather than three times. Changes to different tables get one block each. ## Caveats to review in a batch draft - **Constraints need names.** The rebuild reflects the old table; an unnamed constraint on SQLite cannot be targeted by a drop inside the batch, which is one more reason for a naming convention. - **Unnamed CHECK constraints** on the reflected table are not carried into the rebuilt table; they have to be restated through the batch's `table_args` parameter. - **Offline mode**: autogenerate does not render the `copy_from` table that offline (`--sql`) batch migrations need. - **Cost**: on SQLite a rebuild copies every row, which matters for a large local database. The batch directive has more options of its own; the point for autogenerate is the single env.py flag that makes drafts run on SQLite at all.

  • Is render_as_batch=True safe when production runs PostgreSQL?
    Yes. With the default `recreate="auto"`, a batch block rebuilds the table only on SQLite; on PostgreSQL it emits the ordinary `ALTER TABLE` statements. The draft reads slightly differently, with operations under `batch_op`, but runs the same SQL there.
  • A revision failed halfway on SQLite. Why are some of its changes already in the database?
    Alembic assumes SQLite DDL is non-transactional and logs "Will assume non-transactional DDL." at start-up. Statements before the failing one stay applied, while the version table still names the previous revision. You repair the schema or rebuild the database before rerunning, and autogenerate will meanwhile draft only the changes not yet applied.

  • SQLite supports ALTER COLUMN, so the syntax error must be a driver bug.
  • render_as_batch makes PostgreSQL copy every table on each migration.
  • render_as_batch is a flag passed to alembic upgrade on the command line.
  • Batch mode carries every constraint into the rebuilt table, unnamed CHECKs included.
  • Turning on render_as_batch also fixes revisions that were drafted before it.