如何借助Alembic与SQLAlchemy迁移Postgres数据库至app schema

1. 调整SQLAlchemy模型与连接配置 #

• 统一模型默认schema：修改你的SQLAlchemy基类，让所有继承它的表默认使用app schema

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
Base.__table_args__ = {"schema": "app"}  # 全局指定所有模型表的schema为app

• 配置数据库连接的搜索路径：确保连接时优先读取app schema，兼容旧的public引用

from sqlalchemy import create_engine

engine = create_engine(
    "postgresql://user:password@host/dbname",
    connect_args={"options": "-c search_path=app,public"}
)

2. 修改Alembic配置，指定版本表的schema #

• 编辑alembic.ini，新增版本表schema配置：

# alembic.ini
version_table_schema = app

• 同步修改alembic/env.py的迁移上下文配置，确保Alembic识别版本表的新位置：

# alembic/env.py
def run_migrations_online():
    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,
            version_table_schema="app"  # 明确指定版本表在app schema下
        )

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

3. 编写迁移脚本：创建schema+迁移所有表 #

• 生成空迁移脚本：

alembic revision --autogenerate -m "migrate all tables to app schema"

• 编辑生成的迁移脚本，替换内容为以下逻辑（替换实际表名）：

from alembic import op
import sqlalchemy as sa

def upgrade():
    # 1. 创建app schema（不存在则创建）
    op.execute("CREATE SCHEMA IF NOT EXISTS app;")
    
    # 2. 迁移所有public下的表到app，包含alembic_version
    target_tables = [
        "alembic_version",
        "user",
        "order",
        # 列出你所有需要迁移的表名
    ]
    for table in target_tables:
        op.execute(f"ALTER TABLE public.{table} SET SCHEMA app;")

def downgrade():
    # 回滚逻辑：将表移回public，删除空schema
    target_tables = [
        "alembic_version",
        "user",
        "order",
        # 与upgrade中的表顺序一致
    ]
    for table in target_tables:
        op.execute(f"ALTER TABLE app.{table} SET SCHEMA public;")
    
    op.execute("DROP SCHEMA IF EXISTS app;")

4. 执行迁移并验证 #

• 执行升级迁移：

alembic upgrade head

• 验证结果：连接数据库后，执行\dn确认app schema存在，执行\dt app.*查看所有表（含alembic_version）已迁移完成，public下原表已不存在。

5. 后续注意事项 #

• 后续新增的SQLAlchemy模型会自动在app schema下创建表
• 若数据库存在视图、存储过程等对象，需手动执行ALTER ... SET SCHEMA app迁移
• 确保所有业务代码中直接引用表名时，无需再指定public前缀（依赖连接的search_path配置）

内容的提问来源于stack exchange，提问作者psarka