Checking if the DB is exactly same

[AbdealiLoKo]

I have 2 cases I solve with sqla+alembic ...

1. I am doing a new install of my software: Here i just do metadata.create_all()
2. I am doing an upgrade of my software: Here I do db upgrade

I have found over time, that there are small differences that creep up.
For example, if I rename a table in my model. And use db migrate - it generates op.rename_table()
But renaming the table does not change my constraint names like fk_{table_name}_{col1} or pk_{table_name}

So, I have found that if I later do something like drop_constraint('...') I don't know what the name of the constraint is !
Because:

• With create_all() - the constraint is: pk_new_table_name
• With db upgrade - the constraint is: pk_old_table_name

I assume this is a common issue everyone faces ?
I am trying to understand what is the best practice here

A solution I have in mind is in my CI pipeline I can run both these commands and then "compare" that the 2 generated DBs are exactly same - constraints and all ?

[zzzeek]

Alembic doesnt detect renames of tables, it would see this as a drop of the old table and a create of the new one. to convert that into rename_table you would do these manually. so I don't know what db migrate is, that's not us.

if you want to drop and recreate the constraints, the new table definition would illustrate all the new constraint names so you could just hand-substitute in the old constraint name to write out the drops / adds. or, a second run of autogenerate after you've done "rename_table" would likely also generate these automatically, you could run this on a test database to create two migrations and then merge them together.

I think overall with apps that are already in production, a straight table rename that isn't actually making a new table to copy data into is just not a common operation, if the app is in production, hopefully the table names are set.