Working with Databases
A database is an isolated, server-side data store. Each database is its own instance with its own storage — strong consistency and zero-config scaling. Unlike documents (which are local-first and collaborative), databases live entirely on the server, which makes them the place for shared data, cross-user queries, and admin-controlled content.
Everything your app does with a database happens in a server function. Inside the function, ctx.db(databaseId, "<type>") gives you a typed handle; each model on it has query, count, aggregate, save, patch, delete, and batch, and filters are plain objects. Creating, administering, and looking up databases are ctx.api.databases.* calls from the same code. The code runs on the app's own authority, and who may call the function is decided by the function's access rule. App code invokes the function; it has no database calls of its own.
Key Properties
Schemaless on the Server
Save any JSON records without an upfront schema. There's no CREATE TABLE step — a model comes into existence the first time you write to it, and you can add fields at any time without migrations. You can optionally declare the models in the database type's TOML; that declaration types the function handle and drives indexes (see Database Types).
Organized by Type
A database type is a named configuration — models, indexes, triggers — shared across many database instances. Think of it as a template: if you have one database per tenant, project, or team, they all share one type, so a change to the type reaches every instance.
An individual database holds up to ~5 GB. The per-tenant/per-project pattern is also how you scale past that, since each instance gets its own isolated storage.
Reached Through Functions
The function is the whole access layer. Its access rule says who may call it; its code says which rows they get — a read your caller should see only part of is filtered in the code.
Quick Start
1. Define a Database Type
# primitive/dev/database-type-configs/products.toml
[type]
databaseType = "products"
[models.Product.fields.name]
type = "string"
indexed = true
[models.Product.fields.priceCents]
type = "number"2. Create a Database
primitive databases create "Product Catalog" --type productsThe command prints the new database's id. Apps that create databases as they run — one per team or project — do it from a function instead (see Managing Databases).
A database can also be created with its first resource metadata already stamped, via --initial-metadata (or initialMetadata in the create body).
3. Write a Server Function
primitive config create function list-products# primitive/dev/functions/list-products.toml
[function]
key = "list-products"
description = "List the product catalog"
entry = "functions/list-products/index.ts"
access = "true"// primitive/dev/functions/list-products/index.ts
import { defineFunction } from "primitive-functions";
export default defineFunction(async (input: { databaseId: string }, ctx) => {
const products = ctx.db(input.databaseId, "products").model("Product");
return await products.query({ options: { sort: { name: 1 } } });
});4. Push and Call It
primitive config pushconst result = await client.functions.invoke<{
items: Array<{ id: string; name: string; priceCents: number }>;
hasMore: boolean;
nextCursor?: string;
}>("list-products", { input: { databaseId } });
if (result.status === "completed") {
for (const product of result.output?.items ?? []) {
console.log(product.name, product.priceCents);
}
}struct Product: Decodable, Sendable { let id: String; let name: String; let priceCents: Double }
struct ProductPage: Decodable, Sendable { let items: [Product]; let hasMore: Bool; let nextCursor: String? }
let result: FunctionResult<ProductPage> = try await client.functions.invoke(
"list-products",
input: ["databaseId": databaseId]
)
if result.status == "completed" {
for product in result.output?.items ?? [] {
print(product.name, product.priceCents)
}
}See Invoking for the response envelope and Choosing the runtime for invoke vs. start.
Reading and Writing Records
Everything below runs inside a function. The second argument to ctx.db is the database type key, and it selects which models the handle knows: once config push has written the generated declarations, a model the type doesn't declare is a compile error.
Querying
const orders = ctx.db(input.databaseId, "orders").model("Order");
const page = await orders.query({
filter: { status: "open", total: { $gte: 100 } },
options: { sort: { createdAt: -1 }, limit: 50 },
});
// page: { items, hasMore, nextCursor? }
const { count } = await orders.count({ filter: { status: "open" } });Filters use the same operators document queries do — see the operator reference. Every row a read hands back carries its id, whatever your schema declares.
Every paged read answers { items, hasMore, nextCursor? }; pass nextCursor back as options.uniqueStartKey for the next page. One paged envelope on the functions page has the loop shared with the document handle and ctx.users.list; the cap on how many values a single $in list may hold is below. hasMore: true always comes with a nextCursor, so "page until the cursor runs out" and "page until hasMore is false" are the same loop.
Sorting on a field some records don't have
Records are schemaless, so a model's rows need not all carry the field you sort on — and a row can carry it as null. Absent and null are one value for ordering: a record that never wrote the field sorts exactly where a record holding null sorts. Those rows come first in an ascending sort ({ priority: 1 }) and last in a descending sort ({ priority: -1 }), which is where SQLite puts nulls in an ORDER BY. The rule is the same on the server and in the Swift and JS clients, so the same records page in the same order everywhere.
// { priority: 1 } → rows with no priority, then 1, 2, 3, …
// { priority: -1 } → …, 3, 2, 1, then rows with no priority
const page = await orders.query({ options: { sort: { priority: 1 }, limit: 50 } });Paging over such a set visits every matching record exactly once, in both sort directions and both paging directions. Ties are broken by id, which every record has, so page boundaries are stable even when many rows share a value (or share having none).
Cursors stay opaque — a base64 token, never something to parse or construct. A cursor issued before this ordering was specified keeps working unchanged, so a client holding one does not need to restart its walk.
Writing
const { id } = await orders.save({ data: { label: "first", status: "open", total: 10 } });
await orders.patch(id, { data: { status: "shipped" } });
await orders.delete(id);
// Several writes to the same model in one request, all or nothing.
await orders.batch({
operations: [
{ op: "save", data: { label: "second", status: "open", total: 4 } },
{ op: "delete", id: "o-3" },
],
atomic: true,
});save creates a record (the platform assigns an id when you leave it out) or merges into an existing one; patch changes some fields of one record; delete removes one. patch and delete take the record id first, so the id and the model are always the handle's rather than something a body can change.
A batch that spans several models goes on the database handle instead — ctx.db(databaseId, "orders").batch({ … }) — and each operation names its model with modelName.
Counting and Aggregating
const { result } = await orders.aggregate({
options: {
groupBy: ["status"],
operations: [{ type: "count" }, { type: "sum", field: "total" }],
},
});
// result: { open: { count: 12, sum_total: 480 }, shipped: { … } }Each result key is fixed: count for a count and <type>_<field> for the rest (sum_total here). With exactly one operation there is no key at all — each group's value is that operation's bare value, so a lone { type: "sum", field: "total" } grouped by status gives { open: 480, shipped: … }. That single-operation rule is the same wherever you aggregate: here, the document aggregate, the CLI, workflow steps, and the js-bao JavaScript library's model statics (Model.aggregate). The Swift client's local aggregate is a different API, answering with raw SQL rows rather than a group-keyed object.
Conditional Writes (Compare-and-Swap)
Add a condition to a write to gate it on the record's current state. The condition is a filter object the target record must match, checked in the same transaction as the write — a true compare-and-swap. This is how you serialize concurrent updates without a lock: keep a version field, and write only when it still matches what you read.
const [task] = (await tasks.query({ filter: { id: input.taskId } })).items;
await tasks.patch(task.id, {
data: { status: input.status, version: task.version + 1 },
condition: { version: task.version },
});When the condition holds, the write applies. When it doesn't, nothing is written and the call throws with status 409. If two callers race, exactly one wins the version; the other re-reads and tries again.
The Untyped Form
ctx.api.databases.records.* is the untyped form of the same operations — query, count, aggregate, save, patch, delete, batch — plus the rest of the records surface the typed handle doesn't carry: increment, the string-set verbs, and the index and unique-constraint routes (registerIndex, dropIndex, listIndexes, syncIndexes, registerUniqueConstraint, dropUniqueConstraint, listUniqueConstraints), plus describe and models. Reach for it when you don't have a type key handy, or need an operation the typed handle doesn't wire.
Atomic Increments and String-Set Operations
increment, addToSet, and removeFromSet change a record with no read-modify-write round trip:
await tasks.batch({
operations: [
{ op: "increment", id: task.id, fields: { views: 1 } },
{ op: "addToSet", id: task.id, stringSets: { tags: ["urgent"] } },
{ op: "removeFromSet", id: task.id, stringSets: { tags: ["triage"] } },
],
});The standalone forms are ctx.api.databases.records.increment({ databaseId, body: { modelName, id, fields } }) (answers { success, id, values }), and addToSet / removeFromSet with body: { modelName, id, sets }. On a missing record each fails with 404. None of the three carries a record body, so none of them trips a type's timestamps stamp — see Server-Stamped Fields.
List and Filter Limits
A single $in or $nin list holds up to 1 000 values, whatever the filter around it — a list on one field and a list on another are counted separately, so two 600-value lists in one filter are fine. Past the cap the call is refused with QUERY_IN_LIST_TOO_LARGE, naming the field and the count, rather than failing inside SQL. The list itself costs the statement one bound parameter, so a long list is no more expensive to page or sort than a short one, and reading a thousand keys is one query:
const page = await orders.query({
filter: { id: { $in: keys } }, // up to 1 000 keys
options: { sort: { id: 1 }, limit: 200 },
});For more than a thousand, how you split depends on the operator. Chunk an $in and merge the pages yourself — each chunk matches some of the rows you want, and the union is the answer. Never merge chunked $nin queries that way: a chunk excluding some of the values still returns the rows the other chunks exclude, so merging gives back nearly everything. Put $nin chunks in one filter instead, where $and intersects them rather than unioning them:
const chunks = [];
for (let i = 0; i < keys.length; i += 1000) chunks.push(keys.slice(i, i + 1000));
// Each chunk is its own capped list and its own bound parameter; `$and`
// applies all of them, so this excludes every key in every chunk.
const page = await orders.query({
filter: { $and: chunks.map((chunk) => ({ id: { $nin: chunk } })) },
});The list cap is not the only bound. One statement may bind 100 parameters — the platform's limit per statement inside the storage layer — and the filter shares that budget with the model name and the cursor's sort values. Two shapes cost one parameter however big they are: a list, as above, and a group of equality branches on the same fields, so an $or of a hundred { accountId, month } pairs binds one parameter and pages, sorts and counts like any other filter — a batch of pairs is one query rather than a loop:
const page = await orders.query({
filter: { $or: pairs.map(({ accountId, month }) => ({ accountId, month })) },
options: { limit: 100 },
});A filter with no such group — an $and of a hundred conditions, or a hundred branches that each compare differently — is refused with QUERY_FILTER_TOO_MANY_BINDS naming the bound and the count, rather than failing inside SQL. Narrow it, fold the repeated conditions into an $in or an $or of equality branches, or split the query.
Who Can Reach the Data
A function's access rule is evaluated on every call; once a caller is through it, the code reads and writes as the app. That makes the code responsible for scoping. A read the caller should only see part of is filtered in the code:
export default defineFunction(async (input: { databaseId: string }, ctx) => {
const orders = ctx.db(input.databaseId, "orders").model("Order");
return await orders.query({ filter: { owner: ctx.user!.userId } });
});Write the gate for what the function can do. A function that deletes rows might be access = "user.role == 'admin'"; one that reads the caller's own rows can be access = "true". See The gate runs on every call.
Registered Queries
defineQuery names a read over the handle, so the same query can be called from several places, validated once, scoped to its caller, and — when the platform can verify what it touched — cached.
import { defineQuery, defineMutation } from "primitive-functions";
const myOrders = defineQuery("myOrders", {
models: ["Order"],
params: {
owner: { caller: true }, // $caller: injected, never supplied
since: { type: "string", optional: true },
limit: { type: "number", default: 25 },
},
cache: { ttlMs: 30_000 },
// `params` is typed from the declaration above: owner is a string, since is
// string | undefined, limit is a number — and `params.nope` is a compile error.
run: async (db, params) => {
const answer = await db.model("Order").query({ options: { limit: params.limit } });
return answer.items.filter((row) => row.owner === params.owner);
},
});
// A list parameter — the shape behind every `$in` filter. `items` is the
// element type; `params.ids` is `string[]` inside `run`.
const ordersByIds = defineQuery("ordersByIds", {
models: ["Order"],
params: { ids: { type: "array", items: { type: "string" } } },
run: async (db, params) =>
db.model("Order").query({ filter: { id: { $in: params.ids } } }),
});
const addOrder = defineMutation("addOrder", {
models: ["Order"],
params: { owner: { caller: true }, label: { type: "string" } },
run: (db, params) =>
db.model("Order").save({ data: { owner: params.owner, label: params.label } }),
});
export default async function (input, ctx) {
const db = ctx.db(input.databaseId, "orders");
return { orders: await myOrders(db, { limit: 10 }) };
}Register at module scope. A registration made inside your handler still works for direct calls, but config push will not see it, so it is absent from the manifest primitive functions get shows.
- Names are unique. Registering the same name twice throws.
- Parameters are validated and coerced against what you declared, with defaults applied; an error names the query and the parameter, and an undeclared parameter is refused rather than dropped. A parameter's
typeisstring,number,boolean,any, orarraywith anitemstype from the same list (noitemsaccepts any array);callercannot be combined witharray. A declareddefaultis coerced to the declared type at registration ("25"becomes25,["1"]becomes[1]for numeric items), sorunreceives what the declaration promises — and a default that cannot be coerced is refused at registration, whichconfig pushreports. $calleris a convenience marker, and it is immutable. The platform injects the invoking user; a call that supplies that parameter is refused by name. It scopes a query to whoever asked without your handler threadingctx.userthrough. In an invocation with no initiating user — a webhook or cron fire — a$callerquery throws, because there is nobody to scope to.
Caching is off unless the run is verified. Declaring cache.ttlMs is not enough: the wrapper watches what run really did, and stores the answer only when every model it touched was declared and nothing was written. The TTL is capped at one minute.
A cache entry belongs to one query, one databaseId and resolved type, and one set of parameters after injection — so two databases of the same type never share an answer, and two callers never see each other's rows. A write through the handle to a model drops the entries that read it, and the whole cache lives inside one warm sandbox instance: it bounds staleness, it does not replicate.
Realtime
Realtime updates for database data travel over channels. A function that writes a row publishes to a channel in the same handler, and clients subscribed to that channel hear it. The functions page's Channels section shows the whole flow: authorizing a client for a channel, subscribing, and publishing.
Database Types
A database type is a file under primitive/<env>/database-type-configs/, pushed with primitive config push. It carries the type's model declarations, indexes, and the server-stamped fields below.
Declaring Models
Add [models.<Name>.fields.<field>] blocks to declare a type's models. The database stays schemaless — a write is never rejected for a field you didn't declare — but the declaration types the function handle and says which fields to index:
[models.Product.fields.name]
type = "string"
[models.Product.fields.priceCents]
type = "number"
indexed = true
[models.Product.fields.sku]
type = "string"
unique = trueField types are string, number, boolean, date, id, and stringset. Structured payloads are stored as a string field holding JSON-encoded values, parsed where consumed.
unique applies to scalar fields only: a type whose schema marks a stringset field unique, or names one in a [[models.<Name>.unique_constraints]] entry, is refused with 400 UNIQUE_ON_STRINGSET on create and update, and database-type codegen refuses it too.
When you push a schema that newly marks a field indexed or unique, the server provisions that index across every existing database of the type automatically. To scaffold a schema for a type that already has live data, generate one from the records:
primitive databases schema generate inventoryReserved Field Names
A schema may not declare a field named type, nor one whose name starts with _. Both collide with the storage engine's own columns — type holds the model name, so a filter on a field of that name would match the model name rather than a stored value. A record write carrying either is refused with 400 RESERVED_FIELD_NAME, and so is a push declaring one, which names the block and stores nothing:
[models.product.fields.type]: Field 'type' is reserved (maps to internal _type column in queries)id is not reserved — it's the record's primary key. Rename the declared field (kind, category, status) and push again.
Server-Stamped Fields
Two mechanisms write fields server-side on every save and patch, whichever function made it.
timestamps — stamp created and modified times on every record of a type. Field names are yours; either key can be omitted; add models = [...] to restrict it to specific models.
[type]
databaseType = "orders"
timestamps = { create = "createdAt", update = "modifiedAt" }The stamp applies to each save and patch, including each one inside a batch — upsertOn included, on the create and on the update. delete, increment, addToSet, and removeFromSet carry no record body, so they never stamp. A value you send explicitly for the field wins.
Triggers — per-model computed fields with conditions on the record's own data:
[triggers.orders]
triggers = [
{ on = "create", set = { createdBy = "user.userId" } },
{ on = "save", when = "record.status == 'complete' && record.completedAt == null", set = { completedAt = "now()" } },
]on is "create", "update", or "save" (both); when is an optional CEL condition; set maps field names to CEL values. Trigger expressions see user.* (the user the write is attributed to — a function's caller), record.*, database.*, and now().
Use timestamps for plain audit times, and triggers when the rule depends on the record's data.
Managing Databases
Creating, sharing, and looking up databases are platform calls a function makes through ctx.api.databases. The calls that create, destroy, or hand out authority over a database are high-blast calls, so the function declares each one it uses as a capability: databases:create, databases:delete, databases:transferOwnership, databases:addManager, databases:revokePermission, databases:grantGroupPermission, databases:revokeGroupPermission. Reads — get, listPermissions, listGroupPermissions — need nothing declared.
Creating a Database per Team
The common multi-tenant shape is one database per team, with the database id reused as the group id, so a user's group memberships directly name the databases they belong to:
# primitive/dev/functions/create-workspace.toml
[function]
key = "create-workspace"
entry = "functions/create-workspace/index.ts"
access = "true"
capabilities = ["databases:create"]// primitive/dev/functions/create-workspace/index.ts
import { defineFunction } from "primitive-functions";
export default defineFunction(async (input: { title: string }, ctx) => {
const db = await ctx.api.databases.create({
body: { title: input.title, databaseType: "workspace" },
});
// The database id doubles as the group id; the caller is its first member.
await ctx.api.groups.create({
body: { groupType: "workspace", groupId: db.databaseId, name: input.title },
});
await ctx.api.groups.addMember({
groupType: "workspace",
groupId: db.databaseId,
body: { userId: ctx.user!.userId },
});
return { databaseId: db.databaseId };
});A database a function creates names the function's caller as its creator and owner.
Finding a User's Databases
With that shape, a function finds the caller's databases from their memberships and resolves each one:
import { defineFunction } from "primitive-functions";
export default defineFunction(async (_input: {}, ctx) => {
const memberships = await ctx.api.groups.listUserMemberships({
userId: ctx.user!.userId,
type: "workspace",
});
return await Promise.all(
memberships.map((m: { groupId: string }) =>
ctx.api.databases.get({ databaseId: m.groupId })
)
);
});Each result carries the database's databaseId, title, databaseType, and createdBy.
Owners, Managers, and Group Grants
A database carries administrative permission records: its owner (the creator) and any managers. They are records, not a way in — a user reaches a database only through your functions, and what they can read there is always your functions' decision. App admins manage the records — with primitive databases permissions, or from a function, which runs on the app's authority — and a function reads them with ctx.api.databases.listPermissions to make its own decisions (for example, letting only a database's managers rename it through your function). A group grant gives every member of a group manager at once; manager is the only level a group can hold.
// capabilities = ["databases:addManager", "databases:grantGroupPermission"]
await ctx.api.databases.addManager({
databaseId: input.databaseId,
body: { userId: input.userId, permission: "manager" },
});
await ctx.api.databases.grantGroupPermission({
databaseId: input.databaseId,
body: { groupType: "team", groupId: "ops", permission: "manager" },
});
const users = await ctx.api.databases.listPermissions({ databaseId: input.databaseId });
const groups = await ctx.api.databases.listGroupPermissions({ databaseId: input.databaseId });Keep these for the few accounts that administer a database, not for end-user sharing.
Common Patterns
User-Scoped Data
Filter on the caller inside the function, or register the read with the $caller binding (see Registered Queries) so the platform supplies the caller and no call can substitute another user.
Admin + User Access
Admins see everything, a user sees their own — two functions over the same model, each with its own gate:
# primitive/dev/functions/list-orders-admin.toml
[function]
key = "list-orders-admin"
entry = "functions/list-orders-admin/index.ts"
access = "user.role == 'admin'"# primitive/dev/functions/list-orders.toml
[function]
key = "list-orders"
entry = "functions/list-orders/index.ts"
access = "true"The admin function queries with no filter; list-orders filters on ctx.user!.userId.
Team-Scoped Databases
With one database per team and the database ID used as the group ID (above), a function checks the caller's membership before it touches the rows:
import { defineFunction } from "primitive-functions";
export default defineFunction(async (input: { databaseId: string }, ctx) => {
const memberships = await ctx.api.groups.listUserMemberships({
userId: ctx.user!.userId,
type: "team",
});
if (!memberships.some((m: { groupId: string }) => m.groupId === input.databaseId)) {
throw new Error("Not a member of this team");
}
return await ctx.db(input.databaseId, "team").model("Task").query({
options: { sort: { createdAt: -1 }, limit: 50 },
});
});Administering Records from the CLI
App admins can inspect and fix records directly from a terminal, without writing a function:
primitive databases records models <database-id>
primitive databases records query <database-id> Order --filter '{"status":"open"}'
primitive databases records count <database-id> Order
primitive databases records save <database-id> Order <record-id> --data '{"status":"open"}'
primitive databases records patch <database-id> Order <record-id> --data '{"status":"closed"}'
primitive databases records delete <database-id> Order <record-id>The same terminal manages databases themselves — primitive databases list, get, create, and delete — and primitive databases export and primitive databases import move a database's records, indexes, and constraints between databases or environments.
Next Steps
- Server Functions — The function that fronts every database call, and realtime channels for the rows it writes
- Choosing Your Data Model — When to use databases vs. documents
- Users and Groups — Set up groups your functions check membership against
- Resource Metadata — Attach per-database values with their own read and write rules
- Primitive CLI — Full CLI reference for database management