Alembic Database Migrations for Python & SQLAlchemy

Introduction

Modern web application development inevitably involves evolving database structures. Adding new tables, changing data types, creating indexes, and modifying relationships between entities all require a systematic management approach. Manually applying schema changes is prone to errors, data loss, and inconsistencies across different development environments.

Alembic is a powerful database migration tool built specifically for the SQLAlchemy ecosystem. As the official solution, it provides safe, controlled schema changes, allowing developers to track change history, apply updates, and roll back failed modifications when needed.

What is Alembic

Alembic is a library for managing database migrations in Python projects using SQLAlchemy. It provides tools for creating, applying, and rolling back database schema changes in a controlled, sequential manner.

Key Benefits of Alembic

Database schema versioning — every change is saved as a separate migration with a unique identifier, making it easy to track the evolution of your data structure.

Automatic migration generation — create migration files automatically based on changes to your SQLAlchemy ORM models, significantly speeding up development.

Safe operations — all changes are applied within transactions, minimizing the risk of data corruption.

Multi-DBMS support — works with PostgreSQL, MySQL, SQLite, Oracle, and other databases supported by SQLAlchemy.

Installing and Configuring Alembic

Installing the Package

pip install alembic

For async applications, you'll also need to install additional dependencies:

pip install alembic[asyncio]

Initializing the Migration Project

After installation, initialize the project structure:

alembic init alembic

This command creates the following file structure:

alembic/
├── versions/           # Directory for migration files
├── env.py             # Environment configuration
├── script.py.mako     # Template for generating migrations
└── alembic.ini        # Main configuration file

Configuring the Database Connection

In the alembic.ini file, specify the database connection string:

sqlalchemy.url = postgresql://user:password@localhost/database_name

Alternatively, you can configure the connection programmatically in env.py:

from sqlalchemy import create_engine
from myproject.config import DATABASE_URL

config.set_main_option("sqlalchemy.url", DATABASE_URL)

Integrating with SQLAlchemy

Connecting Models

For auto-generation of migrations to work correctly, you need to specify your model metadata in the alembic/env.py file:

from myproject.models import Base

# Specify metadata for auto-generation
target_metadata = Base.metadata

Basic Model Example

from sqlalchemy import Column, Integer, String, DateTime, Boolean
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True)
    username = Column(String(50), unique=True, nullable=False)
    email = Column(String(100), unique=True, nullable=False)
    is_active = Column(B