Postgres functions + triggers in the schema

[tyrauber]

Postgres is great. Drizzle is great. You know what would make both better?

Defining Postgres primitives in the Drizzle schema.

We run a production app on Drizzle + Postgres: 157 functions (most of them SECURITY DEFINER), a wall of RLS policies, triggers wiring it together. All of it lives in 60 hand-written migration files, because Drizzle can't see any of it. Half those files are just CREATE OR REPLACE FUNCTION with an edited body. Every change is a manual migration.

It would be rad if we could write the function in the schema, change the body, run generate, and be done. Same loop we already get for tables.

I was curious, pointed Opus at it, and we did a first pass, unsolicited, to see if the shape feels right. pgFunction and pgTrigger as first-class entities, on the v1 engine, through the whole pipeline: generate, push, and pull. A test suite the size of the policy suite, round-tripped against a real database.

https://github.com/tyrauber/drizzle-orm/tree/pg-functions-triggers-v1

What it looks like

const api = pgSchema('api');

export const createContent = api.function('create_content', {
  args: { payload: jsonb().$type<{ name: string }>(), revision: integer().default(1) },
  returns: contents,                 // a table ref → InferSelectModel on the way out
}).securityDefiner({ searchPath: '' })
  .stable()
  .as(sql`BEGIN ... END;`);

Triggers stand alone or colocate in the table's extras callback, the way policies do:

export const touch = pgFunction('touch', { returns: 'trigger' })
  .as(sql`BEGIN NEW.updated_at = now(); RETURN NEW; END;`);

pgTable('contents', { /* ... */ }, (t) => [
  pgTrigger('contents_touch')
    .before(updateOf(t.name))
    .forEachRow()
    .when(sql`OLD.name IS DISTINCT FROM NEW.name`)
    .execute(touch),
]);

Args are columns

Function args and returns reuse the column builders. pgEnum, .array(), customType, .$type<T>(), defaults: all of it works as-is, no new type vocabulary. InferFunctionArgs and InferFunctionReturns come for free, so you can type RPC off the schema.

The limit: a column builder has no way to say OUT, INOUT, or VARIADIC, so every arg is IN for now. Covers most functions, not all. This is the call I'd most want your read on. Right approach, or do you want an arg type that carries a mode?

How the diffing behaves

Identity is the full signature, schema.name(argtypes). Overloads coexist. Change the signature and it's a drop + create.

A body or flag edit is one CREATE OR REPLACE. The OID holds, so dependent triggers stay put. A return-type change is the one case Postgres forces to drop + create; dependent triggers drop before and recreate after, in order.

Functions create before tables, with SET check_function_bodies = off up front (pg_dump does the same), so a sql-language body that references a not-yet-created table still applies.

A trigger's WHEN can't be compared as text on pull, because Postgres rewrites tgqual. We hash the source text into a COMMENT ON TRIGGER 'drizzle:when:<sha256>' and compare hashes: changed hash is a real edit, matching hash is just Postgres normalizing. This is the piece I'm least sure about. It writes Drizzle metadata into the database. If there's a cleaner way, I want to hear it.

Scope

First pass: functions and triggers. Out for now, easy to follow up: constraint and deferrable triggers, transition tables, OUT/VARIADIC args, cross-schema moves, grants, extensions.

One limit I'd rather name than hide: push can't track an arg-default edit, because Postgres won't hand back per-arg defaults through introspection. So push warns and sends you to generate, which catches them. Trigger WHEN edits work the same way, detected through the hash above.

What I'm asking

A review, thoughts, guidance.

Purposely a discussion, not a PR.

No pressure. Mostly I just think Postgres-in-the-schema would be rad and wanted to put a real shape in front of you.

Thanks for the awesome library. This was fun.