sqlalchemy / sqlalchemy/alembic

conditional ddl support; add autogeneration support for `ddf_if()`

Open
#1,491 12 comments 5 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

autogenerate - detection autogenerate - rendering
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

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.