Flask-migrate: Migrate model with multi-column UniqueConstraint

[felipejfc]

Hi, I'm trying to create a migration for the following model:

class Function(db.Model):
    __tablename__ = 'function'
    __table_args__ = tuple(UniqueConstraint('name', 'namespace', 'revision',
                           name='name_namespace_revision_unique_constraint'))

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(254), nullable=False)
    code = db.Column(db.Text, nullable=False)
    namespace = db.Column(db.String(254), default="all", nullable=False)
    revision = db.Column(db.String(65), nullable=False)
    created_at = db.Column(db.Date, default=_get_date)
    updated_at = db.Column(db.Date, onupdate=_get_date)

    def __init__(self, name, namespace, code, revision):
        self.name = name
        self.namespace = namespace
        self.code = code
        self.revision = revision

Flask-Migrate is generating the following migration for this model:

"""initial migration

Revision ID: 0485d7255905
Revises: 
Create Date: 2017-03-01 17:16:07.538631

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '0485d7255905'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('function',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=254), nullable=False),
    sa.Column('code', sa.Text(), nullable=False),
    sa.Column('namespace', sa.String(length=254), nullable=False),
    sa.Column('revision', sa.String(length=65), nullable=False),
    sa.Column('created_at', sa.Date(), nullable=True),
    sa.Column('updated_at', sa.Date(), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('function')
    # ### end Alembic commands ###

note that the UniqueConstraint name_namespace_revision_unique_constraint is not being generated in the migration.

Aditional info:
I'm using Flask-SQLAlchemy and python 3.6
If I manually add the uniqueconstraint creation in the migration, the next time I do db migrate a migration deleting this unique constraint is generated.

Any hints?

[miguelgrinberg]

Supposedly Alembic should pick those unique constraints up, even if they are across multiple columns. Could be a bug, you should report it on the Alembic issues board: https://bitbucket.org/zzzeek/alembic/issues.

[ionull]

Instead of defining on model, I finally create one more revision to handle multi-column unique constraint:
flask db revision or ./manager.py db revisioon
then put codes in the new revision file
on upgrade:
op.create_unique_constraint("uq_name", "table_name", ["col1", "col2"])
on downgrade:
op.drop_constraint("uq_name", "table_name")
At least, this works:)

[mradamyee]

The issue is python grammar issue, the __table_args__ should be

__table_args__ = tuple([UniqueConstraint('name', 'namespace', 'revision', name='name_namespace_revision_unique_constraint')])
or
__table_args__ = (UniqueConstraint('name', 'namespace', 'revision', name='name_namespace_revision_unique_constraint'),)

[mradamyee]

@miguelgrinberg This issue should be closed

[FriedrichSal]

In my case the uniqueness constraints are being picked up by alembic on table creation. But thereafter any changes to the constraint seem to be ignored. Any hints highly appreciated.

[balmasi]

The issue is python grammar issue, the table_args should be

__table_args__ = tuple([UniqueConstraint('name', 'namespace', 'revision', name='name_namespace_revision_unique_constraint')])
or
__table_args__ = (UniqueConstraint('name', 'namespace', 'revision', name='name_namespace_revision_unique_constraint'),)

The first one worked for me but not the second one!