Alembic and postgresql multiple schema question

[sector119]

Hello

I have N schemas with the same set of tables, 1 system schema with users, groups, ... tables and 6 schemas with streets, organizations, transactions, ... tables. 

On those schemas tables I don't set __table_args__ = ({'schema': SCHEMA},)

I just call dbsession.execute('SET search_path TO system, %s' % SCHEMA) before sql queries.

When I make some changes in my model structures I want to refactor table in all schemas using Alembic, how can I do that?

Maybe I can make some loop over my schemas somewhere? 

Thanks

[sector119]

I've found some example at https://stackoverflow.com/questions/21109218/alembic-support-for-multiple-postgres-schemas

But when I run alembic revision --autogenerate -m "Initial upgrade" at alembic/versions/24648f118be9_initial_upgrade.py I've got no schema='myschema' keywords on table, indexes, columns items ((

def run_migrations_online():
    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine
    and associate a connection with the context.

    """
    connectable = engine_from_config(
        config.get_section(config.config_ini_section),
        prefix='sqlalchemy.',
        poolclass=pool.NullPool)

    with connectable.connect() as connection:
        for schema_name in schema_names.split():
            conn = connection.execution_options(schema_translate_map={None: schema_name})

            print("Migrating schema %s" % schema_name)

            context.configure(
                connection=conn,
                target_metadata=target_metadata
            )

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

среда, 29 августа 2018 г., 12:12:19 UTC+3 пользователь sector119 написал:

[Mike Bayer]

setting the search path is going to confuse SQLAlchemy's table
reflection process, such that it assumes a Table of a certain schema
does not require a "schema" argument, because it is already in the
search path.

Keep the search path set to "public", see
http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path.
There is an option to change this behavior mentioned in that
section called postgresql_ignore_search_path, however it isn't
guaranteed to suit all use cases. if that makes your case work, then
that would be all you need. if not, then read on...

For the officially supported way to do this, you want to have the
explicit schema name inside the SQL - but this can be automated for a
multi-tenancy application. Use the schema translation map feature:
http://docs.sqlalchemy.org/en/latest/core/connections.html?highlight=execution_options#schema-translating.


>
>
> Thanks
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[sector119]

Mike, but in run_migrations_online() I use conn = connection.execution_options(schema_translate_map={None: schema_name})
But I get no schemas at resulting alembic/versions/file.py

среда, 29 августа 2018 г., 20:46:07 UTC+3 пользователь Mike Bayer написал:

[Mike Bayer]

On Thu, Aug 30, 2018 at 7:11 AM, sector119 <sect...@gmail.com> wrote:
> Mike, but in run_migrations_online() I use conn =
> connection.execution_options(schema_translate_map={None: schema_name})
> But I get no schemas at resulting alembic/versions/file.py

can you share your env.py

[sector119]

from __future__ import with_statement
from alembic import context
from sqlalchemy import engine_from_config, pool
from logging.config import fileConfig

# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config

# Interpret the config file for Python logging.
# This line sets up loggers basically.
fileConfig(config.config_file_name)

# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata
from epsilon.models.meta import metadata
target_metadata = metadata

# other values from the config, defined by the needs of env.py,
# can be acquired:
# my_important_option = config.get_main_option("my_important_option")
# ... etc.

schema_names = config.get_main_option('schemas')


def run_migrations_offline():
    """Run migrations in 'offline' mode.

    This configures the context with just a URL
    and not an Engine, though an Engine is acceptable
    here as well.  By skipping the Engine creation
    we don't even need a DBAPI to be available.

    Calls to context.execute() here emit the given string to the
    script output.

    """
    url = config.get_main_option("sqlalchemy.url")
    context.configure(
        url=url, target_metadata=target_metadata, literal_binds=True)

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

def run_migrations_online():
    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine
    and associate a connection with the context.

    """
    connectable = engine_from_config(
        config.get_section(config.config_ini_section),
        prefix='sqlalchemy.',
        poolclass=pool.NullPool)

    with connectable.connect() as connection:
        for schema_name in schema_names.split():

            conn = connection.execution_options(schema_translate_map={None: schema_name})

            print("Migrating schema %s" % schema_name)

            context.configure(
                connection=conn,
                target_metadata=target_metadata
            )

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

if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()

четверг, 30 августа 2018 г., 16:09:35 UTC+3 пользователь Mike Bayer написал:

[Mike Bayer]

I've spent a long time evaluating this use case and there are a lot of
ways to do it, but ultimately you are saying each schema is identical,
and originally, you just wanted to set "search_path" to each schema,
which is probably how this should be done if each schema is
independent of each other and has the identical tables. This means
that when you autogenerate, you should only "autogenerate" for the
first schema since they are all identical. Then when migrations run,
you don't need to have "schema" set in your create_table because you
are setting search path, that will be the "default" schema.

