0

I've been struggling with this issue on and off for quite some time, and strangely could not find a straightforward question/answer combo on this on SO. Related questions here and here. I finally found a solution so I will ask and answer my own question.

In Flask SQLAlchemy (and regular SQLAlchemy), you can have a column like this:

class Character(db.model):
  background_id = db.Column(db.Integer, db.ForeignKey('backgrounds.id'))

When you run flask db migrate, or alembic revision --autogenerate, this will result in an operation that looks like this:

def upgrade():
  op.create_foreign_key(None, 'characters', 'backgrounds', ['background_id'], ['id'])

def downgrade():
  op.drop_constraint(None, 'characters', type_='foreignkey')

The None here is bad. In fact, if you try to downgrade later, this will always fail, because drop_constraint needs the name of the constraint.

You can change this every time you generate a migration, like this:

def upgrade():
  op.create_foreign_key('fk_characters_backgrounds', 'characters', 'backgrounds', ['background_id'], ['id'])

def downgrade():
  op.drop_constraint('fk_characters_backgrounds', 'characters', type_='foreignkey')

Which works!

But if you're like me, you don't want to have to remember to do this every time you autogenerate a revision with a foreign key.

So the question is, how can we make this automatic?

Willow
  • 1,132
  • 5
  • 20

1 Answers1

0

There is an answer to this question in the best practices suggested here, at the end of the section on The Importance of Naming Conventions. The solution is to add a naming_convention to your sqlalchemy metadata, like this:

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"
}

metadata = MetaData(naming_convention=convention)

More specifically, with Flask-SQLAlchemy, do this when initializing your db:

from sqlalchemy import MetaData

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"
}

db = SQLAlchemy(metadata=MetaData(naming_convention=convention))

And voila! If you run autogenerate, you'll get this:

def upgrade():
  op.create_foreign_key(op.f('fk_characters_background_id_backgrounds'), 'characters', 'backgrounds', ['background_id'], ['id'])

def downgrade():
  op.drop_constraint(op.f('fk_characters_background_id_backgrounds'), 'characters', type_='foreignkey')

Thanks (unsurprisingly) to Miguel Grinberg, creator of Flask Migrate, for having linked to the correct page in the Alembic docs that finally allowed me to solve this problem! Someone had asked about this in an issue on Flask Migrate GitHub, and he correctly pointed out that this was an Alembic issue, not a Flask Migrate issue.

Willow
  • 1,132
  • 5
  • 20