Applying a Naming Convention to Constraints via SQLAlchemy & Alembic

Top Tip!

So we're creating a new database schema, using SQLAlchemy for the ORM and Alembic for the schema management, (if you use Python and need to manage a database sructure I highly recommend these two tools), anyway we just ran into a problem when downgrading a migration that attempted to drop an unnamed Foreign Key.

Wat?!

Why would it allow me to create a constraint that it can't drop, why? Good question, turns out that it's up to you (well me in this case) to name things.

Obviously we don't want to have to remember to do this manually everytime we generate a migration involving a constraint, that would be tedious and prone to error (I will definitely forget to do this), so what do we do?

Well Alembic has you covered, you can set a naming convetion to apply to all migrations, when declaring your Base model class (this should be default behaviour in my opinion, but I'm not hating, nothing but love for you Alembic/SQLAlchemy).

Cut to the chase and tell me how

Well like this;

from sqlalchemy import MetaData
from sqlalchemy.ext.declarative import declarative_base

meta = MetaData(
    naming_convention={
        "ix": "ix_%(column_0_label)s",
        "uq": "uq_%(table_name)s_%(column_0_name)s",
        "ck": "ck_%(table_name)s_%(constraint_name)s",
        "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
        "pk": "pk_%(table_name)s"
    }
)

Base = declarative_base(metadata=meta)

Source alembic docs for reference are here thank you alembic peeps!

So there you go - simply use the Base in your alembic env.py - and also inherit from Base in all your model classes and you're good to go

You'll see autogenerated migrations like this;

    ...
    sa.PrimaryKeyConstraint('id', name=op.f('pk_thing'))
    ...
    sa.ForeignKeyConstraint(['wotsit_id'], name=op.f('fk_thing_wotsit_id_wotsit')),
    ...

Enjoy!

Comments

[Nguyen Kim Son]

There are some length limits for constraints, foreign keys, etc in MySQL (I'm not sure if this is the case for other database types) so in some extreme case, we might need to choose the constraint name explicitly.

[Arash Fatahzade]

I have signup to just say THANK YOU <3.
Worked like charm on postgres.

EDIT: postgres naming conventions:

metadata = MetaData(                                                             
    naming_convention={                                                          
        "ix": "%(column_0_label)s_idx",                                                                                  
        "uq": "%(table_name)s_%(column_0_name)s_key",                            
        "ck": "%(table_name)s_%(constraint_name)s_check",                        
        "fk": "%(table_name)s_%(column_0_name)s_%(referred_table_name)s_fkey",   
        "pk": "%(table_name)s_pkey"                                              
    }                                                                            
)