Database

The app's backend database. Server code (*.server.ts, see Server functions) declares its tables as models and reads and writes them through a typed db: no SQL, no connection string, no migration file. Use it for data shared between devices and users; data that stays on this device goes in localStorage or a stored signal (Storage, Signals) or in files (desktop and iOS, Files & share). There is no SQL database on the device.

save data on the server, shared between users and devices
describe the tables and their fields (model, t)
point a record at another: a post's author, an author's posts
list rows with a filter and a sort, and load the next page
add, change and delete rows; count a view without reading the row first
save a row by its key: update it, or create it the first time (upsert)
tell the person why a write was refused, like an email that is already taken
write several rows together, all of them or none
fill an empty database with starting data (seed)
add a field to a model that already has rows

At a glance

TypeScript

// tasks.server.ts — runs on the app backend
const Task = model({
  title: t.string(),
  done: t.bool().default(false),
  createdAt: t.date().default("now"),
})
const db = defineDb({ Task })

export type TaskView = { id: number, title: string, done: boolean }

export async function listTasks(): Promise<TaskView[]> {
  return db.task.order("id", "desc").select({ id: true, title: true, done: true })
}

export async function addTask(title: string): Promise<TaskView> {
  const text = title.trim()
  if (!text) throw new ApiError(400, "A task needs a title")
  return db.task.insert({ title: text }, { select: { id: true, title: true, done: true } })   // the row as stored
}

export async function setDone(id: number, done: boolean) {
  await db.task.update(id, { done })
}

export async function clearDone() {
  return db.task.where({ done: true }).deleteMany()      // how many were removed
}

TypeScript

// main.ts — the app
import { addTask, clearDone, listTasks, setDone, type TaskView } from "./tasks.server"

const tasks = resource(() => listTasks())                  // asked now, again on tasks.reload()
const add = action((title: string) => addTask(title))      // add.error: why the server refused
const toggle = action((task: TaskView) => setDone(task.id, !task.done))
const clear = action(() => clearDone())
let input: UIInput

const submit = async () => {
  if (await add(input.value)) {                            // null when the call failed
    input.value = ""
    tasks.reload()
  }
}

const Row = (task: TaskView) =>
  UIButton(UIText(`${task.done ? "✓" : "○"}  ${task.title}`).style({ color: "white", fontSize: 18 }))
    .style({ justifyContent: "flex-start", px: 14, height: 44, borderRadius: 10, bgColor: "#2a3042" })
    .named(`task-${task.id}`)
    .onClick(async () => {
      await toggle(task)
      tasks.reload()
    })

UIScreen(
  UIRow(
    input = UIInput()
      .style({ flex: "1", height: 44, px: 12, borderRadius: 10, bgColor: "white", color: "black", placeholder: "New task" })
      .named("title")
      .onSubmit(submit),
    UIButton(UIText("Add").style({ color: "white" }))
      .style({ px: 18, height: 44, borderRadius: 10, bgColor: "#4f7cff" })
      .named("add")
      .onClick(submit),
  ).style({ gap: 8 }),
  UIText(() => add.error.value?.message ?? "").style({ color: "#ff8a80" }),   // "A task needs a title"
  UIColumn(() => dataOf(tasks.value, []).map(Row)).style({ gap: 8 }),
  UIButton(UIText("Clear done").style({ color: "#9aa3b8" }))
    .named("clear")
    .onClick(async () => {
      await clear()
      tasks.reload()
    }),
).style({ bgColor: "#1e2230", p: 20, pt: "safe-top", gap: 16 }).open()

model, t, defineDb and ref are globals of server files. db is server-only: the app reaches the data through the endpoints, never through db. resource and action are the screen's side of a call (Server functions).

Note

the app reaches the database only from a bundle that carries the backend's URL: what le.codes builds, and the local runs (lecodes dev, web dev, desktop run, render, test). A bundle from lecodes web build, lecodes compile or lecodes app sync (a shell's built-in app.js) has none: there every call fails with RpcError status 0 (Where it runs).

Models

A model is a table: its fields, written with t. defineDb takes every model of the project and answers the db that queries go through.

TypeScript

