# Schema

> defineTable() in schema.mjs is the schema — the only thing that changes a database's shape.

[HTML version](https://snoozestack.com/docs/schema/) · [Docs index](https://snoozestack.com/llms.txt)

A project's schema is a committed file: `snoozestack/schema.mjs`, plain JavaScript calling `defineTable()`. The [runtime](https://snoozestack.com/docs/runtime.md) keeps the database matching it — locally on every edit, hosted at publish. That file is the whole story: there are no migration files to write, order or replay.

**Why use it**

Your schema is part of your application, and it has to change in lockstep with the code that depends on it. The alternative — typing DDL into a SQL console whenever something needs adding — works exactly until the second environment or the second developer appears; then nobody can say what shape production is in. Here there is no SQL console to drift through: the file is the schema, it rides in the same commit as the code that needs it, and checking out an old revision gives you the schema that matched it.

It also means there is no second source of truth to disagree with the first. A hand-written migration folder is a parallel history of the same information, and the two drift the moment someone edits one and not the other. Declaring the shape you want and letting the runtime work out the difference is the same bargain an ORM's schema file makes, without the SQL.

### Defining tables

```ts title="snoozestack/schema.mjs"
import { defineTable, schema } from "snoozestack-js";

export const notes = defineTable("notes", {
  id: schema.uuid().primaryKey(),
  user_id: schema.uuid().notNull(),
  body: schema.text().notNull(),
  created_at: schema.timestamp().notNull(),
});
```

The type vocabulary — `text`, `int`, `bigint`, `real`, `boolean`, `timestamp`, `uuid`, `json`, `blob` — is deliberately portable: every type has a defined mapping in more than one SQL engine, so the schema describes your data, not an engine. Under `snoozestack dev`, saving this file updates the local database in place — new tables and columns appear without losing the data you already have — and rebuilds the committed `snoozestack/schema.json` beside it, printing what changed. Without `dev` running, `snoozestack schema build` does the same rebuild, and `snoozestack check` fails while `schema.json` disagrees with `schema.mjs`.

| Surface | Availability |
| --- | --- |
| Portal UI | Read-only: **Database → Schema** shows the shape the runtime built; changes come from this file |
| CLI | `snoozestack schema build` — the schema is a file; nothing pushes it on its own |
| SDK / HTTP | `defineTable()`/`schema.*` are exported by snoozestack-js — the schema file is ordinary code |

### Changing the shape

Edit `schema.mjs` and publish. The runtime works out the difference between what the database records as applied and what the file declares, and applies only that — in one transaction, so a failure leaves the database exactly as it was.

Structural changes — create/drop table, add/drop column, create/drop index — apply directly. Everything inside an existing table's definition — a column's type, nullability or default, a foreign key, a unique or primary key — has no SQLite `ALTER`, so those rebuild the table: a new table with the new definition, every row copied across, the old one dropped, the new one renamed into place, its indexes recreated. No row is lost.

What a rebuild can do is refuse: `.notNull()` over rows that are still NULL, a unique key over rows that already share one, a foreign key over rows whose parent is gone. `snoozestack publish` counts those *in production* before it uploads anything and names what is in the way, so the migration is effectively rehearsed against your real rows before it runs. Fix the rows, then publish again. A drop destroys data, so publish also shows what it would drop with how much each column or table holds, and asks first.

### Changing data, not just shape

Some changes are not expressible as a shape: splitting one table into two, backfilling a new column from an old one, de-duplicating rows before a unique key can exist. Those take two releases — **expand**, then **contract**:

1. **Expand.** Add the new shape in `schema.mjs` — new table, or a new nullable column — and publish. Nothing is dropped, so old and new code both still work.
1. **Move the data.** Write a [function](https://snoozestack.com/docs/functions.md) that reads the old and writes the new, publish it, and call it once. If it will not finish inside a request, have it enqueue the work and let a [queue](https://snoozestack.com/docs/queues.md) drain it in batches.
1. **Contract.** Remove what is now unused from `schema.mjs` and publish again. The drop prompt tells you how many values each column still holds — which is also how you catch a backfill that did not actually run.

It is two releases rather than one for a reason that applies whatever the mechanism: deploying is not instantaneous, so for a moment the old code is still serving. A single release that added the new column, moved the data and dropped the old one would break every request in flight. Expanding first means both shapes are valid at once, which is what makes the change safe to deploy at all.

### Applying it

You never apply a schema by hand. Locally, `snoozestack dev` syncs the database on every save and prints what changed; hosted, `snoozestack publish` ships `schema.json` with the release and the project's runtime applies it as it starts, before it serves a request. There is no command that pushes DDL at a database, and no file anywhere that gets replayed.

A rollback restores code, not data. `snoozestack publish --release <n>` redeploys an earlier release's exact artifacts but leaves the schema where it is — schema moves forward only, so a rollback never removes a column a later release added, and anything already dropped stays dropped. Recovering from a bad data change is a new release, not an undo.

### Seeding data

Schema says what tables exist; a committed `snoozestack/seed.mjs` says what a fresh database starts with. It default-exports `async ({ db }) => {}` — the same `db` surface a function gets — and runs exactly once against a brand-new local database, so every clone of the repo starts with working data. `snoozestack dev reset` then `snoozestack dev` is the reseed path.

### An agent's path

An MCP-driving agent working on your repo edits `schema.mjs` and runs the same commands — there is deliberately no tool that changes the schema behind the file's back. What the [MCP](https://snoozestack.com/docs/mcp.md) endpoint offers is the read side: `tables_list` returns the shape the runtime actually built, so an agent can check its edit landed after a publish.