DEV Community

Cover image for OKModel: the TypeScript ORM that says no to your worst SQL mistakes
Omq Khafi
Omq Khafi

Posted on

OKModel: the TypeScript ORM that says no to your worst SQL mistakes

#typescript #postgres #orm #opensource

You've probably lived one of these:

  • A migration that looked harmless took an exclusive lock on a busy table, and production stalled.
  • A query forgot the tenant filter, and one customer saw another customer's data.
  • An UPDATE ran without a WHERE.
  • Two people edited the same record, and one of them silently lost their work.

None of these are exotic. They're the everyday ways data goes wrong, and most ORMs leave them for you to remember on a busy day.

I'm building OKModel so you don't have to remember. It's a catalog-first TypeScript ORM for SQL databases. Postgres today, SQLite and MySQL next.

Instead of listing features, let's build something: Helpdesk, a small multi-tenant support app. Every customer workspace is a tenant, agents answer tickets, and the team writes help articles.

1. One schema, and the rules live in it 

import { gte, many, one, schema, table, t } from "okmodel/pg";
import { columnTenancy } from "okmodel/tenancy";
import { archivable, timestamps, versioned } from "okmodel/traits";
import { v } from "okmodel/validate";

export const agents = table("agents", {
  id: t.identity(),
  email: t.text().unique().validate([
    v.trim(),
    v.lowercase(),
    v.email("email_invalid"),
  ]),
  name: t.varchar(80).validate([v.min(1, "name_required")]),
}, {
  traits: [timestamps()],
  relations: {
    tickets: many("tickets", "assigneeId"),
  },
});

export const tickets = table("tickets", {
  id: t.identity(),
  subject: t.varchar(200).validate([
    v.trim(),
    v.min(5, "subject_too_short"),
  ]),
  body: t.text(),
  status: t.text().picklist(["open", "pending", "solved"]).default("open"),
  priority: t.integer().default(1),
  assigneeId: t.bigint().nullable().references("agents"),
  search: t.tsvector()
    .generated("to_tsvector('english', subject || ' ' || body)")
    .hidden({ filterable: true }),
}, {
  traits: [timestamps(), archivable()],
  relations: {
    assignee: one("agents", "assigneeId"),
    replies: many("replies", "ticketId"),
  },
  presets: {
    open: (q) => q.where({ status: "open" }),
    urgent: (q) => q.where({ priority: gte(3) }),
    assignedTo: (q, agentId: string) => q.where({ assigneeId: agentId }),
  },
});

export const replies = table("replies", {
  id: t.identity(),
  ticketId: t.bigint().references("tickets"),
  body: t.text().validate([v.min(1, "reply_empty")]),
}, {
  traits: [timestamps()],
  relations: {
    ticket: one("tickets", "ticketId"),
  },
});

export const articles = table("articles", {
  id: t.identity(),
  title: t.text(),
  body: t.text(),
}, {
  traits: [timestamps(), versioned()],
});

export default schema({
  tenancy: columnTenancy({ key: "workspaceId", type: "uuid" }),
  tables: [agents, tickets, replies, articles],
  validation: true,
});
Enter fullscreen mode Exit fullscreen mode

There's a lot packed into this one file:

  • Tenancy is declared once for the whole schema. Every table gets a workspaceId, and every query is scoped by it. Prefer database-enforced isolation? Swap in rlsTenancy(...) for Postgres row-level security.
  • Validation runs inside the ORM: emails are trimmed, lowercased and checked before they're written.
  • Traits add behaviour in one line: timestamps(), archivable() and versioned().
  • Presets are named, chainable filters like open, urgent and assignedTo.
  • Picklists become a typed union in TypeScript and a CHECK constraint in Postgres.
  • A generated search column is hidden from every result and still searchable.

From this file you get migrations, types and runtime rules.

2. A client that's always scoped 

const acme = db.for({ workspaceId });

const maya = await acme.agents.insert({
  email: "  Maya@Acme.IO ",
  name: "Maya",
});
Enter fullscreen mode Exit fullscreen mode

What lands in the database:

{
  "id": "1",
  "email": "maya@acme.io",
  "name": "Maya",
  "workspaceId": "c1b4…",
  "createdAt": "2026-10-18T13:00:25.058Z",
  "updatedAt": "2026-10-18T13:00:25.058Z"
}
Enter fullscreen mode Exit fullscreen mode

The email is trimmed and lowercased, the timestamps are filled, and the row belongs to Acme's workspace without you writing workspaceId anywhere.

3. Validation that speaks HTTP 

