Skip to content
UQL

Type-safe triggers in an ORM. What?

A @Trigger declaration on a Post entity, the writers it fires for (uql, psql, a backfill, another service), and the four SQL engines it is rendered forA @Trigger declaration on a Post entity, the writers it fires for (uql, psql, a backfill, another service), and the four SQL engines it is rendered for
Roger Padilla

Your audit log is written by a lifecycle hook, and it works in every test. Then someone runs a backfill script, fixes a row in psql, or points a second service at the same table, and the log has a gap. Nothing failed. The hook never ran, because an ORM hook only sees the ORM’s own writes.

A database trigger has no such gap: the database runs it on every write, whoever makes it. In UQL you declare the trigger on the entity, the compiler checks its body against your entities, and uql renders it for each SQL engine. As far as I can tell, it is the first TypeScript ORM to do all three.

Why teams avoid triggers

Section titled “Why teams avoid triggers”

Many teams avoid triggers, for good reasons. A trigger usually lives as a SQL string (plain text) in an old migration file. Nobody sees it from the model, nothing checks it against the columns, so a renamed column breaks it when it first fires, and every engine wants its own syntax.

Those are tooling problems, not problems with triggers. Here is the same audit log, declared where the table is:

import { Entity, Field, Id, insertInto, Trigger } from 'uql-orm';


@Entity()
export class PostAudit {
  @Id({ type: Number })
  id?: number;


  @Field({ type: Number, name: 'post_id', nullable: false })
  postId?: Post['id'];


  @Field({ type: String, name: 'from_status' })
  fromStatus?: Post['status'];


  @Field({ type: String, name: 'to_status' })
  toStatus?: Post['status'];
}


@Trigger({
  on: 'afterUpdate',
  name: 'audit',
  of: (post) => [post.status],
  run: (newRow, oldRow) =>
    insertInto(PostAudit, {
      postId: newRow.id,
      fromStatus: oldRow.status,
      toStatus: newRow.status,
    }),
})
@Entity()
export class Post {
  @Id({ type: Number })
  id?: number;


  @Field({ type: String })
  status?: string | null;
}

of fires the trigger only when status changes value, and run writes the audit row. Both read columns as properties, so the compiler knows every name and type in the body. A where adds conditions on either row, such as a transition from { $old: { status: 'draft' } } to { $new: { status: 'published' } }, checked the same way.

What the compiler catches

Section titled “What the compiler catches”

Each of these fails at build time, not when the trigger first fires:

import { defineTrigger, insertInto } from 'uql-orm';


defineTrigger(Post, {
  on: 'afterInsert',
  run: (_newRow, oldRow) => insertInto(PostAudit, { postId: oldRow.id }), // error: no old row on insert
});


defineTrigger(Post, {
  on: 'afterUpdate',
  run: (newRow) => insertInto(PostAudit, { post: newRow.id }), // error: PostAudit has no `post`
});


defineTrigger(Post, {
  on: 'afterUpdate',
  run: (newRow) => insertInto(PostAudit, { postId: newRow.status }), // error: postId holds a number
});

The same goes for watching a column the entity lacks, an updateTable or deleteFrom with no $where (it would hit every row on every fire), and a body for an engine uql does not render triggers for.

One declaration, every SQL engine

Section titled “One declaration, every SQL engine”

This is what sync and generated migrations install for that one decorator. CockroachDB gets the Postgres form, and MariaDB the MySQL one:

CREATE OR REPLACE FUNCTION "_uql_Post__audit_0a57e9"() RETURNS trigger AS $uql$
BEGIN
  INSERT INTO "PostAudit" ("post_id", "from_status", "to_status")
    VALUES (NEW."id", OLD."status", NEW."status");
  RETURN NULL;
END $uql$ LANGUAGE plpgsql;


CREATE TRIGGER "_uql_Post__audit_0a57e9"
AFTER UPDATE OF "status" ON "Post"
FOR EACH ROW
WHEN (OLD."status" IS DISTINCT FROM NEW."status")
EXECUTE FUNCTION "_uql_Post__audit_0a57e9"()

Look at the condition in each. “Fire only when status changes” must count NULL to 'draft' as a change, which a plain <> does not, and each engine spells that differently (IS DISTINCT FROM, <=>, IS NOT, EXCEPT). SQL Server also hands the trigger whole tables rather than rows. That is four chances to get it subtly wrong by hand.

How the other ORMs do it

Section titled “How the other ORMs do it”

The same audit trigger, on PostgreSQL only:

-- No trigger API. This goes in a hand-written migration: `drizzle-kit generate --custom`,
-- `prisma migrate dev --create-only`, or `queryRunner.query(...)` in a TypeORM migration.
CREATE FUNCTION post_audit() RETURNS trigger AS $$
BEGIN
  INSERT INTO post_audit (post_id, from_status, to_status)
    VALUES (NEW.id, OLD.status, NEW.status);
  RETURN NULL;
END $$ LANGUAGE plpgsql;


CREATE TRIGGER post_audit AFTER UPDATE OF status ON post
FOR EACH ROW WHEN (OLD.status IS DISTINCT FROM NEW.status)
EXECUTE FUNCTION post_audit();

MikroORM comes closest: the trigger lives on the entity, and the column names come from its metadata. The SQL around them is still a string, so a wrong type or an OLD read on an insert reaches the database unchecked, and a second engine means a second body. TypeORM’s subscribers, like hooks, only see TypeORM’s own writes.

Keeping triggers in sync

Section titled “Keeping triggers in sync”

sync and generate:entities install what an entity declares and drop what it no longer declares. Each installed name ends in a hash of its SQL, so an unchanged trigger is left alone and an edited one is replaced. Triggers uql did not create are never touched.

Where it stops

Section titled “Where it stops”
  • MongoDB has no triggers that run inside a write, so a write to an entity that declares one is refused rather than made without it.
  • Raw SQL bodies are not translated. A raw body has to run on every engine you use, or you give one per engine.
  • No entity filters apply inside a trigger. It runs outside any request, so soft-delete and security filters do not scope its writes; scope them by the row’s own columns.

SQL Server and MySQL each have a few rules of their own, listed on the Triggers page.

Why it matters more with coding agents

Section titled “Why it matters more with coding agents”

Coding agents now write much of the code that touches a database, including the kind hooks never see: a one-off script, a backfill, a quick UPDATE. A rule the database enforces holds for all of it. And a trigger on the entity sits in the file the agent reads and is checked by the compiler it runs; a trigger in an old migration is neither.

Hook, trigger or stamp

Section titled “Hook, trigger or stamp”
You need Use
Logic around UQL’s own writes: call an API, publish an event, validate a lifecycle hook
A row the database must write whoever changes the table, like an audit entry a trigger
A column the database keeps current on every write, like updatedAt a stamp

A stamp is a trigger whose body uql writes for you: @Field({ computed: raw`CURRENT_TIMESTAMP`, stored: ['update'] }).

Get started

Section titled “Get started”
  • Triggers: every option, the rendered SQL per engine, and the engine notes
  • Comparison: triggers and the rest of the feature matrix, ORM by ORM
  • GitHub

If a trigger you need cannot be declared this way, open an issue with the SQL you would have written.

Tags:
UQLMIT LicenseGitHubnpmRSSllms.txt