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
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
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.
Make sure you have these:
Also a database has to be created first, in my demonstration I name my database as
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
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
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
Run flask db upgrade
This is to upgrade the existing database
migratetest with the new tablename
So there are two tables created, one is the
alembic_version which records the script version it ran, the other is my
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
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:
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
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.