Alembic: Error creating a composite index name.

[LuckyDenis]

Describe the bug
When creating a composite index, the ending is not added, but when creating a сheck сonstraint, it is added.

Expected behavior
Adding an ending when creating a composite index.

...
 sa.CheckConstraint('column_a >= 0', name=op.f('foo_column_a_cannot_be_negative_ck'))
 op.create_index('foo_pk_idx', 'foo', ['column_a', 'column_b'], unique=False)
...

To Reproduce

# model.py

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Index
from sqlalchemy import Integer
from sqlalchemy import Column
from sqlalchemy import CheckConstraint


convention = {
  'ix': '%(column_0_label)s_idx',
  'uq': '%(table_name)s_%(column_0_name)s_uq',
  'ck': '%(table_name)s_%(constraint_name)s_ck',
  'fk': '%(table_name)s_%(column_0_name)s_%(referred_table_name)s_fk',
  'pk': '%(table_name)s_pk'
}


Base = declarative_base()
Base.metadata.naming_convention = convention


class Foo(Base):
    __tablename__ = 'foo'
    __table_args__ = (
        CheckConstraint(
            'column_a >= 0',
            name='column_a_cannot_be_negative'
        ),
        Index('foo_pk', 'column_a', 'column_b')
    )

    column_a = Column(
        Integer(),
        primary_key=True,
        autoincrement=False
    )
    column_b = Column(
        Integer(),
        primary_key=True,
        autoincrement=False
    )

# migrate/versions/1615661a2772_commit_message.py
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '1615661a2772'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('foo',
    sa.Column('column_a', sa.Integer(), autoincrement=False, nullable=False),
    sa.Column('column_b', sa.Integer(), autoincrement=False, nullable=False),
    sa.CheckConstraint('column_a >= 0', name=op.f('foo_column_a_cannot_be_negative_ck')),
    sa.PrimaryKeyConstraint('column_a', 'column_b', name=op.f('foo_pk'))
    )
    op.create_index('foo_pk', 'foo', ['column_a', 'column_b'], unique=False)
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index('foo_pk', table_name='foo')
    op.drop_table('foo')
    # ### end Alembic commands ###

...
 sa.CheckConstraint('column_a >= 0', name=op.f('foo_column_a_cannot_be_negative_ck'))
 op.create_index('foo_pk', 'foo', ['column_a', 'column_b'], unique=False)
...

Versions.

• OS: Linux Mint 20.1 Cinnamon
• Python: Python 3.8.5
• SQLAlchemy: 1.4.7
• Database: PostgreSQL 12.6 (Ubuntu 12.6-0ubuntu0.20.04.1)
• DBAPI: psycopg2-binary == 2.8.6

Additional context
model.py

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Index
from sqlalchemy import Integer
from sqlalchemy import Column
from sqlalchemy import CheckConstraint


convention = {
  'ix': '%(column_0_label)s_idx',
  'uq': '%(table_name)s_%(column_0_name)s_uq',
  'ck': '%(table_name)s_%(constraint_name)s_ck',
  'fk': '%(table_name)s_%(column_0_name)s_%(referred_table_name)s_fk',
  'pk': '%(table_name)s_pk'
}


Base = declarative_base()
Base.metadata.naming_convention = convention


class Foo(Base):
    __tablename__ = 'foo'
    __table_args__ = (
        CheckConstraint(
            'column_a >= 0',
            name='column_a_cannot_be_negative'
        ),
        Index('foo_pk', 'column_a', 'column_b')
    )

    column_a = Column(
        Integer(),
        primary_key=True,
        autoincrement=False
    )
    column_b = Column(
        Integer(),
        primary_key=True,
        autoincrement=False
    )

migrate/env.py

from logging.config import fileConfig

from sqlalchemy import engine_from_config
from sqlalchemy import pool

from alembic import context
from model import Base

config = context.config

fileConfig(config.config_file_name)

target_metadata = Base.metadata

# other values from the config, defined by the needs of env.py,
# can be acquired:
# my_important_option = config.get_main_option("my_important_option")
# ... etc.


def run_migrations_offline():
    """Run migrations in 'offline' mode.

    This configures the context with just a URL
    and not an Engine, though an Engine is acceptable
    here as well.  By skipping the Engine creation
    we don't even need a DBAPI to be available.

    Calls to context.execute() here emit the given string to the
    script output.

    """
    url = config.get_main_option("sqlalchemy.url")
    context.configure(
        url=url,
        target_metadata=target_metadata,
        literal_binds=True,
        dialect_opts={"paramstyle": "named"},
    )

    with context.begin_transaction():
        context.run_migrations()


def run_migrations_online():
    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine
    and associate a connection with the context.

    """
    connectable = engine_from_config(
        config.get_section(config.config_ini_section),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )

    with connectable.connect() as connection:
        context.configure(
            connection=connection, target_metadata=target_metadata
        )

        with context.begin_transaction():
            context.run_migrations()


if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()

migrate/script.py.mako

"""${message}

