#Complete Guide to FastAPI and Alembic Database Migration

📂 Stage: Stage 3 - Data Persistence (Database) 🔗 Related chapters: FastAPI SQLAlchemy 2.0实战 · FastAPIdependency-injection

Have you ever written by hand in a production environment?ALTER TABLESentences, so nervous that your palms sweat? Team members individually modify the database structure, eventually causing the environment to get out of control? If you are building applications using FastAPI + SQLAlchemy, then Alembic is the tool to help you get rid of these nightmares. This guide will take you from scratch to master every key skill in Alembic, making database changes as easy as managing code with Git.

#Table of contents

• 为什么需要数据库迁移工具
• Alembic基础概念与项目集成
• 自动生成迁移脚本
• 手动编写迁移脚本
• 版本控制与迁移命令
• 多环境与生产环境部署
• 常见陷阱与最佳实践
• 总结

#Why do you need a database migration tool?

In the days before migration tools, we could only rely on handwritten SQL to modify the table structure. This "streaking" approach will quickly expose a bunch of problems:

#Alembic’s Core Values

Alembic is the official migration tool launched by SQLAlchemy and is specially designed to solve the above pain points. It brings you:

1. ✅ Database version control like Git
2. ✅ Automatically compare the ORM model with the real database and generate a migration script
3. ✅ Reversible migration, extremely safe when performing rollback
4. ✅ Ensure the structure of development, testing and production environments is consistent
5. ✅ Complete change history, audit records are clear at a glance

#Project dependency installation

Before starting, make sure your project has the necessary dependencies installed.

# requirements.txt
fastapi==0.104.1
sqlalchemy==2.0.23
alembic==1.13.1
asyncpg==0.29.0  # PostgreSQL 异步驱动
pydantic==2.5.0
uvicorn==0.24.0

All dependencies can be installed with one command:

pip install -r requirements.txt

#Alembic basic concepts and project integration

#Quick overview of core components

when you executealembic initAfterwards, the following key sections are generated:

• env.py– A bridge connecting the database and SQLAlchemy model, every migration starts from here
• alembic.ini– Global configuration files (database URL, logs, etc.)
• versions/– A folder that stores all migration scripts, each file is a version
• script.py.mako– Migration script generation template with customizable format

#FastAPI project structure recommendation

After integrating Alembic, the recommended project directory looks like this:

my_fastapi_project/
├── app/
│   ├── __init__.py
│   ├── main.py
│   ├── models/
│   │   ├── __init__.py
│   │   └── base.py  # SQLAlchemy Base
│   └── database.py  # DATABASE_URL 配置
├── alembic/
│   ├── env.py
│   ├── script.py.mako
│   └── versions/
├── alembic.ini
└── requirements.txt

#Initialize Alembic

In the project root directory, just one command can generate the alembic skeleton:

alembic init alembic

#Configurationenv.py(key step)

Need to modify nextalembic/env.py, let Alembic know where your model is and how to connect to the database.

from logging.config import fileConfig
from sqlalchemy import engine_from_config, pool
from alembic import context
import os

# 引入项目配置
from app.models.base import Base
from app.database import DATABASE_URL

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

if config.config_file_name:
    fileConfig(config.config_file_name)

target_metadata = Base.metadata  # 指向模型元数据，Alembic 会自动比对它


def run_migrations_offline() -> None:
    url = config.get_main_option("sqlalchemy.url")
    context.configure(
        url=url, target_metadata=target_metadata, literal_binds=True,
        dialect_opts={"paramstyle": "named"},
    )
    with context.begin_transaction():
        context.run_migrations()


def run_migrations_online() -> None:
    connectable = engine_from_config(
        config.get_section(config.config_ini_section),
        prefix="sqlalchemy.", poolclass=pool.NullPool,
    )
    with connectable.connect() as connection:
        context.configure(
            connection=connection, target_metadata=target_metadata,
            compare_type=True,               # 自动检测列类型变更
            compare_server_default=True,     # 自动检测默认值变更
        )
        with context.begin_transaction():
            context.run_migrations()


if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()

💡 Tip:compare_type=Trueandcompare_server_default=TrueIt is a key parameter for automatically detecting table structure differences. It is recommended to turn it on to avoid missing subtle changes.

#Simplifyalembic.ini

Keep the core configuration and delete redundant comments to make the file cleaner:

[alembic]
script_location = alembic
prepend_sys_path = .
file_template = %%(rev)s_%%(slug)s

