RFC: Postgres functions as first-class schema entities in drizzle-kit v1

[rutmus]

Summary

Postgres functions are the main schema object Drizzle still can't declare or diff. Teams write them by hand in drizzle-kit generate --custom migrations, so the TypeScript schema stops describing the database and generate / push / pull can't see them. This RFC consolidates the existing proposals and prototypes into one design for the v1 engine. It also lists the decisions only maintainers can make, so contributors stop building competing implementations.

Prior work

• Discussions: [FEATURE]: Support for PostgreSQL functions. #2586 (106 upvotes), Typesafe PostgreSQL stored procs / function calls #2434 ("on our roadmap", 2023), Postgres functions + triggers in the schema #5891 (functions + triggers)
• Draft: [Pg] Add support for procedural functions #4753: functions, built on the pre-v1 kit (snapshotsDiffer.ts / sqlgenerator.ts, which no longer exist on v1)
• feat: Initial Supabase Functions/Triggers implementation #4844: functions + triggers in drizzle-orm/supabase, ORM only
• feat(pg): add CREATE TRIGGER support #5373 / [FEATURE]: CREATE TRIGGER functions #843: triggers, ORM only
• [Pg] Add PostGreSQL function support #6467: pgFunction, ORM only, draft on beta
• v1-engine prototype with generate/push/pull: https://github.com/tyrauber/drizzle-orm/tree/pg-functions-triggers-v1 (linked from Postgres functions + triggers in the schema #5891, not opened as a PR)

None of these has had maintainer review yet, which is understandable during v1. This RFC aims to make that review cheaper.

Production experience

We manage Postgres functions in a production Drizzle project with a userland generator. Each function is a TS definition: a signature, its full CREATE OR REPLACE FUNCTION SQL, and its declared attributes. The generator compares each definition with the latest version found in migrations and writes only the changed functions into a generate --custom migration. Tests check that the installed pg_proc attributes match the declaration and that rerunning migrations changes nothing.

It works, but it's a workaround: it can't drop, rename or pull functions, isn't part of snapshots, and relies on comment markers in migration files. The edge cases in "Diff rules" below are what it taught us.

Proposed scope (first iteration)

1. Postgres functions (prokind = 'f') only: a declaration API in drizzle-orm/pg-core, plus a DDL entity, diffing and SQL generation in drizzle-kit, introspection and codegen for pull, and push support.
2. Triggers as a follow-up built on (1) ([FEATURE]: CREATE TRIGGER functions #843, feat(pg): add CREATE TRIGGER support #5373).
3. Procedures, aggregates and OUT / INOUT / VARIADIC arguments deferred.

API sketch (drizzle-orm)

Aligned with #6467 and the v1 prototype so existing work can be reused:

import { sql } from 'drizzle-orm';
import { integer, pgFunction, pgSchema, text } from 'drizzle-orm/pg-core';

export const normalizeEmail = pgFunction('normalize_email', {
	args: { email: text() },
	returns: text(),
	language: 'sql',
	volatility: 'immutable',
	strict: true,
	parallel: 'safe',
}).as(sql`SELECT lower(trim(email))`);

const api = pgSchema('api');

export const bumpCounter = api.function('bump_counter', {
	args: { id: integer(), by: integer().default(1) },
	returns: integer(),
	language: 'plpgsql',
	security: 'definer',
	set: { search_path: '' },
}).as(sql`BEGIN ... END;`);

Functions are exported from schema files like tables, views and enums, and picked up by the same collector. Calling a declared function in queries with typed args and return values can land separately from the kit work.

drizzle-kit design (v1 engine)

DDL entity. Add a functions entity to dialects/postgres/ddl.ts with these fields: schema, name, args ({ name, type, typeSchema, default }[], input args only), returns, language, volatility, strict, securityDefiner, leakproof, parallel, cost, rows, set (from proconfig) and body.

Identity. The v1 composite key is schema:table:name:entityType, but Postgres identifies a function by its name and its input argument types, because functions can be overloaded. So identity must include the normalized input types, as pg_get_function_identity_arguments reports them (int becomes integer, type modifiers are dropped, OUT args are excluded). The v1 prototype encodes the signature into name; a dedicated identity field may be cleaner.

Diff rules. What Postgres allows decides the statement:

Change	Statement
body, language, volatility, strict, security, leakproof, parallel, cost, rows, SET; adding a parameter default	CREATE OR REPLACE FUNCTION (keeps grants, ownership and dependent objects)
return type, input parameter name, removing a parameter default	DROP + CREATE, since Postgres rejects these in OR REPLACE. Emit a hint: dependent objects block the drop, and grants are lost
input argument types	New identity: create the new function and drop the old one, or offer a rename hint like tables do
name or schema	Rename/move hint, then ALTER FUNCTION … RENAME TO / SET SCHEMA
removed from the schema	DROP FUNCTION schema.name(argtypes)

Ordering. A function must come after the types it takes or returns, including table row types. It must come before the defaults, generated columns, checks, expression indexes, policies and triggers that call it. Emitting SET check_function_bodies = off before the creates, as pg_dump does, stops Postgres from validating the tables referenced inside sql bodies, which removes one side of the cycle. Drops run after their dependent objects are dropped.

Body comparison. String bodies are stored verbatim in pg_proc.prosrc, so comparing normalized text is reliable and lets push diff them. SQL-standard bodies (BEGIN ATOMIC / RETURN, Postgres 14+) are stored parsed in prosqlbody, with an empty prosrc. Either reject those in the first iteration, or compare them through pg_get_functiondef.

Introspection / pull. Read pg_proc joined with pg_namespace and pg_language, filtered by prokind = 'f' and the existing schema filters. Exclude extension-owned functions (pg_depend rows with deptype = 'e') so pull doesn't emit PostGIS or pgcrypto functions. Codegen emits pgFunction(...) with sql bodies.

Tests. Kit tests in drizzle-kit/tests/postgres on PGlite should cover:

• create, replace, drop, rename and move
• overloads
• each attribute change
• return-type change producing drop + create
• a second run producing no statements
• pull round-trip
• extension functions excluded from pull

ORM type tests should cover args and return types.

Questions for maintainers

1. Is this on the v1 roadmap, and is someone on the team already designing it? If so, we'd rather follow your design than add another implementation.
2. Which branch should community work target: rc5, beta, or wait for v1 stable? This is also unanswered in feat(pg): add CREATE TRIGGER support #5373.
3. Should identity live in name (signature-encoded) or in a dedicated field?
4. For return-type changes, is generated drop + create with a hint acceptable, or should generate refuse and require a manual migration?
5. Should functions and triggers be one PR, or functions first?
6. Should contributors build on [Pg] Add PostGreSQL function support #6467 (ORM) and the Postgres functions + triggers in the schema #5891 prototype (kit) rather than open new PRs?

We're happy to contribute test cases from production use, review, or implement a piece on whichever branch you point us to.