How to get alembic to emit custom DDL on after_create?

Question

I have some custom DDL statements that I want to run after creating the table:

update_function = DDL(""" CREATE OR REPLACE FUNCTION update_timestamp() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = now(); RETURN NEW; END; $$ language 'pgplsql'; """) update_trigger = DDL(""" CREATE TRIGGER update %(table)s_timestamp BEFORE UPDATE ON %(table)s FOR EACH ROW EXECUTE PROCEDURE update_timestamp(); """) 

And I tied them like this:

 event.listen(Session.__table__, 'after_create', update_function) event.listen(Session.__table__, 'after_create', update_trigger) 

When I do create_all , I get the expected SQL:

 CREATE OR REPLACE FUNCTION update_timestamp() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = now(); RETURN NEW; END; $$ language 'pgplsql'; CREATE TRIGGER update session_timestamp BEFORE UPDATE ON session FOR EACH ROW EXECUTE PROCEDURE update_timestamp(); 

But when I update using Alembic, the statements do not appear:

 -- Running upgrade c0d470e5c81 -> 6692fad7378 CREATE TABLE session ( created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT 'CURRENT_TIMESTAMP', updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT 'CURRENT_TIMESTAMP', id VARCHAR(32) NOT NULL, owner_id INTEGER, database_id VARCHAR(32), content TEXT, PRIMARY KEY (id), FOREIGN KEY(database_id) REFERENCES database (id), FOREIGN KEY(owner_id) REFERENCES users (id) ); INSERT INTO alembic_version (version_num) VALUES ('6692fad7378'); 

Is there a way to get alembic to fire 'after_create' events?

Answer 1

Events of the level before_create / after_create are highlighted at the table level (and not just at the metadata level). you need to make sure that everything that happens in your env.py script ultimately includes the settings of those event listeners.

The code you have here looks a little suspicious:

 event.listen(Session.__table__, 'after_create', update_function) event.listen(Session.__table__, 'after_create', update_trigger) 

Session.__table__ here will be just one instance of Table and probably not the one you see in the alembic script. The alembic create_table command creates the Table locally and simply starts creating on it, so you will need to listen to all the table objects globally:

 from sqlalchemy import Table event.listen(Table, 'after_create', update_function) event.listen(Table, 'after_create', update_trigger) 

If these events are intended only for this particular table, then you will not use any events, you just put DDL () for these triggers directly in your transfer script, right after it calls create_table() .

Answer 2

Turning around @zzzeek's answer, this helper works for me:

 from sqlalchemy import Table from sqlalchemy.event import listen from functools import partial def on_table_create(class_, ddl): def listener(tablename, ddl, table, bind, **kw): if table.name == tablename: ddl(table, bind, **kw) listen(Table, 'after_create', partial(listener, class_.__table__.name, ddl)) 

Then you would do:

 on_table_create(Session, update_function) on_table_create(Session, update_trigger)