[loggers]
keys = root,sqlalchemy,alembic

[handlers]
keys = console

[formatters]
keys = generic

[logger_root]
level = WARN
handlers = console

[logger_sqlalchemy]
level = WARN
qualname = sqlalchemy.engine

[logger_alembic]
level = INFO
qualname = alembic

[handler_console]
class = StreamHandler
args = (sys.stdout,)
level = NOTSET
formatter = generic

[formatter_generic]
format = %(levelname)-5.5s [%(name)s] %(message)s
datefmt = %H:%M:%S

#Automatically generate migration script

#Basic commands

After modifying the model, only one command is needed to generate the migration script:

alembic revision --autogenerate -m "add user avatar column"

After execution, Alembic will compare your ORM model with the table structure of the current database and automatically generate anupgrade()anddowngrade()Function script.

#How automatic generation works

1. Scan all items in the project that inherit fromBaseThe SQLAlchemy model
2. Connect to the database and read existing tables, columns, indexes, constraints and other information
3. Analyze differences (added/deleted tables, columns, type changes, etc.)
4. Generate executable migration scripts, including upgrade and rollback logic

#Generated script example

Below is a typical auto-generated script that will giveusersAdd a tableavatarFields:

# alembic/versions/abc123_add_user_avatar_column.py
"""add user avatar column

Revision ID: abc123
Revises: def456
Create Date: 2026-03-26 14:00:00
"""
from alembic import op
import sqlalchemy as sa

revision = 'abc123'
down_revision = 'def456'
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.add_column('users', sa.Column('avatar', sa.String(500), nullable=True))


def downgrade() -> None:
    op.drop_column('users', 'avatar')

📌 Note: Although the automatically generated script saves time and effort, it only handles "structural" changes. If your changes involve data migration or complex SQL logic, you will need to write scripts manually.

#Manually write migration script

#Which scenarios need to be written manually?

• New/modified fields and corresponding historical data need to be migrated at the same time
• Perform automatically generated complex operations that cannot be inferred, such as splitting tables and merging fields
• Need to finely control the execution sequence, or add conditional judgment and performance optimization

#Practical combat: Convert string status to integer enumeration

Suppose we need touserstablestatusfields from string('active' / 'inactive') to integer enumeration (1 / 0), and requires smooth transformation of existing data.

def upgrade() -> None:
    # 1. 创建一个临时整数列，暂时允许为空
    op.add_column('users', sa.Column('status_new', sa.Integer(), nullable=True))
    
    # 2. 根据旧的字符串值填充新列
    conn = op.get_bind()
    conn.execute(sa.text("""
        UPDATE users 
        SET status_new = CASE 
            WHEN status = 'active' THEN 1 ELSE 0
        END
    """))
    
    # 3. 删除旧列，并将新列重命名，同时设为 NOT NULL
    op.drop_column('users', 'status')
    op.alter_column('users', 'status_new', new_column_name='status', nullable=False)


def downgrade() -> None:
    # 回滚步骤：反向操作，恢复字符串状态
    op.add_column('users', sa.Column('status_old', sa.String(), nullable=True))
    conn = op.get_bind()
    conn.execute(sa.text("""
        UPDATE users 
        SET status_old = CASE WHEN status = 1 THEN 'active' ELSE 'inactive' END
    """))
    op.drop_column('users', 'status')
    op.alter_column('users', 'status_old', new_column_name='status', nullable=False)

🛡️ Security Tip: Before performing data migration, please be sure to fully verify it in the test environmentupgradeanddowngradeloop to ensure data is not lost.

#Version control and migration commands

#Dependency chain between migration files

Each migration file must passdown_revisionPoint to the previous version, thus forming a clear linked list:

base → 001_initial → 002_add_posts → 003_add_comments → head

headIndicates the latest version,baseRepresents an empty database state with no tables.

#Quick check of commonly used commands

# 查看当前数据库处于哪个版本
alembic current

# 查看迁移历史
alembic history

# 升级到最新版本
alembic upgrade head

# 向上迁移一个版本
alembic upgrade +1

# 回滚一个版本
alembic downgrade -1

# 回滚到初始状态（所有表消失，谨慎使用）
alembic downgrade base

# 生成将要执行的 SQL，但不实际应用到数据库（用于审核）
alembic upgrade head --sql

##Multiple environments and production environment deployment {#Multiple environments and production environment deployment}

