@bcap/drizzle-history

Automatic history tracking for Drizzle ORM tables on PostgreSQL.

@bcap/drizzle-history records every insert, update, and delete on a tracked table as a typed snapshot row in a companion history table, in the same transaction as the write itself. Wrap a table once at declaration time and wrap the database once at construction time; everything else is ordinary drizzle code. There are no database triggers, extensions, or background processes: history tables are plain pgTable definitions that drizzle-kit migrates and drizzle queries, and the capture mechanism is a proxy around the drizzle database object. The design is inspired by django-simple-history.

Features

• One-call setup: withHistory(table) derives a typed companion history table, and withHistory(db) records every query-builder mutation against tracked tables.
• Atomic by default: the source write and its history rows commit together or not at all.
• Enforced attribution: defineHistory({ metadataColumns }) requires .setHistory({ ... }) on every write, checked at compile time and again at runtime.
• Point-in-time reads with asOf, per-row timelines with rowHistory, snapshot diffs with diffHistoryRows, and restore building blocks with sourceValuesFromHistory.
• Backfill existing rows with populateHistory and apply retention policies with pruneHistory (including a dryRun preview).
• drizzle-kit integration: defineConfigWithHistory makes generate, migrate, push, and studio see the derived history tables.
• Works with drizzle-orm 0.45.x and 1.x, PostgreSQL 13 and newer, and the postgres-js, node-pg, PGlite, and neon-http drivers.
• Fully typed: history tables are real drizzle tables, so history rows come back as typed select models.

Installation

bun add @bcap/drizzle-history
# or
npm install @bcap/drizzle-history
# or
pnpm add @bcap/drizzle-history

drizzle-orm is a peer dependency with the supported range >=0.45.0 <0.46.0 || >=1.0.0-rc <2. drizzle-kit is an optional peer dependency, needed only for the @bcap/drizzle-history/kit entrypoint. Node.js 22 or newer is required, and both ESM and CJS consumers are supported.

Quickstart

The snippet below runs as-is against in-memory PostgreSQL via PGlite, so you can try the library without a database server. It is also available as examples/quickstart.ts.

import { PGlite } from '@electric-sql/pglite';
import { withHistory } from '@bcap/drizzle-history';
import { eq, sql } from 'drizzle-orm';
import { pgTable, serial, text } from 'drizzle-orm/pg-core';
import { drizzle } from 'drizzle-orm/pglite';

// Wrap the table at declaration time. This derives a companion `users_history`
// table, reachable as `users.history.table`.
const users = withHistory(
	pgTable('users', {
		id: serial().primaryKey(),
		name: text().notNull(),
		status: text().notNull(),
	}),
);

const client = new PGlite();
const db = drizzle({ client });

// Wrap the database once. In an application, export only the wrapped instance.
const trackedDb = withHistory(db);

// In a real project both tables come from drizzle-kit migrations (see
// "Adopting @bcap/drizzle-history on an existing project"). This example inlines the DDL.
await db.execute(
	sql.raw(`
		create table users (
			id serial primary key,
			name text not null,
			status text not null
		)
	`),
);
await db.execute(
	sql.raw(`
		create table users_history (
			id integer,
			name text,
			status text,
			"historyId" uuid primary key default gen_random_uuid(),
			"historyType" varchar(1) not null,
			"historyDate" timestamp with time zone not null default now()
		)
	`),
);

// Mutate through the wrapped database exactly like plain drizzle.
await trackedDb.insert(users).values({ name: 'Ada', status: 'pending' });
await trackedDb.update(users).set({ status: 'active' }).where(eq(users.id, 1));
await trackedDb.delete(users).where(eq(users.id, 1));

// Each mutation captured one snapshot row in the history table, which is a
// plain drizzle table with fully typed rows.
const history = users.history.table;
const snapshots = await db.select().from(history).orderBy(history.historyDate, history.historyId);

console.log('type  id  name  status');
for (const row of snapshots) {
	console.log(`${row.historyType}     ${row.id}   ${row.name}   ${row.status}`);
}
// type  id  name  status
// +     1   Ada   pending
// ~     1   Ada   active
// -     1   Ada   active

Every history table carries three core columns plus a nullable mirror of each source column:

| Column | Type | Meaning | | ------------- | -------------------------- | ---------------------------------------------------------- | | historyId | uuid, primary key | Unique id of the snapshot, gen_random_uuid() by default. | | historyType | varchar(1) | + for insert, ~ for update, - for delete. | | historyDate | timestamp with time zone | When the snapshot was captured, now() by default. |

