gem-hunter

ZenDB

Installation

npm install @b9g/zen zod

# In Node, install a driver (choose one):
npm install better-sqlite3  # for SQLite
npm install postgres        # for PostgreSQL
npm install mysql2          # for MySQL

# Bun has a driver which uses Bun.SQL
bun install @b9g/zen zod

import {Database} from "@b9g/zen";
import BunDriver from "@b9g/zen/bun";
import SQLiteDriver from "@b9g/zen/sqlite";
import PostgresDriver from "@b9g/zen/postgres";
import MySQLDriver from "@b9g/zen/mysql";

// Each driver implements the `Driver` interface
// and is a separate module in the package

const sqliteDriver = new SQLiteDriver("file:app.db");
const postgresDriver = new PostgresDriver("postgresql://localhost/mydb");
const mySQLDriver = new MySQLDriver("mysql://localhost/mydb");

// Bun auto-detects dialect from the connection URL.
const bunDriver = new BunDriver("sqlite://app.db");

const db = new Database(bunDriver);

Quick Start

import {z, table, Database} from "@b9g/zen";
import SQLiteDriver from "@b9g/zen/sqlite";

const driver = new SQLiteDriver("file:app.db");

// 1. Define tables
let Users = table("users", {
  id: z.string().uuid().db.primary().db.auto(),
  email: z.string().email().db.unique(),
  name: z.string(),
});

const Posts = table("posts", {
  id: z.string().uuid().db.primary().db.auto(),
  authorId: z.string().uuid().db.references(Users, "author"),
  title: z.string(),
  published: z.boolean().db.inserted(() => false),
});

// 2. Create database with migrations
const db = new Database(driver);

db.addEventListener("upgradeneeded", (e) => {
  e.waitUntil((async () => {
    if (e.oldVersion < 1) {
      // Create tables (includes PK/unique/FK on new tables)
      await db.ensureTable(Users);
      await db.ensureTable(Posts);
      // For existing tables when adding FKs/uniques later, call ensureConstraints()
    }
    if (e.oldVersion < 2) {
      // Evolve schema: add avatar column (safe, additive)
      Users = table("users", {
        id: z.string().uuid().db.primary().db.auto(),
        email: z.string().email().db.unique(),
        name: z.string(),
        avatar: z.string().optional(), // new field
      });
      await db.ensureTable(Users); // adds missing columns/indexes
      await db.ensureConstraints(Users); // applies new constraints on existing data
    }
  })());
});

await db.open(2);

// 3. Insert with validation (id auto-generated)
const user = await db.insert(Users, {
  email: "alice@example.com",
  name: "Alice",
});

// 4. Query with normalization
const posts = await db.all([Posts, Users])`
  JOIN "users" ON ${Users.on(Posts)}
  WHERE ${Posts.cols.published} = ${true}
`;

const author = posts[0].author;
author?.name; // "Alice" — resolved from JOIN
author === posts[1].author; // true — same instance

// 5. Get by primary key
const post = await db.get(Posts, posts[0].id);

// 6. Update
await db.update(Users, {name: "Alice Smith"}, user.id);

Why ZenDB?

What Zen is not:

Safety

Table Definitions

import {z, table} from "@b9g/zen";
import type {Row} from "@b9g/zen";

const Users = table("users", {
  id: z.string().uuid().db.primary().db.auto(),
  email: z.string().email().db.unique(),
  name: z.string().max(100),
  role: z.enum(["user", "admin"]).db.inserted(() => "user"),
  createdAt: z.date().db.auto(),
});

const Posts = table("posts", {
  id: z.string().uuid().db.primary().db.auto(),
  title: z.string(),
  content: z.string().optional(),
  authorId: z.string().uuid().db.references(Users, "author", {onDelete: "cascade"}),
  published: z.boolean().db.inserted(() => false),
});

import {z} from "zod";
import {extendZod} from "@b9g/zen";
extendZod(z);
// .db is available on all Zod types

const Users = table("users", {
  id: z.string().uuid().db.primary().db.auto(),  // UUID generated on insert
  name: z.string(),
  createdAt: z.date().db.auto(),                 // NOW on insert
});

// id and createdAt are optional - auto-generated if not provided
const user = await db.insert(Users, {name: "Alice"});
user.id; // "550e8400-e29b-41d4-a716-446655440000"
user.createdAt; // 2024-01-15T10:30:00.000Z

