Migrate Database with Alembic

Backend

Why Use Alembic

Alembic is a lightweight database migration tool for usage with the SQLAlchemy Database Toolkit for Python. It is written by the author of SQLAlchemy and it has become the de facto standard tool to perform migrations on SQLAlchemy backed databases.

Installation

pip3 install alembic
pip3 install mysqlclient # only if using mysql

alembic init alembic

Once you initialize, a folder named alembic is created. It should look like below:

•
├── alembic
│   ├── versions
│   ├── env.py
│   ├── README
│   └── script.py.mako
└── alembic.ini

Steps

Update files

# alembic.ini
sqlalchemy.url = mysql://username:password@hostname/dbname
# models.py
import datetime
from sqlalchemy import Boolean, Column, DateTime, Integer, String
from sqlalchemy.ext.declarative import declarative_base


Base = declarative_base()


class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    email = Column(String(254), unique=True, index=True)
    hashed_password = Column(String(254))
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime, default=datetime.datetime.now)
    updated_at = Column(DateTime, default=datetime.datetime.now,
                        onupdate=datetime.datetime.now)
# alembic/env.py
import os
import sys
sys.path.append(os.getcwd())
import models
target_metadata = models.Base.metadata

Once you are done editing the files, run command in terminal to autogenerate tables in your database.

alembic revision --autogenerate -m "Your message here"

alembic upgrade head

Once it has successfully run, you should see a new file under alembic/versions folder.

alembic/versions/b5b9e538837c_1.py
"""1

Revision ID: b5b9e538837c
Revises:
Create Date: 2019-07-08 15:18:50.180877

"""
from alembic import op
import sqlalchemy as sa


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


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('users',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('email', sa.String(length=254), nullable=True),
    sa.Column('hashed_password', sa.String(length=254), nullable=True),
    sa.Column('is_active', sa.Boolean(), nullable=True),
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.Column('updated_at', sa.DateTime(), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_users_email'), 'users', ['email'], unique=True)
    op.create_index(op.f('ix_users_id'), 'users', ['id'], unique=False)
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index(op.f('ix_users_id'), table_name='users')
    op.drop_index(op.f('ix_users_email'), table_name='users')
    op.drop_table('users')
    # ### end Alembic commands ###

Connect to your database to see the auto generated table!

Resources

Alembic documentation

GitHubGitHubLinkedin