Typegres

• Methods on your tables = your API. No routes. No GraphQL. No auto-CRUD.
• Every Postgres/SQLite function, fully typed. All base types, every operator, nullability tracked at the type level.
• Clients compose typed SQL across the wire. Server validates the surface area you expose.
• Live by default..live() re-queries when the underlying data changes — pushed directly to clients.

typegres.com/play · demo.mp4 · docs/ARCHITECTURE.md

Usage

Developer preview — surface is settled, edges still being filed. Not yet recommended for production.

npm install typegres better-sqlite3 zod

import{typegres,expose,sql,Relation}from"typegres";import{doRpc,toRpc,newMessagePortRpcSession,typeShimStub}from"typegres/capnweb";import{SqliteDriver}from"typegres/drivers/sqlite";import{Integer,Text}from"typegres/sqlite";importzfrom"zod";constdb=typegres();db.connect(SqliteDriver.create());awaitdb.defaultConnection.execute(sql`CREATE TABLE users ( id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, team_token TEXT NOT NULL)`);awaitdb.defaultConnection.execute(sql`CREATE TABLE posts ( id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, body TEXT NOT NULL)`);classPostsextendsdb.Table("posts"){
@expose()id=Integer.column({nonNull: true,generated: true});
@expose()user_id=Integer.column({nonNull: true});
@expose()body=Text.column({nonNull: true});}classUsersextendsdb.Table("users"){// 1. Exposed columns: a client may select, filter and order by these.
@expose()id=Integer.column({nonNull: true,generated: true});
@expose()first_name=Text.column({nonNull: true});
@expose()last_name=Text.column({nonNull: true});// 2. No decorator: invisible. The server scopes on it below, and no// client query can select it, filter by it, or learn it exists.team_token=Text.column({nonNull: true});// 3. A "derived column": composes back into the typed query API, so a// client can group and order by it as if it were stored.// (Note, "derived columns" are just methods that return SQL fragments// that can reference `this`, the current row).// Compiles to: "users"."first_name" || ' ' || "users"."last_name"
@expose()fullName(){returnthis.first_name["||"](" ")["||"](this.last_name);}// 4. Relation: a reachability edge. Reaching a Users row reaches that// user's posts, and nothing else. (Note, relations are just methods// that return query builders referencing `this`)
@expose()posts(){returnRelation.has(this,Posts,{user_id: this.id});}}awaitUsers.insert({first_name: "Alice",last_name: "Smith",team_token: "t-acme"},{first_name: "Bob",last_name: "Jones",team_token: "t-acme"},).execute();// Query it directly on the server. `fullName()` works anywhere a column// does — select, where, orderBy:constnames=awaitUsers.from().select(({ users })=>({name: users.fullName()})).execute();console.log(names);// [ { name: 'Alice Smith' }, { name: 'Bob Jones' } ]// ── Now the same data model, reached by a client over RPC ──────────────// Nothing about the classes above changes. The `@expose` marks already// are the contract; all that's left is to hand out a root capability.// A third user, on a different team, plus some posts:awaitUsers.insert({first_name: "Carol",last_name: "Vance",team_token: "t-other",}).execute();awaitPosts.insert({user_id: 1,body: "one"},{user_id: 1,body: "two"},{user_id: 2,body: "three"},{user_id: 3,body: "not yours"},).execute();// The capability root — the entire surface a client can reach.classApi{// 5. Arguments are validated by a schema before the method ever runs.//// Hands back a builder over one team's posts, already joined to authors.// Everything the client writes is rooted here, so it can only narrow.
@expose(z.string())feedFor(teamToken: string){returnPosts.from().join(Users,({ posts, users })=>posts.user_id.eq(users.id)).where(({ users })=>users.team_token.eq(teamToken));}
@expose(z.string())team(teamToken: string){returnUsers.from().where(({ users })=>users.team_token.eq(teamToken));}}// Server and client, joined here by a MessagePort so this runs in one// process. `examples/chat` is the same two lines over a WebSocket.const{ port1, port2 }=newMessageChannel();newMessagePortRpcSession(port1,toRpc(newApi()));constapi=newMessagePortRpcSession<Api>(port2)asunknownasShimStub<Api>;// "Top posters" — written on the client, evaluated on the server. There is// no endpoint for this: the client composed the group-by, the aggregate and// the ordering itself, and grouped by `fullName()` — a method, used exactly// like a column. The team scoping is baked into the builder, so the// refinement can only narrow it, and Carol's row never appears.consttop=awaitdoRpc(api,(a)=>a.feedFor("t-acme").groupBy(({ users })=>[users.fullName()]).select(({ users, posts })=>({author: users.fullName(),posts: posts.id.count()})).orderBy(({ posts })=>[posts.id.count(),"desc"]).execute(),);console.log(top);// [ { author: 'Alice Smith', posts: 2 }, { author: 'Bob Jones', posts: 1 } ]// Rows are capabilities too. `.hydrate()` returns row objects rather than// plain data, and the relation is an edge you can walk from one — so// reaching Alice reaches Alice's posts, without a second endpoint.const[alice]=awaitdoRpc(api,(a)=>a.team("t-acme").where(({ users })=>users.first_name.eq("Alice")).hydrate(),);constalicesPosts=awaitdoRpc(alice,(u)=>u.posts().select(({ posts })=>({body: posts.body})).execute(),);console.log(alicesPosts);// [ { body: 'one' }, { body: 'two' } ]port1.close();port2.close();awaitdb.defaultConnection.close();

