How do you add migrate an existing database with alembic/flask-migrate if you did not start off with it?

Question

This is the chain of events that has and is happening

• Day 0: I developed and deployed my app
• Day 1: I create the new database
• Day 3: I realized I wanted to add a new row to my existing table. I found flask-migrate and I want to use it to migrate my database.

Currently I am at Day 3

There are plenty of documentations on how to get Flask-migrate running if you start from Day 0. You just call flask db init, flask db migrate and flask db upgrade.

However, for my case, it's a bit different. I ran the commands, and my first version of migration is empty. Then I modified my database schema and generated a new migration. Now my latest migration only has 1 line of migration which is adding the new row to the table.

I realized that none of my migrations have the actual schema to create the database, which is supposed to be the first migration you see if you start flask-migrate on day 1.

If I were to clone my repo from scratch:

flask db migrate will result in alembic.util.exc.CommandError: Target database is not up to date..

flask db upgrade will result in sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) relation "offer" does not exist.

What can I do to fix this?

Answer 1

You have two options.

1) If you only want to track database migrations going forward

• Run flask db init, to create the migration repository.
• Add the new column to your database model.
• Run flask db migrate, to generate a migration. The migration script will only have the new column in it.
• Run flask db upgrade to apply the new migration to your database.

At this point your database should have the new column, and you can continue working. Repeat the above steps any time you need to make additional changes.

Note that with this approach, you cannot recreate the entire database from scratch. You have to have a way to initialize the database to the schema you had on Day 1, and then you can apply the migration history to that to upgrade it to your current schema.

2) If you want to keep track of the entire migration history, including the schema on the day you are adding Flask-Migrate to your application.

This is a little bit tricky, but it can be done.

• Start with flask db init, to create the migration repository.
• Next, you need to trick Flask-Migrate into thinking your database is empty. You can do this by renaming your actual db, and creating a new db with the same name that has no tables in it. In that state, run flask db migrate. This will generate a migration that contains the entire schema of your database.
• Once you have that initial migration, restore your database to the correct state.
• Run flask db stamp head to mark the database as updated.
• Add the new column to your database model.
• Run flask db migrate again, to generate a second migration. The migration script will only have the new column in it.
• Run flask db upgrade to apply the new migration to your database.

Hope this helps!