Declare the Postgres objects Drizzle still can't model — functions, triggers, comments, extensions, partitioned tables, and TimescaleDB hypertables — in TypeScript next to your Drizzle tables, and turn them into ordinary SQL migration files.
drizzle-kit migrate applies those files. This package does not patch Drizzle. If Drizzle later generates the same objects itself, the SQL you already committed stays valid.
These are open Drizzle requests, and the v1.0.0-rc.5 notes don't add them. PR #4844 is still unmerged.
| Gap | Request |
|---|---|
CREATE FUNCTION / CREATE TRIGGER |
drizzle-orm#843 |
COMMENT ON tables, columns, enums, views, routines, indexes, constraints, extensions |
drizzle-orm#886 |
CREATE EXTENSION |
drizzle-orm#2124 |
| Partitioned tables | drizzle-orm#2854 |
| TimescaleDB hypertables | drizzle-orm#2962 |
Smaller packages already cover one of those pieces each: drizzle-triggers, @onozaty/drizzle-db-comments-generator, and drizzle-partition. This package is the Postgres slice people keep asking to put in one schema: functions, triggers, comments, extensions, partitions, and TimescaleDB hypertables. drizzle-partition rewrites the CREATE TABLE drizzle-kit already wrote. This package leaves that file alone and appends a migration. See Partitioning and TimescaleDB.
import { pgTable, serial, text, timestamp } from "drizzle-orm/pg-core";
import { pgExtension, updatedAtTrigger, withComment } from "drizzle-pg-extras";
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);drizzle-kit generate && drizzle-pg-extras generate && drizzle-kit migrateupdatedAtTrigger is the case from issue #843: a BEFORE UPDATE trigger that sets the column to now() for every update, including ones that never go through Drizzle. A hand-written function looks like this:
export const setUpdatedAt = pgFunction("set_updated_at", {
returns: "trigger",
language: "plpgsql",
body: "BEGIN NEW.updated_at = now(); RETURN NEW; END;",
});
export const touchUsers = pgTrigger("users_set_updated_at", {
table: users,
timing: "before",
events: ["update"],
columns: [users.email], // UPDATE OF, and the column must belong to `users`
when: ({ OLD, NEW }) => sql`${OLD.email} IS DISTINCT FROM ${NEW.email}`,
function: setUpdatedAt,
});columns is UPDATE OF. when is rendered to text and scanned when pgTrigger runs. Triggers lists what that scan rejects and what is left for Postgres. Constraint triggers, transition tables, INSTEAD OF on a view, and enabled: false are in that section too.
The first generate writes a migration. The next one writes nothing until a definition changes. Only the difference is emitted, and the statements themselves are idempotent (CREATE OR REPLACE, IF NOT EXISTS, DROP … IF EXISTS).
npm install drizzle-pg-extrasPeer dependency: drizzle-orm >=0.36.4, including 1.0 (tested with 0.45 and 1.0.0-rc.5). drizzle-kit >=0.31.0 or 1.0 is optional and only needed to apply migrations. Node.js 18 or newer.
Put the definitions in the same files your drizzle.config.ts schema already points at, and export them. The CLI reads that config (dialect, schema, out, casing, breakpoints, migrations.prefix).
{
"scripts": {
"db:generate": "drizzle-kit generate && drizzle-pg-extras generate",
"db:check": "drizzle-pg-extras check",
"db:migrate": "drizzle-kit migrate"
}
}drizzle-pg-extras check exits 1 when a definition changed and no migration was written. Use it in CI.
Run drizzle-kit generate first when a trigger or comment targets a table. The table migration has to land above the trigger migration in the journal. Extensions that a column type depends on (PostGIS, for example) are the opposite: that CREATE EXTENSION has to run before the table migration. On a new project, generate the extension migration before the first drizzle-kit generate, then add triggers and generate again. This package only appends migrations. It will not insert one in front of a migration you have already committed.
For the stable drizzle-kit 0.31 layout:
drizzle/
0001_pg_extras.sql applied by drizzle-kit migrate
0001_pg_extras.down.sql not applied by drizzle-kit
_pg-extras.json full extras snapshot, commit this
meta/_journal.json one new entry, same shape kit writes
meta/0001_snapshot.json copy of the previous kit snapshot
The kit snapshot is copied the way drizzle-kit generate --custom copies it (new id, prevId pointing at the previous snapshot, schema body unchanged). A later drizzle-kit generate still diffs your tables, not these extras. _pg-extras.json sits outside meta/ so kit does not try to parse it as a schema snapshot.
Up SQL is split on --> statement-breakpoint, which is what drizzle-kit migrate and migrate() from drizzle-orm expect.
Down SQL is the reverse diff: restore the previous function body, drop a trigger that was added, COMMENT … IS NULL for a comment that was removed. drizzle-kit migrate does not run .down.sql, and applying it does not delete the row in __drizzle_migrations. To roll back, run the down file yourself, then remove that journal entry and the matching migration row if kit's history should forget it too.
With drizzle-kit 1.0, migrations are one folder each (a timestamp directory with migration.sql). generate writes the same shape: a new directory with migration.sql, down.sql, and a snapshot.json that is an empty diff chained to the previous snapshot (type: "diff", prevIds pointing at it), which is what drizzle-kit generate --custom writes. The directory always sorts after the last one, so kit never sees two base snapshots. An empty migrations folder gets the layout of the drizzle-kit installed in your project. Both layouts are covered by the test suite, against drizzle-kit 0.31 and 1.0.0-rc.5, including drizzle-kit migrate on Postgres.
drizzle/
20261118090000_users/ written by drizzle-kit generate
20261118090100_pg_extras/
migration.sql applied by drizzle-kit migrate
down.sql not applied by drizzle-kit
snapshot.json empty diff, prevIds -> the users snapshot
_pg-extras.json full extras snapshot, commit this
import { defineExtras, generate, pgExtension, pgFunction, pgTrigger, renderIdempotent } from "drizzle-pg-extras";
const extras = defineExtras({
extensions: [pgExtension("vector", { schema: "extensions" })],
functions: [setUpdatedAt],
triggers: [touchUsers],
tables: [users], // picks up withComment() on the table and its columns
});
generate({ extras, out: "./drizzle", name: "touch_updated_at" });
// Full idempotent script. See "drizzle-kit push" below.
console.log(renderIdempotent(extras));| Export | Role |
|---|---|
pgFunction(name, config) |
plpgsql or sql. Args are IN only. returns is a SQL type string or a Drizzle column (users.id). searchPath: [] emits SET search_path TO ''. |
pgTrigger(name, config) |
Table, pgView(), or { name, schema }. columns is UPDATE OF. when is SQL or ({ OLD, NEW }) => sql\…`. constraint, referencing, and enabled: false` are in Triggers. |
updatedAtTrigger(table, column) |
BEFORE UPDATE trigger. column must be date, time, or timestamp. Tables that share a database column name share one function, set_<column>. |
pgExtension(name, { schema, version, cascade }) |
CREATE EXTENSION IF NOT EXISTS, optionally WITH SCHEMA and VERSION. |
withComment(target, text) |
Table, column builder, enum, finished view or materialized view, pgFunction, trigger, extension, or an index / unique / check / primaryKey / foreignKey builder. Returns the same object. Does not change Drizzle's SQL. |
commentOn(target, text) |
Same COMMENT ON output for a table, column, enum, view, function, trigger, or extension you already hold. Indexes and constraints use withComment inside the table callback. |
partitionedTable(table, { method, columns }) |
Marks a drizzle-kit table as RANGE, LIST, or HASH. The migration converts the empty table. See Partitioning. |
pgPartition(name, { parent, ...bounds }) |
CREATE TABLE ... PARTITION OF. Bounds are from/to, in, modulus/remainder, or default: true. |
pgAttachPartition(table, { parent, ...bounds }) |
ALTER TABLE ... ATTACH PARTITION for a table drizzle-kit already created. |
pgDetachPartition(table, { parent }) |
ALTER TABLE ... DETACH PARTITION. The table is kept. |
hypertable(table, config) |
create_hypertable with if_not_exists, plus optional compression, a retention policy, and a compression policy. Requires pgExtension("timescaledb"). See TimescaleDB. |
defineExtras / collectExtras |
Bundle objects, or scan a module's exports (the CLI does the scan). |
generate / diffSnapshots / snapshotExtras |
Diff against the stored snapshot and write files. |
renderIdempotent |
Create-or-replace script for everything currently declared. No drops. |
casing from drizzle.config.ts is applied to key-named columns (updatedAt with snake_case becomes updated_at), same as drizzle-kit. Explicit database names are left alone.
A function's identity is schema.name(arg types). Two overloads can coexist. A body, volatility, security, or search_path change is CREATE OR REPLACE (the oid stays, so triggers are left in place). A return type or argument type change is DROP FUNCTION plus create, and triggers that call that function are dropped and created around it. Postgres will not CREATE OR REPLACE a new return type.
Comments that disappear become COMMENT … IS NULL. If the table, column, enum, view, index, or constraint also left the schema, the null comment is omitted: drizzle-kit's migration drops the comment with the object. The same omission applies when this package drops the function, trigger, or extension in the same file. Removing a column that a trigger's UPDATE OF or WHEN still references takes two generates: change the trigger first, then drop the column. Postgres will not drop the column while the trigger depends on it.
import { sql } from "drizzle-orm";
import { check, index, pgEnum, pgTable, pgView, serial, text, unique } from "drizzle-orm/pg-core";
import { pgFunction, withComment } from "drizzle-pg-extras";
export const role = withComment(pgEnum("user_role", ["admin", "member"]), "Account role");
export const users = pgTable("users", {
id: serial("id").primaryKey(),
email: text("email").notNull(),
role: role("role").notNull(),
}, (table) => [
withComment(index("users_email_idx").on(table.email), "Lookup by email"),
withComment(unique("users_email_key").on(table.email), "One email"),
withComment(check("users_email_check", sql`length(${table.email}) > 0`), "Email is not empty"),
]);
export const activeUsers = withComment(
pgView("active_users", { email: withComment(text("email"), "Visible email") }).as(sql`select email from users`),
"Users visible to the app",
);
export const setUpdatedAt = withComment(pgFunction("set_updated_at", {
returns: "trigger",
language: "plpgsql",
body: "BEGIN NEW.updated_at = now(); RETURN NEW; END;",
}), "Sets updated_at");COMMENT ON is idempotent. A removed comment is COMMENT … IS NULL. Run drizzle-kit generate before drizzle-pg-extras generate when the comment targets an enum, view, index, or constraint, so that object exists before the comment runs. A later drizzle-kit generate still reports no schema changes: the comment is not part of the kit snapshot.
These sit next to the Drizzle tables they name. profileEmails is a pgView. The functions return trigger.
export const rejectNegative = pgFunction("reject_negative", {
returns: "trigger",
language: "plpgsql",
body: `BEGIN
IF NEW.amount < 0 THEN
RAISE EXCEPTION 'negative balance';
END IF;
RETURN NEW;
END;`,
});
export const balancesAmount = pgTrigger("balances_amount", {
table: balances,
timing: "after",
events: ["insert", "update"],
columns: [balances.amount],
constraint: { deferrable: true, initially: "deferred", from: customers },
function: rejectNegative,
});
export const ledgerInserted = pgTrigger("ledger_inserted", {
table: ledger,
timing: "after",
events: ["insert"],
forEach: "statement",
referencing: { newTable: "inserted" },
function: countInserted,
});
export const profileEmailsInsert = pgTrigger("profile_emails_insert", {
table: profileEmails, // pgView(...)
timing: "instead of",
events: ["insert"],
function: insertProfile,
});
export const profilesGuard = pgTrigger("profiles_guard", {
table: profiles,
timing: "before",
events: ["delete"],
function: raiseAlways,
enabled: false, // ALTER TABLE ... DISABLE TRIGGER
});constraint: true is CREATE CONSTRAINT TRIGGER ... NOT DEFERRABLE. deferrable: true defaults to INITIALLY IMMEDIATE. initially: "deferred" requires deferrable: true. from is the referenced table in FROM. These are the deferrable trigger case from drizzle-orm#1429. SET CONSTRAINTS uses the trigger name. Postgres has no CREATE OR REPLACE for constraint triggers, so the first migration is DROP TRIGGER IF EXISTS plus CREATE, and a later change drops and creates again. A comment on that trigger is written again in the same migration.
referencing is REFERENCING OLD TABLE AS ... NEW TABLE AS .... The names are quoted identifiers. The trigger function reads them as tables (SELECT count(*) FROM inserted). oldTable is the before-image (UPDATE or DELETE). newTable is the after-image (INSERT or UPDATE). One event only. FOR EACH ROW and FOR EACH STATEMENT are both valid. The transition table is the whole statement either way.
enabled: false appends ALTER TABLE ... DISABLE TRIGGER. enabled: true after that emits ENABLE TRIGGER and leaves the definition in place. CREATE OR REPLACE turns a trigger back on, so a body change that stays disabled emits DISABLE again.
withComment on a trigger still emits COMMENT ON TRIGGER ... ON table. A normal CREATE OR REPLACE keeps that comment. A constraint trigger that is dropped and created gets the comment statement again.
when is a string or a sql fragment, or, on a Drizzle table, ({ OLD, NEW }) => sql\…`(the callback may return a string). TypeScript typesOLDandNEWas that table's columns. The callback inserts each column asOLD."name"orNEW."name". A string is used as written. A sqlfragment is rendered to text. Neither is compared to the table's columns, and the expression is not checked as a boolean.pgTriggerrenders the clause, omits an empty one, and strips a trailing;. It throws if a ;is still present, if the trigger isINSTEAD OF, or if a scan of the text finds the word SELECTorEXISTS, the word OLDorNEWon a statement trigger,OLDwhen the events includeINSERT, or NEWwhen the events includeDELETE. The scan skips comments, quoted strings, quoted identifiers, and dollar quotes, and it does not parse SQL. The snapshot render runs that scan again, with the same casing rules as the rest of the snapshot. The stored text is written as WHEN ( ... )`. Postgres reports any other error when the migration runs.
- Constraint triggers are
AFTERandFOR EACH ROWon a table.WHENon one is evaluated when the row changes, including when the trigger itself is deferred. UNIQUEand foreign keys are still drizzle-kit's. This package does not addDEFERRABLEto those table constraints.fromdoes not create a foreign key.- Transition tables are
AFTERtriggers on tables, with one ofINSERT,UPDATE, orDELETE. They cannot be combined withUPDATE OF,TRUNCATE, a constraint trigger, or a view. INSTEAD OFis a view trigger,FOR EACH ROW, withoutWHEN, withoutUPDATE OF, and withoutTRUNCATE.BEFOREandAFTERon a view areforEach: "statement". Materialized views cannot have triggers.WHENis the text scan above: the wordsSELECTorEXISTS,OLDorNEWon a statement trigger,OLDwhen the events includeINSERT, andNEWwhen the events includeDELETE.ALTER TABLE ... DISABLE TRIGGERdoes not work on a view, soenabled: falseis rejected for views and forINSTEAD OF.ENABLE REPLICAandENABLE ALWAYSare not emitted.- A
{ name, schema }relation skips the checks that need a Drizzle table or view. Postgres still rejects a view trigger that was declared as a table. - Row-level transition triggers on a partition are rejected by Postgres when the migration runs. Foreign tables are not detected through
{ name, schema }. - A view column is matched by view name and column name. Drizzle's view column alias does not carry the schema, so two views with the same name in different schemas are not told apart.
Postgres accepts PARTITION BY only on CREATE TABLE. There is no ALTER TABLE ... PARTITION BY. drizzle-kit has already written a normal CREATE TABLE by the time this package runs, and this package does not rewrite that file (that is what drizzle-partition does).
partitionedTable() takes the other road. You keep the table in the Drizzle schema, so drizzle-kit still owns columns, indexes, and foreign keys. generate appends a migration that rebuilds the table as partitioned when it is empty, and raises when it has any rows. Run drizzle-kit generate first, then drizzle-pg-extras generate, then drizzle-kit migrate, and load data after that. Doing it in one drizzle-kit migrate is the normal case: kit creates the empty table, then this migration converts it, before the application writes rows.
The tradeoff is that conversion is a rebuild, not a flag. It is safe on an empty table and refused on a table that already has data. A helper that emitted the parent CREATE TABLE itself would avoid the rebuild, but then the table would have to stay out of drizzle-kit or kit would create it a second time, and this package would have to own later column changes. Keeping the table in the Drizzle schema is the point of fitting the rest of this package.
import { integer, pgTable, serial, text, timestamp, primaryKey } from "drizzle-orm/pg-core";
import { partitionedTable, pgAttachPartition, pgPartition } from "drizzle-pg-extras";
export const events = pgTable("events", {
id: serial("id").notNull(),
createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
region: text("region").notNull(),
payload: text("payload").notNull(),
}, (t) => [
primaryKey({ columns: [t.id, t.createdAt] }),
]);
export const eventsByTime = partitionedTable(events, {
method: "range", // "list" or "hash"
columns: [events.createdAt],
});
export const events2024 = pgPartition("events_2024", {
parent: events,
from: ["2024-01-01"],
to: ["2025-01-01"],
});
export const eventsDefault = pgPartition("events_default", {
parent: events,
default: true,
});
export const eventsArchive = pgTable("events_archive", {
id: integer("id").notNull(),
createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
region: text("region").notNull(),
payload: text("payload").notNull(),
}, (t) => [
primaryKey({ columns: [t.id, t.createdAt] }),
]);
export const attachArchive = pgAttachPartition(eventsArchive, {
parent: events,
from: ["2020-01-01"],
to: ["2024-01-01"],
});
// Later, instead of attachArchive:
// export const detachArchive = pgDetachPartition(eventsArchive, { parent: events });pgPartition creates a child with CREATE TABLE ... PARTITION OF. List bounds use in: ["en", "en-US"] (a tuple per row when the key has several columns). Hash bounds use modulus and remainder. from: ["MINVALUE"] and to: ["MAXVALUE"] are the range keywords, not strings. Export partitionedTable() as well as the children. The CLI only sees what the schema module exports.
Every primary key and unique constraint on a Drizzle table has to include the partition columns. Postgres requires that, and partitionedTable() rejects the table when one does not. A { name, schema } parent skips that check because the columns are only strings.
Removing a pgPartition drops the child when the child is empty. onRemove: "detach" keeps the table and its rows instead. pgAttachPartition always detaches on remove, because drizzle-kit still owns that table. pgDetachPartition is the explicit form of that detach. Set onRemove when you declare the partition. It is written into _pg-extras.json the next time a migration is generated. Changing only onRemove does not write a migration file.
The statements are idempotent. Running the migration twice is a no-op when the parent is already partitioned the same way and the children are already attached. renderIdempotent emits the same create-or-attach script and does not drop partitions you removed from the schema.
The conversion copies columns, defaults, identity, indexes, checks, and column comments with CREATE TABLE ... (LIKE ... INCLUDING ALL), then puts back outgoing and incoming foreign keys, triggers, row level security policies, grants, the table comment, and ownership. serial sequences are reassigned with ALTER SEQUENCE ... OWNED BY before the old table is dropped, so nextval keeps working. Index and constraint names are renamed back to the names drizzle-kit recorded. A later drizzle-kit generate still diffs your tables and reports no schema changes.
- Empty parents only. A table that already has rows is not converted. Move those rows yourself.
- The partition key cannot be changed in place.
generatethrows. It also throws when a child bound changes. Recreating a partition would drop its rows, and this package will not do that quietly. Detach, copy, and attach in a migration you write. - Hash parents cannot have a
DEFAULTpartition. Postgres rejects it. - A table you attach has to match the parent already: column order,
NOT NULL, unique indexes, and check constraints. Postgres checks that atATTACHand the error is Postgres's. - Views and materialized views that depend on the parent are not rewritten. The migration raises and leaves the table in place. Drop the views first.
- No expression keys such as
PARTITION BY RANGE (date_trunc('month', created_at)), no sub-partitions, and no tablespaces. - No
DETACH PARTITION CONCURRENTLY.drizzle-kit migrateruns each migration in a transaction, andCONCURRENTLYcannot run there. drizzle-kit pushdoes not read these definitions. Partition children are real tables kit did not declare, so push can offer to drop them. The supported path is generate, then migrate. If you push anyway, exclude the children withtablesFilter.- A later drizzle-kit migration that drops and recreates the parent (some column type changes) drops the partitions with it. The extras snapshot already says the table is partitioned, so this package will not emit the conversion again. Repair that kit migration by hand.
- Down SQL drops created partitions when they are empty, detaches attached tables, and converts the parent back when it is empty. It raises when a created partition still has rows.
drizzle-kit migratedoes not run the down file.
This is a community package. It is not an official Timescale or Tiger Data tool, and it is not part of Drizzle. Nobody at Timescale reviewed this SQL. It generates the calls people asked for on drizzle-orm#2962: turn a normal table into a hypertable, then optionally compress it and add retention and compression policies.
The table stays in the Drizzle schema, so drizzle-kit still owns columns, indexes, and keys. generate appends SELECT create_hypertable(..., if_not_exists => TRUE). That is the same shape as the custom migration in the issue thread. Timescale's newer CREATE TABLE ... WITH (tsdb.hypertable) has to be on the CREATE TABLE itself, and this package does not rewrite the file drizzle-kit already wrote.
pgExtension("timescaledb") has to be exported from the schema. generate throws if it is missing, and it writes CREATE EXTENSION before create_hypertable when both are new. A hypertable does not need the extension before the table: drizzle-kit creates an ordinary table, then this migration converts it. Run drizzle-kit generate, then drizzle-pg-extras generate, then drizzle-kit migrate, and load data after that.
import { integer, pgTable, text, timestamp, primaryKey } from "drizzle-orm/pg-core";
import { hypertable, pgExtension } from "drizzle-pg-extras";
export const timescale = pgExtension("timescaledb");
export const metrics = pgTable("metrics", {
time: timestamp("time", { withTimezone: true }).notNull(),
deviceId: text("device_id").notNull(),
value: integer("value").notNull(),
}, (t) => [
primaryKey({ columns: [t.time, t.deviceId] }),
]);
export const metricsHypertable = hypertable(metrics, {
timeColumn: metrics.time,
chunkTimeInterval: "1 day", // omitted: Timescale's default is 7 days
partitioningColumn: metrics.deviceId, // hash dimension; requires numberPartitions
numberPartitions: 4,
migrateData: false, // true makes create_hypertable copy rows that are already in the table
compression: {
segmentBy: [metrics.deviceId],
orderBy: [{ column: metrics.time, direction: "desc", nulls: "last" }],
},
retention: { dropAfter: "6 months", scheduleInterval: "1 day" },
compressionPolicy: { compressAfter: "7 days" },
});chunkTimeInterval, dropAfter, compressAfter, and scheduleInterval are interval strings such as "7 days" or "1 day 12 hours". They are quoted into INTERVAL '...'. compression: {} enables compression with Timescale's defaults. A compression policy requires compression, because Timescale rejects the policy until compression is on.
Every primary key and unique index on a Drizzle table has to include the time column and the space column, when you set one. Timescale requires that, and hypertable() rejects the table when one does not. The time column has to be NOT NULL and a date, timestamp, or timestamptz. A { name, schema } table with string column names skips those checks.
The statements are idempotent. create_hypertable, add_retention_policy, and add_compression_policy all pass if_not_exists => TRUE, and the compression ALTER TABLE can run again. Running the migration twice is a no-op when the hypertable, settings, and policies are already in place. if_not_exists does not change an existing policy's interval, so a later edit removes the policy and adds it again. A later chunkTimeInterval emits set_chunk_time_interval. Adding partitioningColumn after the hypertable exists emits add_dimension. migrateData is read only when the hypertable is first created; changing it later does not write a migration.
A later drizzle-kit generate still diffs your tables and reports no schema changes. _pg-extras.json from 0.4.0 still loads. Hypertable keys are added the next time a migration is written.
- Continuous aggregates are out of scope. They are materialized views with
timescaledb.continuous, anddrizzle-kit generaterewrites views, which drops those options. Owning theSELECTwould also make this package responsible for later column changes. Keep the custom migration from the issue (CREATE MATERIALIZED VIEW ... WITH (timescaledb.continuous)andadd_continuous_aggregate_policy) and, if you query the view, declare it with Drizzle'smaterializedView(...).existing(). - The time column cannot be an integer. Integer time needs
set_integer_now_funcand integer chunk sizes, and the policies below are intervals. - The time column cannot be changed. An existing space partition cannot be removed or pointed at a different column or partition count.
generatethrows. Move the data in a migration you write. The down file for a migration that only added a space partition leaves that dimension in place. - Clearing
chunkTimeIntervalis not generated. Set the new interval. The down file for a migration that set an interval for the first time leaves that interval in place. - Removing
hypertable()while the Drizzle table stays is an error. This package will not pretend it turned the hypertable back into a regular table. Remove the table from the Drizzle schema in the same change anddrizzle-kitdrops it, or drop it in a custom migration. - Down SQL removes policies and turns compression off. It leaves the hypertable in place, and it does not
DROP EXTENSION timescaledbwhile that hypertable still exists, because the drop would fail.ALTER TABLE ... SET (timescaledb.compress = false)fails if a chunk is already compressed. Decompress those chunks yourself first.drizzle-kit migratedoes not run the down file. create_hypertableerrors withtable "..." is not emptyunlessmigrateData: true. Migrating data takes a lock and rewrites the rows into chunks.- Compression is emitted as
timescaledb.compress,timescaledb.compress_segmentby,timescaledb.compress_orderby, andadd_compression_policy. Current TimescaleDB still accepts those names and treats them as the columnstore. This package does not emittimescaledb.enable_columnstoreoradd_columnstore_policy. - No
by_range()/by_hash(), reorder policies, retentiondrop_created_before, compressioncompress_created_before, tablespaces, or sparse indexes. create_hypertablestill creates its default index on the time column. That index is not in the Drizzle schema.- Chunks live in
_timescaledb_internal, next to the_timescaledb_catalogand_timescaledb_configschemas.drizzle-kit pushanddrizzle-kit pullcan treat those as tables you forgot to declare.generatedoes not. The supported path is generate, then migrate. - A later drizzle-kit migration that drops and recreates the parent (some column type changes) drops the hypertable with it. The extras snapshot already says the table is a hypertable, so this package will not emit
create_hypertableagain. Repair that kit migration by hand. - A
{ name, schema }parent skips the NOT NULL, type, and unique-index checks.
drizzle-kit push does not read this package and does not run files in the migrations folder. It will not create functions, triggers, comments, extensions, partitions, or hypertables.
Kit 0.31 has no trigger diff, so push does not drop a trigger it never declared. A push that drops or recreates the table still drops that table's triggers and comments, because Postgres drops them with the table. Functions are not attached to the table and stay. Extensions are not created. An extension that installs its own tables (PostGIS) can show up as relations push wants to delete; set extensionsFilters: ["postgis"] in drizzle.config.ts for that case. Partition children show up the same way: they are tables in the database that are not in the Drizzle schema, and push can try to drop them. Timescale chunks and catalog tables show up the same way. Exclude those names with tablesFilter if you push, or don't push.
The supported path is drizzle-kit generate, then drizzle-pg-extras generate, then drizzle-kit migrate. If you use push for tables, apply the extras yourself with renderIdempotent (or by running the latest up file) after a push that recreated a table. See Known limitations.
generate and check diff the current declarations against _pg-extras.json. They do not read the database catalog. When drizzle-kit drops and recreates a table, Postgres drops the triggers and comments on that table. The snapshot still contains them, and the schema still declares them, so the next generate writes nothing for them. Functions are not attached to the table and stay. Partitions and hypertables are the same kind of gap; see Partition limits and Timescale limits.
renderIdempotent(extras) snapshots the declarations you pass and prints idempotent SQL for them. Functions and ordinary triggers are CREATE OR REPLACE. A constraint trigger is DROP TRIGGER IF EXISTS plus CREATE. enabled: false adds DISABLE TRIGGER. Comments are COMMENT ON. Extensions are CREATE EXTENSION IF NOT EXISTS. Partitions are the conversion, create, attach, and detach statements. Hypertables are create_hypertable with if_not_exists, plus the compression ALTER TABLE and policy calls with if_not_exists when those are set. It does not drop objects removed from the schema. Run that script once the table exists again. An up file only repeats its own diff, so the latest up file recreates a trigger only when that CREATE is in the file.
| drizzle-orm | >=0.36.4 and 1.0. Tested with 0.45.3 and 1.0.0-rc.5. Drizzle 0.45 and v1.0.0-rc.5 still do not generate functions, triggers, comments, extensions, partitions, or hypertables. |
| drizzle-kit | >=0.31.0 journal format. Tested with 0.31.11: migrate applies the file, and the next generate reports no schema changes. |
| drizzle-kit 1.0 rc | Writes the folder-per-migration layout with an empty diff snapshot, tested through drizzle-kit generate, check and migrate on 1.0.0-rc.5. A drizzle-kit 0.x journal folder still gets journal entries; upgrade it with drizzle-kit up before moving to 1.0. |
| PostgreSQL | 14 or newer (CREATE OR REPLACE TRIGGER). Tested on 16. |
| TimescaleDB | Hypertables, compression settings, retention policies, and compression policies. Tested with TimescaleDB 2.32 on PostgreSQL 16. CI uses timescale/timescaledb:latest-pg16. |
| Node.js | 18 or newer. ESM and CJS builds. |
- Row level security policies. drizzle-kit already generates policies. The partition conversion copies policies that already exist on a parent; it does not author new ones.
- Creating views and materialized views. drizzle-kit does that. This package comments them and can put triggers on a view. Timescale continuous aggregates are included in that, on purpose. See Timescale limits.
DEFERRABLEonUNIQUEand foreign keys. Deferrable constraint triggers are in Triggers.OUT,INOUT, andVARIADICfunction arguments. Function comments use theINargument types.- Comments on policies, sequences, rules, or individual enum labels.
- Comments on a column-level
.unique(),.primaryKey(), or.references(). Declareunique(),primaryKey(), orforeignKey()in the extra config and wrap that builder. - An index whose key is a SQL expression needs an explicit name before it can be commented. drizzle-kit requires that name too.
updatedAtTrigger's generated function has no comment of its own. Comment the trigger, or declare the function withpgFunctionand comment that.- Call
.as()or.existing()beforewithCommenton a view, and.on()beforewithCommenton an index or unique constraint. - Pulling the live database back into the snapshot. The diff is against
_pg-extras.json, notpg_catalog. See Known limitations. - MySQL, SQLite, and SingleStore.
- Creating the schema named in
WITH SCHEMA. The schema has to exist (drizzle-kit can create it). - Clearing an extension version pin, or moving an extension back to "no schema clause". Set the new version or schema explicitly.
ALTER EXTENSION … UPDATEstill depends on that extension shipping a downgrade path. - Editing migrations that have already been applied. Generate a new file.
drizzle-pg-extras generate [--config drizzle.config.ts] [--name touch_updated_at]
drizzle-pg-extras check [--config drizzle.config.ts]
npm install
npm test
npm run typecheck
npm run lintUnit tests cover SQL generation and the snapshot diff, including comments, trigger rules, partition bounds, and hypertable SQL. POSTGRES_URL points the Postgres integration tests at a real server. One runs drizzle-kit migrate, checks that the trigger fires, and that comments on the table, column, enum, view, function, trigger, index, constraint, and extension exist. Another checks a deferred constraint trigger, transition tables, INSTEAD OF on a view, UPDATE OF / WHEN, and DISABLE TRIGGER. The partition test checks range, list, and hash routing, attach, the empty-table guard, and the down file. TIMESCALE_URL points the Timescale test at a server with the extension. It checks create_hypertable, compression, both policies, a second generate that changes the chunk interval, the down file, and that drizzle-kit generate reports no schema changes. CI keeps the Postgres 16 job as it was and runs the Timescale test in a separate job on timescale/timescaledb:latest-pg16.
MIT