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.

[gitsult4n]

Half of this already works

RLS policies live in the schema today

import { sql } from 'drizzle-orm';
import { pgPolicy, pgTable, uuid } from 'drizzle-orm/pg-core';

export const posts = pgTable('posts', {
  id: uuid().primaryKey(),
  ownerId: uuid().notNull(),
}, (t) => [
  pgPolicy('owner reads own posts', {
    for: 'select',
    to: 'authenticated',
    using: sql`${t.ownerId} = current_setting('app.user_id')::uuid`,
  }),
]).enableRLS();

generate diffs those like any other object

Functions and triggers have no schema level API so the supported path stays custom migrations

drizzle-kit generate --custom --name=touch_updated_at

The file lands in the same journal and runs in order with everything else

What kills most of the churn at your scale
keep one sql file per function under db/functions and have the custom migration read and execute it
the review diff is then on the function file and not on the 40th copy of CREATE OR REPLACE FUNCTION
a small script can also hash each function file and only emit a migration when the hash moves