Open your schema.ts. It looks like your whole database: every table, every column, every index, typed and diffed.
Now go look at your migrations folder.
Somewhere in there is a file like 0007_custom.sql. It has the updated_at trigger. Maybe a CREATE EXTENSION pgcrypto. Maybe the column comments your data team asked for six months ago. Someone wrote it by hand with drizzle-kit generate --custom, and it's the only place any of that exists.
Your schema doesn't know about it. Your teammates don't either, until something breaks. And when a table gets dropped and recreated, Postgres drops its triggers with it, quietly.
If that sounds familiar, you're in big company. "Let Drizzle declare triggers" is one of the most-reacted open issues on the Drizzle repo, and the requests for comments, extensions and partitions aren't far behind:
| What you want | Issue |
|---|---|
CREATE FUNCTION / CREATE TRIGGER
|
#843 |
COMMENT ON tables and columns |
#886 |
CREATE EXTENSION |
#2124 |
| Partitioned tables | #2854 |
| TimescaleDB hypertables | #2962 |
On 10 Nov, the first four had over 1,000 reactions between them.
So I built drizzle-pg-extras, and 0.5.0 is on npm today.
Before and after
Here's what the updated_at case looks like today, by hand:
-- 0007_custom.sql (hope someone remembers this exists)
CREATE OR REPLACE FUNCTION set_users_updated_at() RETURNS trigger AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER users_set_updated_at
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION set_users_updated_at();
COMMENT ON TABLE users IS 'Application users';
COMMENT ON COLUMN users.email IS 'Login email';
CREATE EXTENSION IF NOT EXISTS pgcrypto;
And here it is with drizzle-pg-extras, sitting right next to the table it belongs to:
export const pgcrypto = pgExtension("pgcrypto");
export const users = withComment(pgTable("users", {
id: serial("id").primaryKey(),
email: withComment(text("email").notNull(), "Login email"),
updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
}), "Application users");
export const touchUsers = updatedAtTrigger(users, users.updatedAt);
Then:
drizzle-kit generate && drizzle-pg-extras generate && drizzle-kit migrate
That's it. The trigger catches every update, including the ones that never touch Drizzle: a psql session, a backfill script, another service. .$onUpdate() can't see those, and a trigger does.
Why it doesn't fight drizzle-kit
This was the part I cared about most. drizzle-pg-extras doesn't patch Drizzle and never edits a file drizzle-kit wrote. It appends a normal SQL migration, and drizzle-kit migrate applies it like any other.
It also keeps its own snapshot, the same way drizzle-kit does for tables. So:
- run
generateagain with no changes → nothing is written - change one trigger's
WHENclause → you get a migration with only that change - the next
drizzle-kit generatestill reports no schema changes -
drizzle-pg-extras checkfails CI when someone edits a definition and forgets to generate
And if Drizzle ships all of this natively next month? Your committed SQL still works. You delete one dev dependency and move on. No lock-in.
What else it covers
If you've ever...
-
...needed a real trigger, not just
updated_at:pgFunctionandpgTriggerdoUPDATE OFcolumns, a type-checkedWHEN, constraint and deferrable triggers, transition tables andINSTEAD OFon views. -
...wanted comments to show up in your DB tool:
withCommentworks on tables, columns, enums, views, functions, indexes and constraints. -
...forgotten to
CREATE EXTENSIONbefore a migration needed it:pgExtension("vector", { schema: "extensions" }). -
...wanted a partitioned table without leaving Drizzle:
partitionedTableandpgPartitionfor range, list and hash. -
...run TimescaleDB:
hypertable()with compression, retention and compression policies. Tested on TimescaleDB 2.32. It's a community package, not official Timescale.
export const metricsHypertable = hypertable(metrics, {
timeColumn: metrics.time,
chunkTimeInterval: "1 day",
retention: { dropAfter: "6 months" },
compressionPolicy: { compressAfter: "7 days" },
compression: { segmentBy: [metrics.deviceId] },
});
The fine print (read this before you install)
I'd rather you hear it from me than from your staging database:
- Partitioning only converts an empty table. On a fresh migrate that's the normal case. On a table with data, it refuses rather than moving your rows quietly.
-
drizzle-kit pushdoesn't read any of this. Generate, then migrate. If you push,renderIdempotent()gives you a script to run afterwards. - Postgres 14+, Node 18+, drizzle-kit 0.31's migration layout. The 1.0 rc layout is best-effort.
- No RLS or views. drizzle-kit already handles those.
The full "what it does not do yet" list is in the README, and it's long on purpose.
Smaller packages already solve single pieces of this: drizzle-triggers, @onozaty/drizzle-db-comments-generator, drizzle-partition. If you only need one thing, go look at them too. This is for when you want all of it in one schema with one snapshot.
Try it on your schema
npm install drizzle-pg-extras
Pick the one custom migration you'd most like to delete, declare it next to its table, and run generate. Compare the SQL it writes with yours.
It's MIT and brand new. You'd be one of the first people to run it on a schema that isn't mine, and that's exactly what I need. If it breaks, open an issue with the definition and the migration it wrote, and I'll fix it. If it saves you a custom migration, a ⭐ on the repo helps the next person find it.
Your schema should know about your whole database. Now it can.
Top comments (0)