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:
_countis reserved for relation counts: inselectandincludeit always means the counts of the model’s relations;_distanceis 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) andsurrealdb. A database without a text gets no such index. - A SQL text starts with
CREATE [UNIQUE] INDEX {name} ON {table}, a SurrealQL one withDEFINE 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 namednameortablecannot 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:CONCURRENTLYon PostgreSQL (a raw index is always built in the migration’s transaction,lowLockincluded),COMMENT,ALGORITHMandLOCKon MySQL,COMMENTandCONCURRENTLYon SurrealDB. - A raw index is never a key and never covers a foreign key: a
CREATE UNIQUE INDEXtext gives nofindUniqueselector, and the foreign key keeps its own index. On MySQL its columns are not narrowed toVARCHAR(191)for it, so index aTEXTcolumn 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 ROWIDinto the table’sCREATE 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,statusandpushsee a table that lost or gained it. That includes a table madeWITHOUT ROWIDby hand under a model that does not declare it: it is drift (verifyfails,applyrefuses withV11014,pushrebuilds 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’sAUTOINCREMENTneeds 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"]);