#Load different databases according to the environment

existapp/database.py, we can use environment variables to distinguish development, testing, production and other environments:

import os

ENV = os.getenv("APP_ENV", "development")
DATABASE_URLS = {
    "development": "postgresql+asyncpg://localhost:5432/myapp_dev",
    "testing": "postgresql+asyncpg://localhost:5432/myapp_test",
    "production": os.getenv("DATABASE_URL"),
}
DATABASE_URL = DATABASE_URLS[ENV]

When performing a migration, simply pass inAPP_ENVThat’s it:

# 开发环境
APP_ENV=development alembic upgrade head

# 生产环境（数据库地址从环境变量读取）
APP_ENV=production alembic upgrade head

#Integrate migrations in Docker

Alembic upgrade can be included in the Dockerfile as a necessary step before startup:

FROM python:3.11-slim
WORKDIR /app
COPY requirements.txt .
RUN pip install -r requirements.txt
COPY . .
CMD ["sh", "-c", "alembic upgrade head && uvicorn app.main:app --host 0.0.0.0 --port 8000"]

#Production environment safe migration process script

In a real production environment, it is strongly recommended to use the script solidification process and add backup and manual review links:

#!/bin/bash
set -e
echo "=== 开始生产迁移 ==="

# 1. 备份当前数据库
pg_dump -h $DB_HOST -U $DB_USER -d $DB_NAME > backup_$(date +%Y%m%d).sql
echo "✅ 数据库已备份"

# 2. 生成待执行的 SQL，方便审核
alembic upgrade head --sql > migration.sql
echo "📄 将要执行的 SQL："
cat migration.sql

# 3. 二次确认
read -p "⚠️ 确认执行迁移？输入 yes 继续: " confirm
if [[ $confirm != "yes" ]]; then
    echo "❌ 迁移已取消"
    exit 1
fi

# 4. 执行迁移
alembic upgrade head
echo "✅ 迁移完成！"

🔒 Core Principle: Before production changes, backup + audit + secondary confirmation are indispensable.

#Common Pitfalls and Best Practices

#Common traps, be careful

1. ❌ downgradeForgetting to consider the order of deletion - for example, deleting the table first and then deleting the index will lead to errors. Operations should be performed in the order of Delete constraints/indexes first, then delete tables.
2. ❌ The model is inconsistent with the current version of the database - if you change the model but haven't upgraded to the latest migrations, the automatic generation will produce weird differences. Please execute firstalembic upgrade head, keep the database up to date, then modify the model and generate new migrations.
3. ❌ Using row-by-row update ORM operations on large tables - will bring huge performance overhead. It is recommended to use bulk SQL or batch processing instead.

#Best Practice Checklist

• 📁 Migration file naming: Use a meaningful description and prefix it with a date or serial number, such as20260410_add_user_avatar.py, convenient for sorting and searching.
• 🔄 Full upgrade and rollback: Make sure everyupgradeAll have correspondingdowngrade, and the operation is completely symmetrical, repeatedly verified in the test environmentupgrade → downgrade → upgradeNo data will be lost.
• 🧪 Test migration process: Regularly perform automated migration-rollback testing in CI/CD or local test environments.
• 📀 Backup before production: No matter how small the changes are, you must back up the database before performing migration and develop a habit.

#Recommended migration script template

Save the following template to standardize the format of all migration files in your team:

"""迁移说明（用一句话描述本次变更）

Revision ID: xxx
Revises: yyy
Create Date: ...
"""
from alembic import op
import sqlalchemy as sa

revision = 'xxx'
down_revision = 'yyy'
branch_labels = None
depends_on = None


def upgrade() -> None:
    # 升级操作
    pass


def downgrade() -> None:
    # 按照与 upgrade 相反的顺序执行相反操作
    pass

#Summary

Alembic provides a complete database change management solution for the FastAPI + SQLAlchemy project. Through automated generation scripts, reversible migration, multi-environment support and rigorous deployment processes, it makes the evolution of the database structure transparent, safe and traceable.

💡 Key Points Review:

1. Always verify migration-rollback integrity in a test environment
2. Make sure eachdowngradeandupgradecompletely symmetrical
3. Before execution in the production environment, it must be backed up, reviewed and confirmed twice.

Now, you can safely introduce Alembic into your own project and manage your database structure like code!

🔗 Extended reading

• Alembic 官方文档
• FastAPI SQLAlchemy 教程