postgrails   >   20200915-alembicize  

Alembicize: Move an existing database to alembic

published 2020-09-15

For a project I'm working on, I want to begin managing an existing, deployed database with alembic, using it to migrate the database going forward. Here's the procedure I followed:

  1. Write SQLAlchemy tables and an initial alembic revision that represents the current state of the database ('existing'). This is what we are migrating from.

    The simplest way to generate the existing migration is just to dump the database schema and all stored procedures etc as a SQL script. With PostgreSQL, doing this is a matter of running the following command:

    pg_dumpall -O -s [existing-database] >existing-database.sql

    Now you can create an "existing" alembic revision:

    alembic revision -m existing

    and edit the revision to load the stored sql (move the existing-database.sql into the alembic revisions folder):

    # op.execute is an alembic method to call sql
    with open('existing-database.sql`) as file:

    This revision will serve to bring a new database up to the state of the existing application.

  2. Stamp the database with the 'current state' revision. Inside PACKAGE/alembic folder, I created the following script to "stamp" the database with a revision:

    import os
    from pathlib import Path
    import click
    from alembic import command
    from alembic.config import Config
    PACKAGE_PATH = Path(os.path.abspath(__file__)).parent.parent
    def stamp_revision(rev_id):
        config = Config(PACKAGE_PATH / 'alembic.ini')
        config.set_main_option('sqlalchemy.url', os.getenv('DATABASE_URL'))
        command.stamp(config, rev_id)
    if __name__ == '__main__':

    This script can be run inside the project virtualenv or container. Assuming that the desired revision is the latest one:

    $ python alembic/ head
  3. Create SQL Alchemy table definitions that describe the database-as-it-is, and use alembic revision --autogenerate to test your definitions: When autogenerate doesn't see any changes, your table definitions match your database, congratulations! Don't apply the autogenerated revisions. Instead, delete each autogenerated revision after creating it.