Keelward

A TypeScript ORM for Postgres with a unit of work, and no code generation.

You describe the change; Keelward derives the statement.

await db.tenant(tenantId).unitOfWork(async (uow) => {
  const category = await uow.categories.find(id);
  if (!category) return;

  category.name = 'Espresso';
});

UPDATE "categories" SET "name" = $1, "version" = "version" + 1
WHERE "id" = $2 AND "tenant_id" = $3 AND "version" = $4
RETURNING "version"

Three protections you did not write: only the column you touched reaches the SET, the tenant filter is always there, and the concurrency token refuses to overwrite someone else's change.

Status: 0.2.0. Usable and tested, but young. The API is not frozen — treat the minor version as the breaking one until 1.0.0. It has not run in production anywhere. Read what it does not do before picking it for something that matters.

Coming from 0.1.x: three changes can alter behaviour rather than just adding to it. A float4/float8 column now introspects to real/doublePrecision instead of numeric, so a schema generated from an existing database changes and those values arrive as numbers rather than strings. A query issued on the client while a unit of work is open on the same driver is now refused, because it was silently outside that transaction — pass allowPoolQueriesInTransaction: true to keep the old behaviour. And drift now compares unique constraints and indexes, so a database that was quietly missing one starts saying so.

One more, smaller: unique and index are now methods on a table, so they join tenantScoped as names a column cannot have. A schema with a column called either is refused at startup with a message saying so, rather than behaving oddly.

Two more, smaller: a schema written by introspect from a database with a self-referencing table now uses selfRef() and compiles, where before it used ref() and did not; and closing a driver twice is no longer an error, so a shutdown hook that closes both the driver and the client built on it is fine.

And two things that used to be quietly accepted are now refused, because both were answering a question nobody asked: limit() or offset() before count(), sum(), avg(), min() or max() — they were ignored, so the answer was about the whole table — and a select() that leaves out the primary key on a query the unit of work is tracking, which produced an entity that addressed no row and failed later as a ConcurrencyError blaming another writer.

Install

npm install keelward @keelward/pg pg
npm install -D @keelward/cli

"strict": true is required in your tsconfig.json. Without it, branded ids collapse to plain string and half the safety disappears silently.

Define a schema

Plain TypeScript values. No decorators, no separate schema language, no reflect-metadata, no build step.

import { table, uuid, text, varchar, int, numeric, timestamp, ref } from 'keelward';

export const tenants = table('tenants', {
  id: uuid('id').primaryKey(),
  slug: text('slug').notNull().unique(),
});

export const categories = table('categories', {
  id: uuid('id').primaryKey(),
  tenantId: ref('tenant_id', () => tenants.id).notNull(),
  name: varchar('name', 120).notNull(),
  icon: text('icon'),
  price: numeric('price', { precision: 12, scale: 2 }).notNull(),
  version: int('version').concurrencyToken(),
  createdAt: timestamp('created_at').notNull().defaultNow(),
}).tenantScoped('tenantId');

export const items = table('items', {
  id: uuid('id').primaryKey(),
  tenantId: ref('tenant_id', () => tenants.id).notNull(),
  categoryId: ref('category_id', () => categories.id).notNull(),
  label: text('label').notNull(),
  version: int('version').concurrencyToken(),
}).tenantScoped('tenantId');

export const schema = { tenants, categories, items };

A few things a column method cannot say, because they are about more than one column or about the table's own key:

import { table, uuid, text, timestamp, selfRef, doublePrecision } from 'keelward';

export const pages = table('pages', {
  id: uuid('id').primaryKey(),
  slug: text('slug').notNull(),
  // A foreign key onto this table's own primary key. It names no target:
  // `ref('parent_id', () => pages.id)` written here would make the type of
  // `pages` depend on itself, which TypeScript refuses (TS7022).
  parentId: selfRef('parent_id', { onDelete: 'cascade' }),
  weight: doublePrecision('weight'),
  // now() on insert, and again on every UPDATE that changes something else.
  updatedAt: timestamp('updated_at').updatedAt(),
})
  .unique(['parentId', 'slug'])
  .index(['slug']);

onDelete also goes on an ordinary ref(). Without it a foreign key is NO ACTION, and deleting a parent fails while it still has children — which is a real choice, and rarely the one anybody wanted.

