[python]Flask Migrate

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.

1. flask db init, this is to initialize flask migrate, a migrate folder will be created.
2. 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.
3. flask db upgrade, this executes the Alembic script that invoke changes to the database.
4. flask db downgrade, this executes the Alembic script to revert the changes.

Initial setup
Make sure you have these:

1. flask_sqlalchemy
2. flask_migrate
3. 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.