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

pyproject.toml-integrated (modern projects)

集成pyproject.toml（现代项目）

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

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"

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)))

Drop a column

删除列

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

Add named foreign key (naming convention resolves the name)

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

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')

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

Upgrade to specific revision (partial ID works)

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

alembic upgrade ae10

Upgrade N steps forward

向前升级N步

alembic upgrade +2

Downgrade to base (undo all)

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

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

Show full history

显示完整历史

alembic history --verbose

Show history range

显示指定范围的历史

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

快速参考

Command	Description
`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
```
。

alembic

Original

Translation

Alembic Database Migrations

Alembic数据库迁移

Environment Setup

环境设置

Initialize the Migration Environment

初始化迁移环境

Generic single-database (most common)

通用单数据库（最常用）

pyproject.toml-integrated (modern projects)

集成pyproject.toml（现代项目）

Async DBAPI support

异步DBAPI支持

Project Structure

项目结构

Configure alembic.ini

配置alembic.ini

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

env.py

env.py

Enable Autogenerate in env.py

在env.py中启用自动生成

env.py — replace the None assignment

env.py ——替换None赋值

Constraint Naming Conventions

约束命名规范

Creating and Writing Migrations

创建与编写迁移

Generate a Revision

生成修订版本

Manual (empty script)

手动创建（空脚本）

Autogenerate from model diff

从模型差异自动生成

Anatomy of a Revision File

修订文件结构

Common Operations

常见操作

Add a column

添加列

Drop a column

删除列

Add named foreign key (naming convention resolves the name)

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

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

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

Create index

创建索引

Running Migrations

运行迁移

Upgrade to latest

升级到最新版本

Upgrade to specific revision (partial ID works)

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

Upgrade N steps forward

向前升级N步

Downgrade to base (undo all)

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

Downgrade N steps backward

向后回滚N步

Inspecting State

状态检查

Show current DB revision

显示当前数据库修订版本

Show full history

显示完整历史

Show history range

显示指定范围的历史

Check if new migrations needed (CI-friendly)

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

Post-Write Code Formatting

写入后代码格式化

Quick Reference

快速参考

Key Best Practices

Configure
`alembic.ini`

配置
`alembic.ini`

Enable Autogenerate in
`env.py`

在
`env.py`
中启用自动生成