Alembic/Flask-Migrate not detecting after_create events

Question

I have a simple Flask-SQLAlchemy model (with event listener to create trigger):

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class Confirm(db.Model):
  created = db.Column(db.DateTime, default=db.func.current_timestamp(), nullable=False)
  modified = db.Column(db.DateTime, default=db.func.current_timestamp(), onupdate=db.func.current_timestamp(), nullable=False)
  id = db.Column(db.String(36), primary_key=True) 

class ConfirmOld(db.Model):
  orig_created = db.Column(db.DateTime)
  orig_modified = db.Column(db.DateTime)
  orig_id = db.Column(db.String(36))

confirm_delete = DDL('''\
CREATE TRIGGER confirm_delete 
BEFORE DELETE
  ON confirm FOR EACH ROW
  BEGIN
    INSERT INTO confirm_old ( orig_created, orig_modified, orig_id )
    VALUES ( OLD.created, OLD.modified, OLD.id );
  END;
''')

event.listen(Confirm.__table__, 'after_create', confirm_delete)

When I run Alembic migrate and upgrade, the TRIGGER is not created (in MySQL). However, it is created and works properly when I use db.create_all().

Is it possible to get Alembic / Flask-Migrate to create and manage my triggers (i.e., custom DDL that is run on after_create events)?

Not sure why the trigger doesn't hit when you go through Alembic, but I can tell you if you are working with migrations, it is much better to include the trigger creation in the migration script instead of in a SQLAlchemy event. — Miguel Grinberg, Apr 06 '16 at 16:43
That sounds good - is there a way to keep track of the current state of active DDL? By that, I mean keeping track of each step in the migration history (which is what you propose) together with the currently active state of triggers (or any DDL for that matter)? — user3703588, Apr 06 '16 at 17:03
Not sure I understand the question. By fact of having the migration scripts you have a very well defined schema history. Recall that migration scripts have upgrade and downgrade functions, this allows you to jump to any point in the history of your database schema. — Miguel Grinberg, Apr 06 '16 at 17:13
Alembic does not autodetect custom DDL. You should take a look at the [Replaceable Objects](http://alembic.readthedocs.org/en/latest/cookbook.html#replaceable-objects) section of the docs which outlines how to make managing triggers, functions, and views easier. — univerio, Apr 06 '16 at 18:39
@univerio, that link is now dead, but this one (currently) works: [Replacable Objects](http://alembic.zzzcomputing.com/en/latest/cookbook.html#replaceable-objects) — David Pärsson, Nov 01 '16 at 15:45

score 3 · Answer 1 · answered Sep 18 '18 at 14:50

I have faced the same issue tried a solution with Replacable object but didn't work:

I manage to make it work by editing the migration script and execute the trigger creation query.

Here are the step:

Run flask db migrate -m 'adding custom trigger on table x it will generate a migration script for you under version sub-folder of migration folder.
check the folder created under version and edit it like this :

create your trigger query like this :

in the file :

trigger = '''
CREATE TRIGGER confirm_delete
BEFORE DELETE
ON confirm FOR EACH ROW
BEGIN
INSERT INTO confirm_old ( orig_created, orig_modified, orig_id )
VALUES ( OLD.created, OLD.modified, OLD.id );
END;
'''

in the upgrade method :

add this line :

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###

    # ### end Alembic commands ###

    ### add your queries here execute
    op.execute(trigger)

If you run flask db upgrade it will execute the query and update the database

to downgrade the database add this in the downgrade method:

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    # ### end Alembic commands ###
    op.execute('drop trigger if exists confirm_delete on confirm cascade;')

If you check your database change will be applied .

PS : The more elegant solution should be what is suggest here

with Replaceable object , tried it but It doesn't work may be my alembic is not update .

Here is how the solution should looks like:

create a ReplaceableObjects class :

class ReplaceableObject(object):
    def __init__(self, name, sqltext):
        self.name = name
        self.sqltext = sqltext

instantiate it with your query statement.

delete_trigger = ReplaceableObject('delete_trigger', trigger)

Update your upgrade and downgrade function like this :

def upgrade():
    op.create_sp(delete_trigger)


def downgrade():
    op.drop_sp(delete_trigger)

Hope it will helps others...

score 0 · Answer 2 · answered Dec 14 '22 at 12:19

0

in Flask the listen is ignored.

Fixed this by using Table instead.

def after_create_table_handler(table: Table, conn: Connection, **kwargs):
   pass
event.listen(Table, 'after_create', after_create_table_handler)

answered Dec 14 '22 at 12:19

user2636477

11
1

Alembic/Flask-Migrate not detecting after_create events

2 Answers2