Skip to content
VibORM
Esc
↑↓navigate↵open⌘Jpreview
On this page

Model

Define database tables with fields: scalar columns and relation associations

Creating a Model

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

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: 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 or 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, 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:

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

Chainable Methods

.map(tableName)

Sets the database table name.

.map("users")
.map("user_accounts")

.index(fields, options?)

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

// 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:

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:

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.

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.

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

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:

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:

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

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"]);

Was this page helpful?