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

#1 Collin Meyers said 2013-09-18T15:33:54Z

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 2013-09-18T15:59:05Z

@Collin: yes, you are correct, it's fixed now. Thanks!
#3 Peter Stratton said 2013-09-25T18:26:00Z

Thanks Miguel, this is an extremely handy package. Can't wait for your book!
#4 Mark Khosla said 2013-10-13T18:11:49Z

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 2013-10-21T16:06:13Z

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 2013-10-29T15:59:36Z

Hi Miguel,
Is there anyway to setup the migration scripts for existing database?
Thanks
#7 Miguel Grinberg said 2013-10-29T16:03:40Z

@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 2013-11-18T10:17:34Z
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)
<hr />
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 2013-11-18T15:36:14Z

@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 2013-12-04T10:24:32Z

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?
#11 Miguel Grinberg said 2013-12-04T15:47:48Z

@Jordan: For some reason Alembic thinks there are two indexes that were removed, but these are referenced by "unique" columns, so they cannot be deleted. The easiest way to go forward is to just edit the migration script and remove these two indexes. If you are positive you haven't changed any indexes then this could be a bug in Alembic 0.6.1.
#12 Jordan said 2013-12-05T12:10:13Z

Yup, I downgraded to version 6.0 and the problems stopped. Don't get the autogenerated index stuff, but I can live without that.

Appreciate the input.
#13 thcal said 2013-12-17T23:41:24Z

I'm following your flask mega tutorial and am having trouble understanding how to implement the "sample application that initializes Flask-Migrate and registers it with Flask-Script." Do I spread those lines between app/init.py, run.py, config.py, or am I supposed to put all of that in a separate manage.py file? If the former, where does the if name statement go? If the latter, do I edit the script to reference init.py and config.py where many of these variables are defined already?
#14 Miguel Grinberg said 2013-12-18T05:18:24Z

@thcal: the tutorial does not use Flask-Script, so you'll need to add both Flask-Script and Flask-Migrate. Both can get initialized in app/init.py, along with the other extensions.
#15 lukas said 2014-01-04T13:57:13Z

I do have a problem with it as well http://stackoverflow.com/questions/20920939/local-and-heroku-db-get-out-of-sync-while-migrating-using-alembic
#16 Brad Decker said 2014-01-21T14:10:39Z

Miguel,

Your site and all of the tutorials have been immensely helpful in getting me started with flask. This flask extension has saved me hours of time across multiple work projects and I just wanted to let you know that its greatly appreciated.
#17 Sander said 2014-01-23T15:03:08Z

Hi Miguel,

I have a table with a lot of columns (They are spectral data measurements with each column a certain wavelength resulting in 453 columns.)
When I upgrade my data it gives me a

SyntaxError: more than 255 arguments

I guess these arguments are my columns causing the error...
Would this be Flask-Migrate problem or a Alembic problem ?
#18 Miguel Grinberg said 2014-01-23T15:09:09Z

@Sander: could you show me the complete stack trace of your error?
#19 Sander said 2014-01-23T15:29:45Z

when working on a existing sqlite data I have a "sqlite_sequence" table sqlite-migrate gives me an error it cannot be deleted and it shouldn't as it is an "internal" table.
Somehow sqlite-migrate should "ignore" this and other "sqlite_" tables ?
#20 Miguel Grinberg said 2014-01-23T15:43:40Z

@Sander: the automatic migration scripts are not supposed to be perfect, they are a starting point. You should always review them and edit them to not include internal tables.
#21 sander said 2014-01-23T16:16:41Z

Hi Again ;-)

running into another issue:
using my sqlite database migrate.py db upgrade gives an operational error when deleting a column.
I guess it has to do with the "ALTER TABLE" sql which is not fully supported by sqlite...

Sander.
#22 Miguel Grinberg said 2014-01-23T16:40:53Z

@sander: SQLite databases have some important limitations. One of them is the inability of deleting columns. See the first paragraph in http://www.sqlite.org/lang_altertable.html. If this is a problem for you, then I recommend that you switch to MySQL or Postgres.
#23 Aidan said 2014-01-28T07:59:47Z

Hello Miguel!
Could you tell me how to use Flask-migrate in your microblog tutorial?
#24 Miguel Grinberg said 2014-01-28T08:13:21Z

@Aidan: You could replace the migrations one by one using Flask-Migrate if you like, but I'm not sure what benefit you will get out of it. You should use Flask-Migrate for your new projects.
#25 Aidan said 2014-01-28T08:24:30Z

I'm trying to put this in microblog.

I created a manage.py
<h1>!/usr/bin/env python3</h1>
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
from flask.ext.script import Manager
from flask.ext.migrate import Migrate, MigrateCommand

from app import app

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

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

from app.models import User

if name == 'main':
manager.run()

But after executed python manage.py db migrate. It generated an empty commands.

def upgrade():
### commands auto generated by Alembic - please adjust! ###
pass
### end Alembic commands ###

def downgrade():
### commands auto generated by Alembic - please adjust! ###
pass
### end Alembic commands ##