使用alembic管理数据库版本

Alembic 使用 SQLAlchemy 作为基础引擎，为关系数据库提供了更改管理脚本的创建，管理和调用。

随着项目业务需求的不断变更，数据库的表结构修改难以避免，此时就需要对数据库的修改加以记录和控制，便于项目的版本管理和随意的升级和降级。Alembic 就可以很好的解决这个问题。Alembic 是 SQLAlchemy 作者开发的 Python 数据库版本管理工具。需要注意的是，Alembic 将在 0.9.0 版本中与 SQLAlchemy 一起工作。

# 安装alembic
$ pip install alembic
$ pip install sqlalchemy

# 安装postgres
$ pip install postgres

=> 官网地址

必须 cd 到具有 alembic.ini 的目录才能运行 alembic 命令。也就是说，必须在当前工作目录中找到alembic.ini 文件，或者可以使用 alembic -c path/to/alembic.ini 指定配置文件位置。

编号	命令	解释说明
1	alembic init alembic	初始化
2	alembic revision --autogenerate -m "init db"	创建新版本
3	alembic upgrade head	将数据库升级到最新版本
4	alembic downgrade base	将数据库降级到最初版本
5	alembic downgrade 7b55b3d83158	将数据库降级到指定版本
6	alembic current	查询当前数据库版本号
7	select * from alembic_version	查询当前数据库版本号
8	alembic history	查看所有历史消息
9	alembic heads	查看 Revision ID

# 离线更新（生成sql脚本）
# 如果是数据库降级操作，把upgrade替换为downgrade

# 在某些不适合在线更新的情况，可以采用生成sql脚本的形式进行离线更新
alembic upgrade <version> --sql > migration.sql
alembic upgrade ae1027a6acf --sql > migration.sql

# 从特定起始版本生成sql脚本
alembic upgrade <vsersion>:<vsersion> --sql > migration.sql
alembic upgrade 1975ea83b712:ae1027a6acf --sql > migration.sql

---

1. The Migration Environment

Migration 环境仅创建一次，然后与应用程序的源代码本身一起维护。使用 Alembic 的 init 命令创建环境，然后可以对其进行自定义以适应应用程序的特定需求。=> alembic init alembic

• 目录结构

# 目录结构
yourproject/  # 应用程序根目录
  alembic/    # Migration目录；命名无限制；可有多个数据库
     env.py   # 在调用Alembic时运行的Python脚本；包含配置和生成数据库引擎
     README   # 使用说明文档
     script.py.mako  # Mako模板文件；用于生成新的迁移脚本
     versions/       # 此目录包含各个版本脚本
       20191001145933_add_account.py
       20191003145933_create_log_table.py
       20191006145933_rename_username_field.py

• env.py

from __future__ import with_statement
from alembic import context
from sqlalchemy import engine_from_config, pool, create_engine
from logging.config import fileConfig

# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config

# Interpret the config file for Python logging.
# This line sets up loggers basically.
fileConfig(config.config_file_name)

# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata
target_metadata = None

# other values from the config, defined by the needs of env.py,
# can be acquired:
# my_important_option = config.get_main_option("my_important_option")
# ... etc.

dburl = context.get_x_argument(as_dictionary=True).get('dburl')

def run_migrations_offline():
    """Run migrations in 'offline' mode.

    This configures the context with just a URL
    and not an Engine, though an Engine is acceptable
    here as well.  By skipping the Engine creation
    we don't even need a DBAPI to be available.

    Calls to context.execute() here emit the given string to the
    script output.

    """
    url = dburl or config.get_main_option("sqlalchemy.url")
    context.configure(
        url=url, target_metadata=target_metadata, literal_binds=True)

    with context.begin_transaction():
        context.run_migrations()


def run_migrations_online():
    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine
    and associate a connection with the context.

    """
    if dburl:
        connectable = create_engine(dburl)
    else:
        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
        )

        with context.begin_transaction():
            context.run_migrations()

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

• script.py.mako

"""${message}

Revision ID: ${up_revision}
Revises: ${down_revision | comma,n}
Create Date: ${create_date}

"""
from alembic import op
import sqlalchemy as sa
${imports if imports else ""}

# revision identifiers, used by Alembic.
revision = ${repr(up_revision)}
down_revision = ${repr(down_revision)}
branch_labels = ${repr(branch_labels)}
depends_on = ${repr(depends_on)}


def upgrade():
    ${upgrades if upgrades else "pass"}


def downgrade():
    ${downgrades if downgrades else "pass"}

• 20191001145933_add_account.py

"""Select account table

