sqlalchemy / sqlalchemy/alembic
conditional ddl support; add autogeneration support for `ddf_if()`
Nobody has claimed this yet.
- Dominant language
- Python
- Stars
- 4.4k
- Forks
- 375
- PR merge metrics
- No merged PRs in 30d
Description
Describe the bug
Using conditional Indices with ddl_if, alembic ignores the target dialect and creates all the indices, not just the index that matches the conditional constraint.
from sqlalchemy import Index, String
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
class Base(DeclarativeBase):
pass
class User(Base):
__tablename__ = "user"
__table_args__ = (
Index("my_pg_index_psql", "name").ddl_if(dialect="postgresql"),
Index("my_pg_index_sqlite", "name").ddl_if(dialect="sqlite"),
)
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(30))
./bin/alembic revision --autogenerate -m 'DB init'
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
INFO [alembic.autogenerate.compare] Detected added table 'user_account'
INFO [alembic.autogenerate.compare] Detected added index ''my_pg_index_psql'' on '('name',)'
INFO [alembic.autogenerate.compare] Detected added index ''my_pg_index_sqlite'' on '('name',)'
Generate python file contains two create_index statements, should be a single stmt:
def upgrade() -> None:
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('user',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=30), nullable=False),
sa.PrimaryKeyConstraint('id')
)
op.create_index('my_pg_index_psql', 'user_account', ['name'], unique=False)
op.create_index('my_pg_index_sqlite', 'user_account', ['name'], unique=False)
# ### end Alembic commands ###
Expected behavior
Only the index for the target dialect should be created, not both. Note if you use the same index name for both Index defs, you will get a single op.create_index but the configuration will be random.
To Reproduce
As above, using postgres as the db in alembic.ini:
sqlalchemy.url = postgresql://postgres:password@localhost/postgres
Versions.
- OS: Linux
- Python: 3.12.2
- Alembic: 1.13.1
- SQLAlchemy: 2.0.31
- Database: Postgres 14
- DBAPI: ?
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start with Alembic's autogenerate index comparison and the SQLAlchemy Index.ddl_if() behavior, using the provided PostgreSQL reproduction and alembic.ini configuration. Done means the generated migration contains only the index whose conditional dialect matches the target database, rather than both op.create_index statements.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- postgresql, python, sqlalchemy
- Domain
- database, tooling
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 42/100