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 covers.

Define a schema version

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

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(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.

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 — 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.

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:

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:

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 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.

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() 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 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:

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

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 cascade session.userId and account.userId, and 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:

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:

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.

.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 and resources.

Type inference

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

Next steps

• Migrations — register versions and apply them
• .resource() — generate CRUD for a table
• Database — query the tables you just defined