Inserts and updates record the row as it looked after the write; deletes record the final values the row held. Mirrored source columns are nullable and constraint-free by design, so history rows can outlive any source-side schema rules.

Attribution metadata

defineHistory({ metadataColumns }) creates a toolkit whose tracked tables all share extra metadata columns, such as who made a change and why. A metadata column is required exactly when it has no database default, and nullable columns without defaults are still required: the caller must pass null explicitly rather than omit the key.

import { defineHistory } from '@bcap/drizzle-history';
import { integer, pgTable, serial, text } from 'drizzle-orm/pg-core';

const history = defineHistory({
	metadataColumns: {
		editorId: integer('editor_id').notNull(),
		reason: text('reason'),
	},
});

const articles = history.withHistory(
	pgTable('articles', {
		id: serial().primaryKey(),
		title: text().notNull(),
	}),
);

const trackedDb = history.withHistory(db);

await trackedDb
	.insert(articles)
	.values({ title: 'Hello, world' })
	.setHistory({ editorId: 1, reason: 'first draft' });

await trackedDb
	.update(articles)
	.set({ title: 'Hello, world!' })
	.where(eq(articles.id, 1))
	.setHistory({ editorId: 2, reason: null });

Forgetting the metadata is a compile-time error, not a code-review convention. While any required key is missing, the builder type has no execute, prepare, or promise methods, and a bare await fails to compile:

await trackedDb.insert(articles).values({ title: 'Hello, world' });
// error TS1320: Type of 'await' operand must either be a valid promise or must
// not contain a callable 'then' member.

trackedDb.insert(articles).values({ title: 'Hello, world' }).execute();
// error TS2339: Property 'execute' does not exist on type 'HistoryMutationBuilder<...>'.

The blocked builder's then signature carries the fix as a string literal type, so calling .then(...) directly spells it out:

trackedDb
	.insert(articles)
	.values({ title: 'Hello, world' })
	.then(() => {});
// error TS2345: Argument of type '() => void' is not assignable to parameter of type
// '"Call .setHistory(...) with every required history field before awaiting this builder."'.

Metadata can be supplied across multiple .setHistory(...) calls, and each call subtracts the keys it provides from the missing set. A runtime check enforces the same contract for untyped call sites: missing required keys and null values for notNull columns are rejected before any SQL runs. See examples/attribution.ts for a runnable version.

Querying history

The read-side helpers are standalone functions that accept any drizzle Postgres database or transaction, raw or wrapped, and issue only SELECTs.

import { asOf, diffHistoryRows, rowHistory } from '@bcap/drizzle-history';
import { eq } from 'drizzle-orm';

// Reconstruct the table as it existed at a point in time, with an optional
// filter written against the source table's columns.
const activeLastWeek = await asOf(db, users, lastWeek, {
	where: eq(users.status, 'active'),
});

// Every captured snapshot of one logical row, newest first by default.
const snapshots = await rowHistory(db, users, { id: 42 }, { limit: 10 });

// Which source columns changed between two snapshots.
const { changed } = diffHistoryRows(users, snapshots[1]!, snapshots[0]!);
// [{ column: 'status', before: 'pending', after: 'active' }]

asOf pushes the reconstruction down to PostgreSQL as a single query served by the history table's derived indexes, and drops rows whose latest snapshot is a delete. rowHistory accepts inclusive since and until bounds and returns fully typed history rows, including the core columns and any metadata columns. diffHistoryRows compares Dates by instant and JSONB values structurally, and takes optional include and exclude column lists. See examples/point-in-time.ts for a runnable timeline.

Restoring rows

sourceValuesFromHistory extracts insert-shaped source values from a history row: the core history and metadata columns are stripped, and captured values are preserved exactly, including primary keys. It never writes anything itself; compose it with your own tracked mutations so the restore is recorded in history like any other write.

import { rowHistory, sourceValuesFromHistory } from '@bcap/drizzle-history';

// Undelete: the `-` snapshot captured the row's final values, so re-insert them.
const [lastRow] = await rowHistory(db, users, { id: 42 }, { limit: 1 });
await trackedDb.insert(users).values(sourceValuesFromHistory(users, lastRow!));

// Revert: update the live row back to an earlier snapshot.
await trackedDb
	.update(users)
	.set(sourceValuesFromHistory(users, earlierSnapshot))
	.where(eq(users.id, 42));

Generated columns and always-generated identity columns are omitted from the extracted values because PostgreSQL computes those itself. Columns excluded from the history table (see "Excluding columns" below) were never captured, so they are also absent from the extracted values: an update-based revert leaves them untouched on the live row, and an undelete insert must supply them separately when the column is NOT NULL without a default.

