SQLite STRICT is not propagated in batch mode

[CaselIT]

Discussed in #1756

^{Originally posted by david-fed November 27, 2025}
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