Alembic - Migrations for SQLalchemy (and Flask)
What I recently came across, was the problem that I could not run database upgrades without a lot of pain. Everytime I wanted to change something on the production server or move stuff from the development to another server, I had either to reset the database (only an option when I was working on the development server) or edit the tables manually, which is error-prone and very uncomfortable.
So I did a little research and came across sqlalchemy-migrate and afterwards across the successor alembic.
Alembic is pretty easy to setup and with a few modifications easily integrated to flask. Run
alembic init in your home directory and edit the
alembic.ini. Then replace the
env.py with the following and adapt it to your needs:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 from __future__ import with_statement import os import sys from alembic import context from sqlalchemy import engine_from_config, pool from logging.config import fileConfig sys.path.append(os.getcwd()) from adipositas import app, db # this is the Alembic Config object, which provides # access to the values within the .ini file in use. config = context.config # set the sqlalchemy url to the one defined for flask-sqlalchemy config.set_main_option('sqlalchemy.url', app.config['SQLALCHEMY_DATABASE_URI']) # Interpret the config file for Python logging. # This line sets up loggers basically. fileConfig(config.config_file_name) # add your model's MetaData object here # for 'autogenerate' support # from myapp import mymodel # target_metadata = mymodel.Base.metadata target_metadata = db.Model.metadata # other values from the config, defined by the needs of env.py, # can be acquired: # my_important_option = config.get_main_option("my_important_option") # ... etc. def run_migrations_offline(): """Run migrations in 'offline' mode. This configures the context with just a URL and not an Engine, though an Engine is acceptable here as well. By skipping the Engine creation we don't even need a DBAPI to be available. Calls to context.execute() here emit the given string to the script output. """ url = config.get_main_option("sqlalchemy.url") context.configure(url=url) with context.begin_transaction(): context.run_migrations() def run_migrations_online(): """Run migrations in 'online' mode. In this scenario we need to create an Engine and associate a connection with the context. """ engine = engine_from_config( config.get_section(config.config_ini_section), prefix='sqlalchemy.', poolclass=pool.NullPool) connection = engine.connect() context.configure( connection=connection, target_metadata=target_metadata ) try: with context.begin_transaction(): context.run_migrations() finally: connection.close() if context.is_offline_mode(): run_migrations_offline() else: run_migrations_online()
Afterwards you can use automatic migrations for simple stuff and afterwards adjust the automatic migration to use more complex stuff.
1 alembic revision -m "<your commit message here>" --autogenerate
Adapt the generated file and afterwards run:
1 alembic upgrade head
Which will then change your database. Very easy, actually. You can even perform actions on your data while migrating.
I like alembic already and I am considering to add an automatic
alembic upgrade head to a git hook on the staging server, to make sure that the database layout is always up-to-date and no errors occur.