@askrjs/orm

Postgres-first, SQL-shaped data access for Askr.

@askrjs/orm sits between a micro-ORM and a generated data layer: database definitions are TypeScript, generated artifacts are committed, CRUD is status-first, and read queries retain an explicit SQL shape. It does not track objects, infer relation graphs, or perform nested writes.

The supported runtime is Node 20.19+ or Node 22.12+. PostgreSQL 16, 17, and 18 are the v1 compatibility targets.

Define a database

// database/users.ts
import { table, text, timestampTz, uuid } from "@askrjs/orm";
import { groups } from "./groups";

export const users = table("users", {
  id: uuid().primaryKey().defaultRandom(),
  email: text().notNull().unique(),
  groupId: uuid().notNull().references(() => groups.id),
  createdAt: timestampTz().notNull().defaultNow(),
});

Properties map from camel case to snake case. Use .name("legacy_name") for an explicit SQL identifier. Column codecs preserve application-specific value types while controlling database encoding and decoding.

The root exports lazy factories. Importing definitions never opens a connection:

// database/index.ts
import { database } from "@askrjs/orm";
import { migrationManifest } from "./generated";
import { groups } from "./groups";
import { users } from "./users";
import { openScratch, openTarget } from "./postgres-adapter";

export default database({
  tables: { groups, users },
  manifest: migrationManifest,
  targetIdentity: "app-production",
  scratchIdentity: "app-orm-scratch",
  target: openTarget,
  scratch: openScratch,
});

The application-owned adapter implements DatabaseAdapter; generation uses the separate DatabaseToolingAdapter. This keeps the runtime contract driver-neutral and makes destructive scratch reset an explicit integration boundary.

Generate and validate

Install @askrjs/orm in the project, then use the unified Askr CLI:

askr database generate
askr database validate
askr database migration plan
askr database migration apply --yes

Generation resets only the named scratch database, replays committed migrations, describes keyed SQL, writes a forward migration when needed, and regenerates database/generated/. It never applies to the target.

Validation performs the same scratch replay and then compares both the introspected schema and every generated byte. askr check discovers database/index.ts and runs this validation automatically.

See migration safety and the adapter contract. The structural runtime overhead gate is documented in performance.

CRUD and batches

const db = await database.open("target");

await db.users.get(userId); // User | null
await db.users.insert({ email, groupId }); // { rowsAffected }
await db.users.update(userId, { email }); // { rowsAffected }
await db.users.delete(userId); // { rowsAffected }

const user = await db.users.insert(
  { email, groupId },
  { returning: "row" },
);

await db.users.insertMany(rows, { chunkSize: 500 });
await db.users.upsertMany(rows, { chunkSize: 500 });

const [inserted, removed] = await db.batch([
  (current) => current.users.insert({ email, groupId }),
  (current) => current.users.delete(oldUserId),
] as const);

Bulk operations and db.batch do not create transactions. Wrap them in db.transaction(...) when atomicity is required.

SQL-shaped reads

import { eq } from "@askrjs/orm";

const query = db.users
  .leftJoin(db.groups)
  .on(({ users, groups }) => eq(users.groupId, groups.id))
  .select(({ users, groups }) => ({
    userId: users.id,
    email: users.email,
    groupName: groups.name,
  }))
  .where(({ users }) => eq(users.email, email))
  .orderBy(({ users }) => users.email);

const rows = await query.execute({ signal });
const prepared = query.prepare("users-with-groups");
const sql = query.toSQL();

Joins require an explicit on and projection. Self-joins require as. Outer-join references carry nullable value types. Values are parameters; identifiers must come from generated descriptors or sql.identifier. sql.unsafe is the explicit arbitrary-SQL boundary.

Keyed SQL

Static SQL can live anywhere in project source:

export const userByEmail = sql.key(
  "users.by-email",
  { email: "" as string },
)`
  SELECT id, email
  FROM users
  WHERE email = :email
`;

Keys must be stable and unique. Generation scans the static template, asks the scratch adapter to describe it, and emits the parameter/result registry in database/generated/queries.ts. Runtime execution uses the key as the prepared statement name:

const rows = await executeKeyedSql(adapter, userByEmail, { email });

Non-goals

V1 has no identity map, change tracking, lazy loading, relation includes, nested writes, rollback migrations, startup auto-migration, seeds, materialized views, extensions, triggers, functions, RLS, partitions, or generated runtime validation schemas. Existing databases are not adopted by introspection.