Triggers

@Entity({ triggers }) declares database triggers on an entity. It fires on every write to the table, whether from uql, a console UPDATE, a data migration or another service. uql types it against the entity, renders it for each SQL engine, and installs it with sync and generated migrations.

This one logs every status change of a post:

Decorators

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

@Entity()
export class PostAudit {
  @Field(f.number().autoIncrement()) id?: number;
  @Field(f.number().name('post_id'))
  postId!: number;
  @Field(f.string().nullable().name('from_status')) fromStatus?:
    Post['status'] | null;
  @Field(f.string().nullable().name('to_status')) toStatus?:
    Post['status'] | null;
}

@Entity({
  triggers: (post, trigger) => [
    trigger({
      on: 'afterUpdate',
      name: 'audit',
      of: [post.status],
      run: (newRow, oldRow) =>
        insertInto(PostAudit, {
          postId: newRow.id,
          fromStatus: oldRow.status,
          toStatus: newRow.status,
        }),
    }),
  ],
})
export class Post {
  @Field(f.number().autoIncrement()) id?: number;
  @Field(f.string().nullable()) status?: string | null;
  @Field(f.number().nullable()) views?: number | null;
}

defineEntity

import { f, defineEntity, insertInto } from 'uql-orm';

export const PostAudit = defineEntity({
  name: 'PostAudit',
  fields: {
    id: f.number().autoIncrement(),
    postId: f.number().name('post_id'),
    fromStatus: f.string().nullable().name('from_status'),
    toStatus: f.string().nullable().name('to_status'),
  },
});

export const Post = defineEntity({
  name: 'Post',
  fields: {
    id: f.number().autoIncrement(),
    status: f.string().nullable(),
    views: f.number().nullable(),
  },
}).triggers((post, trigger) => [
  trigger({
    on: 'afterUpdate',
    name: 'audit',
    of: [post.status],
    run: (newRow, oldRow) =>
      insertInto(PostAudit, {
        postId: newRow.id,
        fromStatus: oldRow.status,
        toStatus: newRow.status,
      }),
  }),
]);

on is the event. of, on update events, fires the trigger only when one of the listed columns changes value. run returns the SQL to run, reading the rows through refs such as newRow.status. name is an optional label.

Without decorators, defineEntity’s .triggers() section takes the same entries.

What uql renders

Column names come from the entities, so postId becomes post_id. Each engine fires only when status actually changes:

PostgreSQL

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"()

MySQL

CREATE TRIGGER `_uql_Post__audit_94c077`
AFTER UPDATE ON `Post`
FOR EACH ROW
BEGIN
  IF NOT (OLD.`status` <=> NEW.`status`) THEN
    INSERT INTO `PostAudit` (`post_id`, `from_status`, `to_status`)
      VALUES (NEW.`id`, OLD.`status`, NEW.`status`);
  END IF;
END

MariaDB

CREATE TRIGGER `_uql_Post__audit_94c077`
AFTER UPDATE ON `Post`
FOR EACH ROW
BEGIN
  IF NOT (OLD.`status` <=> NEW.`status`) THEN
    INSERT INTO `PostAudit` (`post_id`, `from_status`, `to_status`)
      VALUES (NEW.`id`, OLD.`status`, NEW.`status`);
  END IF;
END

SQLite

CREATE TRIGGER `_uql_Post__audit_08d35c`
AFTER UPDATE OF `status` ON `Post`
FOR EACH ROW
WHEN (OLD.`status` IS NOT NEW.`status`)
BEGIN
  INSERT INTO `PostAudit` (`post_id`, `from_status`, `to_status`)
    VALUES (NEW.`id`, OLD.`status`, NEW.`status`);
END

SQL Server

CREATE TRIGGER "_uql_Post__audit_d8fbff"
ON "Post" AFTER UPDATE
AS
BEGIN
  SET NOCOUNT ON;
  INSERT INTO "PostAudit" ("post_id", "from_status", "to_status")
    SELECT inserted."id", deleted."status", inserted."status"
    FROM inserted JOIN deleted ON inserted."id" = deleted."id"
    WHERE EXISTS (SELECT deleted."status" EXCEPT SELECT inserted."status");
END

CockroachDB renders the Postgres function with the change check inside it, as an IF, on a plain AFTER UPDATE trigger.

Writing a table: insertInto, upsertInto, updateTable, deleteFrom

These helpers cover most trigger bodies; insertInto is in the example above. Each is typed by the entity it writes, so an unknown field, a wrongly typed value or a $where on a relation does not compile:

import { deleteFrom, updateTable } from 'uql-orm';

@Entity({
  triggers: (_post, trigger) => [
    trigger({
      on: 'afterUpdate',
      run: (newRow) =>
        updateTable(
          PostAudit,
          { $where: { postId: newRow.id } },
          { toStatus: newRow.status },
        ),
    }),
    trigger({
      on: 'afterDelete',
      run: (_newRow, oldRow) =>
        deleteFrom(PostAudit, { $where: { postId: oldRow.id } }),
    }),
  ],
})
export class Post {
  @Field(f.number().autoIncrement()) id?: number;
  @Field(f.string().nullable()) status?: string | null;
  @Field(f.number().nullable()) views?: number | null;
}

upsertInto inserts a row or updates the one its conflict keys find, like upsertOne, a $where included. The existing row takes the incoming values unless the query has an $update, such as a running total:

import { f } from 'uql-orm';
@Entity()
export class PostStats {
  @Field(f.number().autoIncrement()) id?: number;
  @Field(f.number().nullable().unique()) postId?: number | null;
  @Field(f.number().nullable()) edits?: number | null;
}

import { upsertInto } from 'uql-orm';

@Entity({
  triggers: (_post, trigger) => [
    trigger({
      on: 'afterUpdate',
      run: (newRow) =>
        upsertInto(
          PostStats,
          { $conflict: { postId: true }, $update: { edits: { $inc: 1 } } },
          { postId: newRow.id, edits: 1 },
        ),
    }),
  ],
})
export class Post {
  @Field(f.number().autoIncrement()) id?: number;
  @Field(f.string().nullable()) status?: string | null;
  @Field(f.number().nullable()) views?: number | null;
}

In that $update, SQL can read the existing row (sql`${refs(PostStats).edits} + 1` ), $inc can step by a column of the changed row ({ $inc: newRow.amount }), and {} leaves the row as it is.

A trigger runs inside the database, so uql adds nothing to what it writes:

• An insert fills an .onInsert() value or SQL, but runs no callback, and no .onUpdate() applies. An insert leaving out a required column that only a callback fills (.onInsert(() => crypto.randomUUID())) is refused unless the column has a .default(): name it, as SQL if needed, id: sql`gen_random_uuid()` .
• No entity filters apply. deleteFrom on a soft-delete entity removes the row, and security filters are not added, so scope each write by the row’s columns: { tenantId: newRow.tenantId }.
• An update or delete whose $where names no rows is refused, as in the querier.
• SQL Server refuses $inc, $mul and $push: its trigger runs once per statement, so an $inc would count once when several changed rows match it.

Conditions: where

of checks whether a column changed; where checks anything else. Conditions on the new row go under $new, on the old one under $old, with the usual $where operators. With both, where matches a transition:

@Entity({
  triggers: (_post, trigger) => [
    trigger({
      on: 'afterUpdate',
      where: {
        $old: { status: 'draft' },
        $new: { status: { $in: ['published', 'featured'] } },
      },
      run: (newRow, oldRow) =>
        insertInto(PostAudit, { postId: newRow.id, fromStatus: oldRow.status }),
    }),
  ],
})
export class Post {
  @Field(f.number().autoIncrement()) id?: number;
  @Field(f.string().nullable()) status?: string | null;
  @Field(f.number().nullable()) views?: number | null;
}

For anything else, where takes SQL: where: (newRow, oldRow) => sql`${newRow.views} > 2 * ${oldRow.views}` . No soft-delete or security filter is added.

Events and rows

run and a where callback always receive (newRow, oldRow), in that order. Insert events have only newRow, delete events only oldRow, and update events both; reading a row the event lacks is a compile error. Since the positions never change, one body reading only oldRow serves an update trigger and a delete trigger alike.

Your own SQL

Any SQL the engine runs can be the body, written with sql. Refs render as columns, and helpers can sit inside the same sql:

import { deleteFrom, sql } from 'uql-orm';

@Entity({
  triggers: (_post, trigger) => [
    trigger({
      on: 'afterDelete',
      run: (_newRow, oldRow) =>
        sql`${deleteFrom(PostAudit, { $where: { postId: oldRow.id } })}
            DELETE FROM post_cache;`,
    }),
  ],
})
export class Post {
  @Field(f.number().autoIncrement()) id?: number;
  @Field(f.string().nullable()) status?: string | null;
  @Field(f.number().nullable()) views?: number | null;
}

uql does not translate your SQL. It must run on every engine you use, or give a body per engine.

Refusing a write: refuse

refuse(message) fails the write that fired the trigger, on every engine. It keeps a table append-only or, with a where, refuses only some rows:

import { refuse } from 'uql-orm';

@Entity({
  triggers: (_entry, trigger) => [
    trigger({
      on: 'beforeDelete',
      run: () => refuse('ledger entries are kept'),
    }),
    trigger({
      on: 'beforeUpdate',
      where: { $old: { posted: true } },
      run: () => refuse('a posted entry is final'),
    }),
  ],
})
export class LedgerEntry {
  @Field(f.number().autoIncrement()) id?: number;
  @Field(f.number()) transactionId!: number;
  @Field(f.number()) amount!: number;
  @Field(f.boolean()) posted!: boolean;
}

SQL Server has no before* events. Use afterUpdate and afterDelete there; they still roll the statement back.

Deferred to commit: deferred

On Postgres, deferred: true fires an after* trigger at commit instead of after each statement, so a check sees every write in the transaction. A ledger whose entries must sum to zero can then take them in any order. Other engines refuse it.

import { refuse, sql } from 'uql-orm';

@Entity({
  triggers: (_entry, trigger) => [
    trigger({
      on: 'afterInsert',
      deferred: true,
      run: {
        postgres: (newRow) =>
          sql`IF (SELECT SUM(amount) FROM ledger_entry WHERE transaction_id = ${newRow.transactionId}) <> 0 THEN
                ${refuse('a ledger transaction must balance')}
              END IF;`,
      },
    }),
  ],
})
export class LedgerEntry {
  @Field(f.number().autoIncrement()) id?: number;
  @Field(f.number()) transactionId!: number;
  @Field(f.number()) amount!: number;
  @Field(f.boolean()) posted!: boolean;
}

Sync and migrations

sync and generate:entities install the triggers an entity declares and drop the ones it no longer declares. They leave an unchanged trigger alone and replace an edited one, so a second sync runs nothing. A trigger whose name does not start with _uql_ is never touched.

You can retype, rename or drop a column a trigger names. Postgres refuses a retype or drop while the trigger exists, so uql drops the trigger and restores it around the change. A trigger naming none of the changed columns stays in place.

Naming

The installed name is _uql_, the table, your name and a hash of the SQL: name: 'audit' installs as _uql_Post__audit_0a57e9 on Postgres. Without a name, the label is the event and the trigger’s position, such as afterUpdate_0.

Engine notes

MySQL

With binary logging on (the MySQL default), a user without SUPER cannot create triggers unless you set log_bin_trust_function_creators = 1, in the parameter group on managed MySQL. MariaDB needs nothing.

SQL Server

SQL Server fires a trigger once per statement, with inserted and deleted tables instead of single rows, and has no before* events. An update pairs the two tables by primary key, so a statement that changes a row’s key leaves that row out of of, where and the body.

The write helpers work there unchanged. Your own SQL reads inserted and deleted whole, so it is refused alongside of or where. To support SQL Server and another engine, give a body per engine:

@Entity({
  triggers: (_post, trigger) => [
    trigger({
      on: 'afterInsert',
      run: {
        postgres: (newRow) =>
          sql`PERFORM pg_notify('post_status', ${newRow.id}::text);`,
        mssql: () =>
          sql`INSERT INTO post_outbox (post_id) SELECT id FROM inserted;`,
      },
    }),
  ],
})
export class Post {
  @Field(f.number().autoIncrement()) id?: number;
  @Field(f.string().nullable()) status?: string | null;
  @Field(f.number().nullable()) views?: number | null;
}

postgres also covers CockroachDB, and mysql covers MariaDB. A map missing the engine in use is refused at sync.

MongoDB

MongoDB has no trigger that runs within a write, so it refuses writes to an entity that declares one, a stamp included. Atlas Database Triggers run after the commit, so they cannot block, roll back with or stamp a write. For logic that needs to follow only uql’s own writes, use a lifecycle hook.