Alembic database migration

In this chapter, you will learn to use Alembic to manage database schema changes and understand the similarities and differences with Django Migration and Flask-Migrate.

Why do we need migration tools?

Base.metadata.create_all()Also has a fatal flaw: only creates when the table doesn't exist, and modifying an existing table structure won't auto-sync.

AlembicIt is the official migration tool of SQLAlchemy, and Django'smakemigrations/migrateand Flask's Flask-Migrate all borrow from Alembic's design at the underlying level.

Installation and Initialization

(venv) $ pip install alembic
(venv) $ alembic init alembic
Creating directory alembic/... done

Generated directory structure:

alembic/
├── versions/          # 迁移脚本存放目录
├── env.py             # 迁移环境配置（需手动修改！）
└── script.py.mako     # 迁移脚本模板
alembic.ini            # Alembic 配置文件

Configure env.py to connect to your models

This is a key step—Alembic doesn't know where your SQLAlchemy models are by default.

Example

# File path: alembic/env.py (modify key parts)
import sys
from pathlib import Path

# Add the project root directory to sys.path (to ensure you can import your own modules)
sys.path.insert(0, str(Path(__file__).resolve().parent.parent))

from database import Base, engine
from models import Category, Post # Import all models to ensure Base.metadata includes them.

# target_metadata must be set to the model metadata
target_metadata = Base.metadata

Modify simultaneouslyalembic.iniDatabase connection in:

# 文件路径：alembic.ini
sqlalchemy.url = sqlite:///./blog.db

Migration in three steps

Steps	Command	Function	Django equivalent
Initialize	alembic init alembic	Create migration environment (one-time only)	—
Generate	alembic revision --autogenerate -m "description"	Detect model changes, generate migration scripts	makemigrations
Execute	alembic upgrade head	Execute all unapplied migrations	migrate

Generate and execute migrations

(venv) $ alembic revision --autogenerate -m "初始化 Post 和 Category 模型"
INFO  [alembic.autogenerate] Detected added table 'categories'
INFO  [alembic.autogenerate] Detected added table 'posts'
Generating alembic/versions/xxxx_initial.py ... done

(venv) $ alembic upgrade head
INFO  [alembic.runtime.migration] Running upgrade -> xxxx, 初始化

Practical: add the read_count field

Example

# File path: added in the Post class of models.py
class Post(Base):
# ... existing fields ...
read_count = Column(Integer, default=0) # Read Count

(venv) $ alembic revision --autogenerate -m "Post 新增 read_count 字段"
(venv) $ alembic upgrade head

Rollback

(venv) $ alembic downgrade -1        # 回退到上一个版本

alembic upgrade headinheadIndicates the latest version. You can also specify a specific version number:alembic upgrade xxxx(Version ID).

Comparison of migration tools across three frameworks

Operation	Django	Flask（Flask-Migrate）	FastAPI（Alembic）
Initialize	No need	flask db init	alembic init alembic
Generate migration	makemigrations	flask db migrate -m "description"	alembic revision --autogenerate -m "description"
Execute migration	migrate	flask db upgrade	alembic upgrade head
Rollback	migrate app 0001	flask db downgrade	alembic downgrade -1
Degree of automation	Highest	Medium	Requires manual configuration of env.py

Chapter summary

In this chapter, you mastered the complete Alembic workflow: alembic init initialization, configuring env.py to connect models, revision --autogenerate to generate migrations, upgrade head to execute, and downgrade to roll back.

Alembic requires more configuration steps than Django/Flask migration tools, but it's lower-level and more flexible.

Next chapter preview: With more articles, search and pagination are needed. The next chapter implements keyword joint search and pagination based on offset/limit.

other extensions