Defining Tables

Learn how to define tables and the relations between them in schema.ts.

Project layout

A Jazz app is described by a few files at its root:

app-root/
├── schema.ts # Tables, relations and permissions: the file you write
├── migrations/ # Migrations between schema versions, written by `migrations create`
├── jazz.app.json # Generated from schema.ts and migrations/
├── jazz.permissions.json # Generated from the permissions in schema.ts
├── jazz.generated.ts # Typed bindings, generated
└── jazz.computed.ts # Only if the app declares computed fields or transforms
  • schema.ts is the source of truth for your data model. It exports your tables as schema and your policies as permissions. You write it, by hand or with an agent.
  • migrations/ holds the migrations between the schema versions your app still ships, and snapshots of those versions. npx jazz-tools migrations create writes them; see Migrations.
  • jazz.app.json and jazz.permissions.json are what npx jazz-tools generate writes from them: the same tables, history and policies, as plain JSON. Clients and backends in every language read jazz.app.json; only backends read jazz.permissions.json, so no client bundle carries your policies. Don't edit them: see Generated files.
  • jazz.generated.ts is also written by generate. It exports the typed app your code queries, and one TypeScript type per table.
  • jazz.computed.ts implements the computed fields and transforms schema.ts declares. Apps without either don't have it.

Commit all of them. Jazz also finds the files in ./src or ./src/lib (SvelteKit keeps them in src/lib/, the standard location for shared app code). Your app code imports app and the row types from ./jazz.generated; nothing imports schema.ts at runtime.

Note

Coming from alpha.65, with s.defineApp, permissions.ts and migrations/index.ts? Run npx jazz-tools@2.0.0-alpha.66 upgrade: see Upgrading from alpha.65.

Table definitions

schema.ts exports schema, made by s.defineSchema from your tables. Each s.table(columns, relations) declares a table's stored columns, in order, and its named relations. A table with no relations leaves out the second argument.

schema.ts
import { schema as s } from "jazz-tools";
​
export const schema = s.defineSchema({
projects: s.table(
{ name: s.string() },
{
todos: s.reverse("todos", "project"),
},
),
todos: s.table(
{
title: s.string(),
done: s.boolean(),
priority: s.int().optional(),
description: s.string().optional(),
owner_id: s.uuid().optional(),
parentId: s.uuid().optional(),
projectId: s.uuid().optional(),
},
{
children: s.reverse("todos", "parent"),
parent: s.rel("todos", "parentId"),
project: s.rel("projects", "projectId"),
},
),
});

A column is a builder such as s.string(), s.int() or s.uuid(), with .optional() when it may be null, and options such as .default(0). See Column Types for all of them. The permissions follow in the same file.

Explicit relations

Store row IDs in UUID columns, then name a forward relation with s.rel. In the example below, posts.author follows authorId to a user. Once that forward relation is declared, s.reverse lets a user find their authoredPosts:

schema.ts
export const schema = s.defineSchema({
posts: s.table(
{ authorId: s.uuid(), title: s.string() },
{
author: s.rel("users", "authorId"),
},
),
users: s.table(
{ name: s.string() },
{
authoredPosts: s.reverse("posts", "author"),
},
),
});

author is the public relation name, users is its target table, and authorId is the local UUID column.

authoredPosts lets you find all posts written by a user. It refers to the author relation (not the authorId column) on the posts table. Relations must be declared explicitly: a UUID column on its own does not create one.

Use an optional UUID (s.uuid().optional()) if a post might not have an author, or an array of UUIDs (s.array(s.uuid())) if it can have several; s.rel points at the column either way.

A declared relation does not guarantee the target row exists or is readable: a peer may not have synced it, it may have been deleted, or permissions may hide it. See Missing references.

TypeScript checks table and relation names as you type. jazz-tools generate then checks the complete schema. Targets and local columns must exist, forward columns must have supported UUID shapes, and reverse declarations must resolve to a forward relation pointing back to their table. Relation names must not collide with stored column names.

