add entity decorators to specify triggers #5053

BrannJoly · 2023-12-27T11:20:57Z

Is your feature request related to a problem? Please describe.
I'm using a few triggers in my database and I would love to be able to specifiy those in my dbEntities files, hence relying solely on mikroOrm generated migrations to create my db schema.

Describe the solution you'd like
Very similar to the Index decorator, I would love having a Trigger decorator, along those lines:

@Trigger(
{
  name:'my_trigger'
  insert:true,
  update:true,
  delete:false
  fn : `UPDATE x SET t=1 WHERE id=NEW.id; RETURN NEW;`
  }
)
@Entity({ tableName: 'myTable' })

For postgres, this would generate the following sql code :

CREATE OR REPLACE FUNCTION my_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE x SET t=1 WHERE id=NEW.id, 
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER my_trigger
AFTER INSERT OR UPDATE  ON myTable
FOR EACH ROW EXECUTE FUNCTION my_trigger_function()

Describe alternatives you've considered
Currently I update a migration created by mikro orm to add my trigger creation statement. this works fine.

However, to speed up the db schema creation process (notably for our CI), from time to time we remove all our migrations and restart from a clean slate using npx mikro-orm migration:create --initial
doing this will lose the trigger code, so we need to remember to reapply it manually.

Also, the trigger code is hidden in a migration file. it's also in a comment on top of my entity file, but it's a comment and there's no guarantee it's always in sync with the actual code ....

I guess I could also do something like this, but it makes my eyes bleed ... and the down() method won't work

@Index({ name: 'ugly_hack_to_execute_arbitrary_sql_in_migrations', expression: `CREATE OR REPLACE FUNCTION my_trigger_function() RETURNS TRIGGER AS $$ BEGIN UPDATE x SET t=1 WHERE id=NEW.id; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER my_trigger AFTER INSERT OR UPDATE ON myTable FOR EACH ROW EXECUTE FUNCTION my_trigger_function();`})

maybe a decorator dedicated to executing arbitrary sql in the migrations could cover other use cases as well (eg creating stored procedures while leveraging mikro orm migrations system ?).
I have created another issue to suggest this

The text was updated successfully, but these errors were encountered:

BrannJoly · 2023-12-28T13:09:24Z

I ended up creating a migration like this : npx mikro-orm migration:create --blank --name my_trigger

and I then referenced the migration class in a jsdoc comment:

import { Migration20231227161932_mytrigger } from '../../../migrations\Migration20231227161932_my_trigger';
/**
   * @see Migration20231227161932_mytrigger
   */
   @Entity({ tableName: 'my_table' }) 
   // ......

this solves my two issues :

if my migration is overriden by an initlal migration, the jsdoc comment will fail to compile
the actual code for the trigger is just one ctrl+click on the jsdoc away

boenrobot · 2024-02-15T12:05:55Z

Note for my future self or anyone who may be willing to try implementing some integrated support of this...

Postgresql's triggers require the name of a function to be called on the event (source) while MySQL and SQLite triggers require inlined body of a procedure at the trigger's definition. And that kind of complicates things...

The best way I see to support this is to have a Postgresql only option about a function name, and then on schema generation/migration, create/replace that function just before creating/replacing the trigger, and have the body of the function be the body defined for the trigger. Default to name the function as the name of trigger (perhaps prefixed/suffixed somehow if required? I haven't actually tried this, so idk if triggers and functions are in the same namespace). In the case of Postgresql, the body should also be optional, in which case, no function is created. This is needed to support triggers that reference native functions.

Similarly, on entity generation, the type of the function can be determined, and if it's an SQL one, its body can be copied into the trigger definition. Or, if support for SQL function definitions is also integrated, the generator may always generate separate SQL function definitions when running against Postgresql, and never copy the body into the trigger definition, to ensure the function could stay DRY.

BrannJoly added the enhancement New feature or request label Dec 27, 2023

BrannJoly changed the title ~~add entity decorators to specify triggers~~ add entity decorators to specify triggers (or a generic decorator to execute arbitrary SQL in the migrations) Dec 27, 2023

BrannJoly changed the title ~~add entity decorators to specify triggers (or a generic decorator to execute arbitrary SQL in the migrations)~~ add entity decorators to specify triggers Dec 27, 2023

boenrobot mentioned this issue Feb 17, 2024

Represent stored routines and provide type safe way to execute them #5253

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

add entity decorators to specify triggers #5053

add entity decorators to specify triggers #5053

add entity decorators to specify triggers #5053

add entity decorators to specify triggers #5053

Comments