[Discussion] Functions and triggers as code

[deslunes]

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 ?

[Markxuxiao]

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.

[deslunes]

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