Back to Details

alembic

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Alembic Database Migrations

Alembic数据库迁移

Alembic is the migration tool for SQLAlchemy. It manages schema versioning through a directory of revision scripts linked by 
down_revision
pointers, forming a linear (or branched) migration chain.
Alembic是SQLAlchemy的迁移工具。它通过由
down_revision
指针链接的修订脚本目录来管理schema版本控制，形成线性（或分支）的迁移链。

Environment Setup

环境设置

Initialize the Migration Environment

初始化迁移环境

bash
undefined
bash
undefined

Generic single-database (most common)

通用单数据库（最常用）

alembic init alembic
alembic init alembic

pyproject.toml-integrated (modern projects)

集成pyproject.toml（现代项目）

alembic init --template pyproject alembic
alembic init --template pyproject alembic

Async DBAPI support

异步DBAPI支持

alembic init --template async alembic

Use `pyproject` template for modern Python projects that already have a `pyproject.toml`. It separates source-code config (in `pyproject.toml`) from deployment config (database URL, logging in `alembic.ini`).
alembic init --template async alembic

对于已包含`pyproject.toml`的现代Python项目，使用`pyproject`模板。它将源代码配置（在`pyproject.toml`中）与部署配置（数据库URL、`alembic.ini`中的日志配置）分离。

Project Structure

项目结构

yourproject/
├── alembic.ini          # DB URL, logging, deployment config
├── pyproject.toml       # Source/code config (pyproject template)
└── alembic/
    ├── env.py           # Migration runner — customize here
    ├── script.py.mako   # Template for new revision files
    └── versions/
        ├── 3512b954651e_add_account.py
        └── ae1027a6acf_add_column.py
yourproject/
├── alembic.ini          # 数据库URL、日志、部署配置
├── pyproject.toml       # 源代码配置（pyproject模板）
└── alembic/
    ├── env.py           # 迁移运行器——在此处自定义
    ├── script.py.mako   # 新修订文件的模板
    └── versions/
        ├── 3512b954651e_add_account.py
        └── ae1027a6acf_add_column.py

Configure 
alembic.ini

配置
alembic.ini

Set the database URL:
ini
sqlalchemy.url = postgresql+psycopg2://user:pass@localhost/mydb
URL escaping: Special characters in passwords must be percent-encoded, then 
%
doubled for ConfigParser interpolation:
python
import urllib.parse
from sqlalchemy import URL
url = URL.create("postgresql+psycopg2", username="scott", password="P@ss%rd", host="localhost")
设置数据库URL：
ini
sqlalchemy.url = postgresql+psycopg2://user:pass@localhost/mydb
URL转义：密码中的特殊字符必须进行百分比编码，然后为了适配ConfigParser插值，需将
%
加倍：
python
import urllib.parse
from sqlalchemy import URL
url = URL.create("postgresql+psycopg2", username="scott", password="P@ss%rd", host="localhost")

Renders as: postgresql+psycopg2://scott:P%40ss%25rd@localhost

渲染结果：postgresql+psycopg2://scott:P%40ss%25rd@localhost

In alembic.ini: postgresql+psycopg2://scott:P%%40ss%%25rd@localhost

在alembic.ini中：postgresql+psycopg2://scott:P%%40ss%%25rd@localhost


**Never hard-code production credentials.** Read the URL from an environment variable in `env.py` instead:

```python

**切勿硬编码生产环境凭据**。请改为在`env.py`中从环境变量读取URL：

```python

env.py

env.py

import os config.set_main_option("sqlalchemy.url", os.environ["DATABASE_URL"])
undefined
import os config.set_main_option("sqlalchemy.url", os.environ["DATABASE_URL"])
undefined

Enable Autogenerate in 
env.py

在
env.py
中启用自动生成

Link the application's SQLAlchemy 
MetaData
so Alembic can diff schema:
python
undefined
关联应用的SQLAlchemy 
MetaData
，以便Alembic可以对比schema差异：
python
undefined

env.py — replace the None assignment

env.py ——替换None赋值

from myapp.models import Base target_metadata = Base.metadata
undefined
from myapp.models import Base target_metadata = Base.metadata
undefined

Constraint Naming Conventions

约束命名规范

Always configure a naming convention. Autogenerate cannot detect anonymously named constraints, and databases use incompatible auto-naming schemes (PostgreSQL vs Oracle differ significantly).
Set this on the 
MetaData
used by the declarative base:
python
from sqlalchemy import MetaData
from sqlalchemy.orm import DeclarativeBase

class Base(DeclarativeBase):
    metadata = MetaData(naming_convention={
        "ix": "ix_%(column_0_label)s",
        "uq": "uq_%(table_name)s_%(column_0_name)s",
        "ck": "ck_%(table_name)s_`%(constraint_name)s`",
        "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
        "pk": "pk_%(table_name)s",
    })
This allows 
unique=True
and 
index=True
column flags to produce consistently named constraints across databases, and allows autogenerate to detect and drop them reliably.
务必配置命名规范。自动生成无法检测匿名命名的约束，且不同数据库使用不兼容的自动命名方案（PostgreSQL与Oracle差异显著）。
在声明式基类使用的
MetaData
上设置：
python
from sqlalchemy import MetaData
from sqlalchemy.orm import DeclarativeBase

class Base(DeclarativeBase):
    metadata = MetaData(naming_convention={
        "ix": "ix_%(column_0_label)s",
        "uq": "uq_%(table_name)s_%(column_0_name)s",
        "ck": "ck_%(table_name)s_`%(constraint_name)s`",
        "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
        "pk": "pk_%(table_name)s",
    })
这使得
unique=True
和
index=True
列标记可以在不同数据库中生成一致命名的约束，并且自动生成可以可靠地检测和删除这些约束。

Creating and Writing Migrations

创建与编写迁移

Generate a Revision

生成修订版本

bash
undefined
bash
undefined

Manual (empty script)

手动创建（空脚本）

alembic revision -m "add account table"
alembic revision -m "add account table"

Autogenerate from model diff

从模型差异自动生成

alembic revision --autogenerate -m "add account table"

**Always review autogenerated scripts** before running them. Autogenerate cannot detect: table renames, column renames, or anonymously named constraints.
alembic revision --autogenerate -m "add account table"

**运行前务必检查自动生成的脚本**。自动生成无法检测：表重命名、列重命名或匿名命名的约束。

Anatomy of a Revision File

修订文件结构

python
"""add account table

Revision ID: 1975ea83b712
Revises: <previous_rev_id or None>
Create Date: 2024-01-15 10:30:00
"""

revision = '1975ea83b712'
down_revision = None   # None = first migration
branch_labels = None

from alembic import op
import sqlalchemy as sa

def upgrade():
    op.create_table(
        'account',
        sa.Column('id', sa.Integer, primary_key=True),
        sa.Column('name', sa.String(50), nullable=False),
        sa.Column('created_at', sa.DateTime, nullable=False),
    )

def downgrade():
    op.drop_table('account')
Always implement 
downgrade()
. It enables rollback and is required for 
alembic downgrade
.
python
"""add account table

Revision ID: 1975ea83b712
Revises: <previous_rev_id or None>
Create Date: 2024-01-15 10:30:00
"""

revision = '1975ea83b712'
down_revision = None   # None = 首次迁移
branch_labels = None

from alembic import op
import sqlalchemy as sa

def upgrade():
    op.create_table(
        'account',
        sa.Column('id', sa.Integer, primary_key=True),
        sa.Column('name', sa.String(50), nullable=False),
        sa.Column('created_at', sa.DateTime, nullable=False),
    )

def downgrade():
    op.drop_table('account')
务必为每个修订版本实现
downgrade()
。它支持回滚操作，是
alembic downgrade
命令所必需的。

Common Operations

常见操作

python
undefined
python
undefined

Add a column

添加列

op.add_column('account', sa.Column('email', sa.String(255)))
op.add_column('account', sa.Column('email', sa.String(255)))

Drop a column

删除列

op.drop_column('account', 'email')
op.drop_column('account', 'email')

Add named foreign key (naming convention resolves the name)

添加命名外键（命名规范会解析名称）

op.create_foreign_key( None, 'order', 'account', ['account_id'], ['id'] )
op.create_foreign_key( None, 'order', 'account', ['account_id'], ['id'] )

Drop constraint — use op.f() to bypass naming convention tokenization

删除约束——使用op.f()绕过命名规范的标记化处理

op.drop_constraint(op.f('fk_order_account_id_account'), 'order', type_='foreignkey')
op.drop_constraint(op.f('fk_order_account_id_account'), 'order', type_='foreignkey')

Create index

创建索引

op.create_index('ix_account_name', 'account', ['name'])
undefined
op.create_index('ix_account_name', 'account', ['name'])
undefined

Running Migrations

运行迁移

bash
undefined
bash
undefined

Upgrade to latest

升级到最新版本

alembic upgrade head
alembic upgrade head

Upgrade to specific revision (partial ID works)

升级到指定修订版本（部分ID即可）

alembic upgrade ae10
alembic upgrade ae10

Upgrade N steps forward

向前升级N步

alembic upgrade +2
alembic upgrade +2

Downgrade to base (undo all)

回滚到初始状态（撤销所有迁移）

alembic downgrade base
alembic downgrade base

Downgrade N steps backward

向后回滚N步

alembic downgrade -1
undefined
alembic downgrade -1
undefined

Inspecting State

状态检查

bash
undefined
bash
undefined

Show current DB revision

显示当前数据库修订版本

alembic current
alembic current

Show full history

显示完整历史

alembic history --verbose
alembic history --verbose

Show history range

显示指定范围的历史

alembic history -r1975ea:ae1027
alembic history -r1975ea:ae1027

Check if new migrations needed (CI-friendly)

检查是否需要新的迁移（适合CI环境）

alembic check

Use `alembic check` in CI pipelines to assert that all model changes have a corresponding migration committed.
alembic check

在CI流水线中使用`alembic check`来确保所有模型变更都有对应的已提交迁移。

Post-Write Code Formatting

写入后代码格式化

Configure auto-formatting of generated revision files in 
alembic.ini
:
ini
[post_write_hooks]
hooks = ruff

ruff.type = exec
ruff.executable = ruff
ruff.options = check --fix REVISION_SCRIPT_FILENAME
Or with 
pyproject.toml
:
toml
[[tool.alembic.post_write_hooks]]
name = "ruff"
type = "exec"
executable = "ruff"
options = "check --fix REVISION_SCRIPT_FILENAME"
在
alembic.ini
中配置生成的修订文件的自动格式化：
ini
[post_write_hooks]
hooks = ruff

ruff.type = exec
ruff.executable = ruff
ruff.options = check --fix REVISION_SCRIPT_FILENAME
或者使用
pyproject.toml
：
toml
[[tool.alembic.post_write_hooks]]
name = "ruff"
type = "exec"
executable = "ruff"
options = "check --fix REVISION_SCRIPT_FILENAME"

Quick Reference

快速参考

CommandDescription
alembic init alembic
Initialize migration environment
alembic revision -m "..."
Create empty revision
alembic revision --autogenerate -m "..."
Generate revision from model diff
alembic upgrade head
Apply all pending migrations
alembic downgrade base
Roll back all migrations
alembic current
Show current DB revision
alembic history --verbose
List all revisions
alembic check
Assert no pending migrations (CI)
命令描述
alembic init alembic
初始化迁移环境
alembic revision -m "..."
创建空修订版本
alembic revision --autogenerate -m "..."
从模型差异生成修订版本
alembic upgrade head
应用所有待处理的迁移
alembic downgrade base
回滚所有迁移
alembic current
显示当前数据库修订版本
alembic history --verbose
列出所有修订版本
alembic check
断言无待处理迁移（适用于CI）

Key Best Practices

核心最佳实践

  • Configure naming conventions on 
    MetaData
    before creating any migrations.
  • Always review autogenerated scripts — renames are detected as add+drop pairs.
  • Always implement 
    downgrade()
     for every revision.
  • Never hard-code database URLs; read from environment variables.
  • Run 
    alembic check
     in CI to catch missing migrations early.
  • Use 
    pyproject
    template     for modern projects to keep source config separate from deployment config.
  • Use 
    op.f()
     when explicitly naming constraints in drop operations to bypass naming convention tokenization.
  • 在创建任何迁移前，在
    MetaData
    上配置命名规范。
  • 务必检查自动生成的脚本——重命名会被检测为添加+删除的组合操作。
  • 务必为每个修订版本实现
    downgrade()
    。
  • 切勿硬编码数据库URL；从环境变量读取。
  • 在CI中运行
    alembic check
    ，尽早发现缺失的迁移。
  • 对于现代项目使用
    pyproject
    模板    ，将源代码配置与部署配置分离。
  • 在删除操作中显式命名约束时使用
    op.f()
    ，以绕过命名规范的标记化处理。

Additional Resources

额外资源

  • references/env-configuration.md
     — Deep dive on 
    env.py
    customization, async support, multiple databases, and 
    include_name
    /
    include_object
    hooks.
  • references/autogenerate-guide.md
     — What autogenerate detects and does not detect, type comparison, custom hooks, and 
    alembic check
    in CI.
  • references/env-configuration.md
     ——深入讲解
    env.py
    自定义、异步支持、多数据库以及
    include_name
    /
    include_object
    钩子。
  • references/autogenerate-guide.md
     ——自动生成能检测和不能检测的内容、类型对比、自定义钩子以及CI中的
    alembic check
    。