In the Open Event Server, in the pull requests, if there is some change in the sqlalchemy model, sometimes proper migrations for the same are missed in the PR.
The first approach to check whether the migrations were up to date in the database was with the following health check function:
from subprocess import check_output def health_check_migrations(): """ Checks whether database is up to date with migrations, assumes there is a single migration head :return: """ head = check_output(["python", "manage.py", "db", "heads"]).split(" ")[0] if head == version_num: return True, 'database up to date with migrations' return False, 'database out of date with migrations'
In the above function, we get the head according to the migration files as following:
head = check_output(["python", "manage.py", "db", "heads"]).split(" ")[0]
The table alembic_version contains the latest alembic revision to which the database was actually upgraded. We can get this revision from the following line:
version_num = (db.session.execute('SELECT version_num from alembic_version').fetchone())['version_num']
Then we compare both of the given heads and return a proper tuple based on the comparison output.While this method was pretty fast, there was a drawback in this approach. If the user forgets to generate the migration files for the the changes done in the sqlalchemy model, this approach will fail to raise a failure status in the health check.
To overcome this drawback, all the sqlalchemy models were fetched automatically and simple sqlalchemy select queries were made to check whether the migrations were up to date.
Remember that a raw SQL query will not serve our purpose in this case as you’d have to specify the columns explicitly in the query. But in the case of a sqlalchemy query, it generates a SQL query based on the fields defined in the db model, so if migrations are missing to incorporate the said change proper error will be raised.
We can accomplish this from the following function:
def health_check_migrations(): """ Checks whether database is up to date with migrations by performing a select query on each model :return: """ # Get all the models in the db, all models should have a explicit __tablename__ classes, models, table_names = [], [], [] # noinspection PyProtectedMember for class_ in db.Model._decl_class_registry.values(): try: table_names.append(class_.__tablename__) classes.append(class_) except: pass for table in db.metadata.tables.items(): if table[0] in table_names: models.append(classes[table_names.index(table[0])]) for model in models: try: db.session.query(model).first() except: return False, '{} model out of date with migrations'.format(model) return True, 'database up to date with migrations'
In the above code, we automatically get all the models and tables present in the database. Then for each model we try a simple SELECT query which returns the first row found. If there is any error in doing so, False, ‘{} model out of date with migrations’.format(model) is returned, so as to ensure a failure status in health checks.
Related:
- Alembic: http://alembic.zzzcomputing.com/en/latest/
- Alembic migrations quick start: https://michaelheap.com/alembic-python-migrations-quick-start/