The database migrations for the desktop application with Alembic

Stack: Python, PyInstaller, Sqlite, Alembic

This short post shows how to perform an application database upgrade with new version of the application. It covers also the problem of the application packaging.

Database migration are performed with Alembic library. The database is checked and upgraded (if needed) during application startup. Migration files are distributed with the release (exe/rpm) of the application.

First install the alembic library for the database migration, and init in inside your project:

$ pip install alembic
$ alembic init alembic

Update the PyInstaller .spec file to include the migration in the new package:

a = Analysis( 
    datas=[('alembic/env.py', 'alembic'), 
    ('alembic/versions/*', 'alembic/versions'), 
    ('alembic.ini', '.')],

Comment lines in generated alembic/env.py, to work properly in the programmatic database migration approach:

# from logging.config import fileConfig
...
# fileConfig(config.config_file_name)

Implement the upgrade of the database during application startup:

def upgrade_db():
    alembic_cfg = Config('alembic.ini')
    alembic_cfg.set_main_option('script_location', str(Path(get_app_global_path()) / 'alembic'))
    alembic_cfg.set_main_option('sqlalchemy.url', get_db_url())
    command.upgrade(alembic_cfg, 'head')

if __name__ == "__main__":
    upgrade_db()

This approach was used implementing the Savings project.