Relations are declared on the client rather than on the table, so table definitions stay acyclic:

import { createClient, hasMany, belongsTo } from 'keelward';
import { postgres } from '@keelward/pg';

export const db = createClient({
  schema,
  driver: postgres({ connectionString: process.env.DATABASE_URL! }),
  relations: (tables) => ({
    categories: { items: hasMany(tables.items, 'categoryId') },
    items: { category: belongsTo(tables.categories, 'categoryId') },
  }),
});

Read

const shop = db.tenant(tenantId);

await shop.categories.where({ name: 'Espresso' }).all();

// Anything past equality is a small function, imported from 'keelward'.
// where({ price: { gt: 3 } }) is not the shape; the compiler will say so.
await shop.categories
  .where({ price: gt('3.00'), name: like('Caf%'), icon: isNull() })
  .all();
// eq ne gt gte lt lte between like ilike notLike notIlike inArray notInArray isNull isNotNull

// contains, startsWith and endsWith escape the term, so a `%` typed into a
// search box is a percent sign rather than a wildcard that matches everything.
await shop.categories.where({ name: contains('50%') }).all();
await shop.categories.where({ name: startsWith('caf', { caseInsensitive: true }) }).all();

// Only these two properties exist afterwards, in the type and in the object.
await shop.categories.select({ id: true, name: true }).all();

// An include narrows the same way, in the statement and in the type.
await shop.categories.include({ items: { select: { label: true } } }).all();

// One extra query per relation, never a join. An include can itself include.
await shop.categories.include({ items: true }).orderBy('name').limit(20).all();
await shop.categories.include({ items: { include: { category: true } } }).all();

await shop.categories.count();

// Keyset paging: page 2000 costs what page 1 costs.
const first = await shop.categories.orderBy('name').page({ size: 50 });
const next = await shop.categories.orderBy('name').page({ size: 50, after: first.cursor });

page() orders by the primary key as well as whatever you asked for, so the order is total — without a tie-break two rows sharing a value can land either side of a boundary and one of them is never seen. The cursor carries a fingerprint of the ordering, so one made by a differently ordered query is refused rather than quietly skipping rows, and a nullable ordering column is refused too: NULL > x is null, not false, and the comparison stops working. cursor is null on the last page.

Write

Inside a unit of work you assign, and the statements are derived on the way out. Two things do not fit that shape, and both are here rather than in raw SQL.

A row that may already be there. find then add-or-assign is two statements with a gap in the middle: two requests saving the same key both find nothing, both insert, and one of them dies on the unique constraint.

await db.unitOfWork(async (uow) => {
  // INSERT ... ON CONFLICT ("slug") DO UPDATE SET ...
  uow.tenants.upsert({ slug: 'acme' }, { on: 'slug' });

  // An empty `set` records the row if it is new and leaves it exactly as it is
  // if it is not - which is what an idempotency table wants.
  uow.tenants.upsert({ slug: 'acme' }, { on: 'slug', set: [] });
});

The conflict target has to be a real unique constraint — a unique() column or a unique([...]) on the table — checked when the statement is planned rather than by Postgres afterwards. On a tenant-scoped table the update carries the tenant filter, so a unique constraint that forgot the tenant column cannot hand one tenant's row to another. The entity comes back straight away, and is corrected in place on commit: after a collision the id is the one that was already in the table.

Rows you are not holding. The unit of work writes one statement per entity, which is one round trip per row when the rows were never loaded. Replacing a page's blocks was a SELECT and sixty DELETEs.

// One statement each, and each answers with how many rows it touched.
await shop.items.where({ categoryId: id }).delete();
await shop.items.where({ categoryId: id }).update({ label: 'renamed' });

// The database does the arithmetic, so no request has to know the old value.
// On a nullable column with no value this leaves null, because NULL + 1 is NULL.
await shop.categories.where({ id }).update({ price: increment(1) });

// An unfiltered write has to say so out loud.
await shop.items.delete({ every: true });

These are not the unit of work and do not pretend to be: nothing is loaded, nothing is tracked, and an entity you are already holding is not told. What protects you there is the concurrency token — the entity's next write raises ConcurrencyError rather than quietly undoing this one. Built from uow.<table>.query() they run inside the transaction and roll back with it.

Aggregate

The database does the arithmetic, in one statement.