import {z, table, NOW} from "@b9g/zen";

const Posts = table("posts", {
  id: z.string().uuid().db.primary().db.auto(),
  title: z.string(),
  // JS function — runs client-side
  slug: z.string().db.inserted(() => generateSlug()),
  // SQL builtin — runs database-side
  createdAt: z.date().db.inserted(NOW),
  updatedAt: z.date().db.upserted(NOW),  // set on insert AND update
  viewCount: z.number().db.inserted(() => 0).db.updated(() => 0), // reset on update
});

const Settings = table("settings", {
  id: z.string().uuid().db.primary().db.auto(),
  config: z.object({theme: z.string(), fontSize: z.number()}),
  tags: z.array(z.string()),
});

// On insert: config and tags are JSON.stringify'd
const settings = await db.insert(Settings, {
  config: {theme: "dark", fontSize: 14},
  tags: ["admin", "premium"],
});
// Stored as: config='{"theme":"dark","fontSize":14}', tags='["admin","premium"]'

// On read: JSON strings are parsed back to objects/arrays
settings.config.theme; // "dark" (object, not string)
settings.tags[0]; // "admin" (array, not string)

const Articles = table("articles", {
  id: z.string().db.primary(),
  // Store array as CSV instead of JSON
  tags: z.array(z.string())
    .db.encode((arr) => arr.join(","))
    .db.decode((str: string) => str.split(","))
    .db.type("TEXT"), // Required: explicit column type for DDL
});

await db.insert(Articles, {id: "a1", tags: ["news", "tech", "featured"]});
// Stored as: tags='news,tech,featured' (not '["news","tech","featured"]')

const Users = table("users", {
  id: z.string().uuid().db.primary().db.auto(),
  name: z.string(),
  deletedAt: z.date().nullable().db.softDelete(),
});

const userId = "u1";

// Soft delete a record (sets deletedAt to current timestamp)
await db.softDelete(Users, userId);

// Hard delete (permanent removal)
await db.delete(Users, userId);

// Filter out soft-deleted records in queries
const activeUsers = await db.all(Users)`
  WHERE NOT ${Users.deleted()}
`;

// Or use the .active view (auto-generated, read-only)
const activeUsers = await db.all(Users.active)``;

// JOINs with .active automatically filter deleted rows
const posts = await db.all([Posts, Users.active])`
  JOIN "users_active" ON ${Users.active.cols.id} = ${Posts.cols.authorId}
`;

const Posts = table("posts", {
  id: z.string().db.primary(),
  authorId: z.string(),
  slug: z.string(),
  createdAt: z.date(),
}, {
  indexes: [["authorId", "createdAt"]],
  unique: [["authorId", "slug"]],  // unique together
});

const OrderProducts = table("order_products", {
  orderId: z.string().uuid(),
  productId: z.string().uuid(),
  // ... compound primary key
});

const OrderItems = table("order_items", {
  id: z.string().uuid().db.primary(),
  orderId: z.string().uuid(),
  productId: z.string().uuid(),
  quantity: z.number(),
}, {
  references: [{
    fields: ["orderId", "productId"],
    table: OrderProducts,
    as: "orderProduct",
  }],
});

const Posts = table("posts", {
  id: z.string().db.primary(),
  title: z.string(),
  authorId: z.string().db.references(Users, "author", {
    reverseAs: "posts"
  }),
}, {
  derive: {
    // Pure functions only (no I/O, no side effects)
    titleUpper: (post) => post.title.toUpperCase(),
    // Traverse relationships (requires JOIN in query)
    tags: (post) => post.postTags?.map(pt => pt.tag?.name) ?? [],
  }
});

type Post = Row<typeof Posts>;
// Post includes: id, title, authorId, titleUpper, tags

const posts = await db.all([Posts, Users, PostTags, Tags])`
  JOIN ${Users} ON ${Users.on(Posts)}
  LEFT JOIN ${PostTags} ON ${PostTags.cols.postId} = ${Posts.cols.id}
  LEFT JOIN ${Tags} ON ${Tags.on(PostTags)}
`;

const post = posts[0];
post.titleUpper;  // "HELLO WORLD" — typed as string
post.tags; // ["javascript", "typescript"] — traverses relationships
Object.keys(post); // ["id", "title", "authorId", "author"] (no derived props)
JSON.stringify(post); // Excludes derived properties (non-enumerable)

