Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Alembic support for multiple Postgres schemas

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?

like image 910
dtheodor Avatar asked Jan 14 '14 08:01

dtheodor


People also ask

Does Postgres support multiple schemas?

PostgreSQL supports having multiple schemas in a single database there by letting you namespace different features into different schemas. For example, you can have a database named postgres and have multiple schemas based on your application like ecommerce , auth etc.

What is SQLAlchemy schema?

SQLAlchemy schema metadata is a comprehensive system of describing and inspecting database schemas. The core of SQLAlchemy's query and object mapping operations is supported by database metadata.

What is a Postgres schema?

What is a PostgreSQL schema. In PostgreSQL, a schema is a namespace that contains named database objects such as tables, views, indexes, data types, functions, stored procedures and operators. To access an object in a schema, you need to qualify the object by using the following syntax: schema_name.object_name.


2 Answers

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

like image 54
zzzeek Avatar answered Oct 10 '22 04:10

zzzeek


Here's a workable solution: https://gist.github.com/nickretallack/bb8ca0e37829b4722dd1

It still requires you to edit the schema name out of each migration after it's generated, but at least the interesting work is done by Alembic. Thanks to Michael Bayer for help with this on the mailing list.

like image 45
Nick Retallack Avatar answered Oct 10 '22 06:10

Nick Retallack