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:
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.
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
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.