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.

Depth for schema, queries, transactions, raw SQL, migrations, search and seeding. 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.number().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.number().min(0), sold: t.number().default(0) })
export const tickets = table("tickets", { id: t.id(), showId: t.ref(shows), email: t.string(), qty: t.number().min(1) })

Storage: id, string, enum, timestamp, ref, json are TEXT, number REAL, boolean INTEGER 0/1, 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.
t.ref(table) is a foreign key (enforced): delete the 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 } }, 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) ?? "?" }))
}

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 }).
undefined in a filter is ignored, null means IS NULL, ne keeps 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 needs 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 { db, task } 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 hit = await db.sql<{ id: string }>`UPDATE shows SET sold = sold + ${qty} WHERE id = ${showId} AND sold + ${qty} <= seats RETURNING id`
  if (hit.length === 0) return false
  await db.tickets.insert({ showId, email, qty })
  return true
}

// All or nothing; the queued task 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}`
    await task.run("notify-like", { postId, userId })
    return true
  })
}

A transaction runs alone (serializable), within 100 ms, without fetch; a throw rolls it back. Keep it short: compute first, then write.
A unique constraint is the simplest guard: the second insert throws; catch it and answer 409.

db.sql<Row> is a tagged template: ${value} is always a bound parameter (never string-build SQL). 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; each query has 50 ms in a request (250 ms in a task).

Migrations

schema.ts is the migration: cubek dev and cubek deploy --prod diff it. Automatic: new tables, nullable or defaulted columns, indexes, unique, search, relaxed checks. 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 and inspecting

cubek sql --dev "SELECT id, title FROM posts LIMIT 5"
cubek sql --dev --write "INSERT OR IGNORE INTO users (id, email, name, createdAt) VALUES ('u-demo', 'demo@example.com', 'Demo', '2026-01-01T00:00:00.000Z')"
cubek sql "SELECT COUNT(*) AS n FROM users"          # production (read-only without --write)

Fixed ids + INSERT OR IGNORE make seeds rerunnable. Previews start from a copy of production's database.