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:
       op.execute(file.read())
   

   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
   
   
   @click.command()
   @click.argument('rev_id')
   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__':
       stamp_revision()
   

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

   $ python alembic/stamp.py 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.