// db.server.ts — the project's one database; the other server files import `db` from here
const Author = model({
  name: t.string(),
  email: t.string().unique(),
  bio: t.string().optional(),
  role: t.enum("reader", "editor", "admin").default("reader"),
  joinedAt: t.date().default("now"),
  posts: t.many("Post", "author"),
})

const Post = model({
  title: t.string().index(),
  published: t.bool().default(false),
  views: t.int().default(0),
  tags: t.string().array().default([]),
  cover: t.struct({ url: t.string(), width: t.int(), height: t.int() }).optional(),
  author: t.one("Author").onDelete("cascade"),
  editor: t.one("Author").optional(),
  comments: t.many("Comment"),
})

const Comment = model({
  text: t.string(),
  post: t.one("Post").onDelete("cascade"),
  author: t.one("Author"),
})

export const db = defineDb({ Author, Post, Comment })      // db.author, db.post, db.comment

A model's name is its key in defineDb, written in PascalCase. Its table is the same name with a small first letter: Author → db.author, CartItem → db.cartItem.
Every model has an id: number, given by the database on insert, counting from 1. Don't declare it. For an id that can't be guessed, declare id: t.uuid(): it is generated on insert too, and is a string.
A project has one database: call defineDb once, with every model, and import that db into the other server files (import { db } from "./db.server"). A second defineDb fails the server's start (a project has one database, and defineDb is called 2 times …).
Query inside endpoints, never while the file loads: the database is opened and brought to the models after the server files have loaded (under lecodes dev a query before that throws the database is not open yet).

Fields

field	in TypeScript
`t.string()`	`string`
`t.int()`	`number`	a whole number, 64-bit
`t.float()`	`number`
`t.bool()`	`boolean`
`t.date()`	`number`	milliseconds since 1970, as `Date.now()` gives
`t.uuid()`	`string`
`t.enum("a", "b")`	`"a" \| "b"`
`t.json<T>()`	`T`	any JSON value; `T` is only the type you read it as
`t.struct({ … })`	an object of those fields	a nested object of the row, no id of its own
`t.file()`	`{ url, name, type, size, … }`	a file the app uploaded — Stored files
`t.image({ max? })`	`{ url, name, type, size, width, height }`	a picture: stored as WebP, at most `max` px on its longer side (2048) — Stored files

TypeScript

field.optional()            // may be null; left out of an insert, it is null
field.default(value)        // left out of an insert, it is `value`; on a date: .default("now")
field.unique()              // no two rows with the same value
field.unique("pair")        // the fields of the model sharing the name: no two rows with the same values together
field.index()               // makes filtering and sorting by the field fast on a big table
field.array()               // a list of values: t.string().array() is string[]
field.fulltext()            // a string field searched by words: where({ text: { $search: "fox" } })

A field with neither .optional() nor .default() is required in insert. That holds for an array too: declare t.string().array().default([]) to leave it out.

Relations

A relation points a row at rows of another model: a post at its author, an author at their posts.

TypeScript

t.one("Author")              // this row points at one Author
t.one("Author").optional()   // …or at none
t.many("Post")               // the rows of Post that point at this one — read-only
t.many("Post", "author")     // the same, naming the field of Post when it has two t.one("Author")
t.list("Post")               // an ordered list of Posts kept on this row; the same row may repeat

A relation names its model as a string. An unknown name is a type error on that field.
t.many stores nothing. It is the other end of a t.one, there to read by: an author's posts. To add a post to an author, insert a post with that author.
t.list is a list you arrange by hand — a playlist, a ranking. It is written from this side: { $connect: { id } } adds at the end, { $set: [{ id: 3 }, { id: 1 }] } replaces the list (and reorders it), { $remove: { id } } takes out every occurrence.

TypeScript

// comments.server.ts
import { db } from "./db.server"

export async function addComment(postId: number, authorId: number, text: string) {
  return db.comment.insert({ text, post: { id: postId }, author: { id: authorId } })
}

export async function authorWithPosts(id: number) {
  return db.author.where({ id }).select({ name: true, posts: { id: true, title: true } }).first()
}

What happens to the rows that point at a deleted one:

TypeScript

t.one("Post").onDelete("cascade")    // the post is deleted → this row is deleted with it
t.one("Author").onDelete("setNull")  // the author is deleted → this field becomes null (the default for an optional one)
t.one("Author").onDelete("restrict") // the author can't be deleted while this row points at it (the default for a required one)

