SQLite STRICT is not propagated in batch mode

[david-fed]

Hi,

SQLite supports the STRICT and WITHOUT ROWID keywords at the end of a table definition. These keywords are correctly applied in the initial migration but removed in subsequent migrations when the table is being recreated even though the model still contains these keywords.

Specs:

• Windows 11
• Python 3.14.0
• alembic 1.17.2
• sqlalchemy 2.0.44
• SQLite 3.45.3

Sample model:

from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
import sqlalchemy as sa


class Base(DeclarativeBase):
    type_annotation_map = {str: sa.TEXT}


class User(Base):
    __tablename__ = "USER"
    __table_args__ = {"sqlite_with_rowid": False, "sqlite_strict": True}

    id: Mapped[int] = mapped_column(primary_key=True)
    user_name: Mapped[str] = mapped_column("USER_NAME", unique=True)

Steps to reproduce:

1. alembic revision --autogenerate

"""empty message

Revision ID: f7f7a256d2e7
Revises: 
Create Date: 2025-11-27 10:57:03.994674

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision: str = 'f7f7a256d2e7'
down_revision: Union[str, Sequence[str], None] = None
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    """Upgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('USER',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('USER_NAME', sa.TEXT(), nullable=False),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('USER_NAME'),
    sqlite_strict=True,
    sqlite_with_rowid=False
    )
    # ### end Alembic commands ###


def downgrade() -> None:
    """Downgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('USER')
    # ### end Alembic commands ###

2. alembic upgrade head
3. sqlite3 ./test.db

sqlite> .schema USER
CREATE TABLE IF NOT EXISTS "USER" (
        id INTEGER NOT NULL,
        "USER_NAME" TEXT NOT NULL,
        PRIMARY KEY (id),
        UNIQUE ("USER_NAME")
)
 WITHOUT ROWID,
 STRICT

;

4. Comment out the user_name column in the model
5. alembic revision --autogenerate

"""empty message

Revision ID: 1bfe725a1b85
Revises: e097f33239b9
Create Date: 2025-11-27 10:51:10.369671

"""
"""empty message

Revision ID: bf91adff8af4
Revises: f7f7a256d2e7
Create Date: 2025-11-27 10:57:39.134370

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision: str = 'bf91adff8af4'
down_revision: Union[str, Sequence[str], None] = 'f7f7a256d2e7'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    """Upgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('USER', schema=None) as batch_op:
        batch_op.drop_column('USER_NAME')

    # ### end Alembic commands ###


def downgrade() -> None:
    """Downgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('USER', schema=None) as batch_op:
        batch_op.add_column(sa.Column('USER_NAME', sa.TEXT(), nullable=False))

    # ### end Alembic commands ###

7. alembic upgrade head
8. sqlite3 ./test.db

sqlite> .schema USER
CREATE TABLE IF NOT EXISTS "USER" (
        id INTEGER NOT NULL,
        PRIMARY KEY (id)
);

Actual result: Keywords STRICT and WITHOUT ROWID were lost on the table after the 2nd migration which was supposed to only drop a column
Expected result: Keywords STRICT and WITHOUT ROWID stay on the table unless removed in the model

[zzzeek]

hi -

I would classify this as a bug in batch mode, as it needs to recreate the table with all original options.

[zzzeek]

however, batch mode relies upon table reflection to do this, and it's not clear if STRICT is coming back. So re-state the options in batch_alter_table:

def upgrade() -> None:
    """Upgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('USER', schema=None, table_kwargs=dict(sqlite_strict=True, sqlite_with_rowid=False)) as batch_op:
        batch_op.drop_column('USER_NAME')

that makes this more of of an autogenerate rendering issue, since when autogenerate writes out the batch_op, it does know these options and should re-render them. but you can use the above for now.

[david-fed]

Thanks! Workaround works. Can we get an issue in sqlalchemy / alembic to fix this bug?

[zzzeek]

the ModifyTableOps object doesn't carry around the actual Table because it's usually intended to render "ALTER TABLE". "batch" mode was an added-on feature that I only added after people hounded me for many years to add it (I think the real bug is that SQLite doesnt support proper ALTER TABLE). It would be a more involved change since it needs to alter the structure of ModifyTableOps to carry around these options since and also all the autogenerate "compare" cases would need to locate the correct table and pass it in. im not opposed to someone working on this though.

if you're using STRICT for all tables you can also just force it on using a simple rewriter recipe.

[CaselIT]

i think it makes sense to open an issue to track this

[zzzeek]

please proceed!

[david-fed]

Thank you both! I'll certainly tryout the rewriter as all my tables are strict.
In case somebody stumbles across this discussion, this is the issue: #1758