7

I'm using SQLAlchemy's declarative system to define my mapping. Afterwards, I make sure the corresponding tables exist via

Base.metadata.create_all(engine, checkfirst=True)

If I afterwards change one of my declarative classes (for example by adding a column) and run the program again then that change is only detected when I try to commit a session including an instance of that modified class. That happens at a relatively late time during the program's runtime. I would prefer to detect that problem during startup so that I can fail early.

How can I explicitly ask SQLAlchemy to check whether the existing tables match my declarative mapping?

Note that I'm not necessarily looking for how to detect what has changed or how to perform the corresponding migration -- I just want to know whether my tables match my mapping or not.

Florian Brucker
  • 9,621
  • 3
  • 48
  • 81
  • I am not aware of a built in function that will compare your object mapping to your db schema. You may have to use [sqlalchemy inspection](https://docs.sqlalchemy.org/en/latest/orm/mapping_styles.html#runtime-introspection-of-mappings-objects) along with an information_schema query to your db to create your own comparison function. – benvc Nov 11 '18 at 00:12
  • 1
    You could have a look through the [alembic source](https://github.com/zzzeek/alembic/blob/9cb1bbf1f1f71ed2ace1c00d183e420ddc7b9648/alembic/autogenerate/api.py#L12) and see how they do it. If it's a long running script, maybe even trigger an alembic autogenerate at the beginning of your script and check the resulting revision for any changes that it has picked up.. no point reinventing the wheel! – SuperShoot Nov 11 '18 at 08:40
  • I think this is a duplicate of https://stackoverflow.com/questions/30428639/check-database-schema-matches-sqlalchemy-models-on-application-startup/30653553. There is a useful answer there. – Sven van der Burg Jan 03 '22 at 10:50

0 Answers0