Revision ID: ${up_revision}
Revises: ${down_revision | comma,n}
Create Date: ${create_date}

"""
from alembic import op
import sqlalchemy as sa
${imports if imports else ""}

# revision identifiers, used by Alembic.
revision = ${repr(up_revision)}
down_revision = ${repr(down_revision)}
branch_labels = ${repr(branch_labels)}
depends_on = ${repr(depends_on)}


def upgrade():
    ${upgrades if upgrades else "pass"}


def downgrade():
    ${downgrades if downgrades else "pass"}

alembic.ini

[alembic]
script_location = migrate

prepend_sys_path = .

output_encoding = utf-8

sqlalchemy.url = postgresql+psycopg2://postgres:postgres@localhost/dbtest


[post_write_hooks]

[loggers]
keys = root,sqlalchemy,alembic

[handlers]
keys = console

[formatters]
keys = generic

[logger_root]
level = WARN
handlers = console
qualname =

[logger_sqlalchemy]
level = WARN
handlers =
qualname = sqlalchemy.engine

[logger_alembic]
level = INFO
handlers =
qualname = alembic

[handler_console]
class = StreamHandler
args = (sys.stderr,)
level = NOTSET
formatter = generic

[formatter_generic]
format = %(levelname)-5.5s [%(name)s] %(message)s
datefmt = %H:%M:%S

requirements.txt

alembic==1.5.8
greenlet==1.0.0
Mako==1.1.4
MarkupSafe==1.1.1
psycopg2-binary==2.8.6
python-dateutil==2.8.1
python-editor==1.0.4
six==1.15.0
SQLAlchemy==1.4.7

Have a nice day!

[zzzeek]

Describe the bug
When creating a composite index, the ending is not added, but when creating a сheck сonstraint, it is added.

Expected behavior
Adding an ending when creating a composite index.

Hi -

this looks to be an alembic issue, but also I don't have any understanding of what the "error" is. can you please indicate in more concrete terms what it is you refer towards as the "ending", what commands you are running, and very explicitly what it is you are observing? thanks

[CaselIT]

I've migrated to alembic. At worst we can migrate it back

[LuckyDenis]

Describe the bug
When creating a composite index, the ending is not added, but when creating a сheck сonstraint, it is added.
Expected behavior
Adding an ending when creating a composite index.

Hi -

this looks to be an alembic issue, but also I don't have any understanding of what the "error" is. can you please indicate in more concrete terms what it is you refer towards as the "ending", what commands you are running, and very explicitly what it is you are observing? thanks

When we create a migration, an ending is added to the name.
For example.

class Foo(Base):
    __tablename__ = 'foo'
    __table_args__ = (
        CheckConstraint( 'column_a >= 0', name='check_column_a')
    )
   column_a = Column(
        Integer(),
        primary_key=True,
        autoincrement=False
    )

# When use alembic. generate this.
# sa.CheckConstraint('column_a >= 0', name=op.f('check_column_a_ck'))   check_column_a_ck ->  add tail: _ck

For the indexes specified in this way, such an index is added.

For example.

class Foo(Base):
    __tablename__ = 'foo'
    column_a = Column(
        Integer(),
        primary_key=True,
        autoincrement=False,
        index=True
    )

# When use alembic. generate this.
# op.create_index(op.f('foo_column_a_ix'), 'foo', ['foo'], unique=False)  foo_column_a_ix -> add tail: _ix

But if we do this with arguments using the Index wrapper, then the ending is not added.
For example.

class Foo(Base):
    __tablename__ = 'foo'
    __table_args__ = (
        Index('foo_column_a_and_column_b', 'column_a', 'column_b')
    )

    column_a = Column(
        Integer(),
        primary_key=True,
        autoincrement=False
    )
    column_b = Column(
        Integer(),
        primary_key=True,
        autoincrement=False
    )

# When use alembic. generate this.
# op.create_index('foo_column_a_and_column_b', 'foo', ['column_a', 'column_b'], unique=False)
# foo_column_a_and_column_b -> add tail: None

[zzzeek]

OK so if I can restate your issue, the naming convention is not being applied to index objects when autogenerate renders the Python code.

[LuckyDenis]

OK so if I can restate your issue, the naming convention is not being applied to index objects when autogenerate renders the Python code.

Yes. Thank you)

[zzzeek]

OK so what is happening here is your check constraint naming convention includes that it should render with the name of the constraint embedded:

  'ck': '%(table_name)s_%(constraint_name)s_ck'

however the constraint you're using for the index does not:

 'ix': '%(column_0_label)s_idx',

what this means is that because you are giving the Index an explicit name, that supersedes using the naming convention name. Only if the convention contains the %(constraint_name)s token will both strings be combined together.

so to achieve both names combined with Index, include that in your convention:

{"ix": "%(column_0_label)s_%(constraint_name)s_idx"}