const UserSummaries = Users.pick("id", "name");
const posts = await db.all([Posts, UserSummaries])`
  JOIN ${UserSummaries} ON ${UserSummaries.on(Posts)}
`;
// posts[0].author has only id and name

Views

import {z, table, view} from "@b9g/zen";

const Users = table("users", {
  id: z.string().db.primary(),
  name: z.string(),
  role: z.enum(["user", "admin"]),
  deletedAt: z.date().nullable().db.softDelete(),
});

// Define views with explicit names
const ActiveUsers = view("active_users", Users)`
  WHERE ${Users.cols.deletedAt} IS NULL
`;

const AdminUsers = view("admin_users", Users)`
  WHERE ${Users.cols.role} = ${"admin"}
`;

// Query from views (same API as tables)
const admins = await db.all(AdminUsers)``;
const admin = await db.get(AdminUsers, "u1");

// Views are read-only — mutations throw errors
await db.insert(AdminUsers, {...});  // ✗ Error
await db.update(AdminUsers, {...});  // ✗ Error
await db.delete(AdminUsers, "u1");   // ✗ Error

// Equivalent to: view("users_active", Users)`WHERE deletedAt IS NULL`
const activeUsers = await db.all(Users.active)``;

const posts = await db.all([Posts, AdminUsers])`
  JOIN "admin_users" ON ${AdminUsers.on(Posts)}
`;
posts[0].author?.role; // "admin"

Queries

const title = "Hello";
const postId = "p1";
const userId = "u1";

// Single table query
const posts = await db.all(Posts)`WHERE published = ${true}`;

// Multi-table with joins — pass array
const posts = await db.all([Posts, Users])`
  JOIN "users" ON ${Users.on(Posts)}
  WHERE ${Posts.cols.published} = ${true}
`;

// Get single entity
const post = await db.get(Posts)`WHERE ${Posts.cols.title} = ${title}`;

// Get by primary key (convenience)
const post = await db.get(Posts, postId);

// Raw queries (no normalization)
const counts = await db.query<{count: number}>`
  SELECT COUNT(*) as count FROM ${Posts} WHERE ${Posts.cols.authorId} = ${userId}
`;

// Execute statements. Index expressions take a bare column name, so use
// ident() rather than cols, which is table-qualified.
await db.exec`CREATE INDEX idx_posts_author ON ${Posts}(${ident("authorId")})`;

// Single value
const count = await db.val<number>`SELECT COUNT(*) FROM ${Posts}`;

CRUD Helpers

// Insert with Zod validation (uses RETURNING to get actual row)
const user = await db.insert(Users, {
  email: "alice@example.com",
  name: "Alice",
});
// Returns actual row from DB, including auto-generated id and DB-computed defaults
const userId = user.id;

// Update by primary key (uses RETURNING)
const updated = await db.update(Users, {name: "Bob"}, userId);

// Delete by primary key
await db.delete(Users, userId);

// Soft delete (sets deletedAt timestamp, requires softDelete() field)
await db.softDelete(Users, userId);

Fragment Helpers

const postId = "p1";
const rows = [
  {id: "p1", title: "Hello", published: true},
  {id: "p2", title: "World", published: false},
];

// UPDATE with set()
await db.exec`
  UPDATE ${Posts}
  SET ${Posts.set({title: "New Title", published: true})}
  WHERE ${Posts.cols.id} = ${postId}
`;
// → UPDATE "posts" SET "title" = ?, "published" = ? WHERE "posts"."id" = ?

// JOIN with on()
const posts = await db.all([Posts, Users])`
  JOIN "users" ON ${Users.on(Posts)}
  WHERE ${Posts.cols.published} = ${true}
`;
// → JOIN "users" ON "users"."id" = "posts"."authorId"

// Bulk INSERT with values()
await db.exec`
  INSERT INTO ${Posts} ${Posts.values(rows)}
`;
// → INSERT INTO "posts" ("id", "title", "published") VALUES (?, ?, ?), (?, ?, ?)

// Qualified column names with cols
const posts = await db.all([Posts, Users])`
  JOIN "users" ON ${Users.on(Posts)}
  ORDER BY ${Posts.cols.title} DESC
`;
// → ORDER BY "posts"."title" DESC