With the models above, deleting an author deletes their posts and those posts' comments, clears editor on the posts they edited, and is refused while a comment of theirs remains — delete those first.

Reading

db.author is a query of every author. Each method answers a new query; await runs it — or an endpoint returns it, and the call answers the rows.

TypeScript

// posts.server.ts
import { db } from "./db.server"

export async function latestPosts() {
  return db.post.where({ published: true }).order("id", "desc").limit(20)   // rows
}

export async function authorByEmail(email: string) {
  return db.author.where({ email }).first()                                 // a row, or null
}

export async function publishedCount() {
  return db.post.where({ published: true }).count()                         // a number
}

TypeScript

query.where(filter)                    // repeated calls are ANDed
query.order(field, "asc" | "desc")     // default "asc"; a second .order() breaks the ties of the first
query.limit(n)
query.skip(n)
query.after(id)                        // the rows after this one in the current order — the next page
query.select(shape)                    // which fields to answer

await query                            // rows[]
await query.first()                    // row | null
await query.count()                    // number
await query.aggregate({ $sum: "views" })   // { sum }; also $count, $avg, $min, $max — null over no rows (a count: 0)

A query is a value: keep one and build on it (const published = db.post.where({ published: true })).

Note

a query is run by await (or by .then() / .catch() / .finally() on it, like a promise). A query nobody awaits runs nothing.

The next page: `after`

A long list comes a page at a time: the app asks for the rows after the last one it shows, by that row's id. after keeps the query's order; an empty page is the end.

TypeScript

// feed.server.ts
import { db } from "./db.server"

export async function feed(afterId?: number) {
  const page = db.post.where({ published: true }).order("id", "desc").limit(20).select({ id: true, title: true })
  return afterId === undefined ? page : page.after(afterId)
}

Reading related rows: `select`

Without select, a row is its id and its plain fields — values, enums, JSON and files. Relations and structs are not read: await db.post.first() answers { id: 1, title: "Hello", published: true, views: 10, tags: ["intro"] }, with no author, no comments and no cover. select names what to read, related rows included, and the row's type follows it:

TypeScript

// posts.server.ts
import { db } from "./db.server"

export async function postPage(id: number) {
  return db.post.where({ id }).select({
    title: true,
    cover: true,                                   // the whole struct
    author: { name: true },                        // a field of the related row
    comments: { text: true, author: { name: true } },
  }).first()
}
// { title: "Hello", cover: null, author: { name: "Ann" },
//   comments: [{ text: "Nice", author: { name: "Bob" } }, …] }

author: true reads the related row's id and plain fields.
Once you pass select, only what it names is read — add id: true if you need the id.

A relation takes a query of its model too — to filter, sort, cut or count the related rows:

TypeScript

export async function authorsWithTopPosts() {
  return db.author.select({
    name: true,
    posts: db.post.where({ published: true }).order("views", "desc").limit(3).select({ title: true }),
  })
  // [{ name: "Ann", posts: [{ title: "Hello" }] }, …]
}

export async function postsWithCommentCounts() {
  return db.post.select({ title: true, comments: db.comment.count() })
  // [{ title: "Hello", comments: { count: 2 } }, …]
}

Filters

Each line is a filter of some model — the fields are whatever that model has.

TypeScript

where({ published: true })                             // equal
where({ views: { $gte: 5, $lt: 100 } })                // $gt $gte $lt $lte — numbers and dates
where({ role: { $in: ["editor", "admin"] } })          // $in $notIn $ne
where({ title: { $startsWith: "He" } })                // $startsWith $includes — strings
where({ rating: null })                                // is null;  { $ne: null } — is not
where({ text: { $search: "fox" } })                    // a .fulltext() field, by words
where({ tags: ["intro", "news"] })                     // an array field: equal as a whole (no filter by one item)

where({ author: { id: userId } })                      // by the related row…
where({ author: { name: "Ann" } })                     // …by any of its fields
where({ editor: null })                                // no related row
where({ comments: { $some: { text: "Nice" } } })       // $some $every $none — rows with such comments
where({ comments: { $none: {} } })                     // rows with no comments at all