await shop.categories.sum('price');                 // '183407.38' — an exact string
await shop.categories.where({ icon: isNotNull() }).avg('price');
await shop.categories.min('createdAt');            // a Date, or null
await shop.categories.max('price');

// Several at once, over the whole filtered set, in one statement.
await shop.categories.aggregate({ categories: { count: true }, total: { sum: 'price' } });

await shop.categories.groupBy('icon').aggregate({
  categories: { count: true },
  total: { sum: 'price' },
  newest: { max: 'createdAt' },
});
// [{ icon: 'cup', categories: 12, total: '341.90', newest: Date }, ...]

Two things worth knowing before you use the numbers.

A sum is a string. Adding an integer column gives Postgres a bigint, and adding a numeric gives more digits than a JavaScript number holds, so the exact decimal comes back as text. Parse it in cents, never with Number on money. min and max keep the column's own type, because they return a value that was already in the table.

A sum over no rows is null, not 0. That is what SQL means, and flattening it would hide the difference between "no sales" and "sales totalling nothing". The type says string | null so the choice stays yours.

sum and avg accept only numeric columns, and min/max only ordered ones — sum('name') does not compile. Every filter and the tenant scope reach the aggregate, and a grouped query can be ordered, limited and offset by group.

An ungrouped aggregate refuses limit and offset, and ignores orderBy. The line is whether the modifier can change the value. Ordering cannot move a single number, so counting the query you built for a list is fine. A limit can: limit(2).count() reads as "how many of the first two" and there is no such number, so it is refused rather than answered with the count of the whole table.

How it compares

Every ORM below is a good piece of software, and this is not a scoreboard. The table describes two mechanical facts about each: what you have to write to make a change persist, and what has to run before your code does. Those two things are why Keelward exists.

| | Schema lives in | Build step before use | A change to a loaded row | |---|---|---|---| | Prisma | its own .prisma file | prisma generate | you write the update | | Drizzle | TypeScript values | none for the client | you write the update | | TypeORM | classes with decorators, reflect-metadata | none | tracked; save() derives it | | MikroORM | classes, metadata discovered at runtime | none | tracked; flush() derives it | | Keelward | TypeScript values | none | tracked; derived on exit |

Read the last two columns together. The libraries that derive the write for you get there through decorators and runtime metadata; the one with a plain-TypeScript schema and no code generation makes you write the update yourself. Keelward is an attempt at both at once. That is the whole thesis, and it is the only claim here worth judging it on.

MikroORM in particular does the unit of work well, and has done for years. If decorators and metadata discovery are not a problem for you, it is a far more complete library than this one is today.

Two caveats on that table, so it is not read as more than it is. TypeORM and MikroORM both also offer a schema-object API that avoids decorators; the decorator path is simply the one their documentation leads with. And Prisma's generated client is what buys it its type inference — the build step is the mechanism, not an oversight.

Described from each project's own documentation and accurate to the best of our knowledge at the time of writing. They all move quickly — check their docs rather than trusting a table in someone else's README.

What it gives you

Writes are derived, not written. Inside a unit of work you assign to entities and Keelward works out the statements on the way out, in one transaction. Assigning a value that was already there emits no SQL. Inserting and then modifying the same row costs one INSERT. Adding and removing it costs nothing. Inserts are ordered by foreign-key dependency, so declaration order does not matter - inside a table that points at itself too, so a tree can be staged children-first and still goes in parents-first.

Rows added together share a statement. A thousand add() calls on one table become one INSERT with a thousand tuples, split only where Postgres' parameter ceiling makes it necessary. Rows that assign different columns go in separate statements rather than being aligned into one, and a table with a foreign key onto itself is left a row at a time — the order within a statement is not something to lean on. Seeding the 26,000-row example dataset went from 14.3s to 6.9s.

Branded ids. A CategoryId and a ProductId are different types, though both are strings at runtime. Turning a string from a URL or a form into one requires categories.id.parse(raw), which validates first — so an unchecked value cannot reach a query by accident.

Partial selects narrow the type. Ask for two columns and the other fields stop existing, in the type and in the returned object.

Tenant isolation the compiler enforces. A tenantScoped table is absent from the unscoped client: db.categories does not compile. Reaching it needs db.tenant(id), or the deliberate and greppable db.unscoped(). The scope reaches reads, includes, updates, deletes and inserts.

