Sub-skills

Database in depth

For your agentCopy this and give it to the AI you're building with.

Run `cubek skill`, then `cubek skill database`, read all of both and follow them exactly (no CLI: download https://cubek.dev/SKILL.md and https://cubek.dev/skills/database.md verbatim with curl; don't summarize them).
Run `cubek check` after every edit until it is clean.

The core Skill's "Schema and db" section comes first.

Schema

// schema.ts
import { table, t } from "cubek"

export const users = table("users", {
  id: t.id(),
  email: t.string().unique({ nocase: true }),
  name: t.string().max(80),
  createdAt: t.timestamp().default("now"),
})

export const posts = table("posts", {
  id: t.id(),
  authorId: t.ref(users),
  title: t.string().min(1).max(200),
  body: t.string(),
  tags: t.json<string[]>().nullable(),
  status: t.enum(["draft", "published"]).default("draft"),
  likes: t.int().default(0).min(0),
  publishedAt: t.timestamp().nullable(),
  updatedAt: t.timestamp().default("now"),
}).index("status", "publishedAt").index("authorId", "updatedAt").search("title", "body")

export const likes = table("likes", { id: t.id(), postId: t.ref(posts), userId: t.ref(users) }).unique("postId", "userId")

export const shows = table("shows", { id: t.id(), name: t.string(), seats: t.int().min(0), sold: t.int().default(0) })
export const tickets = table("tickets", { id: t.id(), showId: t.ref(shows), email: t.string(), qty: t.int().min(1) })
export const stock = table("stock", { id: t.id(), sku: t.string().unique(), qty: t.int() })

Storage: id, string, enum, timestamp, ref, json are TEXT, number REAL, int and boolean (0/1) INTEGER, bytes BLOB. Timestamps are YYYY-MM-DDTHH:MM:SS.sssZ (UTC).
One index per query shape: equality columns first, then the sort column (.index("status", "publishedAt") serves where: { status }, orderBy: { publishedAt: "desc" }). cubek dev logs slow queries and the missing index; table("plans", {...}, { small: true }) silences scan warnings.
t.ref(table) is an enforced foreign key: delete children first.

Queries

// src/lib/posts.ts
import { db, type Row, type Where } from "cubek"
import type { posts } from "../../schema"

export type Post = Row<typeof posts>

// Pages of 20, newest first; `id` breaks ties so pages never overlap.
export async function page(n: number, authorId: string | null): Promise<{ rows: Post[]; total: number }> {
  let where: Where<Post> = { status: "published" }
  if (authorId !== null) where = { and: [where, { authorId }] }
  const rows = await db.posts.find({ where, orderBy: [{ publishedAt: "desc" }, { id: "desc" }], limit: 20, offset: (n - 1) * 20 })
  return { rows, total: await db.posts.count({ where }) }
}

// Full-text search with a filter and the hit count for "N results".
export async function searchPosts(q: string): Promise<{ rows: Post[]; total: number }> {
  const where: Where<Post> = { status: "published" }
  return { rows: await db.posts.search(q, { where, limit: 20 }), total: await db.posts.count({ where, search: q }) }
}

// "Join": two queries, then a map.
export async function withAuthors(rows: Post[]): Promise<{ post: Post; author: string }[]> {
  const ids = rows.map((p) => p.authorId)
  const users = await db.users.find({ where: { id: { in: ids } }, select: ["id", "name"], limit: ids.length })
  const names = new Map<string, string>()
  for (const u of users) names.set(u.id, u.name)
  return rows.map((post) => ({ post, author: names.get(post.authorId) ?? "?" }))
}

select: [...] returns only those columns (no password hash).
find without limit returns at most 1,000 rows (a check warning); any query at most 10,000. Paginate with limit/offset or a cursor (publishedAt: { lt: last.publishedAt }).
Nulls follow JS: undefined in a filter is ignored, field: null is IS NULL, { ne: null } IS NOT NULL (or isNull: false), { ne: "x" } keeps NULL rows, in: [null, "x"] matches NULL; comparisons (gt...) never do. A nullable .unique() column allows many NULLs.
Text contains/startsWith/endsWith are literal; like takes %, _ and escape: "\\". On a t.json<string[]>() column, contains matches an element: { tags: { contains: q } }.
Comparing two columns: db.sql.
Search: every word matches as a prefix, case and accents ignored; no operators; best first. The table needs .search(...).

Writes that must not race

Read-then-write in two statements can interleave with another request. Make the write itself conditional (one statement), or use a transaction.

// src/lib/tickets.ts
import { crypto, db } from "cubek"

// No overselling: one atomic UPDATE; 0 rows changed = sold out.
export async function buy(showId: string, email: string, qty: number): Promise<boolean> {
  const r = await db.exec`UPDATE shows SET sold = sold + ${qty} WHERE id = ${showId} AND sold + ${qty} <= seats`
  if (r.changes === 0) return false
  await db.tickets.insert({ showId, email, qty })
  return true
}

// All or nothing (a `task.run` inside commits with it).
export async function like(postId: string, userId: string): Promise<boolean> {
  return await db.transaction(async (tx) => {
    const seen = await tx.likes.findOne({ where: { postId, userId } })
    if (seen !== null) return false
    await tx.likes.insert({ postId, userId })
    await tx.sql`UPDATE posts SET likes = likes + 1 WHERE id = ${postId}`
    return true
  })
}

// Upsert: one statement; the unique column picks insert or update.
export async function restock(sku: string, n: number): Promise<number> {
  const rows = await db.sql<{ qty: number }>`INSERT INTO stock (id, sku, qty) VALUES (${crypto.randomUUID()}, ${sku}, ${n}) ON CONFLICT (sku) DO UPDATE SET qty = qty + excluded.qty RETURNING qty`
  return rows[0].qty
}

A transaction runs alone (serializable), within 100 ms, without fetch; a throw rolls it back. Compute first, then write.
Helpers take type Db (db or a transaction's tx); rows Row<typeof posts>.
A unique constraint is the simplest guard: the second insert throws; catch it and answer 409.
insert({ id, ... }) takes an explicit id: browser-made rows (crypto.randomUUID()) render at once; a retried POST cannot duplicate.
There is no upsert(): db.sql INSERT ... ON CONFLICT (col) DO UPDATE (pass id and every column without a default).
A single-row table (settings): fixed id "main", read get("main") ?? defaults, write with that upsert on id.

db.sql<Row> is a tagged template: ${value} is always a bound parameter (never string-build SQL); db.exec`UPDATE ...` runs a write and answers { changes }. Table names are the table("name") names, columns the schema keys (publishedAt), booleans 0/1, results unchecked (type them honestly). Compare timestamps with ISO strings:

// src/lib/stats.ts
import { db } from "cubek"

export async function perDay(days: number): Promise<{ day: string; n: number }[]> {
  const since = new Date(Date.now() - days * 86400000).toISOString()
  return await db.sql<{ day: string; n: number }>`SELECT substr(publishedAt, 1, 10) AS day, COUNT(*) AS n FROM posts WHERE status = 'published' AND publishedAt >= ${since} GROUP BY day ORDER BY day`
}

db.sql cannot reach _cubek_* tables; a query has 50 ms (250 in a task).

Derived values

const top = derived(async (a: { day: string }) => rank(await db.posts.find({ select: ["id"] }), a.day), { stale: "1m" }), await top.get({ day }): kept until a write to what it read (select: those columns; ignore: ["posts.likes"]). Only reads and computes (day in args); stale: old value meanwhile; peek, refresh. SQL cubek_slug/fold/hash; text.slug/fold.

Migrations

schema.ts is the migration (cubek dev, deploy --prod diff it; cubek schema prints it). It imports only "cubek". Automatic: new tables, nullable or defaulted columns, indexes, unique, search, relaxed checks, string ↔ json (json: rows must parse). Destructive (drop, change a type, a new required column, stricter check, removed enum value; a rename is drop + add): cubek deploy --prod --allow-destructive, after an automatic snapshot. To rename safely: add the new column nullable, copy (cubek sql --write "UPDATE posts SET title2 = title"), switch the code, drop the old later.

Seeding, bulk data, inspecting

cubek sql --dev "SELECT id, title FROM posts LIMIT 5"
cubek sql --dev -f seed.sql --write    # many statements, one transaction
cubek db import data/ --dev            # users.csv, posts.ndjson: a table per file
cubek db import old.sqlite --replace   # production; --table T; .csv/.ndjson
cubek sql "SELECT COUNT(*) FROM users"   # production, read-only

db import runs on the server, one transaction (~200k rows/s): CSV header = columns, empty = NULL; values take the column kinds, checked like inserts; any error imports nothing and names the rows. Fixed ids + INSERT OR IGNORE make seeds rerunnable. Big DELETE: sql -f (inline: 250 ms). Previews copy production's database at deploy.