Flask-Migrate: Alembic database migration wrapper for Flask
Posted by
on underIn 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
-
#26 Miguel Grinberg said
@Aidan: the migrate command generates only the differences between the model definition and the current structure of your database. If you want to generate the entire database then you need to destroy all the tables and revert your db to empty.
-
#27 isccarrasco said
Hi Miguel...
I'm using Flask-Migrate, but i need to customize some elements of the migrations, particularly the name and schema of the index, there are a way to do this?...thanks..
-
#28 Miguel Grinberg said
@isccarrasco: yes. After you generate the migration (but before you upgrade your database) you can change the script. Just make sure you change the upgrade and downgrade functions consistently.
-
#29 Miho said
Hi Miguel,
first of all, thanks for the great tutorials!
I have the same problem as Aidan in manage.py.If I replace line "from app.models import User" with actual model from models.py it generates a migrate script for table users.
But if i leave that line, it does no generate any changes for migration?
It simply generates empty file.Thx
-
#30 Miguel Grinberg said
@Miho: as I said above, you need to have an empty database for the migration to be generated. Delete your database tables and then you should be getting a migration script.
-
#31 Miho said
Thanks for the reply Miguel.
The problem is that my database actually is empty. There is no table "user" created in it.
If I take that database and execute manage.py with model declaration in it, i get a migration script with new table.
But if i try to execute manage.py with importing model from models.py, the resulting script is empty?Workaround is to c/p the model into manage.py (to continue with tutorial :)). But there must be a something somewhere I'm missing
-
#32 Miguel Grinberg said
@Miho: can you show me in detail what the alternative is? What code do you add and where? I don't understand what you are changing to make things work.
-
#33 Luke Hansford said
Thanks for the great work on this!
-
#34 John Hitz said
Hey MIguel! I got the following error on openshift: File "/var/lib/openshift/52f50dc84382ec899e000223/python/virtenv/lib/python2.7/site-packages/psycopg2/init.py", line 50, in <module>
from psycopg2._psycopg import BINARY, NUMBER, STRING, DATETIME, ROWID
ImportError: libpq.so.postgresql92-5: cannot open shared object file: No such file or directory. At first I thought this was a problem with openshift and postgresql-9.2. However, had the same problem in 8.4 and mysql-5.1.Could be a mistake I am making...I'm fairly new to programming. Hope this helps you find any potential problems.
-
#35 Miguel Grinberg said
@John: Have you seen this discussion thread? https://bugzilla.redhat.com/show_bug.cgi?format=multiple&id=986219
It appears OpenShift does not set LD_LIBRARY_PATH to include the postgres libraries, so they suggest a workaround where you set it on your side. -
#36 Bogdan Alexandrescu said
Hi Miguel,
Once again, a great tutorial!
I have a really burning question related to flask-migrate and working with multi databases.I am using
SQLALCHEMY_BINDS = {
'db1': 'mysql://user:pass@localhost/db1',
'db2': 'mysql://user:pass@localhost/db2'
}
to provide a dictionary of databases that can be used by sqlalchemy and I am defining tables the usual way but including the
bind_key='db1'
or
bind_key='db2'
in each model.This works well when I want to run db.create_all() but it doesn't run with flask-migrate. I also have tried to find a way to edit the alembic env.py to do this but I can't figure out how to do it and the documentation on env.py didn't help in this case.
Do you have any suggestions? Any help will be greatly appreciated!
-
#37 Miguel Grinberg said
@Bogdan: you have two options. #1 you manage each db separately, each with its own migration directory. For this you pass the migration directory to use in the cmd line. #2 customize env.py to do multiple dbs. A partial example is shown in the Alembic documentation: http://alembic.readthedocs.org/en/latest/tutorial.html#customizing-the-environment. I would go with #1, seems a lot simpler and less error prone.
-
#38 Bogdan said
Hi Miguel,
Thanks for replying to me so fast!
How exactly I configure flask migrate to migrate two different databases? Can I set 2 migrate commands and use them like:
manage.py db1 migrate
manage.py db2 migrate?How do I tell flask migrate where to get the two databases that i want to put in those separate folders? Now I am using SQLALCHEMY_BINDS to tell sqlalchemy what databases to use and then add bind_key to each model so it knows where to look for it.
I am new at using Flask and SQLAlchemy and I am sorry for the noob questions :)
This is how I have my manage.py written:
from app import app, manager
from flask.ext.migrate import MigrateCommand
manager.add_command('db', MigrateCommand)
app.debug = True
manager.run()And this is how I create the instances in my init.py:
...
app = Flask(name)
app.config.from_object('config')
db = SQLAlchemy(app)
migrate = Migrate(app, db)
manager = Manager(app)
...What do you think?
P.S. the alambic documentation is very vague about woking with multiple databases that are using sqlalchemy binds.. unfortunately -
#39 Miguel Grinberg said
@Bogdan: Working with multiple databases is a non-trivial effort. Flask-Migrate will configure the migrations for the main database, but you will need to create either manually (by copying files from the first migration directory) or using the alembic command. Once you have two migration directories configured one for each database you can pass the directory to "manage.py db" using the -d command line option. That way you can tell Flask-Migrate to use the database that you want. If you do not want to deal with such complications then I recommend that you find a way to unify your data into a single database, at least until you are more comfortable to hack the framework.
-
#40 Carlos Valdivia said
excellent job, just a question, how can i configure the alembic.ini file for the script_location?
alembic need this parameter to execute another useful task
Thanks and regards -
#41 Miguel Grinberg said
@Carlos: you can edit alembic.ini manually if you like, but specifically for script_location my extension defaults it to "migrations", and you can pass a different location in the command line using "-d" option.
-
#42 Blake said
Hi Miguel,
Love your website - tutorials have been a big help. I have a separate config file with DB set up as SQLALCHEMY_DATABASE_URI = 'sqlite:///' + os.path.join(basedir,'foodo.db'). When I run Init/migrate/update the 'migrations' folder but the actually db is not created in my source folder.
-
#43 Miguel Grinberg said
@Blake: does the db exist in some other folder? How do you set "basedir"?
-
#44 Paul Aphivantrakul said
Hello Miguel,
Thank you for the great tutorial. Is it possible to use Flask-Migrate on a Flask application running off an apache web server and using mod_wsgi? I don't believe I am using the app.run() command in my application, so I am not sure if the manager.run() command will be executed.
Thank you,
Paul
-
#45 Miguel Grinberg said
@Paul: Yes. Flask-Migrate runs from the command line, it does not care what web server you use because it works outside of the server environment. You have to use the manage.py script to run it, even if your production server does not use it when it runs the application.
-
#46 Crowdstar said
Miguel, I just got your book and am working through my first site. What are your thoughts on using sqlsoup and not declaring the models at all? I started down that path but I got confused when Flask-login had required methods for the User class. I am more comfortable building my db in mysql than having the models generate the tables.
Great book! Thanks! -
#47 Miguel Grinberg said
@Crowdstar: the user object that you give you Flask-Login does not need to be a database model. You can create a User class that just stored the user id and use that. As long as you implement all the required methods and the user loader callback you will be fine.
-
#48 shankar said
hello Miguel,
once i am trying to upgrade using python manage.py db migrate
it shows errorraise util.CommandError('Only a single head is supported. The '
alembic.util.CommandError: Only a single head is supported. The script directory has multiple heads (due to branching), which must be resolved by manually editing the revision files to form a linear sequence. Runalembic branches
to see the divergence(s).. Any suggestion on how can i have linear sequence
-
#49 Miguel Grinberg said
@shankar: 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.
-
#50 hewx said
How does Flask-Migarte detect the migrations automaticly