Optimistic concurrency. A concurrencyToken() column is bumped and checked on every write, so a lost update raises ConcurrencyError instead of disappearing.

No N+1, no cartesian products. include runs one extra query per relation and stitches the rows through the identity map: fifty parents with their children is two queries, and the parent rows are not multiplied. Inside a unit of work every level is tracked, not just the first: a row three includes deep is an entity, and assigning to it writes. There is no lazy loading, on purpose — JavaScript cannot await inside a getter, so lazy loading always ends in dangling promises or hidden N+1.

Money survives. numeric columns travel as strings, because a JavaScript number loses cents before you can do anything about it.

Errors that say what happened. A database that is not running gives you Cannot reach the database at postgres://user:***@host:5432/db (ECONNREFUSED). Is the database running? — password redacted, and without the bound values, which routinely hold personal data.

Safety, stated precisely

Through the query API, SQL injection is not expressible. Every value a caller supplies becomes a bound parameter, and the plan the core produces contains no statement text at all — there is no node in it that can carry SQL. Table and column names come from your schema, never from a string.

default() takes a value, defaultSql() takes SQL. int('n').default(0) is typed against the column and rendered as an escaped literal, so a string holding 0); DROP TABLE … reaches the DDL quoted rather than as another statement. Raw SQL is still there for gen_random_uuid() and friends, spelled out as defaultSql, which is the one piece of hand-written SQL a schema contains — grep for it in review the way you would rawStatement.

There is one escape hatch in the query path, and it is deliberate. @keelward/pg exports Retryable failures arrive as ConnectionError, never as StatementError. That is the distinction an application acts on: a lost connection is worth retrying, a rejected statement never is. A server that goes away mid-statement reaches node-postgres as a bare Error with no SQLSTATE, so it used to arrive carrying the SQL, looking like the query was wrong. A restart, a shutdown, a pool with nothing free and every connection-class SQLSTATE are all ConnectionError now, each with a line saying what to do about it.

rawStatement(sql, params) for what the plan layer does not model. It runs whatever SQL you hand it. If you use it, you own it — grep for it in review, the same way you would grep for db.unscoped().

Tenant isolation has three lines, and they catch different mistakes.

The compiler is the first and the one that does the work: a scoped table is not a property of the unscoped client, so there is nothing to call — a cast finds undefined, not an unfiltered query.

The second refuses rather than guessing. A query built without a scope — through from(), or by hand — raises instead of running unfiltered, and db.tenant(id) rejects an id that is missing rather than falling back to every tenant. db.unscoped() carries a marker, so "every tenant, deliberately" and "no scope reached this query" are different things rather than both being absent.

The third checks the finished statement. Before a scoped table's plan leaves the core, its predicate is searched for the tenant column, and an OR branch or a != does not count. That one is not aimed at your code: it catches this library dropping the filter in a refactor, which is the failure that would quietly return someone else's rows. Disabling the filter makes eleven tests fail with this is a bug in keelward.

Branded ids still end where as any begins. A cast defeats the compiler, and nothing at runtime can tell a well-formed uuid from the wrong table's.

Command line

keelward introspect          # existing database -> db/schema.ts
keelward migrate generate    # schema -> CREATE TABLE for tables not there yet
keelward migrate new <name>  # empty timestamped .sql file you write yourself
keelward migrate up          # apply pending, one transaction per file
keelward drift               # fail in CI when schema and database disagree
keelward check               # unindexed foreign keys, missing tokens, loose tsconfig

The connection string comes from --url, keelward.config.json, the environment, or a .env / .env.local file — in that order, and a real environment variable is never overwritten by a file.

migrate generate writes the CREATE TABLE for tables the database does not have yet, ordered by foreign-key dependency, with the unique constraints, the delete actions and the indexes the schema declares, plus the ones check would ask for anyway. A constraint or index name that would pass Postgres' 63-byte limit is shortened with a digest, because Postgres truncates silently and two long names can arrive as the same one. A table or column name over that limit is warned about instead — that name belongs to the schema, and truncating it would leave drift reporting the table as missing on every run. It never writes an ALTER: changing a table that already holds data means guessing what a difference meant, and a wrong guess drops a column. That part stays yours, and drift makes sure the two never separate without CI saying so.

drift compares tables, columns, nullability, types, primary keys, unique constraints and indexes. A unique constraint dropped in production is an error; a missing index is a warning, because it costs speed rather than correctness.