where({ cover: { width: { $gte: 800 } } })             // into a struct
where({ meta: { source: "import" } })                  // into a t.json() field…
where({ meta: { "stats.words": { $gt: 100 } } })       // …by a dot path

where({ $or: [{ views: { $gt: 100 } }, { pinned: true }] })    // $or $and $not

The conditions of one object are ANDed. So are the operators on one field.
$every is also true of a row with no related rows at all.
$or, $and and $not stand alone in their object. A condition next to one is a type error: put it inside, or in a second .where().

Adding, changing and deleting rows

insert adds a row, update and delete change or remove one by its id, and updateMany / deleteMany write every row a where matches.

TypeScript

// posts.server.ts
import { db } from "./db.server"

export async function createPost(authorId: number, title: string) {
  const { id } = await db.post.insert({ title, author: { id: authorId } })
  return id
}

export async function editPost(id: number, title: string, published: boolean) {
  await db.post.update(id, { title, published })
}

export async function deleteDrafts(authorId: number) {
  return db.post.where({ author: { id: authorId }, published: false }).deleteMany()   // how many
}

insert answers { id }. With { select } it answers the new row by that shape instead — its defaults, dates and files as stored, a relation by its sub-shape — so an endpoint that creates something returns what the database holds, not what it assembled: db.message.insert({ room, text, author }, { select: { id: true, text: true, author: { name: true } } }). That form is a plain promise: it has no place in $transaction.
A relation is written as the related row's { id }. What insert answered fits as it is: db.post.insert({ title, author: await db.author.insert({ … }) }).
update and delete of an id that isn't there throw: the app's call fails with a 404, the row does not exist. To write a row that may be gone without an error, go through a filter: where({ id }).deleteMany() answers 0.
deleteMany() without a where throws. To empty a table, say so: .where({}).deleteMany().
updateMany changes plain fields only — a relation is changed with update, row by row.

To count a view, add to the number where it is stored — no read first, and two calls at the same moment both count:

TypeScript

export async function viewPost(id: number) {
  await db.post.update(id, { views: { $increment: 1 } })
}

What update takes beside plain values, field by field:

TypeScript

views: { $increment: 1 }                  // a number, changed in place: no read needed, safe against two writers
tags: { $push: "news" }                   // an array: $push, $pushUnique, $remove, $set
editor: { $connect: { id: editorId } }    // a t.one: point it at another row…
editor: null                              // …or at none (an optional one)
cover: { $update: { width: 1024 } }       // a struct: change some of its fields

Update a row, or create it when it is missing: `upsert`

upsert updates the row a key names when it exists and creates it when it does not — the settings of a user, a like per user and post. The key is the id or the fields of ONE unique index (a .unique() field, or every field of a .unique("group")).

TypeScript

// authors.server.ts
import { db } from "./db.server"

export async function saveAuthor(email: string, name: string, bio: string) {
  return db.author.upsert({ email }, { name, bio })     // { id }: the same row on every call with this email
}

TypeScript

query.upsert(key, data, { onInsert?, select? })   // answers { id }, or with select the row as stored

data is in the insert form: plain values, { id } for a relation, no $ operators.
onInsert holds fields written only when the row is created.

When a write is refused

A value that breaks .unique(), a relation pointing at no row, a delete that onDelete("restrict") forbids, an update or delete of a row that is gone: the database throws, and the app receives it as it would an ApiError — a 409 for the first three, a 404 for the last — with the database's words: Author.email: the value is already taken (by the row with id 3). Nothing to check first and nothing to catch: the person sees those words. When they deserve other words, catch the error and throw your own ApiError:

TypeScript

// authors.server.ts
import { db } from "./db.server"

export async function addAuthor(name: string, email: string) {
  try {
    return await db.author.insert({ name, email })
  } catch (e) {
    if (e instanceof Error && e.message.startsWith("Author.email:")) throw new ApiError(409, "This email is taken")
    throw e
  }
}

In the app the call rejects with an RpcError of that status and message; an action keeps the message in its error (Server functions). Any other error reaches the app as a 500 Internal error; its stack goes to the server's console.

Several writes as one: `$transaction`

db.$transaction runs several writes as one: all of them are done, or — when one throws — none.

TypeScript

// authors.server.ts
import { db } from "./db.server"