// Safe IN clause with in()
const postIds = ["id1", "id2", "id3"];
const posts = await db.all(Posts)`WHERE ${Posts.in("id", postIds)}`;
// → WHERE "posts"."id" IN (?, ?, ?)

// Empty arrays handled correctly
const posts = await db.all(Posts)`WHERE ${Posts.in("id", [])}`;
// → WHERE 1 = 0

Transactions

await db.transaction(async (tx) => {
  const user = await tx.insert(Users, {
    email: "alice@example.com",
    name: "Alice",
  });
  await tx.insert(Posts, {
    authorId: user.id,
    title: "Hello",
    published: true,
  });
  // Commits on success, rollbacks on error
});

// Returns values
const user = await db.transaction(async (tx) => {
  return await tx.insert(Users, {
    email: "bob@example.com",
    name: "Bob",
  });
});

Migrations

db.addEventListener("upgradeneeded", (e) => {
  e.waitUntil((async () => {
    if (e.oldVersion < 1) {
      await db.ensureTable(Users);
      await db.ensureTable(Posts);
    }
    if (e.oldVersion < 2) {
      // Add a new column - just update the schema and call ensureTable again
      await db.ensureTable(Posts); // Adds missing "views" column
    }
    if (e.oldVersion < 3) {
      // Add constraints after data cleanup
      await db.ensureConstraints(Posts);
    }
  })());
});

await db.open(3); // Opens at version 3, fires upgradeneeded if needed

Safe Migration Helpers

// Add a new column - update schema and call ensureTable
const Posts = table("posts", {
  id: z.string().db.primary(),
  title: z.string(),
  views: z.number().db.inserted(() => 0), // NEW - add to schema
});

if (e.oldVersion < 2) {
  await db.ensureTable(Posts); // Adds missing columns from schema
}
// → ALTER TABLE "posts" ADD COLUMN "views" REAL DEFAULT 0

// Add indexes - defined in schema, applied by ensureTable
const Posts = table("posts", {
  id: z.string().db.primary(),
  title: z.string().db.index(), // NEW - add index
  views: z.number().db.inserted(() => 0),
});

if (e.oldVersion < 3) {
  await db.ensureTable(Posts); // Adds missing indexes
}
// → CREATE INDEX IF NOT EXISTS "idx_posts_title" ON "posts"("title")

// Safe column rename (additive, non-destructive)
const Users = table("users", {
  id: z.string().db.primary(),
  email: z.string().email(), // Keep old column
  emailAddress: z.string().email(), // NEW - add new column
});

if (e.oldVersion < 4) {
  await db.ensureTable(Users); // Adds emailAddress column
  await db.copyColumn(Users, "email", "emailAddress"); // Copy data
  // Keep old "email" column for backwards compat
  // Drop it in a later migration if needed (manual SQL)
}
// → UPDATE "users" SET "emailAddress" = "email" WHERE "emailAddress" IS NULL

// Manual destructive operation
if (e.oldVersion < 5) {
  await db.exec`ALTER TABLE ${Users} DROP COLUMN deprecated_field`;
}

Default column types

Entity Normalization

Forward References (belongs-to)

const Posts = table("posts", {
  id: z.string().db.primary(),
  authorId: z.string().db.references(Users, "author"),
  title: z.string(),
});

const posts = await db.all([Posts, Users])`
  JOIN "users" ON ${Users.on(Posts)}
`;
posts[0].author?.name; // typed as string | undefined

Reverse References (has-many)

const Posts = table("posts", {
  id: z.string().db.primary(),
  authorId: z.string().db.references(Users, "author", {
    reverseAs: "posts" // Populate author.posts = Post[]
  }),
  title: z.string(),
});

const posts = await db.all([Posts, Users])`
  JOIN "users" ON ${Users.on(Posts)}
`;
posts[0].author?.posts; // [{id: "p1", ...}, {id: "p2", ...}]

Many-to-Many

const Posts = table("posts", {
  id: z.string().db.primary(),
  title: z.string(),
});

const Tags = table("tags", {
  id: z.string().db.primary(),
  name: z.string(),
});

const PostTags = table("post_tags", {
  id: z.string().db.primary(),
  postId: z.string().db.references(Posts, "post", {reverseAs: "postTags"}),
  tagId: z.string().db.references(Tags, "tag", {reverseAs: "postTags"}),
});

const postId = "p1";