A type keelward only approximates is a warning too, not silence. There is no date() or smallint() builder, so introspect reaches for the nearest one it has and the column widens on the way through — a date becomes timestamp(), whose SQL type is timestamptz. Both commands say so now: introspect warns where the choice is made, and drift reports the pair rather than filing them under one type family. Silence there meant introspect followed by migrate generate produced a different column from the one it read, with nothing anywhere to show for it.

introspect writes back everything the catalog can tell it: the column types with their modifiers, the delete action on each foreign key, the composite unique() and index() that live on the table rather than on a column, and selfRef() for a table that points at itself - which ref() cannot express, because the thunk would name the const being declared and TypeScript would give up on the whole table. Three things it cannot see are listed in a comment at the top of the file it writes: .concurrencyToken(), .updatedAt(), and .tenantScoped() beyond a plain tenant_id column.

Size

Measured with esbuild and gzip, not estimated. Each figure is the whole package, which is the pessimistic number - a project that imports what it actually uses gets less:

keelward               12,183 B min+gzip
@keelward/pg            2,728 B min+gzip

The core nearly doubled in 0.2.0, from 6,870 B. That is what upsert planning, set-based writes, the string conditions and the table-level constraints cost; none of it is optional at runtime, because the planner is one module. If that matters more to you than those features do, 0.1.3 is still on npm.

Zero runtime dependencies in all three packages. No postinstall, no binary, no generated client directory — npm ci runs no code generation step.

The packages are written in ESM and ship both: import gets the ESM build, require a CommonJS one built from the same source. Without the second, a project without "type": "module" cannot load them at all — including through tsx, which is how most seeds and one-off scripts in this ecosystem are run.

Runtimes

The main entry of keelward imports nothing from Node, and keelward/scope is the one subpath that uses node:async_hooks.

That does not mean you can run Keelward on an edge runtime yet. The only driver today is @keelward/pg, which reaches Postgres over a TCP socket through node-postgres. An HTTP-based driver (Neon, Hyperdrive) would be needed and none exists. The core being runtime-agnostic is groundwork, not a shipped feature.

Node 20+ is what the test suite runs on. Deno and Bun are untested.

What it does not do

• Postgres only. The dialect seam exists and is covered by tests, but with a single implementation it is unproven. Treat it as a design choice, not a feature.
• No having, and no aggregate inside a where. Filter first, group second.
• No expressions in an aggregate — sum('total'), never sum(price * qty).
• No joins in the query API. Relations are fetched as separate queries; anything analytical goes through raw SQL.
• Single-column primary keys only. A non-uuid primary key needs an explicit id in add().
• No lazy loading, ever. See above.
• No down migrations, and no automatic diffing.
• A partial index is not expressible in the schema — unique(['parentId', 'slug']) covers the whole table, and "unique among siblings, where there is a parent" is a CREATE UNIQUE INDEX ... WHERE you write in a migration.
• drift compares type families, so numeric and numeric(12, 2) look the same to it. introspect reproduces the modifier, so a round trip is faithful; a modifier changed by hand in the database is not reported. It does now compare unique constraints and indexes.
• No savepoints, so two units of work on the same driver do not nest: the inner one is refused with an explanation rather than silently committing on its own. Two different drivers are two pools and are allowed.
• Upserts are not batched. A collision can resolve to a row that was already there under a different primary key, which RETURNING could not then be matched back — so each one is its own statement. Plain inserts still share one.

Development

pnpm install
pnpm db:up          # Postgres 18 on port 5434
pnpm -r build
pnpm -r test        # 325 tests
pnpm verify:package # packs it, installs it, and uses it as a stranger would

The suite runs against a real Postgres, a real HTTP server and a real Next.js production build. Two example applications live in examples/.

verify:package is the one that has actually caught things. The tests import from src, which is right for them and is also why they missed every defect that has shipped so far: a brand no consumer could name, a scoped insert that demanded the tenant id it had just been given, and a jsonb array that arrived as a Postgres array literal. So this one packs the three packages, installs the tarballs into an empty project, compiles every TypeScript block of this README exactly as published, checks that the compile-time refusals still refuse, emits declarations from a consumer, builds a schema through the CLI, and then runs the claims above against a live database. It runs on every push.

License

MIT