Flask-Migrate: Alembic database migration wrapper for Flask

Posted by

on under

In this post I introduce you to Flask-Migrate, a new database migration handler for Flask based on Alembic that I just made public.

Is a New Extension Necessary?

If you read the database chapter of my Mega-Tutorial, you know that I have chosen sqlalchemy-migrate for database migrations.

I liked sqlalchemy-migrate back then (I still do, actually), but its development appears to have halted completely. Support for SQLAlchemy 0.8.x has not been implemented yet, six months past the 0.8.0 release.

On the other side, since I wrote my migration Mega-Tutorial chapter Alembic has gained notoriety. Alembic is written by zzzeek (Mike Bayer), who is the author of SQLAlchemy. He is actively developing Alembic on bitbucket.

There is an extension called Flask-Alembic out there that has many similarities to mine, but that project also appears to have stalled, there haven't been any commits or messages from the developers in several months. The project was never made available on the Python Package Index (PyPI), so while it is possible to install directly from git, that is less ideal, and might be a deal breaker for some.

That is why I have decided to write Flask-Migrate. Out of respect for the Flask-Alembic project I decided to use a different name on PyPI, in case they ever decide to resume work on their project and publish it.

Using Flask-Migrate

Flask-Migrate provides a set of command line options that attach to Flask-Script.

To install the extension you use pip as usual:

$ pip install flask-migrate

As part of the installation you will also get Flask, Flask-SQLAlchemy and Flask-Script.

Below is a sample application that initializes Flask-Migrate and registers it with Flask-Script. As is typically the case with Flask-Script, the script is called manage.py:

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
from flask.ext.script import Manager
from flask.ext.migrate import Migrate, MigrateCommand

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///app.db'

db = SQLAlchemy(app)
migrate = Migrate(app, db)

manager = Manager(app)
manager.add_command('db', MigrateCommand)

class User(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    name = db.Column(db.String(128))

if __name__ == '__main__':
    manager.run()

When you run the application you get an additional db option in the command line (you can call it differently if you want, of course):

$ python manage.py --help
usage: manage.py [-h] {shell,db,runserver} ...

positional arguments:
  {shell,db,runserver}
    shell               Runs a Python shell inside Flask application context.
    db                  Perform database migrations
    runserver           Runs the Flask development server i.e. app.run()

optional arguments:
  -h, --help            show this help message and exit

The db command exposes most of the Alembic options:

$ python manage.py db --help
usage: Perform database migrations

positional arguments:
  {upgrade,migrate,current,stamp,init,downgrade,history,revision}
    upgrade             Upgrade to a later version
    migrate             Alias for 'revision --autogenerate'
    current             Display the current revision for each database.
    stamp               'stamp' the revision table with the given revision;
                        dont run any migrations
    init                Generates a new migration
    downgrade           Revert to a previous version
    history             List changeset scripts in chronological order.
    revision            Create a new revision file.

optional arguments:
  -h, --help            show this help message and exit

To add migration support to your database you just need to run the init command:

$ python manage.py db init
  Creating directory /home/miguel/app/migrations...done
  Creating directory /home/miguel/app/migrations/versions...done
  Generating /home/miguel/app/alembic.ini...done
  Generating /home/miguel/app/migrations/env.py...done
  Generating /home/miguel/app/migrations/env.pyc...done
  Generating /home/miguel/app/migrations/README...done
  Generating /home/miguel/app/migrations/script.py.mako...done
  Please edit configuration/connection/logging settings in
  '/home/miguel/app/migrations/alembic.ini' before proceeding.

Note that you should replace manage.py with the name of your launch script if you used a different name.

When you use Alembic alone you have to edit a couple of configuration files, but Flask-Migrate handles all that for you. When the init command completes you will have a migrations folder with the configuration files ready to be used.

To issue your first migration you can run the following command:

$ python manage.py db migrate
INFO  [alembic.migration] Context impl SQLiteImpl.
INFO  [alembic.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate] Detected added table 'user'
  Generating /home/miguel/app/migrations/versions/4708a5190f2_.py...done

The migrate command adds a new migration script. You should review it and edit it to be accurate, as Alembic cannot detect all changes that you make to your models. In particular it does not detect indexes, so those need to be added manually to the script.

If you prefer to write your migration scripts from scratch then use revision instead of migrate:

$ python manage.py db revision
  Generating /home/miguel/app/migrations/versions/15c04479d683_.py...done

You can read Alembic's documentation to learn how to write migration scripts.

The next step is to apply the migration to the database. For this you use the upgrade command:

$ python manage.py db upgrade
INFO  [alembic.migration] Context impl SQLiteImpl.
INFO  [alembic.migration] Will assume non-transactional DDL.
INFO  [alembic.migration] Running upgrade None -> 4708a5190f2, empty message

And that's it! Your database is now synchronized with your models.

You should add all the files in the migrations folder to version control along with your source files. If you need to update another system to the latest database version you just need to update your source tree on that other system and then run db upgrade, like you did above.

If you have any suggestions to improve this extension please let me know below in the comments.

Miguel

Become a Patron!

Hello, and thank you for visiting my blog! If you enjoyed this article, please consider supporting my work on this blog on Patreon!

124 comments

#26 Miguel Grinberg said 2014-01-28T17:27:19Z

@Aidan: the migrate command generates only the differences between the model definition and the current structure of your database. If you want to generate the entire database then you need to destroy all the tables and revert your db to empty.
#27 isccarrasco said 2014-01-30T23:44:24Z

Hi Miguel...
I'm using Flask-Migrate, but i need to customize some elements of the migrations, particularly the name and schema of the index, there are a way to do this?...

thanks..
#28 Miguel Grinberg said 2014-01-30T23:52:34Z

@isccarrasco: yes. After you generate the migration (but before you upgrade your database) you can change the script. Just make sure you change the upgrade and downgrade functions consistently.
#29 Miho said 2014-02-02T18:21:04Z

Hi Miguel,

first of all, thanks for the great tutorials!
I have the same problem as Aidan in manage.py.

If I replace line "from app.models import User" with actual model from models.py it generates a migrate script for table users.

But if i leave that line, it does no generate any changes for migration?
It simply generates empty file.

Thx
#30 Miguel Grinberg said 2014-02-03T00:16:10Z

@Miho: as I said above, you need to have an empty database for the migration to be generated. Delete your database tables and then you should be getting a migration script.
#31 Miho said 2014-02-03T06:47:48Z

Thanks for the reply Miguel.

The problem is that my database actually is empty. There is no table "user" created in it.

If I take that database and execute manage.py with model declaration in it, i get a migration script with new table.
But if i try to execute manage.py with importing model from models.py, the resulting script is empty?

Workaround is to c/p the model into manage.py (to continue with tutorial :)). But there must be a something somewhere I'm missing
#32 Miguel Grinberg said 2014-02-03T15:45:04Z

