How to version-control functions and triggers with alembic?

user_134904 0
Viewed 1340

Suppose, there's some trigger in the database with a function, like this:

-- Insert a new entry into another table
-- every time a NEW row is inserted
CREATE FUNCTION trgfunc_write_log() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO some_other_table (
        -- some columns
        meter_id,
        date_taken,
        temperature,
    ) values (
        NEW.meter_id,
        NEW.time_taken,
        NEW.temperature
    );

    return NEW;
END;
$$ language 'plpgsql';

-- The trigger itself: AFTER INSERT
CREATE TRIGGER trg_temperature_readings
AFTER INSERT ON temperature_readings
FOR EACH ROW
EXECUTE FUNCTION trgfunc_write_log();

Typically, this trigger will live next to my SqlAlchemy models and be auto-created with something like this:

from sqlalchemy import DDL, event
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Reading(Base):
   ...

create_trigger = DDL(""" ...SQL... """)
event.listen(Reading.__table__, 'after_create', create_trigger)

What's your best practice for version-controlling such a trigger and its function with Alembic migrations?

alembic triggers postgresql sql python
1 Answers

I recently had the same question come up in an application and found this article in the Alembic Cookbook.

It outlines a somewhat complex strategy of creating an object that encapsulates the name and SQL used to create a view, stored procedure, or trigger among other objects used to perform the Alembic operations to upgrade and downgrade that schema object. It looks something like this when used in an Alembic revision:

from alembic import op
from my_module import ReplaceableObject


my_trigger = ReplaceableObject(
    "trigger_name",
    """...SQL..."""
)

def upgrade():
    op.create_trigger(my_trigger)

def downgrade():
    op.drop_trigger(my_trigger)

My team is currently discussing if this strategy is too complex for a simple trigger compared to a view or stored proc. You may update those schema objects more frequently making much of the behavior outlined in the Cookbook abstractions more valuable than with a simple trigger.

Another proposed option was something like this:

from alembic import op


create_trigger = """...SQL..."""
drop_trigger = """...SQL..."""

def upgrade():
    op.execute(create_trigger)

def downgrade():
    op.execute(drop_trigger)

The two implementations look almost identical, which is the argument for the Cookbook abstraction being unnecessarily complex for a simple trigger.

user_31285510
Related