Flask migrate error target database is not up to date

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 init command:

(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 migrate command:

(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 migrate command:

(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 stamp command»

(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

Migration Workflow

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

Conclusion

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.