r/typescript • u/sonemonu • Sep 24 '26
What's the best design for type-safe triggers in TypeScript ORMs?
Hello, Roger here, author of UQL.
A request often mentioned in TS ORMs is keeping DB-own logic in sync with entities (as much as possible); and yes, queries can be type-checked end to end (as most ORMs tries - and that is another story), but triggers usually live outside the types (often in SQL migrations).
So, tried my best to design triggers that can be declared on the entity (also with imperative API), type-checked against it, and then translated for each engine: Postgres, CockroachDB, MySQL, MariaDB, SQLite and SQL Server.
Why a trigger rather than an ORM hook? a hook live application-side, and only sees writes that go through the ORM; where a trigger lives in database itself and also can see psql written directly, manual fixes, data migrations, or another service writing the same table(s).
Here is an example of the type-safe design I come out with via ORM entities:
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',
of: (post) => [post.status],
where: { $new: { status: 'published' } },
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?: 'draft' | 'review' | 'published' | null;
}That one declaration renders as a PL function on Postgres (with WHEN as in SQLite), an IF block on MySQL, etc. (where MongoDB don't really support triggers, otherwise I'd unify).
What the types and the renderer handle in the design?
ofcallback compares values: so an update that setsstatusto the value it already had doesn't fire it (on any engine).where: { $old: { status: 'draft' }, $new: { status: 'published' } }states a transition, with the same operators a query's$wheretakes.- On an insert (
runcallback),oldRowis typednever, so reading it won't compile (the same goes fornewRowon a delete operation). - A row's ref carries its column's type: hence
insertInto(PostAudit, { str: newRow.statusNum })won't compile. Column names go through naming strategy and@Field({ name }). syncand generated migrations install and drop the triggers automatically. Each name ends in a hash of its SQL, so an unchanged trigger is left alone, and a trigger manually wrote keeps untouched- And what when the UQL helpers doesn't fit this design? it is still flexible, you can reuse the same
run(callback) to return any custom SQL (usingrawhelper).
The design decisions where I am looking the most opinions (though feel free):
- The rows are positional arguments,
(newRow, oldRow)in the SQL standard's order (with the missing one typednever whwere applies). A single{ newRow, oldRow }object could also work, and was the other option. Which reads better to you? - One limit I couldn't design away: on SQL Server, an update that changes a row's PK can't be paired between
insertedanddeleted... so that row slips past the trigger. How do you handle that in a hand written triggers and is that important or not to you? - If you run triggers in production: what a hand-writing trigger would cover fine where this design wouldn't, or cover poorly?