3

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.
Daniel Kurniadi
  • 178
  • 2
  • 13

1 Answers1

2

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.

Miguel Grinberg
  • 65,299
  • 14
  • 133
  • 152
  • If I get you correctly, the above steps you defined is for fresh application as the case by the quoted posts? – Daniel Kurniadi Dec 27 '19 at 05:56
  • Correct. If you don't care about an initial migration then you can just migrate and upgrade, and you would only be tracking changes going forward. – Miguel Grinberg Dec 27 '19 at 07:22
  • Thanks for the answer @Miguel, do you have any recipe about handling migration on ci/cd pipeline? – Espoir Murhabazi Sep 04 '20 at 07:53
  • Thanks for the answer @Miguel, do you have any recipe about handling migration on ci/cd pipeline? – Espoir Murhabazi Sep 04 '20 at 07:53
  • @EspoirMurhabazi unclear what you are asking about specifically. You will always need to do an upgrade as part of your build. Any concerns besides that? – Miguel Grinberg Sep 04 '20 at 13:43
  • @Miguel: This is not clear to me either. I have been following your Flask Mega Tutorial. I am trying to deploy the blog and his is how my CI/CD is defined to start the flask app. `entrypoint: gunicorn -b :8080 main:app`. I want to ensure that my production DB has the new migrations applied to it during deployment. Where and how would I run the `flask db upgrade` command? I cannot upgrade the db before deployment as new model's will only be available after deployment of the app. – Raqib Nov 15 '20 at 23:37
  • @Raqib you would stop the server, refresh the code (i.e. `git pull` or similar), then run `flask db upgrade`, and finally restart the server. – Miguel Grinberg Nov 16 '20 at 16:48
  • What would I do if stopping the server is not an option (or is hidden from the point of view of the developer) as it is in a production environment? I have used migrations in Java with Flyway where we could call the Flyway API from within our code during app initialization and it would check if there is a diff between the migrations in our migrations folders and the db, if there was a diff, it would apply them i.e. upgrade. That way when I deployed the code to my server using a CI/CD pipeline, the migrations would happen automatically and I would not need manual intervention. – Raqib Nov 16 '20 at 16:59
  • 1
    @Raqib you need an orchestration system. Usually in production, your K8 (kubernetes) pipeline can roll in your new code and along with it the new db migration version. So your pipeline has a canary for testing your auto migration. Github/Gitlab MR --> merged --> CI (test + build) pipeline --> run db migration in canary pipeline --> integration tests pipeline --> K8s prod run automigrate + roll in new app instance --> roll out old instance – Daniel Kurniadi Mar 25 '21 at 11:32
  • 1
    So your orchestration system like K8 will handle roll in new deployment and roll out old instance. You can configure database migration pipeline. Often in many company auto-migration is not possible (they have their own rule). So you have to submit DDL migration script to the DB Admin. – Daniel Kurniadi Mar 25 '21 at 11:35
  • Thanks a lot @DanielKurniadi for the clear explanation. – Raqib Jun 11 '21 at 22:38