Adopting @bcap/drizzle-history on an existing project

Migrations with drizzle-kit

History tables are derived at runtime, so drizzle-kit would not normally see them. defineConfigWithHistory from the @bcap/drizzle-history/kit entrypoint wraps your existing drizzle-kit config and exposes each tracked table's companion history table to every schema-reading command:

// drizzle.config.ts
import { defineConfigWithHistory } from '@bcap/drizzle-history/kit';

export default defineConfigWithHistory(
	{
		dialect: 'postgresql',
		schema: './src/schema.ts',
		out: './drizzle',
	},
	__dirname,
);

Pass __dirname as the second argument so schema paths resolve relative to the config file. drizzle-kit compiles config files to CJS internally, so import.meta.dirname is undefined in that context; __dirname is the reliable choice. After wrapping the config, drizzle-kit generate produces migrations that create and evolve the history tables alongside their sources.

Backfilling existing rows

populateHistory writes an initial + snapshot for every existing source row, so point-in-time queries have a baseline:

import { populateHistory } from '@bcap/drizzle-history';

const result = await populateHistory(db, [users, posts], { batchSize: 10_000 });
console.log(result.totalRows);

The backfill runs as INSERT ... SELECT statements where possible, deduplicates against already-populated rows by row identity (skipPopulated defaults to true, so reruns are safe), wraps the whole run inside a single transaction when the database supports it, and stamps every backfilled row with a single shared historyDate. Toolkits created with defineHistory expose a typed populateHistory that requires the same metadata as tracked writes:

await history.populateHistory(db, [articles], {
	metadata: { editorId: 0, reason: 'initial backfill' },
});

Retention

pruneHistory deletes old history rows according to a retention policy, with a dryRun mode that reports counts without deleting anything:

import { pruneHistory } from '@bcap/drizzle-history';

// Preview: how many rows would a real prune delete?
const preview = await pruneHistory(db, [users, posts], {
	olderThan: oneYearAgo,
	keepLatest: 10,
	dryRun: true,
});

// Delete rows that are both older than the cutoff and outside the newest
// ten snapshots for their row.
const result = await pruneHistory(db, [users, posts], {
	olderThan: oneYearAgo,
	keepLatest: 10,
});
console.log(result.totalDeleted);

olderThan deletes rows recorded strictly before the cutoff, keepLatest keeps the newest N snapshots per logical row, and providing both deletes only rows that satisfy both conditions. Pruning is irreversible and shrinks what asOf can reconstruct before the pruned horizon, so prefer a dryRun first.

Excluding columns

Columns that should never be captured (secrets, large blobs) can be excluded from the history table entirely:

const users = withHistory(
	pgTable('users', {
		id: serial().primaryKey(),
		email: text().notNull(),
		passwordHash: text('password_hash').notNull(),
	}),
	{ exclude: ['passwordHash'] },
);

Excluded columns are absent from the history table's DDL and select model, tracked writes and populateHistory never capture them, and derived indexes that reference them are skipped. Row identity columns cannot be excluded, because history rows must remain correlatable to their source rows.

What gets captured (and what does not)

@bcap/drizzle-history captures mutations by proxying the drizzle database object, not by installing database triggers. That keeps the database dependency-free, but it draws a hard boundary: only writes that flow through the wrapped database object's query builder are recorded. The following writes bypass capture silently:

• Raw SQL through db.execute(...), even on the wrapped instance.
• Writes through the original unwrapped db reference, or through transactions opened on it.
• Writes from any other client or process, including cron jobs, psql, and admin tools.
• Database-side changes: ON DELETE CASCADE and other foreign key actions, triggers, rules, and server-side procedures.
• Schema changes applied by drizzle-kit push that modify or delete data.

If your write path is centralized in an application that can export a single wrapped database instance, this boundary is easy to hold. If many writers touch the database directly, consider a trigger-based or CDC approach instead (see the comparison below); trigger-based capture is also on this project's roadmap. The full list of limitations, each with its consequence and workaround, is in docs/limitations.md.

Compatibility

| Component | Supported | | ----------- | ---------------------------------------------------------------------------------------------------------- | | drizzle-orm | 0.45.x and 1.x (including 1.0.0 release candidates) | | drizzle-kit | 0.30 or 0.31 with drizzle-orm 0.45.x; 1.x with drizzle-orm 1.x (optional, for @bcap/drizzle-history/kit) | | PostgreSQL | 13 and newer | | Drivers | postgres-js, node-pg, PGlite, neon-http | | Node.js | 22 and newer, ESM and CJS |

