Appearance
Data
std/db keeps an app's data in SQLite, Postgres or MySQL behind one API. The schema is Weft, every query is typed, and a user's queries go through the tables' policies.
db/schema.weft
weft
import { table, schema, primary, references, index, immutable, default, owned, flag, public } from "std/db"
export type User = { @primary id: string, name: string, role: "member" | "admin" }
export type Todo = {
@primary @default(uuid) id: string,
@references(users.id) @index @immutable owner: string,
title: string,
@index @default(false) done: boolean,
@index @default(false) public: boolean,
@default(now) created: number,
}
export let users = table<User, User>("users", { read: public(), update: owned("id") })
export let todos = table<Todo, User>("todos", {
read: owned("owner").or(flag("public")),
insert: owned("owner"),
update: owned("owner"),
// done todos are kept
delete: (row, user) => row.owner == user.id && !row.done,
})
export let app = schema({ tables: { users, todos } })A table is a record type and a name. Its fields are the columns, in order; attributes say more about them: @primary is the key, @default(uuid) and @default(now) are filled in on insert, @references(users.id) points at another table, @index indexes it and @immutable keeps every update from changing it. The second type argument, User, is who the policies are about.
Connecting
lib/db.weft
weft
import { sqlite, postgres } from "std/db"
import { env } from "std/process"
import { app } from "db/schema"
// Made once per process: a module's top level runs once, and every importer shares it.
let url = env("DATABASE_URL") ?? "app.db"
export let pool = if url.startsWith("postgres") { try await postgres(app, url) } else { try await sqlite(app, url) }sqlite(schema, path) opens a file (or ":memory:"); postgres and mysql take a URL and keep a pool of connections. A query goes through a handle: pool.as(user) queries as that user, under the policies, and pool.as(null) as no one signed in.
Queries
There are three levels, and all three are typed against the schema when the program compiles.
lib/todos.weft
weft
import { pool } from "lib/db"
import { todos, User, Todo } from "db/schema"
// 1. Objects: a filter of columns.
export async fn mine(user: User): Todo[] {
return try await pool.as(user).query.todos({ owner: user.id, done: false })
}
export async fn add(user: User, title: string): Todo {
return try await pool.as(user).insert.todos({ owner: user.id, title })
}
export async fn finish(user: User, id: string): Todo[] {
return try await pool.as(user).update.todos({ id }, { done: true })
}
// 2. Queries read like lists: one SQL statement, run when awaited, conditions with Weft's meaning.
export async fn search(user: User, q: string): Todo[] {
return try await pool.as(user).query.todos().filter(t => !t.done && t.title.startsWith(q)).sortBy(t => t.created, { desc: true }).take(20)
}
// 3. SQL: checked against the schema; every {…} is a parameter.
export async fn openCounts(user: User): { name: string, open: number }[] {
let db = pool.as(user)
return try await db.sql"select u.name, count(t.id) as open from users u left join todos t on t.owner = u.id and t.done = {false} group by u.name order by u.name"
}Rows come back as the function's type. A filter naming a column the table doesn't have is a compile error, as is SQL naming one:
lib/typo.weftrejected by the compiler (E2011)
weft
import { pool } from "lib/db"
import { User, Todo } from "db/schema"
export async fn late(user: User): Todo[] {
return try await pool.as(user).query.todos({ finished: true })
}A query reads like a list: filter, map, sortBy, take, skip, then await (or find, count, some, every), and the chain is one SQL statement, the one you'd write by hand. db.query.todos() is the table's rows as a Query<Todo>, a value you can pass around and add to; nothing runs until it is awaited. Its closures become SQL, so one that calls your own function is a compile error with a fix-it: fetch the rows, then use the list's methods.
lib/urgent.weftrejected by the compiler (E2026)
weft
import { pool } from "lib/db"
import { User, Todo } from "db/schema"
fn urgent(t: Todo): boolean { return t.title.endsWith("!") }
export async fn urgentOnes(user: User): Todo[] {
return try await pool.as(user).query.todos().filter(t => urgent(t))
}A query can fail with a DbError, so each one above takes a try (Errors). A policy's refusal is its Denied { table, access }; the database's are Conflict, Invalid, Connection and Failed. h.transaction(async tx => …) runs its body in a transaction, committed when the body returns and rolled back when it fails or panics; the transaction then fails with the body's error.
Policies
db/notes.weft
weft
import { table, schema, primary, references, index, owned, role, public } from "std/db"
export type User = { @primary id: string, role: string }
export type Note = { @primary id: string, @references(users.id) @index author: string, text: string }
export let users = table<User, User>("users", { read: public() })
export let notes = table<Note, User>("notes", {
read: public(),
// the author, or an admin
write: owned("author").or(role("admin")),
})
export let app = schema({ tables: { users, notes } })| building block | true for the user when |
|---|---|
public() | always, signed in or not |
owned("c") | row.c == user.id |
flag("c") | the row's boolean column c is true |
role("r") | user.role == "r", or user.roles holds "r" |
member("c", m.x, m.u) | the row's c is in the user's memberships |
(row, user) => … | the closure says so (written in Weft, run as SQL) |
.and, .or and not combine them, and write stands for the writes not given. A table denies what it has no policy for. Reads apply to every query, joins and subqueries of raw SQL included. A write is checked against the rows before and after, so an update can't hand a row to someone else.
The compiler rejects a policy that can't depend on the user:
db/schema.weftrejected by the compiler (E2025)
weft
import { table, schema, primary } from "std/db"
export type User = { @primary id: string }
export type Note = { @primary id: string, text: string }
export let users = table<User, User>("users", {})
export let notes = table<Note, User>("notes", { delete: (row, user) => row.text == "" })
export let app = schema({ tables: { users, notes } })That closure reads only the row: everyone could delete an empty note. flag or public() say that on purpose.
admin(db), from its own module std/db/admin, is the only way around the policies, and weft audit lists every place that imports it.
Testing who can do what
lib/access.test.weft
weft
import { accessMatrix } from "std/db"
import { app, User } from "db/schema"
let anna: User = { id: "anna", name: "Anna", role: "member" }
let bob: User = { id: "bob", name: "Bob", role: "member" }
test "who can do what" {
await accessMatrix(app, {
principals: [anna, bob],
rows: {
users: [anna, bob],
todos: [
{ id: "a-open", owner: "anna", title: "a", done: false, public: false },
{ id: "b-public", owner: "bob", title: "b", done: false, public: true },
],
},
})
}weft test puts the sample rows in an in-memory database and writes, per table, who can read (r), update (u), delete (d) and insert (i) each row:
text
# todos: who can read (r), update (u), delete (d) and insert (i) each sample row
| row | anyone | anna | bob |
|---|---|---|---|
| a-open | ---- | rudi | ---- |
| b-public | r--- | r--- | rudi |The file is db/access/todos.txt, kept with the code. A policy change that changes who can do what fails the test with both versions; UPDATE_SNAPSHOTS=1 weft test accepts it, and the diff shows in review.
Migrations
toml
[db]
schema = "db/schema"
export = "app"
url = "$DATABASE_URL"With that in weft.toml:
sh
weft db migrate --name add-public # writes db/migrations/0002_add-public/{sqlite,postgres,mysql}.sql
weft db apply # runs the pending ones against the url
weft db push --url sqlite:dev.db # applies the schema directly, for development and testsweft db migrate compares the schema with db/snapshot.json, the schema as of the last migration, and writes the SQL for each dialect.
From a page
A page reaches the database through server functions that say what they do: a query fn reads, a mutation fn writes, an action fn does I/O. Each queries as the signed-in user:
lib/server/todos.weft
weft
import { request } from "std/http"
import { principal } from "std/auth"
import type { Principal } from "std/http"
import { table, schema, primary, index, sqlite, owned, identify } from "std/db"
export type Todo = { @primary id: string, @index owner: string, title: string, done: boolean }
let todos = table<Todo, Principal>("todos", { read: owned("owner"), write: owned("owner") })
let app = schema({ tables: { todos } })
let pool = try await sqlite(app, "app.db")
// Who calls: in a query or mutation fn, the pool is this user's handle.
identify(pool, async () => try await principal(request()) else null)
/// Reads, in one snapshot.
export query fn myTodos(): Todo[] {
return try await pool.query.todos().sortBy(t => t.title)
}
/// Reads, then writes: one transaction, run again if another one got in the way.
export mutation fn toggle(id: string): void {
let t = try await pool.query.todos().find(t => t.id == id)
if let t = t { try await pool.update.todos({ id }, { done: !t.done }) }
}identify says who is calling: here the signed-in user, or null; as null, owned is false, so a signed-out caller gets no rows. pool.as(user) is for actions and scripts, which say whom they run for. A mutation that conflicts with another (both read a row, then wrote it) is rolled back and run again, so no update is lost; that is why a query or mutation fn may not do I/O, which would happen twice. The compiler checks it, through every function it calls:
lib/server/feed.weftrejected by the compiler (E2031)
weft
import { fetch } from "std/http"
export mutation fn refresh(url: string): number {
let res = try await fetch(url)
return res.status
}Do it in an action fn, which may do I/O and call mutations (each its own transaction). Inside a query or mutation fn, now() is the time the call started and random()/uuid() are seeded for the call, so a run again sees the same values.
Live queries
routes/page.weft
weft
import { myTodos, toggle } from "lib/server/todos"
derived todos = await myTodos()
// A handler can't fail: a write that fails changes nothing, and the list keeps showing what is stored.
async fn flip(id: string) {
try await toggle(id) else null
}
---
@for t of todos.value ?? [] key t.id {
<Checkbox checked={t.done} label={t.title} onchange={() => flip(t.id)} />
} @empty {
<Text>Nothing yet.</Text>
}A query fn awaited in a derived value is live: no keyword. On the server, std/db records what each query read; a committed write that could change the result runs the call again, and the page gets only the rows that changed, by key. The value is an Async<T>, so the markup reads todos.value. Calls with the same function, arguments and user share one run, and the policies apply as for any query of that user. For messages rather than rows, a channel: for await m of chat { … } in an effect listens until the effect ends (std/live).
The live list recipe builds a whole page on this. The whole API: std/db and std/live. A full example: examples/todo-db in the repository.