Revision ID: 38db66e3xxxx
Revises:
Create Date: 2019-10-01 14:59:33.849911
"""

from alembic import op
import sqlalchemy as sqla
from sqlalchemy.dialects import postgresql


revision = '38db66e3xxxx'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    op.add_column('account', sqla.Column('id', postgresql.JSON))


def downgrade():
    op.drop_column('account', 'id')

• 20191003145933_create_log_table.py

"""Create table log

Revision ID: f655b554xxxx
Revises: 38db66e3xxxx
Create Date: 2019-10-03 14:59:33.849911
"""

import json

from alembic import op
import sqlalchemy as sqla


# revision identifiers, used by Alembic.
revision = 'f655b554xxxx'
down_revision = '38db66e3xxxx'
branch_labels = None
depends_on = None


def upgrade():
    op.create_table(
        'nginx_log',
        sqla.Column('id', sqla.Integer, primary_key=True)
    )

def downgrade():
    op.drop_table('nginx_log')

---

2. Creating an Environment

基本了解 Alembic 是什么之后，我们可以使用 Alembic init 创建一个通用的环境。

# 创建通用模板
$ cd yourproject
$ alembic init alembic

# 自动会生成通用模板
Creating directory /path/to/yourproject/alembic...done
Creating directory /path/to/yourproject/alembic/versions...done
Generating /path/to/yourproject/alembic.ini...done
Generating /path/to/yourproject/alembic/env.py...done
Generating /path/to/yourproject/alembic/README...done
Generating /path/to/yourproject/alembic/script.py.mako...done
Please edit configuration/connection/logging settings in
'/path/to/yourproject/alembic.ini' before proceeding.

Alembic 还包括其他环境模板信息，可以使用 list_templates 命令列出。

$ alembic list_templates
Available templates:

generic - Generic single-database configuration.
multidb - Rudimentary multi-database configuration.
pylons - Configuration that reads from a Pylons project environment.

Templates are used via the 'init' command, e.g.:
  alembic init --template pylons ./scripts

---

3. Editing the .ini File

Alembic 将文件 alembic.ini 放入当前目录，这是一个调用 Alembic 脚本时要查找的文件。该文件可以在任何地方，可以在通常从其调用 alembic 脚本的目录中，也可以在其他目录中，可以通过使用 --lemon 的 --config 选项指定。

# A generic, single database configuration.

[alembic]
# path to migration scripts
script_location = alembic

# template used to generate migration files
# file_template = %%(rev)s_%%(slug)s

# timezone to use when rendering the date
# within the migration file as well as the filename.
# string value is passed to dateutil.tz.gettz()
# leave blank for localtime
# timezone =

# max length of characters to apply to the
# "slug" field
# truncate_slug_length = 40

# set to 'true' to run the environment during
# the 'revision' command, regardless of autogenerate
# revision_environment = false

# set to 'true' to allow .pyc and .pyo files without
# a source .py file to be detected as revisions in the
# versions/ directory
# sourceless = false

# version location specification; this defaults
# to alembic/versions.  When using multiple version
# directories, initial revisions must be specified with --version-path
# version_locations = %(here)s/bar %(here)s/bat alembic/versions

# the output encoding used when revision files
# are written from script.py.mako
# output_encoding = utf-8

sqlalchemy.url = driver://user:pass@localhost/dbname

# post_write_hooks defines scripts or Python functions that are run
# on newly generated revision scripts.  See the documentation for further
# detail and examples

# format using "black" - use the console_scripts runner,
# against the "black" entrypoint
# hooks=black
# black.type=console_scripts
# black.entrypoint=black
# black.options=-l 79

# Logging configuration
[loggers]
keys = root,sqlalchemy,alembic

[handlers]
keys = console

[formatters]
keys = generic

[logger_root]
level = WARN
handlers = console
qualname =

[logger_sqlalchemy]
level = WARN
handlers =
qualname = sqlalchemy.engine

[logger_alembic]
level = INFO
handlers =
qualname = alembic

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

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

---

4. Create a Migration Script

在适当的环境下，我们可以使用 Alembic 创建新的修订版的数据库脚本。

$ alembic revision -m "create account table"
Generating /path/to/yourproject/alembic/versions/1975ea83b712_create_accoun
t_table.py...done

# 1975ea83b712_create_account_table.py
"""create account table

