Drizzle ORM & Generator

@kalamdb/orm is the Drizzle package for KalamDB. Use it when you want generated tables, typed queries, or table-level live helpers. It runs on top of @kalamdb/client.

Install it with the client and Drizzle:

1npm i @kalamdb/client @kalamdb/orm drizzle-orm

Driver quick start

Copy this after you have a generated schema.ts file.

1import { Auth, createClient } from '@kalamdb/client';2import { kalamDriver } from '@kalamdb/orm';3import { drizzle } from 'drizzle-orm/pg-proxy';4import { messages } from './schema';5 6const client = createClient({7  url: 'http://localhost:2900',8  authProvider: async () => Auth.basic('admin', 'AdminPass123!'),9});10 11const db = drizzle(kalamDriver(client));12const recent = await db.select().from(messages).limit(20);

The driver uses KalamDB SQL over HTTP and normalizes temporal values for Drizzle columns. timestamp(..., { mode: 'date' }), date(..., { mode: 'date' }), and time(...) can read KalamDB’s numeric wire values without app-side parsing.

Pass a full KalamDBClient to kalamDriver() so FILE uploads can route to queryWithFiles() when needed.

Table helpers

Use kTable instead of raw pgTable so the schema retains KalamDB table type metadata.

1import { bytes, embedding, file, kTable } from '@kalamdb/orm';2import { bigint, jsonb, text, timestamp, uuid } from 'drizzle-orm/pg-core';3 4export const docs = kTable.shared('app.docs', {5  id: bigint('id', { mode: 'bigint' }).primaryKey(),6  owner_id: uuid('owner_id').notNull(),7  title: text('title').notNull(),8  metadata: jsonb('metadata'),9  attachment: file('attachment'),10  raw_bytes: bytes('raw_bytes'),11  doc_embedding: embedding('doc_embedding', 384),12  created_at: timestamp('created_at', { mode: 'date' }).notNull(),13});

Available table helpers:

KalamDB-specific columns:

Generate schema.ts

Generate a Drizzle schema from a running KalamDB server:

1npx kalamdb-orm \2  --url http://localhost:2900 \3  --user admin \4  --password AdminPass123! \5  --namespace app \6  --include-system-columns \7  --out src/db/schema.ts

The generator reads SHOW TABLES, calls DESCRIBE <namespace.table> when column metadata is incomplete, and emits only the imports needed by the generated tables. It preserves primary keys, not-null constraints, system-column options, comments, table config constants such as chat_messagesConfig, and $inferSelect / $inferInsert aliases.

Options:

BIGINT defaults to text() because KalamDB transports Int64 values as strings to preserve precision. Choose --bigint-mode bigint when you want Drizzle to coerce values to native bigint, or --bigint-mode number only when values fit safely in JavaScript numbers.

Keep schema.ts fresh during local development

Add a generator script to your app so the command stays short:

1{2  "scripts": {3    "schema:gen": "kalamdb-orm --url http://localhost:2900 --user admin --password AdminPass123! --namespace app --out src/db/schema.ts"4  }5}

In a kalam.toml project, kalam dev watches schema.sql and reruns kalam schema gen when [dev].generate_types = true:

1kalam dev

See Local Development. The legacy kalam --watch-schema server-polling flag is deprecated.

PostgreSQL types and Drizzle mapping

KalamDB schemas use PostgreSQL-oriented SQL types. That is why standard typed columns come from drizzle-orm/pg-core: text, integer, boolean, numeric, timestamp, and related builders describe the server columns. kTable adds KalamDB table metadata. Dart and Rust use the same server type model, with their own language representations.

The table below separates recommended SQL declarations from the current generator’s output. A generated JavaScript value does not change the server column’s SQL type. See PostgreSQL data types and compatibility limits.

Generator distinctions to review

The server distinguishes JSON from JSONB, and TIMESTAMP from TIMESTAMPTZ. The current ORM generator does not preserve all of those distinctions: its Json descriptor emits jsonb(), and the type-name parser has no TIMESTAMPTZ case, so that spelling falls back to Text. Do not use the generated file as proof of a lossless PostgreSQL schema round trip.

For a hand-authored schema, Drizzle exposes json() versus jsonb() and timestamp(name, { withTimezone: true, mode: 'date' }). Check the server declaration and generated output before using those columns. DATETIME may still appear in legacy descriptors, but new SQL should use the appropriate PostgreSQL timestamp declaration. The bytes() helper represents SQL BYTEA; it does not remove the server’s current PGWire binary-result limitation.

Upsert and returning

Drizzle returning() and onConflictDoUpdate() compile to KalamDB SQL and run through the same driver:

1import { sql } from 'drizzle-orm';2 3const inserted = await db4  .insert(users)5  .values({ id: 1, name: 'Nader', age: 3 })6  .returning();7 8const updated = await db9  .insert(users)10  .values({ id: 1, name: 'Nader Updated', age: 5 })11  .onConflictDoUpdate({12    target: users.id,13    set: { name: sql`excluded.name`, age: sql`excluded.age` },14  })15  .returning();

FILE uploads through Drizzle

You do not wrap every query. Normal select, insert, update, and delete calls go through db directly. Only FILE bytes need multipart SQL, and the driver handles that when upload values appear in .values() or .set():

