---
title: "Defining Schema"
description: "Define tables, columns, and relations with the schema() builder, including the built-in auth and file tables."
---

> Documentation Index
> Fetch the complete documentation index at: https://outer.now/llms.txt
> Use this file to discover all available pages before exploring further.

# Defining Schema

The `schema()` builder describes your tables and the links between them. It is also your migration history: every version you register becomes one migration step, which [Migrations](/schema/migrations) covers.

## Define a schema version

Describe tables with `.table()` and links between them with `.relation()`:

```ts
import { schema } from "@outerjs/server/schema";

const v1_0 = schema("1.0.0")
  .table("user", (t) => ({
id: t.text().primaryKey(),
email: t.text().unique(),
name: t.text(),
image: t.text().nullable(),
  }))
  .table("post", (t) => ({
id: t.serial().primaryKey(),
title: t.text(),
body: t.text().nullable(),
authorId: t.text().references("user", "id"),
  }))
  .relation("user", (rel) => rel.hasMany("post", { from: "id", to: "authorId" }))
  .relation("post", (rel) => rel.belongsTo("user", { from: "authorId", to: "id" }))
  .build();
```

Keep schema versions in their own file. You add versions over time and never edit old ones — or derive the next version from the previous one with [`.extend()`](#extend-a-previous-version).

## Extend a previous version

`.extend(previous)` deep-merges another schema's tables and relations into the current builder. Builder columns win on collision (same as re-declaring via `.table()`). Relations are concatenated and deduped by identity.

```ts
const v1_0 = schema("1.0.0")
  .auth()
  .table("post", (t) => ({
id: t.serial().primaryKey(),
title: t.text(),
  }))
  .build();

const v1_1 = schema("1.1.0")
  .extend(v1_0) // inherits user, session, account, verification, post
  .table("post", (t) => ({ tags: t.text().nullable() })) // adds tags
  .build();
// v1_1.post has id, title, and tags
```

Call `.extend()` anywhere in the chain. Tables you declare before it still win on column collisions, because the builder's current columns overlay the previous ones.

## Auth tables

Call `.auth()` to register the [Better Auth core schema](https://better-auth.com/docs/concepts/database) — `user`, `session`, `account`, and `verification`. It includes the admin plugin's fields by default (`user.role`, `banned`, `banReason`, `banExpires`, and `session.impersonatedBy`) plus the `user` ↔ `session`/`account` relations. Email OTP needs no extra columns, since it uses the `verification` table.

```ts
const v1_0 = schema("1.0.0")
  .auth()
  .table("todo", (t) => ({ id: t.text().primaryKey(), title: t.text() }))
  .build();
```

Re-declaring a table merges columns, and the later definition wins on name collisions. Use that to extend an auth table:

```ts
schema("1.0.0")
  .auth()
  .table("user", (t) => ({ plan: t.text().default("free") })); // adds to the auth user table
```

These tables are typed like hand-written ones and exported as `AuthTables`, so `context.db.query.user` stays fully typed.

`user.role` is unconstrained by default, because Better Auth's admin plugin allows custom role names and comma-separated lists such as `"support,admin"`. Pass `roles` to declare the set your app recognizes. A user can still hold several roles at once:

```ts
schema("1.0.0").auth({ roles: ["user", "admin", "support"] });
// "admin,support" ✓   "admin,root" ✗ rejected
```

Pass `.auth({ apiKeys: true })` to also declare the `apikey` table that [API keys](/outer/auth#api-keys) need.

`session.userId` and `account.userId` cascade on delete, so removing a user is never blocked by their sessions.

## File tables

`.files()` does the same for uploads. It registers a `file` metadata table — `id`, `key` (unique, the storage key the bytes live under), `name`, `type`, `size`, `userId`, and `timestamps(t)`. Only the pointer and its ownership live in the database.

```ts
const v1_0 = schema("1.0.0")
  .auth()
  .table("post", (t) => ({ id: t.text().primaryKey(), title: t.text() }))
  .files({ attachTo: ["post"] })
  .build();
```

`attachTo` links files to existing tables. Each name `x` gets a pivot table `x_file` with `fileId`, `entityId` (typed to match `x`'s primary key), a nullable `role` so one table can carry several kinds of attachment, and an integer `position` for ordered galleries. It also declares a `manyToMany` relation both ways. Only tables already declared on the builder are accepted; unknown names are a type error.

`owner` defaults to `true` and adds `file.userId` plus the `user` ↔ `file` relations, so it requires `.auth()`. Pass `owner: false` for files with no per-user owner. The tables are exported as `FileTables`.

The schema only registers the tables. To serve the bytes, call [`.files()`](/outer/files) on the builder chain.

## Column types

`text` · `varchar` · `integer` · `serial` · `bigint` · `decimal` · `real` · `boolean` · `timestamp` · `date` · `jsonb` · `uuid` · `bytes`

`timestamp` maps to `timestamptz` in the generated DDL. The types worth a second look:

| Column    | Postgres           | SQLite    | TS type      | Notes                                                                                     |
| --------- | ------------------ | --------- | ------------ | ----------------------------------------------------------------------------------------- |
| `bigint`  | `bigint`           | `integer` | `string`     | 64-bit. A JS `number` loses precision past 2^53, so it is read and written as a string.   |
| `decimal` | `numeric`          | `text`    | `string`     | Exact. SQLite maps to `text`, not `NUMERIC`, whose float affinity would defeat the point. |
| `real`    | `double precision` | `real`    | `number`     | Approximate. Do not store money in it.                                                    |
| `date`    | `date`             | `text`    | `Date`       | Calendar date, no time component.                                                         |
| `bytes`   | `bytea`            | `blob`    | `Uint8Array` | Raw bytes.                                                                                |

## Column modifiers

`.primaryKey()` · `.unique()` · `.nullable()` · `.index()` · `.default(value)` · `.defaultSql(expr)` · `.references(table, column, actions?)` · `.enum(values, options?)`

### Defaults

`.default()` takes the **value**, quoted for you by column type. It is not a SQL fragment:

```ts
t.text().default("user"); // → default 'user'
t.boolean().default(false); // → default false (postgres) / default 0 (sqlite)
t.integer().default(0); // → default 0
```

Embedded quotes are escaped for you. For an expression, use `.defaultSql("CURRENT_TIMESTAMP")`, which is emitted verbatim.

The `timestamps(t)` helper returns `createdAt` and `updatedAt` columns already set up that way. Spread it into a column object.

### Foreign keys

```ts
userId: t.text().references("user", "id", { onDelete: "cascade" });
```

`onDelete` and `onUpdate` accept `"cascade"`, `"set null"`, `"restrict"`, or `"no action"`.

Without one, deleting a referenced row fails with a foreign-key violation. That is why the built-in [auth tables](#auth-tables) cascade `session.userId` and `account.userId`, and [file tables](#file-tables) cascade their pivots.

### Indexes

`.index()` adds a non-unique index named `{table}_{column}_idx`. On a `.unique()` column it is a no-op rather than a duplicate index.

### Enums

`.enum(values)` restricts a `text` or `varchar` column to a fixed set. The TS type narrows from `string` to the union, resource inputs validate with `z.enum`, and `_admin.meta` reports the values so the Hub renders a select instead of a text box:

```ts
status: t.text().enum(["draft", "published"]).default("draft");
```

The SQL type is unchanged — no `CREATE TYPE`, no `CHECK` constraint. The constraint lives in Outer, so editing the value list never produces a migration. Values written by raw SQL are not validated.

`{ multiple: true }` stores a **set** of the declared values in one comma-separated text column:

```ts
role: t.text().enum(["user", "admin", "support"], { multiple: true });
// "admin,support" ✓   "admin,root" ✗ unknown   "admin,admin" ✗ duplicate
```

Each part is validated independently. The TS type stays `string`, because enumerating every legal combination would be combinatorial. Read a set with `parseSet(value)` and build one with `toSet([...])`, both exported from `@outerjs/server/schema`.

## Relations

Four kinds are available: `hasMany`, `hasOne`, `belongsTo`, and `manyToMany`.

```ts
.relation("user", (rel) => rel.hasMany("post", { from: "id", to: "authorId" }))
.relation("post", (rel) => rel.manyToMany("tag", "post_tag", {
  from: "id", to: "id", pivotFrom: "postId", pivotTo: "tagId",
}))
```

Relations drive `include` in both the [Sola ORM](/outer/database#include) and [resources](/outer/resource#include-relations).

## Type inference

`SchemaResult<T>["_db"]` is the fully inferred Kysely database type — `{ [tableName]: { [column]: TSType } }`. Nullable columns become `TSType | null | undefined`.

## Next steps

- [Migrations](/schema/migrations) — register versions and apply them
- [`.resource()`](/outer/resource) — generate CRUD for a table
- [Database](/outer/database) — query the tables you just defined

Source: https://outer.now/schema/defining-schema/index.mdx