Alembic 支持多个 Postgres 模式 [英] Alembic support for multiple Postgres schemas

问题描述

如何使用 Alembic 的 --autogenerate 迁移多个 SQL Alchemy 模型中未硬编码的 Postgres 模式?(SQLAlchemy 对 Postgres 模式的支持 的镜像问题，但对于 Alembic).

特别是，我们使用 Postgres 模式来分离共享同一组表的不同客户端.此外，还有一个在客户端之间共享内容的架构.SQL Alchemy 模型不知道模式，模式是在运行时使用 session.execute("SET search_path TO client1,shared") 设置的.

默认的 --autogenerate 根本没有帮助，因为它检测模型中不存在的多个模式，最终删除模式并重新创建默认模式中的每个表.

不过，我真的很想使用 --autogenerate，并使用适当的管道来正确设置模式.关于 Alembic 的 API 是否/如何做到这一点的任何建议?

解决方案

来自 issue 409，可以使用 翻译的架构名称，这也是您通常为多租户执行主应用程序的方式.>

进入 env.py:

def run_migrations_online():可连接 = engine_from_config(config.get_section(config.config_ini_section),前缀='sqlalchemy.',poolclass=pool.NullPool)使用 connectable.connect() 作为连接:对于 all_my_tenant_names 中的 tenant_schema_name:conn = connection.execution_options(schema_translate_map={无:tenant_schema_name}logger.info("正在迁移租户架构 %s" % tenant_schema_name)上下文.配置(连接=连接，target_metadata=target_metadata)# 在自己的事务中执行每个租户.# 将其向上移动以在一笔巨额交易中处理所有租户使用 context.begin_transaction():context.run_migrations()

以上会将无"模式名称转换为给定的租户名称.如果应用程序与具有全局表的默认架构共享基于租户的架构，那么您将使用诸如tenant_schema"之类的标记作为符号:

对于 all_my_tenant_names 中的 tenant_schema_name:conn = connection.execution_options(schema_translate_map={tenant_schema":tenant_schema_name}

并且在迁移文件中引用tenant_schema"，其中实际租户特定的架构名称:

def upgrade():op.alter_column("some_table", "some_column", <迁移选项>, schema="tenant_schema")

对于自动生成"的情况，@nick-retallack 提供的解决方案有更多的部分你会在这方面使用，即使用 include_schemas 以便自动生成只查看代表最新的样本"模式租户特定架构的版本.

为了将 env.py 设置为对正确的命令使用正确的系统，可以使用带有 migration_context.get_x_argument().

How can I use Alembic's --autogenerate to migrate multiple Postgres schemas that are not hard-coded in the SQL Alchemy model? (mirror question of SQLAlchemy support of Postgres Schemas, but for Alembic).

In particular, we use Postgres schemas to seperate different clients that share the same set of tables. Moreover, there is a schema with shared stuff among clients. The SQL Alchemy model has no knowledge of schemas, the schema is set at run-time using session.execute("SET search_path TO client1,shared").

The default --autogenerate is not helping at all, as it is detecting multiple schemas that do not exist in the model and ends up deleting the schemas and re-creating every table in the default schema.

I would really like to use --autogenerate though, with the proper plumbing to set the schemas correctly. Any suggestions on if/how Alembic's API can do this?

解决方案

from issue 409, the application to tenant-specific schemas of upgrade/downgrade operations can most easily be done using translated schema names, which is also how you would normally be doing the main application as well for multi-tenant.

Go into 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:
        for tenant_schema_name in all_my_tenant_names:
             conn = connection.execution_options(schema_translate_map={None: tenant_schema_name}

            logger.info("Migrating tenant schema %s" % tenant_schema_name)
            context.configure(
                connection=conn,
                target_metadata=target_metadata
            )

            # to do each tenant in its own transaction.
            # move this up to do all tenants in one giant transaction
            with context.begin_transaction():
                context.run_migrations()

Above will translate the "None" schema name into the given tenant name. If the application shares tenant-based schemas with a default schema that has global tables, then you'd be using some token like "tenant_schema" as the symbol:

for tenant_schema_name in all_my_tenant_names:
     conn = connection.execution_options(schema_translate_map={"tenant_schema": tenant_schema_name}

and in migration files refer to "tenant_schema" where the actual tenant-specific schema name goes:

def upgrade():
    op.alter_column("some_table", "some_column", <migration options>, schema="tenant_schema")

For the "autogenerate" case, the solution @nick-retallack provides has some more of the pieces you would use on that end, namely the use of include_schemas so that autogenerate looks only at a "specimen" schema that represents the latest version of the tenant-specific schema.

In order to set up env.py to use the right system for the right command, the behaviors can be controlled using user-defined options with migration_context.get_x_argument().

这篇关于Alembic 支持多个 Postgres 模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！