Search code examples
flaskflask-sqlalchemydatabase-migrationcontinuous-deploymentflask-migrate

Using Flask-Migration in development and deployment

I've been looking through the stackoverflow posts on flask development regarding database migrations using Flask-Migrate. Yet I'm still not satisfied and left pondering for the best practice on database migration for managing development and deployment server.

What I learned so far

Suggested by Miguel himself in another post,

The ideal solution is that you generate an initial migration for your db schema as it was the day you started tracking migrations with Flask-Migrate and Alembic. ... Just create a separate empty database (leave your real db alone), configure your app to use the empty database, and then generate a migration. This migration will have the entire schema. Once you have that migration generated, get rid of the empty database and restore your configuration back to the real db.

The practice above require me to make two database in deployment server, one is dummy database in which I perform the two commands:

flask db migrate # to empty dummy database
# then get rid of dummy database
# then change config (e.g. DATABASE_URI) to real deployment database
flask db upgrade # then upgrade the real database

My Questions remains

  1. What really is the best practice when dealing with database migrations on deployment/prod environment with some "live fish" (real data already populates the production DB). My question doesn't have to be specific to Flask-Migrate and I do sometimes deal with this problem in Django and more often in Flask where in the end, I have to completely reset the migration script to initial state. I hope guys from different tech stack can relate to me.
  2. When deploying on fresh container, my current practice is to copy over the flask's migrations script folder from development, then perform flask db upgrade immediately. Does the table in the database need to exists first? Does upgrade command guarantee to perform creation just like db.create_all() ?

Note: On question 2 is Flask-Migrate specific. The reason I doubted this is because seldom on fresh container, I get strange error on flask db upgrade:

Cannot create User table. Table already existed...

while there had been no table setup in the fresh container before and I remember this happened while ago when I deploy my app locally using Docker. Another time, the flask db upgrade doesn't perform any table creation which is strange. Note that I never used/invoke db.create_all() before in the fresh container nor in development.

Update 25th of March 2021

It's been a year and now I'm dealing a lot with this kind of problems. There are indeed many different way of performing DB migration and this question is not tied to just Flask Migrate.

Any web-app developers (Go, Python, Ruby, etc) that needs a relational-DBMS as their storage needs to think on how to provide an always backward compatible migration script with the previous DB.

Most of the time your code app need to change along with the change in DB migration, but it doesn't mean the migration has to be coupled with app deployment. It depends on your devops policy.

As a rule of thumb:

  • Have an up and down script
  • Up script should not contains drop column or table. If a column of table is obsolete, drop it manually after the migration is successfull, the older app instance is rolled out and everything is stable with the new migration + app code.
  • Alter in up script is okay but mind you that changing column name or data type is non-compatible. Handle with care. You can make new column, transform data in old column to new column, then drop the older column. Or downtime is okay, just take time to deploy your new app that is compatibl with the new db schema.
  • Down script should not contains create, insert or alter.
Solution

  • I believe you have partially misinterpreted the comment that you quoted, or maybe haven't seen the full answer.

    Migrations are always generated in your development database. Production have nothing to do with generating migrations.

    If you have an existing database and you want to generate an initial migration, then the process is:

    • create an empty database
    • configure the app to use this empty db
    • run flask db migrate to generate the initial migration
    • switch back to the original database
    • run flask db stamp head in all of your database (dev, prod, etc.) to mark them as upgraded
    • delete the empty database
    • from now on, you can migrate and upgrade the database in the normal way.

    Regarding your second question, the answer is yes. Alembic (through Flask-Migrate) creates new tables when they do not exist.