Flask-Migrate: Alembic database migration wrapper for Flask

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:

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 app.py --help
usage: app.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 app.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

54 comments

  • #1 Collin Meyers said :

    Possible typo: In the last section where you're describing the upgrade command, you've got: $ python manage.py upgrade Should it be: $ python manage.py db upgrade

  • #2 Miguel Grinberg said :

    @Collin: yes, you are correct, it's fixed now. Thanks!

  • #3 Peter Stratton said :

    Thanks Miguel, this is an extremely handy package. Can't wait for your book!

  • #4 Mark Khosla said :

    Thanks so much for this and the Flask Mega Tutorial. I'm learning how to use Flask and these resources have been invaluable.

  • #5 jaco smuts said :

    Hello sqlalchemy-migrate failed me with boolean fields (mysql back-end). I got albemic working, was about to post about it on your database section in the tutorial, then stumbled across your extension. Got it working in minutes. Thank you - and looking forward to book.

  • #6 ryzhiy said :

    Hi Miguel, Is there anyway to setup the migration scripts for existing database? Thanks

  • #7 Miguel Grinberg said :

    @ryzhiy: yes, this is actually automatic. Let's say you have a database with some tables, and the model definitions that match that. You can now initialize Flask-Migrate/Alembic as indicated above, and continue working. When you modify the models and create a migration that migration will contain just the differences from the state the database was when you started. Those original contents will not be tracked by Alembic.

  • #8 Zhuo said :

    Hi Miguel, something goes wrong when I install both flask-script and flask-migrate. the installation was corrupted by an error with following traceback info: d:\work>pip install flask-migrate Downloading/unpacking flask-migrate Downloading Flask-Migrate-0.1.4.tar.gz Running setup.py egg_info for package flask-migrate Traceback (most recent call last): File "<string>", line 3, in <module> File "build\bdist.win-amd64\egg\setuptools\__init__.py", line 11, in <modu le> File "build\bdist.win-amd64\egg\setuptools\extension.py", line 5, in <modu le> File "build\bdist.win-amd64\egg\setuptools\dist.py", line 15, in <module> File "build\bdist.win-amd64\egg\setuptools\compat.py", line 19, in <module > File "C:\Python27\lib\SimpleHTTPServer.py", line 27, in <module> class SimpleHTTPRequestHandler(BaseHTTPServer.BaseHTTPRequestHandler): File "C:\Python27\lib\SimpleHTTPServer.py", line 208, in SimpleHTTPRequest Handler mimetypes.init() # try to read system mime.types File "C:\Python27\lib\mimetypes.py", line 358, in init db.read_windows_registry() File "C:\Python27\lib\mimetypes.py", line 258, in read_windows_registry for subkeyname in enum_types(hkcr): File "C:\Python27\lib\mimetypes.py", line 249, in enum_types ctype = ctype.encode(default_encoding) # omit in 3.x! UnicodeDecodeError: 'ascii' codec can't decode byte 0xb0 in position 1: ordi nal not in range(128) Complete output from command python setup.py egg_info: Traceback (most recent call last): File "<string>", line 3, in <module> File "build\bdist.win-amd64\egg\setuptools\__init__.py", line 11, in <module> File "build\bdist.win-amd64\egg\setuptools\extension.py", line 5, in <module> File "build\bdist.win-amd64\egg\setuptools\dist.py", line 15, in <module> File "build\bdist.win-amd64\egg\setuptools\compat.py", line 19, in <module> File "C:\Python27\lib\SimpleHTTPServer.py", line 27, in <module> class SimpleHTTPRequestHandler(BaseHTTPServer.BaseHTTPRequestHandler): File "C:\Python27\lib\SimpleHTTPServer.py", line 208, in SimpleHTTPRequestHand ler mimetypes.init() # try to read system mime.types File "C:\Python27\lib\mimetypes.py", line 358, in init db.read_windows_registry() File "C:\Python27\lib\mimetypes.py", line 258, in read_windows_registry for subkeyname in enum_types(hkcr): File "C:\Python27\lib\mimetypes.py", line 249, in enum_types ctype = ctype.encode(default_encoding) # omit in 3.x! UnicodeDecodeError: 'ascii' codec can't decode byte 0xb0 in position 1: ordinal not in range(128) ---------------------------------------- Cleaning up... Command python setup.py egg_info failed with error code 1 in c:\users\vincen~1\a ppdata\local\temp\pip_build_VincentZhuo\flask-migrate Storing complete log in C:\Users\VincentZhuo\pip\pip.log Any idea?

  • #9 Miguel Grinberg said :

    @Zhuo: I do not see this error when I run "pip install flask-script flask-migrate". Did you try making a new virtualenv?

  • #10 Jordan said :

    Hey Miguel, Running into a bit of a problem with updating my Postgresql database. When I first initialize, migrate, and upgrade - everything is fine. Here's the script that's created: http://pastebin.com/5PFvWWM5 The second script that's created during the next migration, however, I cannot use. I made no alterations to my database. This is the script: http://pastebin.com/D9qk3jS3 And this is the error I get when I try to update with said script: INFO [alembic.migration] Context impl PostgresqlImpl. INFO [alembic.migration] Will assume transactional DDL. INFO [alembic.migration] Running upgrade 22ae33e15a27 -> 1305c5436d15, empty message Traceback (most recent call last): File "manage.py", line 20, in <module> manager.run() File "/home/jordan/env/local/lib/python2.7/site-packages/flask_script/__init__.py", line 366, in run raise e sqlalchemy.exc.InternalError: (InternalError) cannot drop index user_name_key because constraint user_name_key on table "user" requires it HINT: You can drop constraint user_name_key on table "user" instead. '\nDROP INDEX user_name_key' {} Looks like Alembic is automatically dealing with indexes for my unique columns after the initial migration. Any suggestion on how I can get Postgres to play nice with these scripts?

Leave a Comment

Note: all comments are screened before they are published. Thank you for your patience!