const results = await db.all([PostTags, Posts, Tags])`
  JOIN "posts" ON ${Posts.on(PostTags)}
  JOIN "tags" ON ${Tags.on(PostTags)}
  WHERE ${Posts.cols.id} = ${postId}
`;

// Access through join table:
const tags = results.map((pt) => pt.tag);

// Or access via reverse relationship:
const post = results[0].post;
post?.postTags?.forEach((pt) => console.log(pt.tag?.name));

Serialization Rules

const posts = await db.all([Posts, Users])`
  JOIN "users" ON ${Users.on(Posts)}
`;

const post = posts[0];

// Forward references (belongs-to): enumerable and immutable
Object.keys(post);    // ["id", "title", "authorId", "author"]
JSON.stringify(post); // Includes "author"

// Reverse references (has-many): non-enumerable and immutable
const author = post.author;
if (author) {
  Object.keys(author);    // ["id", "name"] (no "posts")
  JSON.stringify(author); // Excludes "posts" (prevents circular JSON)
  author.posts;           // Accessible (just hidden from enumeration)

// Circular references are safe:
JSON.stringify(post); // No error
// {
//   "id": "p1",
//   "title": "Hello",
//   "authorId": "u1",
//   "author": {"id": "u1", "name": "Alice"}  // No "posts" = no cycle
// }

  // Explicit inclusion when needed:
  const explicit = {...author, posts: author.posts};
  JSON.stringify(explicit);  // Now includes posts
}

Type Inference

type User = Row<typeof Users>;       // Full row type (after read)
type NewUser = Insert<typeof Users>; // Insert type (respects defaults/.db.auto())

Field Metadata

const fields = Users.fields();

// Preferred: raw schema and db metadata
fields.email.name;              // "email"
fields.email.schema;            // ZodString - use Zod APIs (isOptional(), etc.)
fields.email.db;                // FieldDBMeta object
fields.email.db.unique;         // true
fields.email.db.primaryKey;     // undefined

const pkName = Users.meta.primary;    // "id" (field name)
const pkFragment = Users.primary;     // SQLTemplate: "users"."id"
const refs = Posts.meta.references;   // [{fieldName: "authorId", table: Users, as: "author"}]

// Relation navigation (forward and reverse)
Posts.relations().author.table;       // Users table
Posts.relations().author.fields();    // Users field metadata
Users.relations().posts.table;        // Posts table (if reverseAs: "posts" defined)

// Chain navigation through relationships
Users.relations().posts.fields().author.fields().email;  // Back to Users.email field

Performance

Driver Interface

interface Driver {
  // Query methods - build SQL with native placeholders (? or $1, $2, ...)
  all<T>(strings: TemplateStringsArray, values: unknown[]): Promise<T[]>;
  get<T>(strings: TemplateStringsArray, values: unknown[]): Promise<T | null>;
  run(strings: TemplateStringsArray, values: unknown[]): Promise<number>;
  val<T>(strings: TemplateStringsArray, values: unknown[]): Promise<T | null>;

  // Connection management
  close(): Promise<void>;
  transaction<T>(fn: (tx: Driver) => Promise<T>): Promise<T>;

  // Capabilities
  readonly supportsReturning: boolean;

  // Optional
  withMigrationLock?<T>(fn: () => Promise<T>): Promise<T>;
}

Error Handling

import {
  DatabaseError,
  ValidationError,
  ConstraintViolationError,
  NotFoundError,
  isDatabaseError,
  hasErrorCode
} from "@b9g/zen";

// Validation errors (Zod/Standard Schema)
try {
  await db.insert(Users, { email: "not-an-email" });
} catch (e) {
  if (hasErrorCode(e, "VALIDATION_ERROR")) {
    console.log(e.fieldErrors); // {email: ["Invalid email"]}
  }
}

// Constraint violations (database-level)
try {
  await db.insert(Users, { id: "1", email: "duplicate@example.com" });
} catch (e) {
  if (e instanceof ConstraintViolationError) {
    console.log(e.kind);        // "unique"
    console.log(e.constraint);  // "users_email_unique"
    console.log(e.table);       // "users"
    console.log(e.column);      // "email"
  }
}

// Transaction errors (rolled back automatically)
await db.transaction(async (tx) => {
  await tx.insert(Users, newUser);
  await tx.insert(Posts, newPost); // Fails → transaction rolled back
});