For a complete scaffold with migrations + codegen, see the examples. Or try it interactively at typegres.com/play.

Clients compose the queries

The class surface is the contract. A client composes against @expose-marked members, the closure is serialized, and the server evaluates it under a constrained interpreter — so a client can write any query it likes, and still reach only what you exposed.

The client-authored "top posters" query compiles to plain SQL, with fullName() expanded in both the select list and the GROUP BY — which is what "used exactly like a column" means in practice. The team scoping the client never wrote is in the WHERE:

SELECT (("users"."first_name"|| ?) ||"users"."last_name") as"author",
"count"("posts"."id") as"posts"FROM"posts"AS"posts"JOIN"users"AS"users"ON ("posts"."user_id"="users"."id")
WHERE ("users"."team_token"= ?)
GROUP BY (("users"."first_name"|| ?) ||"users"."last_name")
ORDER BY"count"("posts"."id") DESC-- params: [" ", "t-acme", " "]

Note what the client never does: name a table. Posts and Users are server-side identifiers, and a closure that references one won't serialize. A client starts from a capability it was handed and narrows — which is why feedFor's join and its team scoping can't be composed away.

Swap the MessagePort for newWebSocketRpcSession / newWorkersRpcResponse and the same code runs browser-to-server, with capabilities, promise pipelining, and live subscriptions — see examples/chat.

Backends

typegres() is a synchronous schema handle — no top-level await, so table classes can be declared at module load. The backend arrives separately via db.connect(driver), and the same schema classes and query builder run against any of them:

import{PgDriver}from"typegres/drivers/pg";// node-postgresimport{PgliteDriver}from"typegres/drivers/pglite";// in-process WASM Postgresimport{SqliteDriver}from"typegres/drivers/sqlite";// better-sqlite3import{DoSqliteDriver}from"typegres/drivers/do";// Cloudflare Durable Objectconstdb=typegres();// Pick the one you're running against — the driver names the backend, and// `db` takes its dialect from it:db.connect(PgDriver.create(process.env.DATABASE_URL!));db.connect(awaitPgliteDriver.create());// the one async driver: booting WASM is real I/Odb.connect(SqliteDriver.create("dev.db"));// omit the filename for :memory:db.connect(DoSqliteDriver.create(ctx.storage));// in the DO constructor — no npm peer needed

Drivers are imported explicitly from typegres/drivers/* so optional peers stay out of bundles that never use them — install only the one you need.

With exactly one connection (the Durable Object model), it's also the default: .execute() / .live() take no argument, and you can ignore what connect returns. Pass a Connection explicitly when you have several — read replicas, database-per-tenant, or a transaction's tx. Several is fine as long as they agree on dialect; the schema classes compiled against one.

How it works

1. Types codegen'd from the engine itself. Postgres from its catalog, SQLite from its docs — all base types, full method/operator coverage, nullability tracked at the type level.
2. Object-capability queries. Clients can only reach what you've exposed as @expose methods — columns, relations, scoped reads, mutations. The class surface is the contract; the schema underneath is free to move.
3. Object-capability RPC. The query builder ships to a constrained interpreter on the server; only @expose-marked methods reach evaluation.
4. Live queries. Tables opt in with db.Table("name", { live: true }). .live() watches the predicates your query depends on and re-yields when committed mutations would change the result — via a polling bus on Postgres, and synchronous mutation capture on SQLite.

Deeper dive in docs/ARCHITECTURE.md.

Examples

• examples/basic — Postgres/PGLite scaffold: migrations, tg generate codegen, relations (Relation.belongsTo / .has).
• examples/sqlite — the same scaffold on better-sqlite3.
• examples/chat — full-stack chat on a Cloudflare Durable Object: SQLite storage, Cap'n Web RPC from the browser, live queries pushed to clients, and facet-based capability security (the whole server is the schema — there are no routes).

Status

• Full pg type system + operator/function codegen
• SQLite dialect — typed function/operator surface from the same codegen; drivers for better-sqlite3 and Durable Objects
• Query builder (.select + .join + .where + .groupBy + .having + .orderBy + .limit)
• Mutations (.insert / .update / .delete / .returning)
• Subqueries, scalar/array aggregation
• Table codegen from live schema (tg generate, both dialects)
• Live queries — .live() returns a LiveQuery: an async iterable you can also .observe() for push delivery (including over RPC)
• Capability-rooted RPC — closures composed against @expose-marked classes/methods are serialized, evaluated server-side under a constrained interpreter, and streamed back
• Cap'n Web transport (typegres/capnweb) — capabilities, promises, and live subscriptions over a single WebSocket

Import Cap'n Web from typegres/capnweb, not from capnweb. It ships bundled until cloudflare/capnweb#162 lands, so installing capnweb yourself gives you a second copy whose RpcTarget/RpcStub fail instanceof against the bundled one.

Planned

• pg_notify-driven live updates (Postgres currently uses a single shared polling loop, not per-subscription)
• WAL-mode live updates for Postgres (currently uses an auxiliary table)
• Upstream the Cap'n Web integration (in-tree shim today; cloudflare/capnweb#162)

Development

Recommended: Nix the package manager

• direnv. The .envrc (use flake) auto-activates the pinned toolchain when you cd into the repo, and bin/startpg works out of the box. Without Nix, point DATABASE_URL at any local Postgres and skip startpg.

./bin/startpg # one-time dev Postgres socket (Nix)
npm install
npm run check # lint + typecheck + tests

License

MIT — see LICENSE.