try {
  await acme.agents.insert({
    email: "maya(at)acme",
    name: "",
  });
} catch (error) {
  if (error instanceof OkmError) {
    return error.toHttp();
  }
}
Enter fullscreen mode Exit fullscreen mode 
{
  status: 422,
  body: {
    code: "OKM1200",
    reason: "Validation failed: email email_invalid",
    fields: { email: "invalid", name: "invalid" },
  },
}
Enter fullscreen mode Exit fullscreen mode

It's one error with every field, and it maps straight to your API response. And status: "closed" on a ticket doesn't even compile, because status is typed "open" | "pending" | "solved".

4. First-or-create, without the race 

const sam = await acme.agents.insert(
  { email: "SAM@acme.io", name: "Sam" },
  { onConflict: { on: "email", return: true } },
);
Enter fullscreen mode Exit fullscreen mode

If Sam already exists, you get the existing Sam back. Validation lowercases first, so SAM@acme.io still matches. And the unique constraint is per workspace: two customers can each have their own Sam.

5. A ticket queue two agents can't fight over 

const [next] = await acme.tickets.open().update({
  where: { assigneeId: null },
  set: { assigneeId: maya.id, status: "pending" },
  limit: 1,
  lock: "skip",
}, {
  returning: true,
});
Enter fullscreen mode Exit fullscreen mode

"Give me the next open, unassigned ticket" is one statement with SKIP LOCKED. Ten agents can click at once, and nobody gets the same ticket.

6. The inbox: presets and nested includes 

const inbox = await acme.tickets.urgent().find({
  include: {
    assignee: true,
    replies: { orderBy: { id: "desc" }, limit: 3 },
  },
  orderBy: { priority: "desc" },
  limit: 20,
});
Enter fullscreen mode Exit fullscreen mode

Every urgent ticket comes with its assignee and its three latest replies, in a single query compiled to LATERAL joins. The result is fully typed: status is the union, createdAt is a Temporal.Instant, assignee is nullable, and the hidden search column isn't in the type at all.

7. Search, ranked 

const hits = await acme.tickets.find({
  search: { for: "refund", language: "english" },
  include: { assignee: true },
  limit: 10,
});
Enter fullscreen mode Exit fullscreen mode 
[
  { "id": "1", "subject": "Refund for double charge", "assignee": "Maya" },
  { "id": "3", "subject": "Refund status?", "assignee": null }
]
Enter fullscreen mode Exit fullscreen mode

This is Postgres full-text search with the closest match first, and it stays tenant-scoped. No extra search service needed.

8. Two agents, one help article

Maya and Sam both open the refund article. Maya saves first. Then Sam saves:

await acme.articles.update({
  where: { id },
  set: { body: "Refunds land in 5 business days." },
  version: article.version,
});
Enter fullscreen mode Exit fullscreen mode 
update on articles tried to write body at version 1, and the row changed.
Reread the row and retry with the version you read.
Enter fullscreen mode Exit fullscreen mode

With versioned(), Sam's write is refused (HTTP 409), so Maya's edit isn't silently overwritten.

9. A public API endpoint, safely 

const ticketFilters = tickets.filters({
  allow: {
    status: ["eq", "in"],
    priority: ["gte"],
  },
  sort: ["priority"],
});

const query = await ticketFilters.parse(request.query);

const rows = await acme.tickets.find({
  ...query,
  limit: 20,
});
Enter fullscreen mode Exit fullscreen mode

Clients can filter by status and priority, and nothing else. A request for ?body=... is refused:

OKM1123 Field body is not in the allow list.
Enter fullscreen mode Exit fullscreen mode

10. The dashboard 

const daily = await acme.tickets.aggregate({
  groupBy: [
    "status",
    bucket("createdAt", "day", { tz: "Asia/Riyadh", as: "day" }),
  ],
  count: true,
  orderBy: { day: "desc" },
  limit: 30,
});
Enter fullscreen mode Exit fullscreen mode 
[
  { "status": "open", "day": "2026-10-18", "count": 2 },
  { "status": "pending", "day": "2026-10-18", "count": 1 }
]
Enter fullscreen mode Exit fullscreen mode

Time buckets respect the time zone, and day comes back as a Temporal.PlainDate. For long lists there's keyset paging:

const first = await acme.tickets.page({
  orderBy: { priority: "desc" },
  limit: 20,
});

const next = await acme.tickets.page({
  orderBy: { priority: "desc" },
  limit: 20,
  after: first.next,
});
Enter fullscreen mode Exit fullscreen mode

11. Archive, and change your mind 

const { archiveId } = await acme.tickets.archive({
  where: { status: "solved" },
});

