2013-09-09T06:12:19Z

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. 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

124 comments

  • #76 Robert said 2015-02-16T23:39:50Z

    First, excellent tutorials and scripts - I'm following them to create my first real project outside of academic uses. I'm not 100% what I'm doing half the time but it's all making sense so far.

    I ran into the problem of the old migrate not working with with current version of SQL Alch so I'm trying to implement this fix to make the migrate work again.

    One part I'm not clear about is the use for migrate in the example which you said to add to init.py. Why would you define a Class in your init file that is in the models.py file? If you have to update the migrate folder manually, isn't that redundant and doesn't it leave room for error?

    Or do you simply add

    from app import views, models

    like in the original init? I'll bang around for a few but please let me know what to do and why.

    Thanks again!

  • #77 Don Dwiggins said 2015-06-04T21:26:34Z

    I've just manually merged your instructions above with the database instructions in the mega-tutorial, part IV. Took some trial and error, to find the bits I needed to replace, remove, and add, but I've got a good DB, and can proceed with the tutorial. The key insight was that I needed to drop db_create.py, and put manage.py in this article into the microblog folder (mutatis mutandis). If/when you have time (right! 8^), it might be worth updating that page of the tutorial.

    Thanks for an excellent set of articles!

  • #78 Nana Okyere said 2015-07-06T02:32:18Z

    Miguel, Thanks for flask-migrate and flask tutorial. I'm going through the mega tutorial and when I got to part 4 (iv), I decided to use flask migrate instead of SQLAlchemy-migrate. I've run into two issues that I'm hoping you could help me with. I'm not using sqlite; I'm using oracle 11g xe as my database.

    First, when the models.py was modified to add the Post model and the 'posts' field was added to the User model as: posts = db.relationship('Post', backref='author', lazy='dynamic'), when I run ./run.py db migrate and checked the migration script in the versions folder, the upgrade method does not pick up the change to the User model. It does however pick up the addition of the Post model. Is this because I'm using oracle? Did I need to tweak something in the alembic.ini file? I'm totally new to this. Can you help?

    Second issue (still doing mega tutorial in part iv): In the 'Play Time' part, I'm able to import db and models on the python command line. I'm able to create the user as : u = models.User(nickname='john', email='john@email.com') . I'm also able to add the user to the db session as: db.session.add(u) . When I try the db.session.commit(), I get an exception.

    sqlalchemy.exc.IntegrityError: (cx_Oracle.IntegrityError) ORA-01400: cannot inse rt NULL into ("SYSTEM"."user"."ID") [SQL: b'INSERT INTO "user" (nickname, email) VALUES (:nickname, :email) RETURNI NG "user".id INTO :ret_0'] [parameters: {'ret_0': , b'ret_0': None, b'email': 'john@mail.com', b'nickname': 'John'}]

    So it looks to me like the ID is not being populated; and Oracle won't allow that because that field is not nullable. I thought sqlalchemy / flask-sqlalchemy was supposed to populated the id field for me. But I don't think this is happening. Any thoughts on that? Thanks in advance for your help. If you need more information, let me know.

  • #79 Miguel Grinberg said 2015-07-06T06:00:53Z

    @Nana: for the first questions, "posts" is not a database field, it is a relationship. It does not go in the database, SQLAlchemy manages the relationship using the foregin keys. Second question I'm not sure. The model sets the id field as an auto-increment field, it should automatically get a unique ID assigned on insertion. Any chance you did not set the autoincrement in your model?

  • #80 Nana Okyere said 2015-07-06T17:44:06Z

    Miguel,

    I was using the same code you have for my User model and you didn't use the autoincrement arguement in the id column definition. I have added that argument now but the commit is still failing. I thought the flask sqlalchemy extension does that for me when I specify a column as the primary key. Is there anything else that can cause this commit to fail with that message?

  • #81 Nana Okyere said 2015-07-06T23:23:14Z

    Miguel,

    So I just found out that Oracle does not support the auto increment feature. Instead most people suggest a sequence. What is it and how do it use it in my models definition? Thanks.

  • #82 Miguel Grinberg said 2015-07-07T01:53:55Z

    @Nana: have you seen the SQLAlchemy docs on how to do this? http://docs.sqlalchemy.org/en/latest/dialects/oracle.html#auto-increment-behavior

  • #83 Nana Okyere said 2015-07-07T11:21:16Z

    Miguel, I had read the docs even before you posted the link. Based on that and other people's example I saw on stackoverflow, I defined the id field as:

    id = db.Column(db.Integer,db.Sequence('seq_reg_id', start=1, increment=1), primary_key=True,)

    Now when I do the commit, I get : cursor.execute(statement, parameters) sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-02289: sequence does not exist .

    So it is saying that the sequence does not exist. But I thought it is what I am creating in that id definition code. I'm confused. I'm using Oracle because it is the db I'll be developing against once I finish the tutorial & I learn more about flask. Any help?

  • #84 Miguel Grinberg said 2015-07-07T21:55:05Z

    @Nana: I don't use Oracle, so I don't really know and don't have a way to test. Maybe the sequence needs to be created by separately?

  • #85 Nana Okyere said 2015-07-11T18:29:11Z

    Thanks Miguel. I have another question. Still on databases section of your tutorial. When I run ./manage.py db upgrade and I check my oracle database, the user table name is all lowercase but the post table name is all upper case. Is this a sqlalchemy thing or alembic thing? I'm especially confused because they're both defined similarly in models.py ie. Post and User . In the migration script, they both have all lowercase names in the op.create_table construct. How or why does one table become all lowercase on the physical table and the other become all uppercase? I read this: http://docs.sqlalchemy.org/en/latest/dialects/oracle.html under 'Identifier Casing' but I don't understand it nor know if it applies here. Got an insight?

  • #86 Miguel Grinberg said 2015-07-16T05:59:09Z

    @Nana: is the difference in case causing a problem? As I mentioned above, I have never used Oracle, maybe it's something specific to that database.

  • #87 Alexey Shildyakov said 2015-07-29T05:58:02Z

    Could you please change 'app.py' to 'manage.py' (as I inderstand this is the same script) and moving up (or copy) description "Note that you should replace manage.py with the name of your launch script if you used a different name." to the first using code. Now it's ($ python app.py --help usage: app.py [-h] {shell,db,runserver} ...) Also It would be great to add the filename in the head of code inside the file, like: "

    File 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) ..." And possibly move the 'manage.py' description to that block.

  • #88 Miguel Grinberg said 2015-08-02T05:35:24Z

    @Alexey: Thanks, I have corrected the script names.

  • #89 Jeremmm said 2015-09-14T23:27:42Z

    Hello, I use sqlalchemy_utils, but when the upgrade script is created, sqlalchemy_utils is not imported. Is there a methodology for either import lib to the creation of the upgrade script?

    Thanks

  • #90 Miguel Grinberg said 2015-09-15T17:58:16Z

    @Jeremmm: what I would do is just edit the migration script by hand to make it right. If that is really inconvenient, you can edit the migration script template: https://github.com/miguelgrinberg/Flask-Migrate/blob/master/flask_migrate/templates/flask/script.py.mako.

  • #91 Nana Okyere said 2015-12-29T08:48:25Z

    I incorporated this into an application I made. First migration and upgrade goes well. My problem is, when I make changes to the models and run db migrate and I inspect the version file created, it generates code that recreates ALL the tables all over again. I was expecting it to only be the difference of the state of the schema and what are defined in the models. Am I missing something? I'm using Oracle 11.2. I don't know if it matters. Due to this, I've had to delete and recreate everything every time I change something in my models. Please help. Thanks.

  • #92 Miguel Grinberg said 2016-01-03T22:51:02Z

    @Nana: we have discussed this on Github, but in case others have this issue, the explanation and solution is in this Github comment: https://github.com/miguelgrinberg/Flask-Migrate/issues/93#issuecomment-168540045

  • #93 PJ said 2016-05-20T23:32:47Z

    Hi Miguel, thanks for a nice tutorial. I am in the middle of regenerating my migration due to some previous issues. I am using python/SQLAlchemy/sqlite3 and I need to drop my alembic_version table and have it regenerated. But the plain old Drop table alembic_version from the shell isnt working (NameError: name 'drop' is not defined). Do you know a way to drop this table from the shell? Thanks in advance!

  • #94 Miguel Grinberg said 2016-05-22T00:39:02Z

    @PJ: drop the table directly using SQL on the sqlite3 command line app.

  • #95 Mark said 2016-06-01T06:18:41Z

    Hi Miguel, really love the tutorial! Now that Flask 0.11 includes a built-in CLI, do you think it makes sense to update Flask-Migrate to support the new CLI in addition to Flash-Script? I was considering hacking my local version of Flask-Migrate to have it expose a new object that can be passed into app.cli.group(). I'd be interested in your opinion on that approach. Thanks!

  • #96 Miguel Grinberg said 2016-06-02T02:36:41Z

    @Mark: Not sure how I am going to handle the transition to click yet. For starters, Flask-Script works fine with 0.11, so there is no immediate problem of people not being able to use the extension. My preference would be to add click support without having to drop Flask-Script, because I suspect a lot of people will wait to adopt 0.11, and I don't want to stop supporting the 0.10.x users when 0.11 is so new.

  • #97 Mark said 2016-06-02T15:36:32Z

    Miguel, I think that makes a lot of sense. I've got a preliminary version where I split out the function bodies and command setup in Flask-Migrate, and create separate setup routines for Click and Flask-Script. Would you be interested in taking a look and giving me feedback? it needs some cleanup, but I'd be happy to submit a pull request for Flask-Migrate if you'd like.

  • #98 Miguel Grinberg said 2016-06-05T06:02:11Z

    @Mark: Yes, I would be interested in seeing how you solved the problem. Thanks!

  • #99 Michael said 2016-06-05T11:13:46Z

    Hello Miguel,

    I am now at your commit 10d from your book. After adding the Gravatar. I am not able to run the commit on my windows desktop. it says : " OperationalError: (OperationalError) no such column: users.avatar_hash u'SELECT users.id AS users_id, users.email AS users_email, users.username AS users_username, users.role_id AS users_role_id, users.password_hash AS users_password_hash, users.confirmed AS users_confirmed, users.name AS users_name, users.location AS users_location, users.about_me AS users_about_me, users.member_since AS users_member_since, users.last_seen AS users_last_seen, users.avatar_hash AS users_avatar_hash \nFROM users \nWHERE users.id = ?' (3,) "

    So I though, since it is missing a column I would do a migrate or upgrade. But then I get the error:

    " raise util.CommandError("No such revision '%s'" %id_) alembic.util.CommandError: No such revision '1fc2a72b927f' "

    From a previous comment on page 2 here from @shankar You answered "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."

    How exactly do I look at the migration files, and how do I create a linear chain to collapse the branches? And also, how do I make sure that his won't happen again ?

    BTW, your book is amazing !

  • #100 Miguel Grinberg said 2016-06-05T18:19:01Z

    @Michael: First of all, if you are working with the official Flasky repository, you do not need to create any migrations yourself. All the migrations are included in the repository. All you need to do is do upgrades every time you advance to a newer tag. So if you are using the official repo, my recommendation at this point is that you delete the database and then do an upgrade to regenerate in the correct format.

    Now, if you want to learn how to fix this type of conflicts, I wrote a blog article on how to resolve them: http://blog.miguelgrinberg.com/post/resolving-database-schema-conflicts.

Leave a Comment