Support PostgreSQL "named schemas"

[sqlalchemy-bot]

Migrated issue, originally created by Petri Savolainen ()

See https://www.postgresql.org/docs/current/static/ddl-schemas.html

For multi-tenant applications, named schemas are commonly used.

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

these are fully supported. see http://alembic.zzzcomputing.com/en/latest/ops.html?highlight=create_table#alembic.operations.Operations.create_table.params.schema for example. is there some specific aspect that you're not seeing?

[sqlalchemy-bot]

Omer Katz () wrote:

This script from this StackOverflow question allows you to perform the same schema changes over all named schemas automatically.

The limitation of this script is that you have to edit every migration file to change which schema is used.

I think what @petri_savolainen is asking is to provide a way to apply the same changes over all schemas automatically using a configuration option.

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

You can do that, 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:

#!python

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

[sqlalchemy-bot]

Omer Katz () wrote:

I think this is useful. Can we include this in the documentation?

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

sure. people are going to be clamoring for the autogenerate part though so something more complete would be nice.

[sqlalchemy-bot]

Omer Katz () wrote:

But in the meanwhile, people who are looking for this feature should at least be able to copy/paste from the documentation.
Also how would you provide this feature? Using a flag?

How would you manage multiple schemas for each tenant? There's only so much you can do with automatic generation.

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

it would be in the "recipes" section. "autogenerate" would be looking at the application's table metadata compared to a fixed "specimen" schema in order to create new migrations that apply to every tenant's schema.