A question that I frequently get is how to start tracking database migrations with my Flask-Migrate extension when the project has an established database, making it impossible to delete everything and start over from scratch. In this article and companion video I'm going to show you how to set up Flask-Migrate with zero risk for your current database.
Installing and Configuring Flask-Migrate
This step is the same for new and existing projects. Assuming you have a project that uses Flask-SQLAlchemy to manage a database, you begin by installing the Flask-Migrate extension:
(venv) $ pip install flask-migrate
The extension then needs to be added to the project. This involves creating and initializing a
Migrate instance. If you use the direct method of creating extensions:
from flask_migrate import Migrate app = Flask(__name__) db = SQLAlchemy(app) migrate = Migrate(app, db)
If you use an application factory function you very likely use the delayed method to create your extensions. In that case you would initialize Flask-Migrate as follows:
from flask_migrate import Migrate db = SQLAlchemy() migrate = Migrate() def create_app(): app = Flask(__name__) db.init_app(app) migrate.init_app(app, db) return app
Once the extension is added to the project you will have a
flask db command available:
(venv) $ flask db Usage: flask db [OPTIONS] COMMAND [ARGS]... Perform database migrations. Options: --help Show this message and exit. Commands: branches Show current branch points current Display the current revision for each... downgrade Revert to a previous version edit Edit a revision file heads Show current available heads in the script... history List changeset scripts in chronological... init Creates a new migration repository. merge Merge two revisions together, creating a new... migrate Autogenerate a new revision file (Alias for... revision Create a new revision file. show Show the revision denoted by the given... stamp 'stamp' the revision table with the given... upgrade Upgrade to a later version
Creating the Migration Repository
The next step in the process is to create a database migration repository. This is a directory where all the migration scripts are going to be stored. The migration repository should be considered part of your source code, and should be added to your source control.
To create the repository you can use the
(venv) $ flask db init Creating directory /Users/mgrinberg/microblog/migrations ... done Creating directory /Users/mgrinberg/microblog/migrations/versions ... done Generating /Users/mgrinberg/microblog/migrations/script.py.mako ... done Generating /Users/mgrinberg/microblog/migrations/env.py ... done Generating /Users/mgrinberg/microblog/migrations/README ... done Generating /Users/mgrinberg/microblog/migrations/alembic.ini ... done Please edit configuration/connection/logging settings in 'migrations/alembic.ini' before proceeding.
This command adds a migrations directory in the root of your project. Add the directory and all of its contents to source control.
Creating the Initial Migration
If you look at the documentation, the next step in the process is to create an initial migration for your project, using the
(venv) $ flask db migrate
This step does not actually work if you have an existing project that has a populated database:
(venv) $ flask db migrate INFO [alembic.runtime.migration] Context impl SQLiteImpl. INFO [alembic.runtime.migration] Will assume non-transactional DDL. INFO [alembic.env] No changes in schema detected.
The "No changes in schema detected" message indicates that there is no need to migrate the database. But how can that be if we are just getting started with migrations in this project?
To understand the problem you have to think about how Alembic (the migration engine used by Flask-Migrate) generates database migrations. The contents of a migration are obtained by running a comparison between the current model definitions and the current schema of your database.
In a new project the models are going to be compared against a brand new (empty) database, so the migration script will include all the model definitions. For an existing project the database is up to date and in sync with the models, so there are no differences, and thus, Alembic thinks that there is nothing to put in the database migration script.
The trick to generate this initial migration for a project that uses an up to date database is to temporarily switch to an empty database, just for the
flask db migrate command. In my projects I use a
DATABASE_URL environment variable to configure the location of the database. This is a good practice that I recommend you follow as well, because with this variable it is easy to redirect to another database.
In general you can replace your real database with an in-memory SQLite database, just for the
(venv) $ DATABASE_URL=sqlite:/// flask db migrate INFO [alembic.runtime.migration] Context impl SQLiteImpl. INFO [alembic.runtime.migration] Will assume non-transactional DDL. INFO [alembic.autogenerate.compare] Detected added table 'user' INFO [alembic.autogenerate.compare] Detected added index 'ix_user_email' on '['email']' INFO [alembic.autogenerate.compare] Detected added index 'ix_user_token' on '['token']' INFO [alembic.autogenerate.compare] Detected added index 'ix_user_username' on '['username']' INFO [alembic.autogenerate.compare] Detected added table 'followers' INFO [alembic.autogenerate.compare] Detected added table 'message' INFO [alembic.autogenerate.compare] Detected added index 'ix_message_timestamp' on '['timestamp']' INFO [alembic.autogenerate.compare] Detected added table 'notification' INFO [alembic.autogenerate.compare] Detected added index 'ix_notification_name' on '['name']' INFO [alembic.autogenerate.compare] Detected added index 'ix_notification_timestamp' on '['timestamp']' INFO [alembic.autogenerate.compare] Detected added table 'post' INFO [alembic.autogenerate.compare] Detected added index 'ix_post_timestamp' on '['timestamp']' INFO [alembic.autogenerate.compare] Detected added table 'task' INFO [alembic.autogenerate.compare] Detected added index 'ix_task_name' on '['name']' Generating migrations/versions/97180cecd04b_.py ... done
Using an in-memory SQLite database is very convenient, but will only work if you are in one of the two following situations:
- You are also using SQLite for your real database
- You are using another database engine (MySQL, Postgres, etc.), but your models are written generically, without using any constructs that are specific to your chosen engine.
If you cannot use an in-memory SQLite database, then go to your database server and create a new database there, and then set
DATABASE_URL to it for the
migrate command. Once you have generated the first migration you can delete the empty database from your server.
The next step according to the documentation is to run the
upgrade command, which executes the migration script and applies the changes in it to your database. Obviously this is also going to fail, because the database does not need updating. Instead you have to tell Flask-Migrate and Alembic that the database is up to date. You can do this with the
(venv) $ flask db stamp head
This command will add a
alembic_version table to your database, and will store the initial migration generated above as the current migration. The
head alias always points to the most recent migration, which in our case is the first and only one currently in the repository.
As I mentioned above, the contents of the migrations directory needs to be under source control. Remember to add the new migration script, which you will find in the migrations/versions directory.
Updating Additional Databases
You will likely have multiple databases for your application, at least one for development and one for production. You may be part of a team, so each team member will have their own development database in their local systems. Once you created the initial migration and added it to source control, you can just "stamp" all the additional databases. For each additional database make sure the code is refreshed from source control to include the migration repository and then just run the
stamp command from above to "fix" the database:
(venv) $ flask db stamp head
From this point on you have a project that is fully enabled to use database migrations. The normal migration process goes as follows:
- You will make some changes to your models in your Python source code.
- You will then run
flask db migrateto generate a new database migration for these changes.
- You will finally apply the changes to the database by running
flask db upgrade.
This cycle repeats every time new changes to the database schema are needed.
I hope this short tutorial clarifies how to add database migrations to your existing project. If you want to avoid the extra trouble required to get that first migration generated, in your next project consider adding Flask-Migrate from the start. It may seem unnecessary at first, but you'll eventually want to have it.