migrate-db.py

@click.command("db-migration")
@with_appcontext
def migrate_schema():
    migrations_path = "./migrations"
    # find the database 'user_version'
    data = get_db()
    current_version = data.execute("PRAGMA user_version").fetchone()[0]

    # iterate files in schema dir and find right file to start with
    files = [
        f
        for f in os.listdir(migrations_path)
        if os.path.isfile(os.path.join(migrations_path, f))
        and int(os.path.splitext(f)[0]) > current_version
    ]

    if len(files) > 0:
        try:
            with data:
                for migration in files:
                    with open(os.path.join(migrations_path, migration)) as f:
                        data.executescript(f.read())
                data.execute(f"PRAGMA user_version = {current_version + 1}").fetchone()
        except sqlite3.Error as err:
            print(f"Could not execute script {migration}:\n{err}")
    else:
        print(f"Database schema up to date at version {current_version}")