Flask - How to Upgrade/Downgrade Database Migrations?

The relational databases are very good at storing the structured content into the database. To make this happen we need to provide the structured database schema to the ORM.

But the problem here is while developing, it happens to change the schema very frequently, so to make this transition simple, several good tools in the market are dedicated to helping the transition schemas from one version to the next, these are called migration frameworks.

flask-migrateis a kind of framework that helps us to do the same migrations. So let’s head into the virtual environment and install the flask-migrate extension.

(venv) flask-tutorials % pip install flask-migrate
Collecting flask-migrate
  Downloading Flask_Migrate-3.0.1-py2.py3-none-any.whl (12 kB)
Requirement already satisfied: Flask-SQLAlchemy>=1.0 in /Users/chandra/Work/MyWork/flask-tutorials/venv/lib/python3.8/site-packages (from flask-migrate) (2.5.1)
Collecting alembic>=0.7
  Downloading alembic-1.6.5-py2.py3-none-any.whl (164 kB)
     |████████████████████████████████| 164 kB 398 kB/s 
Requirement already satisfied: Flask>=0.9 in /Users/chandra/Work/MyWork/flask-tutorials/venv/lib/python3.8/site-packages (from flask-migrate) (2.0.1)
Requirement already satisfied: SQLAlchemy>=0.8.0 in /Users/chandra/Work/MyWork/flask-tutorials/venv/lib/python3.8/site-packages (from Flask-SQLAlchemy>=1.0->flask-migrate) (1.4.21)
Collecting Mako
  Downloading Mako-1.1.4-py2.py3-none-any.whl (75 kB)
     |████████████████████████████████| 75 kB 958 kB/s 
Collecting python-editor>=0.3
  Downloading python_editor-1.0.4-py3-none-any.whl (4.9 kB)
Collecting python-dateutil
  Downloading python_dateutil-2.8.2-py2.py3-none-any.whl (247 kB)
     |████████████████████████████████| 247 kB 2.0 MB/s 
Requirement already satisfied: itsdangerous>=2.0 in /Users/chandra/Work/MyWork/flask-tutorials/venv/lib/python3.8/site-packages (from Flask>=0.9->flask-migrate) (2.0.1)
Requirement already satisfied: Jinja2>=3.0 in /Users/chandra/Work/MyWork/flask-tutorials/venv/lib/python3.8/site-packages (from Flask>=0.9->flask-migrate) (3.0.1)
Requirement already satisfied: click>=7.1.2 in /Users/chandra/Work/MyWork/flask-tutorials/venv/lib/python3.8/site-packages (from Flask>=0.9->flask-migrate) (8.0.1)
Requirement already satisfied: Werkzeug>=2.0 in /Users/chandra/Work/MyWork/flask-tutorials/venv/lib/python3.8/site-packages (from Flask>=0.9->flask-migrate) (2.0.1)
Requirement already satisfied: greenlet!=0.4.17; python_version >= "3" in /Users/chandra/Work/MyWork/flask-tutorials/venv/lib/python3.8/site-packages (from SQLAlchemy>=0.8.0->Flask-SQLAlchemy>=1.0->flask-migrate) (1.1.0)
Requirement already satisfied: MarkupSafe>=0.9.2 in /Users/chandra/Work/MyWork/flask-tutorials/venv/lib/python3.8/site-packages (from Mako->alembic>=0.7->flask-migrate) (2.0.1)
Collecting six>=1.5
  Using cached six-1.16.0-py2.py3-none-any.whl (11 kB)
Installing collected packages: Mako, python-editor, six, python-dateutil, alembic, flask-migrate
Successfully installed Mako-1.1.4 alembic-1.6.5 flask-migrate-3.0.1 python-dateutil-2.8.2 python-editor-1.0.4 six-1.16.0

Integrate flask-mgrateextension with the application.

from flask import Flask
from config import Config
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate

app = Flask(__name__)
app.config.from_object(Config)

db = SQLAlchemy(app)
migrate = Migrate(app, db)

from app import routes, models

The Migrate class coming from falsk_migratepackage and it needs two arguments that are app and the db instance.

Now let’s create a simple User model and insert some data init.

from app import db

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)  # primary key column
    name = db.Column(db.String(32), index=True, unique=True) # creating indexed column and unique
    password = db.Column(db.String(32))

    def __repr__(self):
        return f'<User {self.name}>'

Now let’s migrate this User table.

Flask Database Migrations:

We have a User model class, with this model we can access it as a regular python object, so as part of this example we are going to make this model into a persistent table that way all our users get persisted into the database table.

1. Initialize a migration repo:

Now it’s time to initialize the migration framework so that we can apply the first migration for our users table.

flask_migrate provides all the migration-related commands as an extension to the flask command. All the commands related to database migrations start with flask db initialize a migration repository this is something you do to start any project.

The command to initialize the migration repo is flask db init

(venv) flask-login-form % export FLASK_APP=app.py
(venv) flask-login-form % flask db init          
  Creating directory /Users/chandra/Work/MyWork/flask-tutorials/flask-login-logout/migrations ...  done
  Creating directory /Users/chandra/Work/MyWork/flask-tutorials/flask-login-logout/migrations/versions ...  done
  Generating /Users/chandra/Work/MyWork/flask-tutorials/flask-login-logout/migrations/script.py.mako ...  done
  Generating /Users/chandra/Work/MyWork/flask-tutorials/flask-login-logout/migrations/env.py ...  done
  Generating /Users/chandra/Work/MyWork/flask-tutorials/flask-login-logout/migrations/README ...  done
  Generating /Users/chandra/Work/MyWork/flask-tutorials/flask-login-logout/migrations/alembic.ini ...  done

The flask db initcommand is going to create a new migrations directory, that’s going to have all our database migrations. Typically this directory has python scripts that describe the changes to the database schema. You can reference this to something similar to the source control repo (git) as that will have all the track on all source code changes.

2. Falsk Create Migration:

Now that we have a migration repository ready, we are ready to create the first migration. this can be done using flask db migratecommand.

The flask db migrate takes an optional description, so that we can provide a comment that describes the migration, in this case, we are creating a users table.

(venv) flask-login-logout % flask db migrate -m "creating user table"
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 'user'
INFO  [alembic.autogenerate.compare] Detected added index 'ix_user_name' on '['name']'
  Generating /Users/chandra/Work/MyWork/flask-tutorials/flask-login-logout/migrations/versions/daf164a57e83_.py ...  done

This creates a new script inside a migrations directory, that represents the current migration. The major functionality it provides is to upgrade or downgrade the DB. The upgrade function modifies the database to make it match the database definitions that we have in the Model. In this case it the upgrade function adds a users table matching the User model.

The downgrade function performs the reverse, in cases, we want to undo this migration.

3. Flask Upgrade Migration:

The flask db upgradecommand takes you to update the database.

(venv) flask-login-logout % flask db upgrade
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> daf164a57e83, creating user table

As a result of the above command, it generates a app.db database (SQLite) in the same directory. Now the app.dbhas an empty user table.

4. Flask Downgrade Migration:

The flask db downgrade command performs undo operation on the previous upgrade.

(venv) flask-login-logout % flask db downgrade
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running downgrade daf164a57e83 -> , creating user table

Done.

References:

Happy Learning 🙂