---
title: Model
description: "Define database tables with fields: scalar columns and relation associations"
icon: table
---

## Creating a Model

Use `s.model()` with an object of field definitions (scalars and relations):

```ts
import { s } from "viborm";

const user = s.model({
  id: s.string().id().ulid(),
  email: s.string().unique(),
  name: s.string(),
  createdAt: s.dateTime().default(() => new Date()),
});
```

### Reserved member names

Two keys belong to what a selection produces, so no member may take them:

- `_count` is reserved for [relation counts](/docs/client/selecting#count-relations):
  in `select` and `include` it always means the counts of the model's relations;
- `_distance` is reserved for distance results: it is the key a selected
  [point](/docs/schema/scalars/point#select-and-order-by-distance) or
  [vector](/docs/schema/scalars/vector) distance is returned under.

A model that declares a member named `_count` or `_distance` — a scalar, a
relation or a polymorphic slot — is refused when the schema is validated, at
client construction and by `validateSchema` (`F010`). In TypeScript, `s.model`
and `.extends` already refuse the key at compile time, with a diagnostic that
names the reason (`` '"`_count` is reserved for relation counts (F010)"' ``,
`` '"`_distance` is reserved for distance results (F010)"' ``); `F010` is the
check that holds for every caller, JavaScript included. To keep an existing
column of either name, give the scalar another name and map it:
`tally: s.int().map("_count")` reads and writes the column `_count` as the
member `tally`, and `rank: s.int().map("_distance")` the column `_distance` as
`rank`, beside a selected distance.

`$through`, a many-to-many's
[link row](/docs/schema/relations/many-to-many#a-model-as-the-junction), is a
selection's key as well. A member can never take it, because `$` is no part of a
field name (`F001`).

## Table Name

By default, VibORM uses the variable name. Use `.map()` to set a custom table name:

```ts
const user = s
  .model({
    id: s.string().id(),
    // ...
  })
  .map("users"); // SQL: CREATE TABLE "users" (...)
```

## Chainable Methods

### `.map(tableName)`

Sets the database table name.

```ts
.map("users")
.map("user_accounts")
```

### `.index(fields, options?)`

Adds an index on one or more fields. Fields are always passed as an array:

```ts
// Single field index
.index(["email"])

// Composite index
.index(["lastName", "firstName"])

// With options
.index(["status", "createdAt"], {
  name: "idx_status_date",
  type: "btree",        // see the table below
  unique: false,
  where: "status != 'deleted'"  // Partial index (PostgreSQL, SQLite)
})
```

Not every `type` exists on every database. `push` and `migrate` refuse an index
whose type the target does not have, naming the index and listing what that
dialect supports — they never quietly build a different index:

| `type` | PostgreSQL | MySQL | SQLite |
|---|---|---|---|
| `btree` (default) | ✅ | ✅ | ✅ |
| `hash` | ✅ | ❌ (InnoDB has no user-defined HASH index) | ❌ |
| `gin`, `gist` | ✅ | ❌ | ❌ |
| `fulltext`, `spatial` | ❌ | ✅ (cannot be combined with `unique`) | ❌ |

`where` is the same story: PostgreSQL and SQLite build the partial index, and
MySQL — which has no partial index at all — refuses the declaration by name
rather than indexing the rows your schema excluded.

The foreign-key fields of a relation are indexed for you: `push`
and `migrate` add an index over them on every database. Declare your own index
over the same fields — first in the list, so the index still serves them — when
you want more columns in it; the automatic index is then not added.

#### Composite indexes for ordered includes

An `include` that both **orders and limits** a to-many relation is the case
worth widening that index for. Each parent's children are read by a subquery
that filters on the foreign key, sorts, and takes the first N:

```ts
await client.user.findMany({
  include: {
    posts: { orderBy: { createdAt: "desc" }, take: 5 },
  },
});
```

With an index on the foreign key alone the database can find a user's posts
quickly but must then read **all** of them and sort before it can take five. An
index over the foreign key **and the order column, in that order**, is already
sorted within each parent, so the database walks it and stops at five:

```ts
const post = s.model({
  id: s.string().id().ulid(),
  title: s.string(),
  createdAt: s.dateTime().now(),
  authorId: s.string(),
  author: s.toOne(() => user).fields("authorId").references("id"),
}).index(["authorId", "createdAt"]);
```

The foreign key comes first because it is the equality; the order column comes
second because it is the sort. Reversing them serves neither. Declaring it also
replaces the automatic foreign-key index rather than adding a second one, since
its leading column is the same.

A relation you include **without** `orderBy`, or without `take`/`skip`, does not
need this — the automatic foreign-key index already answers it.

### `.rawIndex(name, templates)`

Declares an index in each database's own language, for what `.index()` cannot
say: a pgvector or SurrealDB HNSW index, an expression, a key order, a prefix.

```ts
const document = s
  .model({
    id: s.string().id(),
    embedding: s.vector().dimension(1536),
  })
  .rawIndex("document_embedding_hnsw", {
    pg: "CREATE INDEX {name} ON {table} USING hnsw ({embedding} vector_cosine_ops)",
    surrealdb:
      "DEFINE INDEX {name} ON {table} FIELDS {embedding} HNSW DIMENSION 1536 DIST COSINE",
  });
```

- The keys are `pg`, `mysql`, `sqlite` (also libSQL and D1) and `surrealdb`. A
  database without a text gets no such index.
- A SQL text starts with `CREATE [UNIQUE] INDEX {name} ON {table}`, a SurrealQL
  one with `DEFINE INDEX {name} ON [TABLE] {table}`.
- `{name}` and `{table}` are the index's name and its table, the table qualified
  with the driver's namespace. Every other `{identifier}` must be a scalar field
  of the model, and becomes its escaped column. Each field written this way is a
  column the index covers; a text names at least one. A field named `name` or
  `table` cannot be a placeholder, and a column written out literally does not
  follow a rename. A `{word}` is a placeholder wherever it stands, inside a
  string literal too, and there is no escape for it.
- It is one statement, without a terminating `;`. VibORM writes some words
  itself, so the text cannot: `CONCURRENTLY` on PostgreSQL (a raw index is always
  built in the migration's transaction, `lowLock` included), `COMMENT`,
  `ALGORITHM` and `LOCK` on MySQL, `COMMENT` and `CONCURRENTLY` on SurrealDB.
- A raw index is never a key and never covers a foreign key: a `CREATE UNIQUE
  INDEX` text gives no `findUnique` selector, and the foreign key keeps its own
  index. On MySQL its columns are not narrowed to `VARCHAR(191)` for it, so index
  a `TEXT` column through a prefix (`{body}(32)`).
- Migrations create it, recognize it on the next `push`, drop and re-create it
  when its text changes, and drop it when the declaration goes. A renamed column
  is followed. VibORM recognizes its own raw indexes by a marker it keeps beside
  each one (the index's comment, or a trailing comment in SQLite's stored text);
  do not edit it.
- An index you made by hand under the same name is replaced, when VibORM can read
  it as an ordinary index. One it cannot read (an HNSW, an expression) still
  refuses synchronization before effects, as each migration driver's page says:
  drop it first, and the next push creates the declared one.

A JSON schema document cannot carry a raw index (`J009`).

### `.withoutRowid()`

On SQLite (also libSQL, D1 and Durable Objects), stores the table
`WITHOUT ROWID`: rows live in the primary key's order, with no hidden rowid,
which suits a table read by ranges of a compound key.

```ts
const textValue = s
  .model({
    prop: s.string(),
    v: s.string(),
    page: s.string(),
  })
  .id(["prop", "v", "page"])
  .withoutRowid();
```

- Migrations write `WITHOUT ROWID` into the table's `CREATE TABLE`, and keep it
  when they rebuild the table for another change. Adding or removing it later
  rebuilds the table.
- Introspection reads it back from SQLite's stored statement, so `verify`,
  `status` and `push` see a table that lost or gained it. That includes a
  table made `WITHOUT ROWID` by hand under a model that does not declare it:
  it is drift (`verify` fails, `apply` refuses with `V11014`, `push` rebuilds
  it as a rowid table), and adopting it is refused. Declare `.withoutRowid()`
  to keep it, or rebuild it without the option.
- On SQLite, an ID generated by `.increment()` refuses it before any migration
  runs: SQLite's `AUTOINCREMENT` needs a rowid.
- PostgreSQL, MySQL and SurrealDB ignore it.

### `.id(fields, options?)`

Defines a compound primary key (when multiple fields form the PK):

```ts
const membership = s.model({
  orgId: s.string(),
  userId: s.string(),
  role: s.string(),
}).id(["orgId", "userId"]);

// With custom selector name
.id(["orgId", "userId"], { name: "membership_pk" })
```

### `.unique(fields, options?)`

Adds a compound unique constraint:

```ts
const user = s.model({
  id: s.string().id(),
  email: s.string(),
  orgId: s.string(),
}).unique(["email", "orgId"]);

// With custom selector name
.unique(["email", "orgId"], { name: "user_email_org_unique" })
```

The `name` option controls the compound key used in `where`, cursors and other
unique selectors. It does not declare the database constraint's physical name;
VibORM generates that name. PostgreSQL/MySQL synchronization refuses an existing
equivalent constraint with a different, unrepresentable physical name before
applying effects, rather than dropping and recreating it solely to rename it.

A unique selector cannot be `null`: a unique index admits any number of NULLs,
so `{ email_orgId: { email: "a@b", orgId: null } }`, or `{ code: null }` on a
nullable `.unique()` field, names no single row. Every unique `where` refuses
it, in its types and at run time with a `ValidationError` before any
statement: `findUnique`, `findUniqueOrThrow`, `update`, `delete`, `upsert`,
every cursor, every nested link or target selector (`connect`,
`connectOrCreate`, `disconnect`, `set`, `update`, `upsert`, `delete`,
polymorphic items included) and the permissions extension's `can` and
`assert`, as Prisma does. Use `findFirst`, `updateMany` or `deleteMany` to
address rows by a NULL.

### `.extends(fields)`

Adds additional fields to an existing model:

```ts
const baseModel = s.model({
  id: s.string().id(),
  createdAt: s.dateTime().default(() => new Date()),
});

const user = baseModel.extends({
  email: s.string().unique(),
  name: s.string(),
});
```

## Index Options

| Option   | Type                                   | Description             |
| -------- | -------------------------------------- | ----------------------- |
| `name`   | `string`                               | Custom index name       |
| `type`   | `"btree" \| "hash" \| "gin" \| "gist"` | Index type (PostgreSQL) |
| `unique` | `boolean`                              | Unique index            |
| `where`  | `string`                               | Partial index condition |

## Complete Example

```ts
const user = s
  .model({
    // Primary key with auto-generation
    id: s.string().id().ulid(),

    // Required fields
    email: s.string().unique(),
    passwordHash: s.string(),
    orgId: s.string(),

    // Optional fields
    name: s.string().nullable(),
    bio: s.string().nullable(),

    // With defaults
    role: s.enum(["USER", "ADMIN"]).default("USER"),
    active: s.boolean().default(true),
    createdAt: s.dateTime().default(() => new Date()),
    updatedAt: s.dateTime().updatedAt(),

    // Relations
    posts: s.toMany(() => post),
    profile: s.toOne(() => profile),
  })
  .index(["email"])
  .index(["role", "active"])
  .unique(["email", "orgId"]);
```
