2020-04-09T17:03:54Z

How To Add Flask-Migrate To An Existing Project

Posted by Miguel Grinberg under Database, Programming, Python, Flask, Video.

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.

Hello, and thank you for visiting my blog! If you enjoyed this article, please consider supporting my work on this blog on Patreon!

66 comments

  • #1 ed said 2020-05-07T00:44:45Z 

    Could you give an update on how to fix the error "ERROR [root] Error: Can't locate revision identified by '3f915c78527a"  I noticed you said its on your github but no clue where been looking!

  • #2 Miguel Grinberg said 2020-05-07T12:55:11Z 

    @ed: this error happens when you remove migration scripts by hand. There isn't a generic fix, it really depends on what you've deleted and if it is recoverable. Not sure what you found that I said about this, can you provide a reference so that I have more context?

  • #3 Guillermo said 2020-05-16T02:36:41Z 

    I really enjoyed and learned a LOT from your blog posts on Flask, and now (watching this video on Flask-migrate) I picked up a slight Argentinian English speaker accent! Congratulations on your awesome work! Keep it up and greetings from Argentina!

  • #4 Sven Fanghänel said 2020-06-04T06:02:05Z 

    Hi Miguel,
Thanx for your post.
I have a question.
Where is the point where i can migrate my existing Database content to the new Database?
Thanx Sven

  • #5 Miguel Grinberg said 2020-06-04T09:31:02Z 

    @Sven: this article isn't about migrating the data to a new database. It is about incorporating database migrations into an existing project and database. The term "migration" here applies to transforming a database in place, not moving the data to another database.

  • #6 Shankar Ram Ramasubramanian said 2020-06-05T08:58:11Z 

    Hi Miguel, i am having Trouble with the same. i created a database using Pandas.read_sql and now i have to implement key constraints. But for an exisiting database i am not getting a way to add a new  Primary key column.  I am facing Errors. i have added the link to my Problem in stackoverflow. can you help me out of how to get it done?
    

    https://stackoverflow.com/questions/62197051/flask-sqlalchemy-cannot-add-primary-key-constraint-with-migration

  • #7 Miguel Grinberg said 2020-06-05T10:14:13Z 

    @Shankar: I would create a database schema properly in SQLAlchemy, then import the data from Pandas into it. The way you are doing it seems much more complicated.

  • #8 Shankar Ram Ramasubramanian said 2020-06-05T11:04:26Z 

    @Miguel: i could do this if i have a small data. I have like around 100 files with 350 columns in it. Writing a Schema for everything would be a very bigger Task. that's why. Are there any other alternatives to get this working?

  • #9 Miguel Grinberg said 2020-06-05T11:13:44Z 

    @Shankar: Flask-Migrate will require you having schemas anyway, it will not work without models. The alternative would be to not use database migrations, just generate your database and then use SQLAlchemy with it. Why do you need Flask-Migrate?

  • #10 Shankar Ram Ramasubramanian said 2020-06-05T11:23:35Z 

    @Miguel: my Scenario is to store all the files in the database for easy querying of various columns from different tables. So for querying i have implement Primary and foreign key constraints for either select Statements ( (or) joins. i cannot implement key constraints in my case while creation. So for that Purpose i tried this Migration(flask-migrate)

  • #11 Miguel Grinberg said 2020-06-05T12:49:03Z 

    @Shankar: run the "alter column" commands to add your constraints directly on the database. As I said, Flask-Migrate uses models, if you are not going to define models then it won't work for you.

  • #12 vipin said 2020-06-16T06:15:17Z 

    Hi miguel,
i am new to flask-migrate and i am not able to use flask-migrate api as i wanted to do db migrations programmatically in my api. please help!!!

  • #13 Cristian said 2020-06-16T20:40:52Z 

    Hola Miguel, esto me sirve para hacer que mi app construya automáticamente el contenido de mi base de datos con sus respectivos datos? o qué puedo hacer para lograr eso.
Necesito hacer que al iniciar una app en flask se me cree la estructura de una base de datos y con los datos que tengo en un .sql
qué me recomendas ?
Gracias !

  • #14 Miguel Grinberg said 2020-06-17T10:54:57Z 

    @vipin: the documentation includes everything that you can do with the project. Not sure what you mean by "programmatically" in this context, but check the docs, and if it isn't there, it cannot be done.

  • #15 Miguel Grinberg said 2020-06-17T11:06:02Z

  • #16 Pat said 2020-06-17T15:58:44Z 

    Hi Miguel, This sounds similar to @vipin query but I'll ask anyway. I have a flask app using migrate and what I want is to check the current version of the database upon restart if the server goes down. I want to check this automatically from with the server code when the database comes back up. I can do it off the command line no problem as per your docs. My problem is how to put this in my server code so it does it 'programmatically'.
hopefully you can shed some light on this. Hope your enjoying this lovely Irish weather!

  • #17 Miguel Grinberg said 2020-06-17T22:04:05Z 

    @Pat: create a script that runs flask db upgrade and then starts the server. Would that work? Doing this from the server itself is more complicated, depends on which server you use, how many worker processes, etc. Not worth the trouble.

  • #18 Luc said 2020-06-26T10:36:55Z 

    Hey Miguel !
First of all, thanks for your thorough posts and the Mega Tutorial, you're a very brillant pedagogue!
Still new to web development in general, I got one question:
Is there anything wrong with migrating only additional schemas modifications from an up-to-date db and not migrating from the "empty-db trick" ? I guess only new incremental modifications will be recorded, bad practice ? which use-case are you targeting by migrating from an empty db?

  • #19 Miguel Grinberg said 2020-06-26T22:53:28Z 

    @Luc: there isn't anything wrong, but you will not be able to stand up a brand new database from your migration history. You will need to create the new database up to the place where the first migration was recorded and only then run your migrations.

  • #20 Aravind said 2020-07-27T17:34:33Z 

    Hey Miguel, thanks for sharing the info. 
Working on a production db in which it is already been accommodated with migration for postgresql db. It's multitenant application connected to single database. 
i)Running db migrate and db upgrade is not updating the table records, instead drops it. 
ii)The db upgrade is dropping all the table and upgrade will have all the old ran db version details. Why so? unable to rectify.

  • #21 Miguel Grinberg said 2020-07-27T21:51:40Z 

    @Aravind: If the migration wants to drop all tables then it means that Flask-Migrate/Alembic do not see your models. Maybe you are not importing them, so they are not getting registered with SQLAlchemy.

  • #22 Troy said 2020-09-23T21:03:28Z 

    Hi, thank you for this information. I can't get past creating the empty database. I get this error:
    

    'DATABASE_URL' is not recognized as an internal or external command,
