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

#101 Leon said 2016-08-17T19:27:59Z

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 2016-08-24T14:22:50Z

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>
manager.run()
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 2016-08-25T05:19:27Z

@Shahab: did you initialize the Migrate class?
#104 Miguel Grinberg said 2016-08-25T05:24:34Z

@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 2016-09-29T06:54:14Z

Hi,

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 2016-09-29T18:29:58Z

@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 2017-06-20T21:34:51Z

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 2017-06-21T04:44:06Z

@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 2019-09-12T06:34:11Z

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 2019-09-12T19:49:36Z

@Pranay: you need to import your models. If you do not import them, then SQLAlchemy does not know about them.
#111 Mike A said 2019-09-18T14:14:57Z

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 2019-09-19T08:17:17Z

@Mike: this issue explains the problem and provides some solutions: https://github.com/miguelgrinberg/Flask-Migrate/issues/143.
#113 Ravina said 2019-10-07T07:59:55Z

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 2019-10-07T21:59:02Z

@Ravina: you need to install pymysql into your virtual environment.
#115 Tim said 2019-10-15T10:33:00Z

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 2019-10-15T16:59:10Z

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 2019-10-17T12:46:01Z
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
conn.query(q)
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
result.read()
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
packet.check_error()
File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/pymysql/protocol.py", line 220, in check_error
err.raise_mysql_exception(self._data)
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>
sys.exit(main())
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, ctx.params)
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, kwargs)
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
f(*args, kwargs)
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
script.run_env()
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
spec.loader.exec_module(module)
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>
run_migrations_online()
File "migrations/env.py", line 90, in run_migrations_online
context.run_migrations()
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
self.get_context().run_migrations(kw)
File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/alembic/runtime/migration.py", line 362, in run_migrations
step.migration_fn(kw)
File "/mnt/c/flask/myapp/migrations/versions/4ee052488c48_changed_task_id_type.py", line 24, in upgrade
existing_nullable=True)
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
operation.kw
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, params)
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
compiled,
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
conn.query(q)
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
result.read()
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
packet.check_error()
File "/home/user/.virtualenvs/venv/lib/python3.6/site-packages/pymysql/protocol.py", line 220, in check_error
err.raise_mysql_exception(self._data)
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 2019-10-17T16:42:11Z

@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 2020-04-19T00:37:28Z

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 2020-04-19T10:58:00Z

@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 2021-01-21T04:03:54Z

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.

Regards

Andrés
#122 Miguel Grinberg said 2021-01-21T12:26:13Z

@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 2021-08-31T17:44:34Z

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 2021-08-31T18:40:32Z

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