Flask Migrate advantage

This is for easy update of the existing database, such as create a new column, create a new table, drop a table, revert previous database. With migrate, I do not need to do the db.create_all() anymore.

A note on sqlite3, once a table is created you cannot insert column directly, need to do some modification in the Alembic script for upgrade. If you going to drop a column from the table it is not possible to revert the column back.

for this demonstration I am using MariaDB which is almost the same as MySQL. Additional modules are flask_migrate and pymysql

Migrate commands

There are four commands which will be used.

flask db init , this is to initialize flask migrate, a migrate folder will be created. flask db migrate , this checks if there is changes in the database, if there is no change no Alembic script will be created if there is changes the Alembic script is created. flask db upgrade , this executes the Alembic script that invoke changes to the database. flask db downgrade , this executes the Alembic script to revert the changes.

Initial setup

Make sure you have these:

flask_sqlalchemy flask_migrate flask

Also a database has to be created first, in my demonstration I name my database as migratetest .

You will need to create a flask app app = Flask(__name__) , then create a db object from SQLAlchemy db = SQLAlchemy(app) , after that create a Migrate object migrate = Migrate(app, db) .

from flask import Flask from flask_sqlalchemy import SQLAlchemy from flask_migrate import Migrate app = Flask(__name__) # config/config.py has my mysql uri with username and password. # SQLALCHEMY_DATABASE_URI = 'mysql+pymysql://username:password@ip_address_of_db/db_name' # I disable the SQLALCHEMY_TRACK_MODIFICATIONS app.config.from_pyfile('config/config.py') # then create a db object from SQLAlchemy db = SQLAlchemy(app) # then create a migrate object from Migrate. migrate = Migrate(app, db) # Declare the table, __tablename__ is not specified hence # class name will be used for table name. class User(db.Model): # the columns of the table. id = db.Column(db.Integer, primary_key=True) username = db.Column(db.String(60), unique=True) email = db.Column(db.String(100)) if __name__ == '__main__': app.run()

This is my structure before running flask db init



Run flask db init

From my project folder I run flask db init



You will see a folder Migrate is created with some files.



The versions folder contains the Alembic script for upgrade and downgrade, if there is a change in existing database a script will be created.

Run flask db migrate

This flask db migrate attempts to check if there is new elements for the database, if there is a script is created. As there are no tables, the table specified in class User(db.Model) will be created.

So there is no tables present in my migratetest database.



So this is the output from the command line that has no issue:



An Alembic script file for upgrade and downgrade is created:



Migrate script generated by Alembic

"""empty message Revision ID: d870d092305e Revises: Create Date: 2019-10-07 18:48:56.475479 """ from alembic import op import sqlalchemy as sa # revision identifiers, used by Alembic. revision = 'd870d092305e' down_revision = None branch_labels = None depends_on = None def upgrade(): # ### commands auto generated by Alembic - please adjust! ### op.create_table('user', sa.Column('id', sa.Integer(), nullable=False), sa.Column('username', sa.String(length=60), nullable=True), sa.Column('email', sa.String(length=100), nullable=True), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('username') ) # ### end Alembic commands ### def downgrade(): # ### commands auto generated by Alembic - please adjust! ### op.drop_table('user') # ### end Alembic commands ###

As can be seen from the function upgrade it creates a user table with the column as specified in my class User(db.Model) .

Run flask db upgrade

This is to upgrade the existing database migratetest with the new tablename user



So there are two tables created, one is the alembic_version which records the script version it ran, the other is my user table.



Using a mysql browser might be good to easily see the layout of the database, for windows you can use HeidiSQL for Mac you can use MySQL Workbench.

Below is a screenshot from MySQL Workbench:





Adding a new table

So now I will be adding an additional table for the user to store user’s more information such as user’s own biography and home address. This table has a foreign key in user_id column which is related to the primary key in user table’s id column.

All the same except the highlighted.

class User(db.Model): # the columns of the table. id = db.Column(db.Integer, primary_key=True) username = db.Column(db.String(60), unique=True) email = db.Column(db.String(100)) # Establish a relationship with MoreUserInfo class. # Takes in the class name, the backref refers to the tablename of this class. # lazy is how the data is loaded, I chose dynamic which works for me. more_info = db.relationship('MoreUserInfo', backref='user', lazy='dynamic') class MoreUserInfo(db.Model): id = db.Column(db.Integer, primary_key=True) home_address = db.Column(db.String(200)) user_bio = db.Column(db.Text) # this is a foreign key to user.id, where user is the tablename, and id is the column of the user table. user_id = db.Column(db.Integer, db.ForeignKey('user.id'))

To upgrade the Alembic script, run the flask db migrate .



A new Alembic py file appears:



Run the flask db upgrade to take effect of the changes.



The new table is created but the new column in user is ignored, it seems flask_migrate does not work well with relationship?



Roll back the database

To roll back the database use flask db downgrade



After the roll back the more_user_info disappears.



Conclusion

flask_migrate is a handy plugin for managing the database, it makes updating changes easily, however it tries to detect the changes as much as possible you will need to check the migration scripts and change it before actually doing the flask db upgrade , so that the changes are what you want.