Arrays or reverse relations?

  • One-to-many: point each child row at its parent with a UUID column, and give the parent an s.reverse relation, as authoredPosts does above. Each link is its own row, so two people adding at the same time never collide, any number of children fits, and each child has its own permissions and history. Use this for comments on a post or tasks in a project.
  • An array of UUIDs on one row suits a short list that belongs to that row and is usually edited by one person at a time, such as a post's co-authors or an ordered list of pinned items. The array keeps its order, and policies can check membership. It is written as a whole, though: if two people change it at once, the edit the server receives first wins, and the other is kept only in the row's history.
  • Many-to-many links that people edit concurrently, or that carry data of their own (a role, when someone joined): use a join table with an s.rel to each side, like the members table in Group permissions.

See Relationships for more.

Indexes

A table has the indexes you declare, plus one on each relation column, which Jazz always indexes. .indexOnly([...]) lists the columns that get a single-column index each, and .compositeIndex([...]) declares an index over several columns, which may end in $createdAt or $updatedAt. Queries can filter and sort by any column; an index on the columns they use lets them read only the matching rows instead of the whole table.

schema.ts
export const schema = s.defineSchema({
messages: s
.table({ chatId: s.uuid(), text: s.string() })
.indexOnly(["chatId"])
.compositeIndex(["chatId", "$createdAt"]),
});

Adding, removing or changing single-column indexes needs no migration: run generate, and the new bindings use them. Composite indexes are part of the schema version, so changing one is a schema change: generate asks for a migration, and migrations create writes an empty step for the table (rows pass unchanged). See Migrations.

Latest-only tables

By default a table keeps every version of every row, for history reads, snapshots and branches. For a table whose rows change often and whose history you don't need, such as editor presence or cursors, keep only each row's latest version with .history("latest"):

schema.ts
export const schema = s.defineSchema({
presence: s
.table({
doc: s.string(),
cursor: s.string(),
})
.history("latest"),
});

A latest-only table can't be read through history, at a snapshot or in a serializable transaction, and can't use branchBy, text-merging columns or values too large to store inline: over 64 KiB, a write fails with "a value over 65536 bytes in latest-only table presence is not supported yet". Concurrent writes can overwrite one another, and Jazz can't report whether a write overwrote a change the writer hadn't seen. On a client, queries involving latest-only tables may show their newest values alongside older data from other tables. For example, a cursor position may briefly be newer than the document shown with it.

Switching an existing table to latest-only stops history queries on it at once, and deletes each row's earlier versions the next time that row is written; those versions can't be recovered. So the first deployment that does it must pass allowDroppingHistory: true to createJazz. A row that already holds a value too large to store inline keeps it, but can't be written with such a value again.

Rows in a latest-only table usually come and go, so delete the stale ones yourself, for example presence rows that haven't been refreshed for a minute:

const cutoff = new Date(Date.now() - 60_000);
for (const row of await db.all(app.presence.where({ $updatedAt: { lt: cutoff } }))) {
db.delete(app.presence, row.id);
}

For an ephemeral payload larger than 64 KiB, there's no latest-only option yet: split it into rows under the limit, or keep it in an ordinary table and accept its history (deleting a row doesn't free its large values).

Each write follows the history settings of the client's schema. If you roll back to a schema that keeps full history, writes using that schema stop deleting old versions. However, once a table has been converted to latest-only, history queries for it remain unavailable, because Jazz can't guarantee its history is complete.

Validate locally

Check your schema and permissions and regenerate the app files:

npx jazz-tools generate

This loads schema.ts (and permissions.ts, if your permissions live there) and writes jazz.app.json, jazz.permissions.json and the typed bindings in jazz.generated.ts. The two JSON files are generated: edit the TypeScript, not them. generate refuses to overwrite a hand-edited one unless you pass --force. Commit all of them. In CI, npx jazz-tools generate --check writes nothing and fails when a generated file is missing or out of date.

