Add support for triggers

[sqlalchemy-bot]

Migrated issue, originally created by Michael Nagel

As far as I can tell it is currently not possible to create and/or manage triggers. Is this correct? Has there been any discussion on this topic? Are there any plans to add support for this?

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

you can create or manage triggers using op.execute():

op.execute("DROP TRIGGER my_other_trigger ....")
op.execute("CREATE TRIGGER my_trigger ....")

SQLAlchemy has no Core expression concept for trigger DDL, since trigger DDL and capabilties vary significantly across different databases such that it's not worth it to create a new language that tries to accommodate these (in reality, the litmus test is that triggers and stored procedures are more imperative than declarative hence are a poor match for SQLAlchemy core constructs). It is unlikely such a construct will be added beyond the DDL object (see http://docs.sqlalchemy.org/en/latest/core/ddl.html?highlight=ddl) which is mostly a container for raw DDL text. So there's no "op.create_trigger()" command feasible for Alembic without a third party extension that implements trigger-specific DDL constructs.

Additionally, SQLAlchemy has no facilities for reflection of triggers either, which would be the first thing Alembic would need in order to implement "autogenerate". If it did, it would have to be retrieving the DDL text of each trigger and comparing it to what is specified in the model somewhere.

There is a recipe in the documentation that provides a framework for slightly more automated handling of imperative, scripted constructs like triggers and stored procedures, at http://alembic.zzzcomputing.com/en/latest/cookbook.html#replaceable-objects. However, that recipe basically gives you the "DROP " statement when you're replacing a structure with a new one, and some slightly more specific syntax, and that's pretty much it.

All of this said, a third party could build a very nice third party plugin for Alembic that provides for whatever declarative trigger specification language it wants, however within Alembic / SQLAlchemy proper, trigger DDL is passed as the raw string.

[sqlalchemy-bot]

Michael Nagel wrote:

I am already doing it this way (with op.execute), and I even support Sqlite and Postgres via the constuct outlined below. Writing that code made me very unhappy (especially after having seen the nice, database agnostic code used to create, alter, ... tables). It also made me realize how different the dialects are for triggers and I concluded that a DSL to describe triggers for multiple/all databases would be a huge productivity boost. I would be fine with just support for create and drop trigger (no alter) because you don't get anything else with plain SQL either.

As a side note, I do not use the OR mapping features of Alembic/SQLAlchemy. I use Alembic more or less standalone to upgrade/downgrade a Postgres database for a C++ application. I had the choice between Alembic, https://github.com/mattes/migrate or roll-my-own. I chose Alembic. I wrote plain SQL migrations before, now I use the constructs from Alembic and one great benefit is that I can now create a SQLite instance for testing purposes very easily. The only complicated thing was to add the triggers, which is what brought me here...

from alembic import op

revision = '...'
down_revision = '...'

from sqlalchemy.orm import sessionmaker

Session = sessionmaker()

def upgrade():
    bind = op.get_bind()
    session = Session(bind=bind)

    sqls = ["""
CREATE TRIGGER update_setting_value ...
""", """
CREATE TRIGGER insert_setting_value ...
        """]

    sqls_postgres = ["""
...
"""]

    if bind.engine.name == "postgresql":
        print("postgresql detected")
        sqls = sqls_postgres
    else:
        print("assuming sqlite")

    for sql in sqls:
        session.execute(sql)
    session.close()

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

I concluded that a DSL to describe triggers for multiple/all databases would be a huge productivity boost.

feel free to propose what that would look like (also it would likely be part of SQLAlchemy). I have no idea how that would look, and additionally how to be able to reflect it back from the database also which you need if you want Alembic to have meaningful support, at the very least it has to be a way of comparing the DSL in Python for a given trigger to a trigger that is reflected from the DB.