If you need to setup database migrations that work both locally and on Heroku in Python, these steps may be helpful.
For a recent project, I wanted to be able to manage the database schema. I’m not using an ORM, however the migration features in SQLAlchemy turn out to be quite useful.
Install the SQLAlchemy migration package, by adding the following to requirements.txt.
# Migrations sqlalchemy-migrate>=0.7.2
pip install -r requirements.txt
Create a repository for database migrations
migrate create db "Cihui"
This creates a directory
db/where the code and migrations will reside.
Update the migrate script to use Heroku DB url
#!/usr/bin/env python from migrate.versioning.shell import main import os if __name__ == '__main__': db_url = os.environ.get('DATABASE_URL', 'postgresql://localhost:5432/cihui') db_url = db_url.replace('postgres:', 'postgresql:', 1) main(url=db_url, debug='False', repository='db')
This should allow you to run migrations locally or on Heroku. There was a warning message on Heroku for the url starting with postgres rather than postgresql, thus the
This sets up a few useful targets to run on Heroku and locally using foreman.
db_init: python db/manage.py version_control db_version: python db/manage.py db_version migrate: python db/manage.py upgrade
To migrate locally
foreman run init_db # first time foreman run migrate
To run on Heroku
heroku run db_init heroku run migrate
Write migrations as SQL
I prefer to manually write the migrations, and SQLAlchemy has great support for this. Refer to their docs for details.
python db/manage.py script_sql postgresql 'add list table'
This generates an upgrade and downgrade sql script in
db/versionswith an appropriate version number.
Test the migration:
python db/manage.py test
Runs upgrade and then downgrade on a single version.
DB access to Heroku
Console access is available via:
Useful for checking specific values or fixing broken migrations.
I’m still iterating on this project, so may change some things around as I go. For the moment, this allowed me to manage the database schema easily in multiple databases.