operable program or batch file.
    

    Thanks!

  • #23 Miguel Grinberg said 2020-09-25T10:01:03Z 

    @Troy: I assume you are on Windows. To set the DATABASE_URL environment variable on windows do it as follows:
    
set DATABASE_URL=sqlite:///
flask db migrate

  • #24 Kevin A Beier said 2020-11-16T04:51:52Z 

    getting this error when I do the DATABASE_url=url_to_db flask db migrate step
    

    Traceback (most recent call last):
  File "/home/fox/Desktop/api factory/venv/bin/flask", line 12, in <module>
    sys.exit(main())
  File "/home/fox/Desktop/api factory/venv/lib/python3.8/site-packages/flask/cli.py", line 967, in main
    cli.main(args=sys.argv[1:], prog_name="python -m flask" if as_module else None)
  File "/home/fox/Desktop/api factory/venv/lib/python3.8/site-packages/flask/cli.py", line 586, in main
    return super(FlaskGroup, self).main(args, kwargs)
  File "/home/fox/Desktop/api factory/venv/lib/python3.8/site-packages/click/core.py", line 782, in main
    rv = self.invoke(ctx)
  File "/home/fox/Desktop/api factory/venv/lib/python3.8/site-packages/click/core.py", line 1259, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/fox/Desktop/api factory/venv/lib/python3.8/site-packages/click/core.py", line 1259, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/fox/Desktop/api factory/venv/lib/python3.8/site-packages/click/core.py", line 1066, in invoke
    return ctx.invoke(self.callback, ctx.params)
  File "/home/fox/Desktop/api factory/venv/lib/python3.8/site-packages/click/core.py", line 610, in invoke
    return callback(args, kwargs)
  File "/home/fox/Desktop/api factory/venv/lib/python3.8/site-packages/click/decorators.py", line 21, in new_func
    return f(get_current_context(), *args, kwargs)
  File "/home/fox/Desktop/api factory/venv/lib/python3.8/site-packages/flask/cli.py", line 426, in decorator
    return ctx.invoke(f, args, kwargs)
  File "/home/fox/Desktop/api factory/venv/lib/python3.8/site-packages/click/core.py", line 610, in invoke
    return callback(args, **kwargs)
  File "/home/fox/Desktop/api factory/venv/lib/python3.8/site-packages/flask_migrate/cli.py", line 89, in migrate
    _migrate(directory, message, sql, head, splice, branch_label, version_path,
  File "/home/fox/Desktop/api factory/venv/lib/python3.8/site-packages/flask_migrate/init.py", line 96, in wrapped
    f(args, *kwargs)
  File "/home/fox/Desktop/api factory/venv/lib/python3.8/site-packages/flask_migrate/init.py", line 210, in migrate
    command.revision(config, message, autogenerate=True, sql=sql,
  File "/home/fox/Desktop/api factory/venv/lib/python3.8/site-packages/alembic/command.py", line 214, in revision
    script_directory.run_env()
  File "/home/fox/Desktop/api factory/venv/lib/python3.8/site-packages/alembic/script/base.py", line 489, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/home/fox/Desktop/api factory/venv/lib/python3.8/site-packages/alembic/util/pyfiles.py", line 98, in load_python_file
    module = load_module_py(module_id, path)
  File "/home/fox/Desktop/api factory/venv/lib/python3.8/site-packages/alembic/util/compat.py", line 184, in load_module_py
    spec.loader.exec_module(module)
  File "<frozen importlib._bootstrap_external>", line 783, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "migrations/env.py", line 36, in <module>
    str(current_app.extensions['migrate'].db.get_engine(
  File "/home/fox/Desktop/api factory/venv/lib/python3.8/site-packages/flask_sqlalchemy/init.py", line 962, in get_engine
    return connector.get_engine()
  File "/home/fox/Desktop/api factory/venv/lib/python3.8/site-packages/flask_sqlalchemy/init.py", line 549, in get_engine
    uri = self.get_uri()
  File "/home/fox/Desktop/api factory/venv/lib/python3.8/site-packages/flask_sqlalchemy/init__.py", line 545, in get_uri
    return binds[self._bind]
TypeError: string indices must be integers

  • #25 Miguel Grinberg said 2020-11-16T18:41:33Z 

    @Kevin: it is probably something wrong in your database configuration. Can you show me all the configuration variables related to database that you have in your application?

Leave a Comment