The full test suite runs against both drizzle-orm release lines on every change, and the first withHistory(db) call validates the installed version at runtime. Details, including driver result-shape handling and family-specific notes, are in docs/compatibility.md.

Comparison with alternatives

Each of these approaches is the right tool for some systems; the differences are about where capture happens and what the history data looks like.

PostgreSQL audit triggers. Triggers capture every write, including raw SQL, other clients, and cascades, which is their decisive advantage when many writers touch the database. The tradeoffs are that capture logic lives in the database (trigger DDL to write, migrate, and keep in sync per table), and application-level context such as the acting user must be smuggled in through session settings. @bcap/drizzle-history keeps capture in the application, where attribution metadata is a typed, compiler-enforced function argument, at the cost of only seeing writes that flow through the wrapped client.

Generic JSONB audit logs (supa_audit and similar). These record row images as JSONB in one shared table, which requires no per-table DDL and tolerates schema drift. The cost is that history rows are untyped documents: reconstructing typed rows, diffing, and indexing per-column all require JSONB operators and manual casting. @bcap/drizzle-history mirrors each source column as a real typed column, so history rows are ordinary drizzle select models and point-in-time queries are plain SQL over indexed columns.

Change data capture pipelines (Debezium, logical replication). CDC observes the write-ahead log, so it captures everything with low overhead and can stream changes into warehouses and event systems. It is operationally heavier (connectors, topics, consumers), history lands outside the source database, and attribution metadata is not naturally part of the stream. Prefer CDC when history must feed other systems; prefer @bcap/drizzle-history when you want queryable, attributed history inside the same database and transaction.

Roadmap

• Trigger-based capture, so out-of-process writes and cascades can be recorded.
• Transaction and revision grouping, so related writes share one logical change id.
• No-op update suppression, so updates that change nothing record nothing.
• Masked columns: capture that a value changed without storing the value.
• Shared audit table mode as an alternative to one history table per source table.
• A monotonic ordering tiebreaker (a sequence column or UUIDv7-style historyId values) with a compatible migration path, so multiple writes to one row inside a single transaction order deterministically.

How it works

withHistory(db) intercepts exactly five properties of the drizzle database: insert, update, delete, transaction, and batch (which refuses history-tracked builders); everything else forwards untouched. Mutation builders on tracked tables record each chained call, and execution replays the chain with a forced RETURNING of all source columns so the affected rows can be copied into the history table inside the same transaction. Upserts are classified per row using PostgreSQL's xmax system column, so onConflictDoUpdate records + for inserted rows and ~ for updated ones in a single statement. The full design, including history table derivation rules, index naming, atomicity semantics, and the memory profile of large mutations, is described in docs/how-it-works.md.

API summary

The complete reference, with options, error conditions, and examples for every export, is in docs/api.md. Practical integration patterns (Next.js wiring, AsyncLocalStorage attribution, testing with PGlite, adoption, retention, and point-in-time debugging) are in docs/recipes.md.

| Export | Purpose | | ------------------------------------------------------------------------------------- | ---------------------------------------------------------------------- | | withHistory(table, options?) | Derive a companion history table and mark the table as tracked. | | withHistory(db, options?) | Wrap a drizzle database so tracked mutations record history. | | defineHistory({ metadataColumns }) | Create a toolkit whose tracked tables share required metadata columns. | | createHistoryTable(table, options?) | Derive a standalone history table without tracking. | | asOf(db, table, timestamp, options?) | Reconstruct the table's rows as of a point in time. | | rowHistory(db, table, identity, options?) | Fetch every snapshot of one logical row. | | diffHistoryRows(table, older, newer, options?) | List the source columns that changed between two snapshots. | | sourceValuesFromHistory(table, historyRow) | Extract insert-shaped source values for undelete and revert flows. | | populateHistory(db, tables, options?) | Backfill initial + snapshots for existing source rows. | | pruneHistory(db, tables, options) | Apply a retention policy, with dryRun preview. | | isTrackedTable(value) / getTrackedTablePair(table) / expandTrackedTables(input) | Introspect tracked tables and schema objects. | | getHistoryTableMetadata(historyTable) / validateHistorySetup(tables) | Inspect and validate history table configuration. | | checkDrizzleCompatibility() | Assert the installed drizzle-orm version is supported. | | defineConfigWithHistory(config, __dirname) (from @bcap/drizzle-history/kit) | Expose derived history tables to drizzle-kit commands. |

Contributing

Development uses Bun, and the entire test suite runs against in-memory PostgreSQL via PGlite, so no database setup is needed. See CONTRIBUTING.md for the everyday commands and expectations.

License

BSD-2-Clause