export async function joinWithFirstPost(name: string, email: string, title: string) {
  const [author, post] = await db.$transaction([
    db.author.insert({ name, email }),
    db.post.insert({ title, author: { id: ref("0.id") } }),
  ])
  return { authorId: author.id, postId: post.id }
}

Pass the queries without await. The answers come back in the same order.
ref("0.id") is the id that item 0 will answer: how a later write uses a row an earlier one inserts.

Starting data: `seed`

A database starts empty. Fill it with the data it should start with — a menu, the services, the questions of a quiz — in the definition's seed, which the host runs ONCE on an empty database, wherever the database opens: under lecodes dev, in lecodes test (before every case, whose database starts empty), at the first deploy. A database with any row of your models is left as it is: the seed is the start, not a migration.

TypeScript

// db.server.ts
const Dish = model({ name: t.string(), price: t.int(), photo: t.image().optional() })
const Order = model({ dish: t.one("Dish"), qty: t.int() })

const menu = [
  { name: "Espresso", price: 190, photo: "images/espresso.jpg" },   // a picture of the project, by its path
  { name: "Latte", price: 290, photo: "images/latte.jpg" },
  { name: "Water", price: 90 },
]

export const db = defineDb({ Dish, Order }).seed(async (db) => {
  for (const dish of menu) await db.dish.insert(dish)
})

Inside the seed a file field takes a resource of the project by its path — the picture a row names in the design — and stores it the way an upload is stored: a t.image() field makes it fit, the row reads it back as a StoredImage. Outside the seed a string is never a file. A seed that fails stops the start with its own words (a path that is no file of the project, a row the model refuses); what it wrote before stands.

.seed() is part of the definition like .withAuth(): write it on defineDb(...) itself, once, in either order.

Changing the models: adding and removing fields

To add, remove or change a field of a model that already has rows, edit the model and save: under lecodes dev the database follows at once. A new field or model keeps the rows that are there; a removed one takes its data with it.

Note

a field added later is null in the rows inserted before it — also when it is required or has a default, whatever its type says. The default fills new rows only. Add a field to a table that has rows as .optional(), and read null as "not set".

TypeScript

// db.server.ts — `dueAt` came after the app had tasks
const Task = model({
  title: t.string(),
  done: t.bool().default(false),
  dueAt: t.date().optional(),                    // null in every row that was there before
})
export const db = defineDb({ Task })

export async function datedTasks() {
  return db.task.where({ dueAt: { $ne: null } }).order("dueAt")
}

Removing a field or a model deletes what it held: under lecodes dev on save; on le.codes the deploy waits until the author confirms it.
A field that changes its type can't be followed (String → Int). Under lecodes dev the database then starts empty and says so in the terminal; the old one is kept beside it as .lecodes/db-old. On le.codes the editor refuses it until the database is reset (its rows and files are deleted), and a publish is refused outright. Add a field under a new name instead and move the values in code.

Pitfalls

TypeScript

const post = db.post.where({ id }).first()          // ✗ a query, not a row — nothing ran
const post = await db.post.where({ id }).first()    // ✓

await db.post.insert({ title, author: authorId })               // ✗ type error: a relation is not a bare id
await db.post.insert({ title, author: { id: authorId } })       // ✓

const { id, views } = await db.post.insert({ title, author })                                  // ✗ insert answers { id } only
const { id, views } = await db.post.insert({ title, author }, { select: { id: true, views: true } })   // ✓ the row as stored

await db.post.select({ title: true }).where({ $or: [a, b], published: true })   // ✗ type error
await db.post.select({ title: true }).where({ $or: [a, b] }).where({ published: true })   // ✓

let views = 0                                        // ✗ a module variable is gone at the next restart
await db.post.update(id, { views: { $increment: 1 } })          // ✓

Database

At a glance

Models

Fields

Relations

Reading

The next page: after

Reading related rows: select

Filters

Adding, changing and deleting rows

Update a row, or create it when it is missing: upsert

When a write is refused

Several writes as one: $transaction

Starting data: seed

Changing the models: adding and removing fields

Pitfalls

See also

The next page: `after`

Reading related rows: `select`

Update a row, or create it when it is missing: `upsert`

Several writes as one: `$transaction`

Starting data: `seed`