await acme.tickets.count();                // solved tickets are gone
await acme.tickets.withArchived().count(); // ...but still stored

await acme.tickets.restore({ archiveId }); // undo the whole batch
Enter fullscreen mode Exit fullscreen mode

Every archive gets an id, so restoring is one call, and every normal query hides archived rows automatically.

12. The safety net 

await db.tickets.find({ limit: 20 });
// TS error: tickets is tenant-scoped. It doesn't exist on the root client.

await acme.tickets.find({ where: { status: "open" } });
// OKM1101 find() needs a limit, or .all(reason) when the full set is intentional.

await acme.tickets.update({ set: { status: "solved" } });
// OKM1102 update on tickets needs a where. Pass a filter, or .all(reason).

// When you really mean it, say why:
await acme.tickets
  .find({ where: { status: "open" } })
  .all("nightly export");
Enter fullscreen mode Exit fullscreen mode

You can't forget the tenant, because the table isn't in your types until you scope it. Unbounded reads and whole-table writes need a reason, and another workspace's count() of Acme's tickets is 0.

13. Migrations that tell you the truth

Add a column to tickets:

firstResponseAt: t.timestamptz().nullable(),
Enter fullscreen mode Exit fullscreen mode 
okm generate first_response
Enter fullscreen mode Exit fullscreen mode 
-- class: expand
-- kind: add-column
-- lock: ACCESS EXCLUSIVE
alter table "public"."tickets" add column "firstResponseAt" timestamptz;
Enter fullscreen mode Exit fullscreen mode

Every step states its class and the lock it takes. Now remove priority while the app still reads it:

error OKM1512 step 1: drops a column
  fix: Stop reading the column in an expand migration,
       then drop it in a later contract.
       Or allow OKM1512 with a reason.
Enter fullscreen mode Exit fullscreen mode

okm migrate apply refuses until you fix it. Indexes come out as create index concurrently, okm migrate check replays everything in a transaction that's always rolled back, and backfills are resumable.

14. Catch the N+1 before code review 

const h = await testing(schema, { driver: open() });

const f = h.factories({
  tickets: (x) => ({ subject: x.words(4), body: x.words(12) }),
  replies: (x) => ({ body: x.words(6), ticketId: x.ref("tickets") }),
});

await f.tickets.with({ replies: 5 }).create({ workspaceId });
const acme = h.db.for({ workspaceId });

// The inbox must stay one query, however many replies there are.
await h.expectQueries(1, () =>
  acme.tickets.find({
    include: { replies: { limit: 10 } },
    limit: 20,
  }),
);
Enter fullscreen mode Exit fullscreen mode

Write the inbox as a loop instead, and the test fails and shows you the SQL:

expected 1 query, ran 2
select ... from "tickets" t where t."workspaceId" = $1 and t."archivedAt" is null limit $2
select ... from "replies" t where t."workspaceId" = $1 and t."ticketId" = $2 limit $3
Enter fullscreen mode Exit fullscreen mode

Notice the tenant and archive filters you never wrote. They're there in every query.

And there's more

  • typed raw() SQL that keeps the tenant predicate, plus explain()
  • Postgres row-level security, database roles and column permissions
  • sensitive() columns, redacted from logs, errors and traces
  • read replicas with routing and read-your-writes
  • trees, sortable lists, polymorphic relations, window functions and streaming
  • views, functions, triggers and extension packs
  • OpenTelemetry and query observers
  • okm pull to start from an existing database, and an official GitHub Action that posts the migration plan on your PR
  • five drivers, and zero runtime dependencies

What's next

OKModel has a public roadmap, and the next trains are already planned:

  • 0.10: okm init to bootstrap OKModel in any project (monorepos included), faster CI, and a first agent surface: stable --json output on every CLI command, version-pinned docs inside the package, and llms.txt.
  • 0.11: the SQLite family (SQLite, libSQL, Turso, Cloudflare D1), plus a read-only MCP server so coding agents can understand your schema safely.
  • 0.12: MySQL and MariaDB.
  • 0.13: write and query observers on every dialect, transaction-local session settings, and custom RLS policies with table({ policies }).
  • Later: sharding and schema-per-tenant, then public, reproducible benchmarks against the other TypeScript ORMs, with the method, the raw data, and the tasks we lose included.

Try it in five minutes 

bun add okmodel
Enter fullscreen mode Exit fullscreen mode

Docs and the interactive demo: okm.omqkhafi.dev
Source: github.com/omqkhafi/okmodel

OKModel is pre-1.0 and growing with every release. Build something with it, try to break it, and tell me what you find.

Top comments (0)