@Miho: can you show me in detail what the alternative is? What code do you add and where? I don't understand what you are changing to make things work.
#33 Luke Hansford said 2014-02-08T03:33:02Z

Thanks for the great work on this!
#34 John Hitz said 2014-02-20T06:13:43Z

Hey MIguel! I got the following error on openshift: File "/var/lib/openshift/52f50dc84382ec899e000223/python/virtenv/lib/python2.7/site-packages/psycopg2/init.py", line 50, in <module>
from psycopg2._psycopg import BINARY, NUMBER, STRING, DATETIME, ROWID
ImportError: libpq.so.postgresql92-5: cannot open shared object file: No such file or directory. At first I thought this was a problem with openshift and postgresql-9.2. However, had the same problem in 8.4 and mysql-5.1.

Could be a mistake I am making...I'm fairly new to programming. Hope this helps you find any potential problems.
#35 Miguel Grinberg said 2014-02-20T07:34:21Z

@John: Have you seen this discussion thread? https://bugzilla.redhat.com/show_bug.cgi?format=multiple&id=986219
It appears OpenShift does not set LD_LIBRARY_PATH to include the postgres libraries, so they suggest a workaround where you set it on your side.
#36 Bogdan Alexandrescu said 2014-02-26T03:02:13Z

Hi Miguel,
Once again, a great tutorial!
I have a really burning question related to flask-migrate and working with multi databases.

I am using
SQLALCHEMY_BINDS = {
'db1': 'mysql://user:pass@localhost/db1',
'db2': 'mysql://user:pass@localhost/db2'
}
to provide a dictionary of databases that can be used by sqlalchemy and I am defining tables the usual way but including the
bind_key='db1'
or
bind_key='db2'
in each model.

This works well when I want to run db.create_all() but it doesn't run with flask-migrate. I also have tried to find a way to edit the alembic env.py to do this but I can't figure out how to do it and the documentation on env.py didn't help in this case.

Do you have any suggestions? Any help will be greatly appreciated!
#37 Miguel Grinberg said 2014-02-26T15:24:33Z

@Bogdan: you have two options. #1 you manage each db separately, each with its own migration directory. For this you pass the migration directory to use in the cmd line. #2 customize env.py to do multiple dbs. A partial example is shown in the Alembic documentation: http://alembic.readthedocs.org/en/latest/tutorial.html#customizing-the-environment. I would go with #1, seems a lot simpler and less error prone.
#38 Bogdan said 2014-02-27T06:49:18Z