Revision ID: 1975ea83b712
Revises:
Create Date: 2011-11-08 11:40:27.089406
"""

revision = '1975ea83b712'
down_revision = None
branch_labels = None

from alembic import op
import sqlalchemy as sa


def upgrade():
    pass

def downgrade():
    pass

# revision identifiers, used by Alembic.
revision = 'ae1027a6acf'
down_revision = '1975ea83b712'

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

def downgrade():
    op.drop_table('account')

---

5. Running our Migration

• Running our First Migration

现在，我们要进行数据库迁移。假设我们的数据库是完全干净的，那么它尚未版本化。Alembic upgrade命令将运行升级操作，从当前数据库修订版（在此示例中为None）到给定的目标修订版。我们可以指定 1975ea83b712 作为要升级到的修订版本，但是在大多数情况下，仅将其称为”最新”即可。

$ alembic upgrade head
INFO  [alembic.context] Context class PostgresqlContext.
INFO  [alembic.context] Will assume transactional DDL.
INFO  [alembic.context] Running upgrade None -> 1975ea83b712

• Running our Second Migration

我们再次创建一个修订数据库脚本，如下所示：

$ alembic revision -m "Add a column"
Generating /path/to/yourapp/alembic/versions/ae1027a6acf_add_a_column.py...
done

# ae1027a6acf_add_a_column.py

"""Add a column

Revision ID: ae1027a6acf
Revises: 1975ea83b712
Create Date: 2011-11-08 12:37:36.714947
"""

# revision identifiers, used by Alembic.
revision = 'ae1027a6acf'
down_revision = '1975ea83b712'

from alembic import op
import sqlalchemy as sa

def upgrade():
    op.add_column('account', sa.Column('last_transaction_date', sa.DateTime))

def downgrade():
    op.drop_column('account', 'last_transaction_date')

再次运行：

$ alembic upgrade head
INFO  [alembic.context] Context class PostgresqlContext.
INFO  [alembic.context] Will assume transactional DDL.
INFO  [alembic.context] Running upgrade 1975ea83b712 -> ae1027a6acf

---

6. Relative Migration Identifiers

# ae1 -> ae1027a6acf
$ alembic upgrade ae1

# upgrades
$ alembic upgrade +2

# downgrades
$ alembic downgrade -1

# ae1027a6acf
$ alembic upgrade ae10+2

---

7. Getting Information

首先，我们可以查看当前的修订，仅当此数据库的修订版标识符与 head 修订版匹配时才显示 head。

$ alembic current
INFO  [alembic.context] Context class PostgresqlContext.
INFO  [alembic.context] Will assume transactional DDL.
Current revision for postgresql://scott:XXXXX@localhost/test: 1975ea83b712 -> ae1027a6acf (head), Add a column

我们还可以查看具有历史性的历史，使用--verbose选项将向我们显示有关每个修订的完整信息。

$ alembic history --verbose

Rev: ae1027a6acf (head)
Parent: 1975ea83b712
Path: /path/to/yourproject/alembic/versions/ae1027a6acf_add_a_column.py

    add a column

    Revision ID: ae1027a6acf
    Revises: 1975ea83b712
    Create Date: 2014-11-20 13:02:54.849677

Rev: 1975ea83b712
Parent: <base>
Path: /path/to/yourproject/alembic/versions/1975ea83b712_add_account_table.py

    create account table

    Revision ID: 1975ea83b712
    Revises:
    Create Date: 2014-11-20 13:02:46.257104

# Viewing History Ranges
$ alembic history -r1975ea:ae1027
$ alembic history -r-3:current
$ alembic history -r1975ea:

$ alembic downgrade base
INFO  [alembic.context] Context class PostgresqlContext.
INFO  [alembic.context] Will assume transactional DDL.
INFO  [alembic.context] Running downgrade ae1027a6acf -> 1975ea83b712
INFO  [alembic.context] Running downgrade 1975ea83b712 -> None

$ alembic upgrade head
INFO  [alembic.context] Context class PostgresqlContext.
INFO  [alembic.context] Will assume transactional DDL.
INFO  [alembic.context] Running upgrade None -> 1975ea83b712
INFO  [alembic.context] Running upgrade 1975ea83b712 -> ae1027a6acf

---

8. Solve the Problem

记录日常使用 alembic 命令时遇到的问题以及对应的解决方法！

• [1] 数据库升级找不到对应代码

镜像包启动的时候，升级数据库，发现如下报错信息，提示找不到需要使用的 revision id，很明显是因为找不到对应升级文件。但是确认之后，发现对应文件是好的，且开发本地测试也是没有任何问题的。

$ alembic -c misc/alembic.ini -n db -x dburl=postgresql+psycopg2://postgres:app@postgres:5432/app upgrade
FAILED: Can't locate revision identified by '1111324a57ff'

后来发现，原来是因为容器打包的时候，将 python 代码打包成了 pyc，导致其找不到了。这时，需要我们在 alembic.ini 文件里面，开启对应配置，即可。

# set to 'true' to allow .pyc and .pyo files without
# a source .py file to be detected as revisions in the
# versions/ directory
# sourceless = false

[db]
sourceless = true