Complete Guide to Using Alembic: Database Migration Made Easy

Alembic is a powerful and flexible database migration tool for SQLAlchemy. It enables you to track database schema changes with version control, create migration scripts, and easily perform database upgrades or downgrades.

In this tutorial, we'll learn Alembic from basics to advanced usage with practical examples.

Why Alembic?

Alembic Advantages:

Version Control for Database: Track every database schema change

Reversible Migrations: Support for upgrade and downgrade

Autogenerate: Automatically generate migrations from SQLAlchemy model changes

Branching: Support for multiple migration branches

SQLAlchemy Integration: Seamless integration with SQLAlchemy ORM

Offline Mode: Generate SQL scripts without database connection

Installation

Install Alembic

# Install Alembic
pip install alembic

Install with SQLAlchemy (if not already installed)
pip install sqlalchemy

Install database driver as needed
pip install psycopg2-binary  # PostgreSQL
pip install pymysql          # MySQL
pip install aiosqlite        # SQLite async

Verify Installation

alembic --version
Output: alembic 1.13.x

Project Setup: Initial Configuration

1. Initialize Alembic

# Create project folder
mkdir myproject && cd myproject

Initialize Alembic
alembic init alembic

Generated folder structure:

myproject/
├── alembic/
│   ├── versions/           # Folder for migration files
│   ├── env.py              # Environment configuration
│   ├── README              # Documentation
│   └── script.py.mako      # Template for migrations
├── alembic.ini             # Main Alembic configuration

2. Configure Database URL

Edit alembic.ini file:

# alembic.ini
[alembic]
scriptlocation = alembic
prependsyspath = .

Database URL
sqlalchemy.url = postgresql://user:password@localhost/mydatabase

For SQLite
sqlalchemy.url = sqlite:///./app.db

For MySQL
sqlalchemy.url = mysql+pymysql://user:password@localhost/mydatabase

3. Setup SQLAlchemy Models

Create models.py file:

# models.py
from sqlalchemy import Column, Integer, String, DateTime, ForeignKey, Boolean, Text
from sqlalchemy.orm import declarativebase, relationship

from datetime import datetime

Base = declarativebase()

class User(Base):
    tablename = 'users'

    id = Column(Integer, primarykey=True)

    username = Column(String(50), unique=True, nullable=False)
    email = Column(String(100), unique=True, nullable=False)
    passwordhash = Column(String(255), nullable=False)
    isactive = Column(Boolean, default=True)

    createdat = Column(DateTime, default=datetime.utcnow)

    # Relationship
    posts = relationship("Post", backpopulates="author")


class Post(Base):
    tablename = 'posts'

    id = Column(Integer, primarykey=True)
    title = Column(String(200), nullable=False)
    content = Column(Text)
    userid = Column(Integer, ForeignKey('users.id'), nullable=False)

    createdat = Column(DateTime, default=datetime.utcnow)
    updatedat = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)


    # Relationship
    author = relationship("User", backpopulates="posts")

4. Configure env.py for Autogenerate

Edit alembic/env.py file:

# alembic/env.py
from logging.config import fileConfig
from sqlalchemy import enginefromconfig, pool
from alembic import context

Import Base from models
import sys
from pathlib import Path
sys.path.append(str(Path(file).parent.parent))
from models import Base

Alembic Config object
config = context.config

Setup logging
if config.configfilename is not None: