[Discussion] Functions and triggers as code

@productdevbook

Hello ! This post is meant to discuss and debate rather than just implementing some feature.

When I work with ORMs like Drizzle, most of my DB structure is in my codebase (schemas, tables, enums, queries), but there's still a part of the SQL work that doesn't live in my DB, and is tedious to deploy, which is bad for git and versioning in general. I'm referring to SQL functions and triggers, and eventually RLS security.

I get that the original goal of ORMs is to make queries type safe, but achieving this turned most backend codebases as the single source of truth to deploy the app (frontend, backend and db), but when it gets complicated (use of functions, trigger, rls) you either have to do manual deployment steps, or add scripts during deployment to make sure the functions that your whole infra depends on, are really there.

I'm questioning if ORMs should become a "typesafe way to write db infrastructure" rather than just tables and queries. I'm not asking for implementation because, obviously this would result in a huge amount of work, but I think it would improve a lot how we think and interact with our DBs.

On a side note, that's kinda what Convex does, you can define triggers and RLS in code, and their "query as endpoint callable from the client" concept kinda replaces the need for SQL functions altogether.

This SQL Code:

CREATE OR REPLACE FUNCTION "compute_taxes"(
  "price" numeric,
  "tax" numeric DEFAULT 0.20
) 
RETURNS numeric 
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN ("price" * (1 + "tax"));
END;
$$;

Would look like this:

export const computeTaxes = pgFunction("compute_taxes")
  .args({
    price: numeric("price").notNull(),
    tax: numeric("tax").default(0.20)
  })
  .returns(numeric())
  .as((args, db) => 
    db
    .begin()
    .return(
      mul(args.price, add(1, args.tax))
    )
    .end()
  );

In my opinion, this would solve a lot of things, we could have a proper understanding of what a sql function takes as input and returns as output, and therefore use it in queries without template literals.

What do you think about that, @productdevbook ?

I fully understand your intention to codify database functions, triggers, and RLS entirely in code and bring them under version control. This approach would indeed solve the problems of scattered SQL scripts and fragmented deployment workflows.
That said, I feel this line of thinking is somewhat idealistic. While focusing on unified code management and type safety, it overlooks the practical realities of native database tooling, DBA collaboration, debugging efficiency, and cross-environment migration costs.
ORMs are fundamentally designed for modeling schemas and building type‑safe queries. Over-extending their scope to fully abstract low-level database infrastructure leads to excessive indirection, raising long-term maintenance overhead, onboarding complexity, and production troubleshooting difficulty.

Hey, sorry for the late response. I generally agree with what you said, even though I have less experience than you.

But I disagree about the overhead. Any piece of new tech, and abstraction in general, introduces some kind of overhead. We just measure if that overhead is worth it.

In my opinion, integrating db functions, triggers and RLS into the migration tooling that sumak provides (or the journal if we're taking the drizzle's vocabulary) would provide typesafety, deterministic behaviors (by ensuring the functions, triggers, and RLS are applied when we deploy the whole infra for the first time.

I think it would be hard to implement the whole set of keywords and syntax of plpgsql, but at the same time that's not really what I'm suggesting. I'm suggesting to partially implement it, with guardrails so that it stays simple and easy to read as code.

I appreciate the feedback by pointing out the issues in the proposal, let's think about how to address them instead of turning down the idea all together... I think it has a lot of potential

Hello ! Just here to ask wether sumak's development continues, and what is planned next :) have a nice week

Before I can move forward, I need to get my finances in order, and I’m actively looking for a job. I hope I can find one soon. I really want to come back too. :/

Good luck in that regard ! I'm looking for one, too, and it's very hard nowadays. You're very talented, I'm sure you'll be able to find one, it's a matter of time. Good luck mate.

docs(adr): ADR 005 — functions and triggers as typed code (#91) (#190)

feat(ddl): typed CREATE FUNCTION / CREATE TRIGGER for PG (Phase 1, #91)…

@deslunes

Hey @deslunes, @notwouf, @Markxuxiao — picking this thread back up. The framing here has been good and I wanted to ship a real response rather than another "we'll think about it."

Status

RLS already landed in #172 (CREATE / DROP POLICY + ALTER TABLE RLS toggles) and #179 (ALTER POLICY rename + modify). One of the three pieces from your proposal is done.

Functions and triggers — Phase 1 just shipped:

ADR 005 — docs/adr/005-functions-and-triggers.md — frames the whole question. (docs(adr): ADR 005 — functions and triggers as typed code (#91) #190)
Phase 1 implementation — typed CREATE FUNCTION with typed expression body and call-site inference, plus full CREATE TRIGGER DDL. PG-only for the first cut. (feat(ddl): typed CREATE FUNCTION / CREATE TRIGGER for PG (Phase 1, #91) #191)

What Phase 1 looks like in code

Your original example, almost verbatim:

const computeTaxes = db.schema
  .createFunction("compute_taxes")
  .args({
    price: arg("numeric"),
    tax:   arg("numeric", { default: val(0.20) }),
  })
  .returns("numeric")
  .languageSql()
  .body(({ price, tax }) => mul(price, add(val(1), tax)))
  .build()

// CREATE FUNCTION "compute_taxes"("price" numeric, "tax" numeric DEFAULT 0.2)
//   RETURNS numeric LANGUAGE sql AS $$ SELECT ("price" * (1 + "tax")) $$;

// And the payoff — typed call site:
db.selectFrom("products").select({
  withTax: computeTaxes.call({ price: typedCol("price"), tax: val(0.18) }),
})
// SELECT compute_taxes("price", 0.18) AS "withTax" FROM "products"

computeTaxes.call({...}) returns Expression<number> — the same type the body callback's price / tax proxies carry. Function bodies that call other typed functions stay end-to-end typed. No template literals at the call site.

Triggers are full-grammar (BEFORE / AFTER / INSTEAD OF, multi-event INSERT OR UPDATE, UPDATE OF cols, FOR EACH ROW / STATEMENT, optional WHEN(...), deferrable constraint triggers), and they reference the function by name.

What's deferred to Phase 2

The procedural plpgsql surface — IF / LOOP / RAISE EXCEPTION / variable declarations / NEW / OLD / TG_OP magic variables. ADR 005's "Phase 2 sketch" section lays out the AST shape (StatementBlockNode, IfStatementNode, LoopStatementNode, …), the plpgsql printer plan, and how the magic variables become typed proxies. The AST is shaped so Phase 2 is purely additive — Phase 1 callsites stay valid.

On @Markxuxiao's pushback

Fair concern. The ADR's response is the phasing itself: Phase 1 is the guardrail. The builder simply doesn't surface .if(...), .loop(...), .raise(...) yet — for procedural functions you still write raw SQL today and migrate to the typed surface when Phase 2 lands. We're not over-extending sumak into "all of plpgsql" in one move; we're committing to typed expression-body functions (which is most compute_* / format_* / validate_* helpers in practice) and reserving the harder part for a separate, deliberate cycle.

Status of this issue

I'm keeping this issue open as the umbrella for the schema-as-code direction. Phase 1 lands a concrete first step; happy to discuss the Phase 2 AST shape here before someone (me or a contributor) starts on it. The ADR has the design surface for that conversation.

Thanks for the patient framing on this one — it gave the implementation a clearer shape than starting from a blank page.

feat(builder): a write accepts an expression where a value goes

fix: three correctness defects, TypeScript 7, plpgsql bodies for #91,…

Navigation Menu

Repository navigation

[Discussion] Functions and triggers as code #91

Status

What Phase 1 looks like in code

What's deferred to Phase 2

On @Markxuxiao's pushback

Status of this issue

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

[Discussion] Functions and triggers as code #91

Description

Activity

Markxuxiao commented on Apr 25, 2026

deslunes commented on Apr 27, 2026

notwouf commented on May 17, 2026

productdevbook commented on May 18, 2026

notwouf commented on May 18, 2026

productdevbook commented on May 20, 2026

Status

What Phase 1 looks like in code

What's deferred to Phase 2

On @Markxuxiao's pushback

Status of this issue

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions