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__':

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


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!

  • #101 Leon said

    Hi Miguel, Thank you for your tutorial. I was trying to add a custom data type in flasky to support json. The module is in app/utils/customDataType.py. And the new migration scripts is generated in migrations/versions/...

    In the script, I need to import the 'JsonType'.

    from ...app.utils.customDataType import JsonType

    Also, I add init.py files in root, migrations and versions folder, which didn't help solve the import exception "Attempted relative import in non-package".

    How can I import module in app? Thank you.

  • #102 Shahab said

    Hi Miguel,

    I am following you tutorial, but get stock at this point:

    $ python manage.py db init
    Traceback (most recent call last):
    File "./manage.py", line 139, in <module>
    File "/home/shahab/projects/coreapis/venv/local/lib/python2.7/site-packages/flask_script/init.py", line 412, in run
    result = self.handle(sys.argv[0], sys.argv[1:])
    File "/home/shahab/projects/coreapis/venv/local/lib/python2.7/site-packages/flask_script/init.py", line 383, in handle
    res = handle(args, config)
    File "/home/shahab/projects/coreapis/venv/local/lib/python2.7/site-packages/flask_script/commands.py", line 216, in call
    return self.run(
    args, **kwargs)
    File "/home/shahab/projects/coreapis/venv/local/lib/python2.7/site-packages/flask_migrate/init.py", line 58, in init
    directory = current_app.extensions['migrate'].directory
    KeyError: 'migrate'

    Could you please tell me what I have done wrong.

    Thank you

  • #103 Miguel Grinberg said

    @Shahab: did you initialize the Migrate class?

  • #104 Miguel Grinberg said

    @Leon: It seems you have a Python import problem, unrelated to Flask or Flask-Migrate. I recommend that you write a question on Stack Overflow and add the relevant code and the full stack trace of the error.

  • #105 Valentin said


    Thanks for this great tool.
    I just have one problem/question.
    I have now a bunch of revisions files. It works great to update any system to the last version. I wonder however how I can handle setting up a new system from scratch.
    I can of course generate the database using sqlalchemy db.create_all() but I don't consider it a good solution because it's different from what I do for existing systems. I want to be sure they ends with the same results. It can also be a problem if some custom statements were added to the revisions scripts, they would not be executed for this new system.
    What I would expect: a way to create the database in the initial version before any migrations applied and then apply all migrations scripts.
    Does this make sense? Is there a simple way to do this?

  • #106 Miguel Grinberg said

    @Valentin: database creation always happens outside of SQLAlchemy and Alembic, and you need to do it on your own (except for sqlite, where the db file is created automatically by SQLAlchemy). The db.create_all() function only creates tables, it does not create a database. So the steps to create an updated database from scratch are two: #1 create the database using the db admin tool of your choice, and #2 run the "db upgrade" command to let Alembic create the tables via the migration scripts.

  • #107 Jason said

    Hi Miguel,
    Thanks so much for the tutorials, they are really helpful!
    I have been able to follow it while using multiple databases:
    $ python manage.py db init --multidb
    $ python manage.py db migrate
    However, it throws an database is locked error when I try to do the upgrade:
    $ python manage.py db upgrade
    The error:
    sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) database is locked

    Could you help me solve this please? Thanks!

  • #108 Miguel Grinberg said

    @Jason: Could be that your database file is open by another process at the time you are trying to migrate it. The error is unrelated to Flask-Migrate, Alembic or SQLAlchemy.

  • #109 Pranay Khilari said

    Hi Miguel,

    I read your document which is excellent, Also I read all the comments one thing is still not clear for me is below case.
    1) I have model package which contains all the models.
    2) I am not writing the model in manage.py file.
    3) If I used the flask-migrate it is not detecting the models which are present in my model package.
    4) My database is empty.

    Please help me.

  • #110 Miguel Grinberg said

    @Pranay: you need to import your models. If you do not import them, then SQLAlchemy does not know about them.

  • #111 Mike A said

    Dear Sir. First, than you so much for all your contributions to the community. Your mega-tutorial gave me the leg up I needed.

    When using Migrate in a simple flask app, there is an issue with mysql and boolean fields. In the models file, the field is specified like so: "db.Column(db.Boolean, unique=False, default=False)". When the column is created in the DB, its created as a tinyint, which is fine, but every time migrate is used, it detects the tinyint/boolean difference and tries to update it:
    "INFO [alembic.autogenerate.compare] Detected type change from TINYINT(display_width=1) to Boolean() on 'circuits.is_child'"

    I currently go in and curate the .py file in the versions folder to deal with it, but it becomes tedious now that I have 40 booleans in my app. I know I could change the column type in models to "db.Column(TINYINT(display_width=1), default=0)" but this outputs as a "1" rather than as "True" so im not sure its the best way to go.

    Any thoughts?

  • #112 Miguel Grinberg said

    @Mike: this issue explains the problem and provides some solutions: https://github.com/miguelgrinberg/Flask-Migrate/issues/143.

  • #113 Ravina said

    Hi Miguel,
    Can u help me out this error
    Can't load plugin: sqlalchemy.dialects:mysql.pymysql
    How to rectify this error

  • #114 Miguel Grinberg said

    @Ravina: you need to install pymysql into your virtual environment.

  • #115 Tim said

    Hi Miguel and thanks for the great tutorial!

    I created a model with incorrect column type (instead of String I used Integer on task id). I tried changing it afterwards using ALTER TABLE via MYSQL Workbench but because the table had a foreign key restriction to result table I was not able to do this. I tried to remove the foreign key restriction, but eventually got tired of figuring out how to do this, and removed both task and result tables with DROP TABLE.

    I thought that Flask-Migrate would just re-create the missing tables. But now when I run "flask db migrate" and "flask db upgrade" I get an error. So is there a way to just start over? All I have is one line in the user table, so I would just like to be able to re-create all tables.

  • #116 Miguel Grinberg said

    If you want tables to be recreated you would run the upgrade command. The migrate command generates a new migration, that is used when you make changes to the models and want those changes applied to the database.

    Maybe you should show the error that you are getting if you can't get this to work when you run the upgrade command alone.

  • #117 Tim said

    It seems that it wants the tables to exist:

    (venv) user@mypc:/mnt/c/flask/myapp$ flask db upgrade
    INFO [alembic.runtime.migration] Context impl MySQLImpl.
    INFO [alembic.runtime.migration] Will assume non-transactional DDL.
    INFO [alembic.runtime.migration] Running upgrade 26159dd471d8 -> 4ee052488c48, changed task id type
    Traceback (most recent call last):
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
    cursor, statement, parameters, context
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 550, in do_execute
    cursor.execute(statement, parameters)
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/pymysql/cursors.py", line 328, in _query
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/pymysql/connections.py", line 517, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/pymysql/connections.py", line 732, in _read_query_result
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/pymysql/connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/pymysql/connections.py", line 684, in _read_packet
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/pymysql/protocol.py", line 220, in check_error
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
    pymysql.err.ProgrammingError: (1146, "Table 'myapp.result' doesn't exist")

    The above exception was the direct cause of the following exception:

    Traceback (most recent call last):
    File "/home/user/.virtualenvs/venv/bin/flask", line 10, in <module>
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/flask/cli.py", line 966, in main
    cli.main(prog_name="python -m flask" if as_module else None)
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/flask/cli.py", line 586, in main
    return super(FlaskGroup, self).main(args, kwargs)
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/click/core.py", line 717, in main
    rv = self.invoke(ctx)
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/click/core.py", line 1137, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/click/core.py", line 1137, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/click/core.py", line 956, in invoke
    return ctx.invoke(self.callback,
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/click/core.py", line 555, in invoke
    return callback(
    args, kwargs)
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/click/decorators.py", line 17, in new_func
    return f(get_current_context(), *args,
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/flask/cli.py", line 426, in decorator
    return ctx.invoke(f, args, kwargs)
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/click/core.py", line 555, in invoke
    return callback(
    args, kwargs)
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/flask_migrate/cli.py", line 134, in upgrade
    _upgrade(directory, revision, sql, tag, x_arg)
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/flask_migrate/init.py", line 95, in wrapped
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/flask_migrate/__init
    .py", line 280, in upgrade
    command.upgrade(config, revision, sql=sql, tag=tag)
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/alembic/command.py", line 276, in upgrade
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/alembic/script/base.py", line 475, in run_env
    util.load_python_file(self.dir, "env.py")
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/alembic/util/pyfiles.py", line 90, in load_python_file
    module = load_module_py(module_id, path)
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/alembic/util/compat.py", line 177, in load_module_py
    File "<frozen importlib._bootstrap_external>", line 678, in exec_module
    File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
    File "migrations/env.py", line 96, in <module>
    File "migrations/env.py", line 90, in run_migrations_online
    File "<string>", line 8, in run_migrations
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/alembic/runtime/environment.py", line 839, in run_migrations
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/alembic/runtime/migration.py", line 362, in run_migrations
    File "/mnt/c/flask/myapp/migrations/versions/4ee052488c48_changed_task_id_type.py", line 24, in upgrade
    File "<string>", line 8, in alter_column
    File "<string>", line 3, in alter_column
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/alembic/operations/ops.py", line 1775, in alter_column
    return operations.invoke(alt)
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/alembic/operations/base.py", line 345, in invoke
    return fn(self, operation)
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/alembic/operations/toimpl.py", line 56, in alter_column
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/alembic/ddl/mysql.py", line 96, in alter_column
    else existing_comment,
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/alembic/ddl/impl.py", line 134, in _exec
    return conn.execute(construct, *multiparams,
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 988, in execute
    return meth(self, multiparams, params)
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/sqlalchemy/sql/ddl.py", line 72, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1050, in _execute_ddl
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
    e, statement, parameters, cursor, context
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1466, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 399, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 153, in reraise
    raise value.with_traceback(tb)
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
    cursor, statement, parameters, context
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 550, in do_execute
    cursor.execute(statement, parameters)
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/pymysql/cursors.py", line 328, in _query
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/pymysql/connections.py", line 517, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/pymysql/connections.py", line 732, in _read_query_result
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/pymysql/connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/pymysql/connections.py", line 684, in _read_packet
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/pymysql/protocol.py", line 220, in check_error
    File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
    sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1146, "Table 'myapp.result' doesn't exist")
    [SQL: ALTER TABLE result MODIFY payload_json TEXT(2000) NULL]
    (Background on this error at: http://sqlalche.me/e/f405)

    I want to run several tasks at the same time and also save the task results. I am still trying to figure out the best database design for this, so most likely I will be modifying the DB as I go. Here are my models for 'task' and 'result' tables:

    class Result(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    task_id = db.Column(db.String, db.ForeignKey('task.id'))
    payload_json = db.Column(db.Text(2000))

    class Task(db.Model):

    __tablename__ = 'task'
    id = db.Column(db.String(50), primary_key=True)
    name = db.Column(db.String(128), index=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    results = db.relationship('Result', backref='task', lazy='dynamic')
    complete = db.Column(db.Boolean, default=False)

    I thought about re-creating the tables via MySQL Workbench but I wasn't 100% sure what SQLAlchemy actually does with backref and lazy commands (what are the corresponding SQL commands).

  • #118 Miguel Grinberg said

    @Tim: the migration script seems to assume the myapp.result table exists, but in reality it does not. You may want to delete that migration script and generate a new one. Then you should ensure this table is detected as a new table, not as an existing table that needs a change.

  • #119 Vova said

    Hi Miguel. I have a question. Is it okay that - after opening app.db - I see a wrong encoding? I mean like this https://clip2net.com/s/4767OF3. After I change the encoding, the migration breaks, and it's impossible to do anything even if I return to the previous encoding. I have to completely remove the app.db to make everything work again, but it's a bed solution in many ways I guess...

  • #120 Miguel Grinberg said

    @Vova: were you opening the sqlite file in a text editor? That will never work, this file is not in any encoding, it is a binary file, so it will never display correctly in a text editor.

  • #121 Andrés Darío Altamirano said

    Hello Miguel! I have a doubt regarding the db migrate. What do you think it is the best approach for using migrations that are from different packages such as sqlalchemy_utils?
    I am trying to use sqlalchemy_utils.UUID.types.UUIDType for a field on a MySQL database, but when a migration is created, Alembic does not import that sqlalchemy_utils library so the migration crashes. Of course it would be easy to add the import inside the migration file, or, add it in the script.py.mako so it can be used broadly, but, in case you are in a project where the whole migrations folder is not versioned and docker takes care of creating that folder and apply the detected migrations, I can't find an decent way to achieve the use of a third party library as this one.



  • #122 Miguel Grinberg said

    @Andrés: the only two solutions I know are the two that you mentioned: either add the import manually, or add it to the template migration file. I do not know what use case you have where migrations aren't versioned. Neither Flask-Migrate nor Alembic are intended to be use that way, since automatic migration generation is not always possible.

  • #123 Rahul said

    How do we manage migrations when working on the same project with multiple team members using a central remote repo, what strategy can be used so that migration versions don't diverge when someone is making changes?

  • #124 Miguel Grinberg said

    @Rahul: This is handled by Alembic, not this extension. See the branching and merging support in the Alembic docs.

Leave a Comment