[Python] How can I version-control functions and triggers with Alembic?

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?

Continue reading...