for that exact use case, this works:

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 schema_name in ['test_schema', 'test_schema_2']:
connection.execute("SET search_path TO %s" % schema_name)
connection.dialect.default_schema_name = schema_name

context.configure(
connection=connection,

target_metadata=target_metadata
)

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

if context.config.cmd_opts.cmd[0].__name__ == 'revision':
break

note I had to set the default_schema_name on the dialect since we are
changing the search path without re-initializing an engine.

if you want individual migration sections for each schema, with or
without "schema" written in, there's ways to do all that also but that
doesn't seem necessary if you are sharing a single model with multiple
identical schemas.

[sector119]

if you want individual migration sections for each schema, with or
without "schema" written in, there's ways to do all that also but that
doesn't seem necessary if you are sharing a single model with multiple
identical schemas.

The problem is that some one might alter some tables at some schemas with plain sql in psql ((

So it would be perfect to have individual migration sections for each schema, _with_ "schema" written in.

Thank you

[Mike Bayer]

OK I had wondered if you would actually want that. but that means
if schemas have been altered manually like that, then the next
migration is going to show those changes being reversed, if you are OK
with that.

What we do now is start using techniques from the "multidb" template
that's included with Alembic.

Assume you added this to alembic.ini:

schema_names = test_schema, test_schema_2

then you can genrerate migrations for each schema individually like this:

import re
schema_names = re.split(r',\s*', config.get_main_option('schema_names'))

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 schema_name in schema_names:

connection.execute("SET search_path TO %s" % schema_name)
connection.dialect.default_schema_name = schema_name
context.configure(
connection=connection,

target_metadata=target_metadata,
upgrade_token="%s_upgrades" % schema_name,
downgrade_token="%s_downgrades" % schema_name,
)

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


you also need to modify script.py.mako to render these sections:

def upgrade(schema_name):
globals()["upgrade_%s" % schema_name]()


def downgrade(schema_name):
globals()["downgrade_%s" % schema_name]()

<%
import re
schema_names = re.split(r',\s*', config.get_main_option('schema_names'))
%>


% for schema_name in schema_names:

def upgrade_${schema_name}():
${context.get("%s_upgrades" % schema_name, "pass")}


def downgrade_${schema_name}():
${context.get("%s_downgrades" % schema_name, "pass")}

% endfor


When you run an autogenerate, you will get this:

def upgrade(schema_name):
globals()["upgrade_%s" % schema_name]()


def downgrade(schema_name):
globals()["downgrade_%s" % schema_name]()

def upgrade_test_schema():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('t1',
sa.Column('id', sa.Integer(), nullable=False),
sa.PrimaryKeyConstraint('id')
)
# ### end Alembic commands ###


def downgrade_test_schema():
# ### commands auto generated by Alembic - please adjust! ###
op.drop_table('t1')
# ### end Alembic commands ###


def upgrade_test_schema_2():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('t1',
sa.Column('id', sa.Integer(), nullable=False),
sa.PrimaryKeyConstraint('id')
)
# ### end Alembic commands ###


def downgrade_test_schema_2():
# ### commands auto generated by Alembic - please adjust! ###
op.drop_table('t1')
# ### end Alembic commands ###


note that the "schema=<name>" is still not present, but is not needed
since your env.py sets the search path to just that single schema, the
"system" schema is not included (and is not needed). When you run
migrations, that same logic occurs for migrations being run and you
each set of DDL will run within the scope of that SEARCH_PATH. I have
tested this and can confirm it works.

Now if you really *want* the "schema=<name>" anyway, you can add it in
with a rewriter, like this:

with connectable.connect() as connection:

def process_revision_directives(context, revision, directives):
script = directives[0]
schema_name = context.opts['x_schema_name']

upgrade_ops = script.upgrade_ops_list[-1]
downgrade_ops = script.downgrade_ops_list[-1]

for op in upgrade_ops.ops + downgrade_ops.ops:
op.schema = schema_name
if hasattr(op, "ops"):
for sub_op in op.ops:
sub_op.schema = schema_name

for schema_name in ['test_schema', 'test_schema_2']:

conn = connection
conn.execute("SET search_path TO %s" % schema_name)
conn.dialect.default_schema_name = schema_name
context.configure(
connection=conn,
target_metadata=target_metadata,
upgrade_token="%s_upgrades" % schema_name,
downgrade_token="%s_downgrades" % schema_name,
process_revision_directives=process_revision_directives,
x_schema_name=schema_name
)

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

I've added an extra param to the context for each schema so the above
revision processing function can get at it.

I was working on this as a new cookbook section which is in WIP at
https://gerrit.sqlalchemy.org/#/c/zzzeek/alembic/+/864/, however I'm
still skeptical that the use case of same model for multiple schemas
that are *not* identical is going to be that useful, so I may amend
this recipe to work like the "multidb" template and have individual
MetaData objects per schema just as the main example, it can be
modified to have a single MetaData if that's what someone wants.










>
> Thank you