When you change the tables in schema.ts after the files were generated, generate refuses until the change has a migration:

the schema changed since the last version: run `jazz-tools migrations create --name <name>`

Run it as it says, review the migration file it writes into migrations/, and see Migrations. Then restart your main backend (locally) or deploy it as usual: on start, createJazz publishes the new schema, permissions and migrations.

Generated files

generate writes jazz.app.json and jazz.permissions.json from schema.ts and migrations/. They describe your app to every language and to your backend, so commit them, but treat them like jazz.generated.ts: change schema.ts and run generate again. For the schema above, jazz.app.json starts like this:

jazz.app.json
{
"$schema": "https://jazz.tools/schemas/jazz.app.v1.json",
"$generated": {
"by": "jazz-tools generate from schema.ts — edit the TypeScript, not this file",
"sha256": "…"
},
"tables": {
"projects": {
"columns": {
"name": "string"
},
"relations": {
"todos": { "reverse": "todos", "relation": "project" }
}
}
}
}

The $generated marker holds a checksum of the rest of the file. Reformatting the file is fine, but generate refuses to overwrite a file whose content was edited by hand, unless you pass --force. The format is described in App files; this site shows the JSON next to each policy example, so you can see what a rule compiles to.

npx jazz-tools generate --check compares what schema.ts would generate with the committed files and bindings without writing anything, and fails in CI when they differ.

The generated bindings

jazz-tools generate also writes typed bindings from your schema, in your app's language: a row type and an insert type per table, and an enum type per enum column. For StagePlan's tasks table (the StagePlan example):

jazz.generated.ts
export type TaskStatus = "todo" | "doing" | "done" | "blocked";
​
export type Task = {
readonly id: string;
showId: string;
title: string;
status: TaskStatus;
assigneeId: string | null;
notes: string | null;
rank: number;
};
​
export type TaskInsert = {
showId: string;
title: string;
status: TaskStatus;
assigneeId?: string | null;
notes?: string | null;
rank: number;
};

The rest of this section is about the TypeScript bindings; each language's setup page covers its own.

In TypeScript, the bindings are jazz.generated.ts. It exports the typed app you pass to queries, mutations, and subscriptions throughout your application code:

app.ts
import { app, type Todo } from "./jazz.generated";
​
const openTodos = await db.all(app.todos.where({ done: false }).include({ project: true }));

The module also embeds every schema version your app ships and the migrations between them. Clients use them to lay out storage exactly like the server does and to read data written under older versions, so a client never has to rename or reshape stored data later.

Commit it with schema.ts and the JSON files. npx jazz-tools generate --check fails in CI when it is missing or out of date, and createJazz refuses to start with bindings generated from another jazz.app.json: "bindings out of date: run jazz-tools generate". Formatters may reformat the file, except the export const app statement, which carries prettier-ignore and biome-ignore comments.

The app object has one typed table handle per table (e.g. app.todos, app.projects). Table handles are query builders — you chain .where(), .include(), .orderBy() and other methods directly on them.

Types

For each table the module exports a row type and an insert type, named after the table in the singular: Todo and TodoInsert for todos. Enums and typed JSON columns get named types too (TaskStatus for the status column of tasks). Names that would clash are an error in generate; rename the table or column.

jazz-tools also has helpers to get types from any table handle:

HelperReturns
RowOf<typeof app.todos>The row type (all columns, id included)
InsertOf<typeof app.todos>The insert shape (no id, respects optionals and defaults)
WhereOf<typeof app.todos>The where(...) input shape for that table
import type { RowOf, WhereOf } from "jazz-tools";
import { app } from "./jazz.generated";
​
type TodoFilter = WhereOf<typeof app.todos>;

Inside schema.ts, where the bindings don't exist yet, s.Row<typeof schema, "todos"> is a table's row type, for example to type a helper's parameter.