Flask-Migrate `db upgrade` fails with "relation does not exist"

Question

I'm working in a development environment on a flask-app with a Postgres 10 database that has ~80 tables. There are lots of relationships and ForeignKeyConstraints networking it all together.

It was working fine with Flask-Migrate. I'd bootstrapped and migrated up to this point with ~80 tables. But, I wanted to test out some new scripts to seed the database tables, and thought it would be quickest to just drop the database and bring it back up again using Flask-Migrate.

In this process, the migration folder was deleted, so I just started over fresh with a db init. Then ran db migrate. I manually fixed a few imports in the migrate script. Finally, I ran db upgrade.

However, now with all these 80 create_table commands in my migrate script, when I run db_upgrade, I receive an error:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) relation "items" does not exist

I receive this error for every Child table that has a ForeignKeyConstraint if the Child table is not in an order which is below the Parent table in the migration file.

But, the autogenerated script from db migrate has the tables sorted alphabetically, ordered by table name.

Referring to documentation, I don't see this importance of sort order mentioned.

Bottom line is, it seems I'm either forced to write a script to sort all these tables in an order where the Parent table is above the Child table. Or else, just cut and paste like a jigsaw puzzle until all the tables are in the required order.

What am I missing? Is there an easier way to do this with Flask-Migrate or Alembic?

Answer 1

After researching this, it seems flask-migrate and/or Alembic does not have any built-in methods to resolve this sort order issue. I fixed it by cutting and pasting the tables in an order which ensured the Parent table was above the child tables in the migration file.

Answer 2

I've faced some problems when using flask-sqlalchemy and flask-migrate, I solved it using python interactive shell.

>>> from yourapp import db, create_app
>>> db.create_all(app=create_app())

Check this link to get more information.

Happy coding...

Answer 3

I've just encountered this myself, and could not find a better and/or official answer.

My approach was to separate the table creation from the creation of foreign key constraints:

• Edit Alembic's auto-generated migration script: In each table create operation, remove all lines creating foreign key constraints
• Run Alembic's upgrade command (tables are created, minus the FK constraints, of course)
• Run Alembic's migrate command (additional migration script created, that adds all FK constraints)
• Run Alembic's upgrade command (FK constraints added to tables)