Support PostgreSQL "named schemas"

Issue #409 new
Petri Savolainen
created an issue

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

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

Comments (7)

  1. Omer Katz

    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.

  2. Michael Bayer repo owner

    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:

    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")
    
  3. Omer Katz

    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.

  4. Michael Bayer repo owner

    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.

  5. Log in to comment