Alembic - lightweight database migration tool

Introduction

Lets talk about what database migration is and why is it essential to use them. Wikipedia describes database migration as the management of incremental reversible to a relational database. 

Suppose you've deployed your application in Prod and now you have thousands of users signed up. A Few months later you are in need to introduce an incremental upgrade which requires adding a column to one of your database model and also requires adding it to SQL-Alchemy model. At this point we cannot delete and re-create database tables as it will delete all user's data. This is where database migration tools fit in and they're not exclusive to Flask and SQL-Alchemy. Other frameworks such as Ruby on Rails support database migration too.

Alembic works very well with SQL-Alchemy and will allow us to modify our existing database without having to clear out old data. This tool allows us to both apply and revert changes. 

Documentation 

https://alembic.sqlalchemy.org/en/latest/

Installation 

pip install alembic

Adding New Table

Suppose we are in need to add a new table to our existing database and model looks like following 

class Fib(ResourceMixin, db.Model):

    __tablename__ = 'fibtable'

    id = db.Column(db.Integer, primary_key=True)
    fib_index = db.Column(db.Integer, nullable=False, default=0)
    fib_value = db.Column(db.Integer, nullable=False, default=0)

    def __init__(self, fib_index, fib_value):
        self.fib_index = fib_index
        self.fib_value = fib_value

    @classmethod
    def get_fib_values(cls, limit):
        r = Fib.query.with_entities(
            Fib.fib_index, Fib.fib_value).order_by(
            Fib.id.desc()).limit(limit).all()
        return r

In order for Alembic to perform this upgrade, Alembic must revise and create a version file. This version file will hold set of instructions to either upgrade or downgrade the change.

Following command can be run to create a version file.

docker exec <container_name> alembic revision -m "Create foo table"

This command will create a version file with ".py" extension within migrations/versions folder of the root folder of your application. The output of the command will provide the absolute path of the revision file create. 

Following is the example of revision file 

import sqlalchemy as sa

from alembic import op

from webapp.lib.util_datetime import tzware_datetime
from webapp.lib.util_sqlalchemy import AwareDateTime


"""
create foo table

Revision ID: bb73aa30e9cb
Revises: 
Create Date: 2020-08-30 06:25:51.995563
"""

# Revision identifiers, used by Alembic.
revision = 'bb73aa30e9cb'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    pass


def downgrade():
    pass

At this point only revision file has been created but database has not yet been changed. There are few things to note here.

• Each revision will have its own unique hash and its mention at couple of places in the file
  • revision hash is the current hash, since upgrade will be performed and tracked under this hash. You can also see in database a table "alembic_version" is created with one column "version_number"
  • down_revision hold the previous hash and is used to revert changes to previous version number. 
• upgrade function
  • Holds set of instruction it must perform to upgrade database 
• downgrade function 
  • Holds set of instruction to revert changes 

We can now manually edit the upgrade function and provide set of instruction it require to perform upgrade. 

def upgrade():
    op.create_table('fibtable', 
                    sa.Column('id', sa.Integer, primary_key=True), 
                    sa.Column('created_on', AwareDateTime(), 
                                default=tzware_datetime), 
                    sa.Column('updated_on', AwareDateTime(), 
                                default=tzware_datetime, onupdate=tzware_datetime), 
                    sa.Column('fib_index', sa.Integer, nullable=False, default=0),
                    sa.Column('fib_value', sa.Integer, nullable=False, default=0)
                    )

def downgrade():
    op.drop_table('foos')

With instructions updated in revision file, we can now perform upgrade with following command

docker exec <container_name> alembic upgrade head

In case if changes must be reverted, following command can be run

docker exec <container_name> alembic downgrade -1

Adding New Column to existing table

In this example we will add a new column 'title' to existing table. 

class Fib(ResourceMixin, db.Model):

    __tablename__ = 'fibtable'

    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(512), nullable=True, server_default='')
    fib_index = db.Column(db.Integer, nullable=False, default=0)
    fib_value = db.Column(db.Integer, nullable=False, default=0)

Run following command to create a revision file 

docker exec <container_name> alembic revision -m "Adding new column `title` to fibtable"

Lets examine revision file

import sqlalchemy as sa

from alembic import op

from lib.util_datetime import tzware_datetime
from lib.util_sqlalchemy import AwareDateTime



"""
added a new column for title

Revision ID: 641f5c82c88e
Revises: bb73aa30e9cb

Create Date: 2020-09-16 06:34:20.479903
"""

# Revision identifiers, used by Alembic.
revision = '641f5c82c88e'
down_revision = 'bb73aa30e9cb'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('fibtable', 
            sa.Column('title', sa.String(length=512), server_default='', nullable=True))
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_column('fibtable', 'title')
    # ### end Alembic commands ###

Lets perform upgrade

docker exec <container_name> alembic upgrade head

Useful Commands

Add Revision

docker exec <container_name> alembic revision -m "Adding new column `title` to fibtable"

Autogenerate Revision

docker exec <container_name> alembic revision --autogenerate -m "Adding new column `title` to fibtable"

Perform Upgrade

docker exec <container_name> alembic upgrade head

Perform Downgrade

docker exec <container_name> alembic downgrade -1

View Current History

docker exec <container_name> alembic current

History Verbos

docker exec <container_name> alembic history --verbos