On Wed, Dec 12, 2018 at 10:40 AM <ncani...@kpler.com> wrote:
>
> Dear Alembic User Community,
>
> I am looking for suggestion for the best way to address the following problem:
>
> We have a use case where we'd like to make sure some SQL queries are always
> executed after any set migration.
> Our particular use case is with PostgreSQL. We have several users who can
> create new tables and types in a database.
> But we want to make sure the ownership of objects is a group all those users
> belong to. But we'd rather make that
> transparent so it does not get forgotten in some migrations.
>
> So far we managed to do this by overriding the do_run_migrations() function
> in the env.py (cf. code below).
> That has some good and bad qualities:
>
> - The "hook" runs once whether we pass 1 or more migrations;
> - The migrations are applied, even if this last step fails;
> - It does not work in "online" mode unless we force a commit (offline mode w.
> `--sql` does emit a commit afterwards);
>
> So I wondered if someone would have encountered similar use cases and if
> there would be other / better ways to achieve this ?
> In particular, a better way to know what is the command being invoked, cause
> you don't want to apply this "hook" for commands other
> than upgrade and downgrade.
>
> Here is the code snippet:
>
> def do_run_migrations(context):
> with context.begin_transaction():
> context.run_migrations()
> command_name = context.config.cmd_opts.cmd[0].__name__
> if command_name in ('downgrade', 'upgrade', ):
> context.execute('REASSIGN OWNED BY "{user}" TO "schema-admin"'
> .format(user=settings.ALEMBIC_DB.username))
> context.execute('COMMIT') # Does not work "online", unless we do
> this
There's support for running a hook after each migration:
https://alembic.sqlalchemy.org/en/latest/api/runtime.html?highlight=on_version_apply#alembic.runtime.environment.EnvironmentContext.configure.params.on_version_apply
although to use that hook and figure out how to just run for the
"last" step would require comparing the step given to the last step
requested.
if you are looking to run this only once at the end, what you're doing
should not require that "COMMIT" unless you are running
transaction_per_migration=True. Tried it here and with
transactional_ddl=True the COMMIT is emitted just once at the end of
the block, so I cannot reproduce your "does not work in 'online'" case
without transaction_per_migration=True.
More generally, if what you're doing is a step that should be before
*any* commit, which is what it seems like, use a commit event:
https://docs.sqlalchemy.org/en/latest/core/events.html?highlight=connectionevent#sqlalchemy.events.ConnectionEvents.commit
@event.listens_for(connection, "commit")
def on_commit(conn):
conn.execute("ddl...")
>
>
> Regards,
> Nicolas.
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy-alembic" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy-alembic+unsubscr...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