1import { kalamFile } from '@kalamdb/orm';2import { attachments } from './schema';3 4await db.insert(attachments).values({5  id: 'att_1',6  file_data: kalamFile('upload', selectedFile),7});8 9await db10  .insert(context_files)11  .values({12    path: 'notes/readme.md',13    file_ref: kalamFile('upload', uploadFile),14    updated_at: new Date(),15  })16  .onConflictDoUpdate({17    target: context_files.path,18    set: {19      file_ref: kalamFile('upload', uploadFile),20      updated_at: new Date(),21    },22  });

You can also pass a File or Blob directly to a generated file() column; the driver uses the filename (or upload for plain blobs) as the multipart field name.

kalamDriver() compiles the Drizzle builder, normalizes quoted identifiers and DEFAULT columns for KalamDB, replaces upload params with FILE("name"), and calls client.queryWithFiles() when bytes are present.

For raw SQL strings (no Drizzle builder), use queryWithFiles() from @kalamdb/orm or @kalamdb/client. Use compileQuery() when you need normalized SQL and params without executing.

See FILE Columns & Uploads for download URLs, FileRef, and progress callbacks.

Execute as a user

Agents and service workers can compile a Drizzle builder and run it through KalamDB’s EXECUTE AS USER path:

1import { executeAsUser } from '@kalamdb/orm';2 3await executeAsUser(4  client,5  db.insert(messages).values({6    room: 'main',7    role: 'assistant',8    author: 'KalamDB Copilot',9    sender_username: 'alice',10    content: 'Done.',11  }),12  'alice',13);

Only pass a user id that your service account is authorized to impersonate.

Live table helpers

Use ORM liveTable() for a typed live list outside React. In React, prefer useLiveQuery, which handles the component lifecycle. A normal db.select() runs once; it does not open a subscription.

Create the inbox table and copy or generate the typed messages schema as schema.ts. Then add:

1import type { KalamDBClient } from '@kalamdb/client';2import { liveTable } from '@kalamdb/orm';3import { asc, eq } from 'drizzle-orm';4import { messages } from './schema';5 6type Message = typeof messages.$inferSelect;7 8export async function watchRoom(9  client: KalamDBClient,10  room: string,11  render: (rows: Message[]) => void,12  reportError: (error: Error) => void,13) {14  try {15    return await liveTable(client, messages, render, {16      where: eq(messages.room, room),17      orderBy: asc(messages.id),18      limit: 100,19      onError: (event) => reportError(new Error(`[${event.code}] ${event.message}`)),20    });21  } catch (error) {22    // Startup failures reject; later subscription errors use onError above.23    reportError(error instanceof Error ? error : new Error(String(error)));24    throw error;25  }26}

Call await watchRoom(client, room, render, reportError) from your view/service startup and keep the returned stop function. Catch startup rejection at that boundary. Show loading before startup, replace the list in render, and show an empty state only after receiving an empty list. Keep an error visible if reportError runs; cached rows may be stale.

When the room changes, await stop() before opening the next room. Await it again on view disposal as appropriate; only the application owner disconnects the shared client. To verify the example, run the inbox write sequence with the same authenticated user.

where: eq(messages.room, room) compiles values through Drizzle; do not build predicates by interpolating user input into SQL. The ORM maps database column names to schema property names and decodes timestamp/date/time fields. Rows are inferred from messages.$inferSelect, so render row.body directly instead of calling a cell accessor.

orderBy and limit are local presentation controls. Unlike the direct client’s materializer limit, the ORM keeps the matching set behind the visible slice. Use a selective where for large tables; limit: 100 does not bound subscription memory. The underlying live SQL remains SELECT ... FROM ... WHERE ....

Use liveEvents() for protocol events. Use the recovery guidance before adding durable checkpoints or manual retry loops.

UI and example fit

Use this package in UI code or service code that wants typed tables. Worker code should import @kalamdb/consumer only when it needs topic consumption.

Copy This Project Shape

1src/2  db/3    schema.ts      # generated by kalamdb-orm4    client.ts      # createClient() + drizzle(kalamDriver(client))5  app/6    messages.ts    # Drizzle queries against generated tables

Live query projection and generated row types

liveTable(client, table, callback, { orderBy, limit }) and compileLiveTableDescriptor(table, options) use the same projection behavior: Drizzle columns are mapped to their public property names, then ordered, then limited. Date columns sort chronologically. The transport retains the full matching set so deleting a visible row can reveal the next row. Narrow the SQL filter to bound memory; a presentation limit is not a retention bound.

A disposed LiveQueryController is terminal. Create another controller to open a new view. Callbacks from obsolete subscriptions cannot update its state or forward stale errors/checkpoints. Custom-key live subscriptions accumulate initial batches rather than replacing earlier batches.

The SQL-first generator preserves declared primary keys and emits array column builders. Its built-in File shape aliases the SDK’s PgCatalogFile, and generated FILE columns narrow their read type to that reference shape. For uploads through these narrowed generated columns, use a Drizzle SQL FILE("placeholder") expression with queryWithFiles; hand-authored file() builders retain their convenience upload-input union. This keeps generated read models free of Blob/upload markers.

Raw live SQL normalization preserves literal whitespace. Its v1 clause recognition still accepts a restricted SELECT grammar; for expressions outside that subset, provide explicit subscription SQL and projection via createLiveQueryDescriptor or use a Drizzle descriptor. This is not a general SQL parser.