Hi Miguel,
Thanks for replying to me so fast!
How exactly I configure flask migrate to migrate two different databases? Can I set 2 migrate commands and use them like:
manage.py db1 migrate
manage.py db2 migrate?

How do I tell flask migrate where to get the two databases that i want to put in those separate folders? Now I am using SQLALCHEMY_BINDS to tell sqlalchemy what databases to use and then add bind_key to each model so it knows where to look for it.

I am new at using Flask and SQLAlchemy and I am sorry for the noob questions :)

This is how I have my manage.py written:

from app import app, manager
from flask.ext.migrate import MigrateCommand
manager.add_command('db', MigrateCommand)
app.debug = True
manager.run()

And this is how I create the instances in my init.py:
...
app = Flask(name)
app.config.from_object('config')
db = SQLAlchemy(app)
migrate = Migrate(app, db)
manager = Manager(app)
...

What do you think?
P.S. the alambic documentation is very vague about woking with multiple databases that are using sqlalchemy binds.. unfortunately
#39 Miguel Grinberg said 2014-02-27T06:57:22Z

@Bogdan: Working with multiple databases is a non-trivial effort. Flask-Migrate will configure the migrations for the main database, but you will need to create either manually (by copying files from the first migration directory) or using the alembic command. Once you have two migration directories configured one for each database you can pass the directory to "manage.py db" using the -d command line option. That way you can tell Flask-Migrate to use the database that you want. If you do not want to deal with such complications then I recommend that you find a way to unify your data into a single database, at least until you are more comfortable to hack the framework.
#40 Carlos Valdivia said 2014-04-03T12:43:15Z

excellent job, just a question, how can i configure the alembic.ini file for the script_location?
alembic need this parameter to execute another useful task
Thanks and regards
#41 Miguel Grinberg said 2014-04-03T14:16:24Z

@Carlos: you can edit alembic.ini manually if you like, but specifically for script_location my extension defaults it to "migrations", and you can pass a different location in the command line using "-d" option.
#42 Blake said 2014-04-10T18:35:42Z

Hi Miguel,

Love your website - tutorials have been a big help. I have a separate config file with DB set up as SQLALCHEMY_DATABASE_URI = 'sqlite:///' + os.path.join(basedir,'foodo.db'). When I run Init/migrate/update the 'migrations' folder but the actually db is not created in my source folder.
#43 Miguel Grinberg said 2014-04-12T22:02:44Z

@Blake: does the db exist in some other folder? How do you set "basedir"?
#44 Paul Aphivantrakul said 2014-04-17T01:19:09Z

Hello Miguel,

Thank you for the great tutorial. Is it possible to use Flask-Migrate on a Flask application running off an apache web server and using mod_wsgi? I don't believe I am using the app.run() command in my application, so I am not sure if the manager.run() command will be executed.

Thank you,

Paul
#45 Miguel Grinberg said 2014-04-17T03:43:14Z

@Paul: Yes. Flask-Migrate runs from the command line, it does not care what web server you use because it works outside of the server environment. You have to use the manage.py script to run it, even if your production server does not use it when it runs the application.
#46 Crowdstar said 2014-05-14T03:14:57Z

Miguel, I just got your book and am working through my first site. What are your thoughts on using sqlsoup and not declaring the models at all? I started down that path but I got confused when Flask-login had required methods for the User class. I am more comfortable building my db in mysql than having the models generate the tables.
Great book! Thanks!
#47 Miguel Grinberg said 2014-05-14T05:46:09Z

@Crowdstar: the user object that you give you Flask-Login does not need to be a database model. You can create a User class that just stored the user id and use that. As long as you implement all the required methods and the user loader callback you will be fine.
#48 shankar said 2014-05-17T05:30:00Z

hello Miguel,

once i am trying to upgrade using python manage.py db migrate
it shows error

raise util.CommandError('Only a single head is supported. The '
alembic.util.CommandError: Only a single head is supported. The script directory has multiple heads (due to branching), which must be resolved by manually editing the revision files to form a linear sequence. Run alembic branches to see the divergence(s).

. Any suggestion on how can i have linear sequence
#49 Miguel Grinberg said 2014-05-17T06:26:06Z

@shankar: this happens when you go back to a revision that is not the last and then create a new migration. Now you have two branches, which Alembic cannot handle. Look at how the migration files are chained together through migration ids, you need to create a linear chain to collapse the branches.
#50 hewx said 2014-06-10T13:46:15Z

How does Flask-Migarte detect the migrations automaticly