Cookbook

Examples

Every example is generated and verified against the live driver — the TypeScript you author on the left, the native DDL Schemic emits on the right. Switch databases with the picker in the nav, and filter by category below.

tables

SCHEMAFULL (the default)

defineTable is SCHEMAFULL by default — fields are constrained to the declared shape.

schema.ts TypeScript

defineTable("account", { id: s.string(), name: s.string() })

Generated SurrealQL

DEFINE TABLE account TYPE NORMAL SCHEMAFULL;
DEFINE FIELD name ON TABLE account TYPE string;

SCHEMALESS

Opt out with .schemaless() — records may carry undeclared fields.

schema.ts TypeScript

defineTable("blob", { id: s.string() }).schemaless()

Generated SurrealQL

DEFINE TABLE blob TYPE NORMAL SCHEMALESS;

TYPE ANY

Accept any record shape (.typeAny()).

schema.ts TypeScript

defineTable("misc", { id: s.string() }).typeAny()

Generated SurrealQL

DEFINE TABLE misc TYPE ANY SCHEMAFULL;

COMMENT

schema.ts TypeScript

defineTable("account", { id: s.string() }).comment("billing accounts")

Generated SurrealQL

DEFINE TABLE account TYPE NORMAL SCHEMAFULL COMMENT "billing accounts";

table PERMISSIONS

Per-operation row guards; an omitted op defaults to NONE.

schema.ts TypeScript

defineTable("doc", { id: s.string() }).permissions({
  select: surql`true`,
  create: surql`$auth.id != NONE`,
  update: surql`$auth.id = id.owner`,
  delete: surql`$auth.admin = true`,
})

Generated SurrealQL

DEFINE TABLE doc
  TYPE NORMAL
  SCHEMAFULL
  PERMISSIONS
    FOR select WHERE true
    FOR create WHERE $auth.id != NONE
    FOR update WHERE $auth.id = id.owner
    FOR delete WHERE $auth.admin = true;

CHANGEFEED

Retain a change feed for the table; INCLUDE ORIGINAL via the option.

schema.ts TypeScript

defineTable("audited", { id: s.string() }).changefeed("7d", { includeOriginal: true })

Generated SurrealQL

DEFINE TABLE audited
  TYPE NORMAL
  SCHEMAFULL
  CHANGEFEED 7d INCLUDE ORIGINAL;

DROP

A DROP table discards writes (TYPE NORMAL DROP) — useful to retire a table's data.

schema.ts TypeScript

defineTable("legacy", { id: s.string() }).drop(true)

Generated SurrealQL

DEFINE TABLE legacy TYPE NORMAL DROP SCHEMAFULL;

TYPE RELATION (edge table) with FROM / TO / ENFORCED

defineRelation + .from()/.to() restrict endpoints; .enforced() requires both to exist on RELATE.

schema.ts TypeScript

defineRelation("likes", { since: s.datetime() })
  .from(defineTable("user", { id: s.string() }))
  .to(defineTable("post", { id: s.string() }))
  .enforced()

Generated SurrealQL

DEFINE TABLE likes TYPE RELATION FROM user TO post ENFORCED SCHEMAFULL;
DEFINE FIELD since ON TABLE likes TYPE datetime;

TYPE RELATION — unrestricted endpoints

Endpoints are optional; a bare relation links any record to any record.

schema.ts TypeScript

defineRelation("touches", {})

Generated SurrealQL

DEFINE TABLE touches TYPE RELATION SCHEMAFULL;

Pre-computed VIEW (AS SELECT)

defineView emits TYPE ANY SCHEMALESS AS <query>; rows are kept in sync by SurrealDB. No authored fields.

schema.ts TypeScript

defineView("adults", surql`SELECT name, age FROM user WHERE age >= 18`)

Generated SurrealQL

DEFINE TABLE adults
  TYPE ANY
  SCHEMALESS AS SELECT name, age FROM user WHERE age >= 18;

field-types

Scalars

Plus aliases: s.int32/uint32/bigint, s.date (datetime).

schema.ts TypeScript

defineTable("scalars", {
  id: s.string(),
  str: s.string(),
  i: s.int(),
  f: s.float(),
  dec: s.decimal(),
  num: s.number(),
  b: s.boolean(),
  when: s.datetime(),
  uid: s.uuid(),
  raw: s.bytes(),
  dur: s.duration(),
  doc: s.file(),
})

Generated SurrealQL

DEFINE TABLE scalars TYPE NORMAL SCHEMAFULL;
DEFINE FIELD str ON TABLE scalars TYPE string;
DEFINE FIELD i ON TABLE scalars TYPE int;
DEFINE FIELD f ON TABLE scalars TYPE float;
DEFINE FIELD dec ON TABLE scalars TYPE decimal;
DEFINE FIELD num ON TABLE scalars TYPE number;
DEFINE FIELD b ON TABLE scalars TYPE bool;
DEFINE FIELD when ON TABLE scalars TYPE datetime;
DEFINE FIELD uid ON TABLE scalars TYPE uuid;
DEFINE FIELD raw ON TABLE scalars TYPE bytes;
DEFINE FIELD dur ON TABLE scalars TYPE duration;
DEFINE FIELD doc ON TABLE scalars TYPE file;

any / null

schema.ts TypeScript

defineTable("loose", { id: s.string(), anything: s.any(), nothing: s.null() })

Generated SurrealQL

DEFINE TABLE loose TYPE NORMAL SCHEMAFULL;
DEFINE FIELD anything ON TABLE loose TYPE any;
DEFINE FIELD nothing ON TABLE loose TYPE null;

Optionality — option<T> vs T | null vs option<T | null>

Kept distinct (absent vs present-null vs both), unlike SQL drivers that collapse them.

schema.ts TypeScript

defineTable("opt", {
  id: s.string(),
  maybe: s.string().optional(),
  nullable: s.string().nullable(),
  both: s.string().nullish(),
})

Generated SurrealQL

DEFINE TABLE opt TYPE NORMAL SCHEMAFULL;
DEFINE FIELD maybe ON TABLE opt TYPE option<string>;
DEFINE FIELD nullable ON TABLE opt TYPE string | null;
DEFINE FIELD both ON TABLE opt TYPE option<string | null>;

Containers — array / set (with max), object, tuple

schema.ts TypeScript

defineTable("containers", {
  id: s.string(),
  tags: s.array(s.string()),
  top3: s.array(s.string(), { max: 3 }),
  uniq: s.set(s.int()),
  coords: s.tuple([s.float(), s.float()]),
  meta: s.object({ k: s.string(), n: s.int() }),
})

Generated SurrealQL

DEFINE TABLE containers TYPE NORMAL SCHEMAFULL;
DEFINE FIELD tags ON TABLE containers TYPE array<string>;
DEFINE FIELD top3 ON TABLE containers TYPE array<string, 3>;
DEFINE FIELD uniq ON TABLE containers TYPE set<int>;
DEFINE FIELD coords ON TABLE containers TYPE [float, float];
DEFINE FIELD meta ON TABLE containers TYPE object;
DEFINE FIELD meta.k ON TABLE containers TYPE string;
DEFINE FIELD meta.n ON TABLE containers TYPE int;

Literals, enums, scalar unions

schema.ts TypeScript

defineTable("choice", {
  id: s.string(),
  kind: s.literal("a"),
  status: s.enum(["draft", "live", "archived"]),
  idOrName: s.union([s.int(), s.string()]),
})

Generated SurrealQL

DEFINE TABLE choice TYPE NORMAL SCHEMAFULL;
DEFINE FIELD kind ON TABLE choice TYPE "a";
DEFINE FIELD status ON TABLE choice TYPE "draft" | "live" | "archived";
DEFINE FIELD idOrName ON TABLE choice TYPE int | string;

Record links — record<table>, multi-table, array<record<…>>

schema.ts TypeScript

defineTable("links", {
  id: s.string(),
  author: s.recordId("user"),
  owner: s.recordId(["user", "org"]),
  tags: s.array(s.recordId("tag")),
})

Generated SurrealQL

DEFINE TABLE links TYPE NORMAL SCHEMAFULL;
DEFINE FIELD author ON TABLE links TYPE record<user>;
DEFINE FIELD owner ON TABLE links TYPE record<user | org>;
DEFINE FIELD tags ON TABLE links TYPE array<record<tag>>;

Geometry — bare and the 7 kinds

schema.ts TypeScript

defineTable("geo", {
  id: s.string(),
  any: s.geometry(),
  pt: s.geometry("point"),
  ln: s.geometry("line"),
  poly: s.geometry("polygon"),
  mpt: s.geometry("multipoint"),
  mln: s.geometry("multiline"),
  mpoly: s.geometry("multipolygon"),
  coll: s.geometry("collection"),
})

Generated SurrealQL

DEFINE TABLE geo TYPE NORMAL SCHEMAFULL;
DEFINE FIELD any ON TABLE geo TYPE geometry;
DEFINE FIELD pt ON TABLE geo TYPE geometry<point>;
DEFINE FIELD ln ON TABLE geo TYPE geometry<line>;
DEFINE FIELD poly ON TABLE geo TYPE geometry<polygon>;
DEFINE FIELD mpt ON TABLE geo TYPE geometry<multipoint>;
DEFINE FIELD mln ON TABLE geo TYPE geometry<multiline>;
DEFINE FIELD mpoly ON TABLE geo TYPE geometry<multipolygon>;
DEFINE FIELD coll ON TABLE geo TYPE geometry<collection>;

field-clauses

DEFAULT and DEFAULT ALWAYS

Literals stay bare; wrap an expression in surql`…`. ALWAYS re-applies on every update.

schema.ts TypeScript

defineTable("d", {
  id: s.string(),
  role: s.string().$default("member"),
  seen: s.int().$default(0),
  touched: s.datetime().$defaultAlways(surql`time::now()`),
})

Generated SurrealQL

DEFINE TABLE d TYPE NORMAL SCHEMAFULL;
DEFINE FIELD role ON TABLE d TYPE string DEFAULT "member";
DEFINE FIELD seen ON TABLE d TYPE int DEFAULT 0;
DEFINE FIELD touched ON TABLE d TYPE datetime DEFAULT ALWAYS time::now();

VALUE and COMPUTED

VALUE computes/coerces on write; COMPUTED is a derived (virtual) value.

schema.ts TypeScript

defineTable("c", {
  id: s.string(),
  email: s.string().$value(surql`string::lowercase($value)`),
  full: s.string().$computed(surql`name.first + ' ' + name.last`),
})

Generated SurrealQL

DEFINE TABLE c TYPE NORMAL SCHEMAFULL;
DEFINE FIELD email ON TABLE c
  TYPE string
  VALUE string::lowercase($value);
DEFINE FIELD full ON TABLE c
  TYPE string COMPUTED name.first + ' ' + name.last;

ASSERT — raw and baked $-constraints

$min/$max/$length/$gt/... bake into ASSERT; $assert(surql`…`) is the raw escape.

schema.ts TypeScript

defineTable("a", {
  id: s.string(),
  age: s.int().$min(0).$max(120),
  name: s.string().$length(64),
  score: s.float().$gte(0).$lte(1),
  slug: s.string().$assert(surql`$value = /^[a-z-]+$/`),
})

Generated SurrealQL

DEFINE TABLE a TYPE NORMAL SCHEMAFULL;
DEFINE FIELD age ON TABLE a
  TYPE int
  ASSERT $value >= 0 AND $value <= 120;
DEFINE FIELD name ON TABLE a
  TYPE string
  ASSERT string::len($value) == 64;
DEFINE FIELD score ON TABLE a
  TYPE float
  ASSERT $value >= 0 AND $value <= 1;
DEFINE FIELD slug ON TABLE a TYPE string ASSERT $value = /^[a-z-]+$/;

String-format builders (reverse to the builder on pull)

Each bakes an ASSERT; pull recovers the builder (s.email()) not the raw string ASSERT.

schema.ts TypeScript

defineTable("fmt", {
  id: s.string(),
  email: s.email(),
  site: s.url(),
  ip: s.ipv4(),
  id2: s.ulid(),
})

Generated SurrealQL

DEFINE TABLE fmt TYPE NORMAL SCHEMAFULL;
DEFINE FIELD email ON TABLE fmt
  TYPE string
  ASSERT string::is_email($value);
DEFINE FIELD site ON TABLE fmt TYPE string ASSERT string::is_url($value);
DEFINE FIELD ip ON TABLE fmt TYPE string ASSERT string::is_ipv4($value);
DEFINE FIELD id2 ON TABLE fmt TYPE string ASSERT string::is_ulid($value);

READONLY and field COMMENT

schema.ts TypeScript

defineTable("rc", {
  id: s.string(),
  createdAt: s.datetime().$readonly(),
  note: s.string().$comment("free-form note"),
})

Generated SurrealQL

DEFINE TABLE rc TYPE NORMAL SCHEMAFULL;
DEFINE FIELD createdAt ON TABLE rc TYPE datetime READONLY;
DEFINE FIELD note ON TABLE rc TYPE string COMMENT "free-form note";

FLEXIBLE object

.flexible() (alias .loose()) lets a typed object also carry undeclared keys.

schema.ts TypeScript

defineTable("fx", { id: s.string(), meta: s.object({ k: s.string() }).flexible() })

Generated SurrealQL

DEFINE TABLE fx TYPE NORMAL SCHEMAFULL;
DEFINE FIELD meta ON TABLE fx TYPE object FLEXIBLE;
DEFINE FIELD meta.k ON TABLE fx TYPE string;

Field PERMISSIONS

Field-level guards: select/create/update (no delete at field level, matching SurrealQL).

schema.ts TypeScript

defineTable("fp", {
  id: s.string(),
  secret: s.string().$permissions({
    select: surql`$auth.admin = true`,
    update: surql`false`,
  }),
})

Generated SurrealQL

DEFINE TABLE fp TYPE NORMAL SCHEMAFULL;
DEFINE FIELD secret ON TABLE fp
  TYPE string
  PERMISSIONS
    FOR select WHERE $auth.admin = true
    FOR update WHERE false;

REFERENCE — record link with ON DELETE

Reference integrity: REJECT | CASCADE | UNSET | IGNORE | THEN <expr>.

schema.ts TypeScript

defineTable("ref", {
  id: s.string(),
  author: s.recordId("user").reference({ onDelete: "cascade" }),
})

Generated SurrealQL

DEFINE TABLE ref TYPE NORMAL SCHEMAFULL;
DEFINE FIELD author ON TABLE ref
  TYPE record<user>
  REFERENCE ON DELETE CASCADE;

indexes

Plain and composite index

schema.ts TypeScript

defineTable("p", { id: s.string(), a: s.string(), b: s.string() }).index("ab", ["a", "b"])

Generated SurrealQL

DEFINE TABLE p TYPE NORMAL SCHEMAFULL;
DEFINE FIELD a ON TABLE p TYPE string;
DEFINE FIELD b ON TABLE p TYPE string;
DEFINE INDEX ab ON TABLE p FIELDS a, b;

UNIQUE (single field via field.unique() or table.index)

schema.ts TypeScript

defineTable("u", { id: s.string(), email: s.string() }).index("uq", ["email"], { unique: true })

Generated SurrealQL

DEFINE TABLE u TYPE NORMAL SCHEMAFULL;
DEFINE FIELD email ON TABLE u TYPE string;
DEFINE INDEX uq ON TABLE u FIELDS email UNIQUE;

COUNT (materialized row-count, no FIELDS)

schema.ts TypeScript

defineTable("ct", { id: s.string() }).index("rows", [], { count: true })

Generated SurrealQL

DEFINE TABLE ct TYPE NORMAL SCHEMAFULL;
DEFINE INDEX rows ON TABLE ct COUNT;

COMMENT

schema.ts TypeScript

defineTable("cm", { id: s.string(), email: s.string() }).index("uq", ["email"], { unique: true, comment: "email is unique" })

Generated SurrealQL

DEFINE TABLE cm TYPE NORMAL SCHEMAFULL;
DEFINE FIELD email ON TABLE cm TYPE string;
DEFINE INDEX uq ON TABLE cm FIELDS email UNIQUE
  COMMENT "email is unique";

Vector HNSW — minimal (defaults stripped)

Only DIMENSION authored; SurrealDB materializes DIST/TYPE/EFC/M/M0/LM — all stripped so it round-trips.

schema.ts TypeScript

defineTable("vh", { id: s.string(), emb: s.array(s.float()) }).index("vec", ["emb"], { hnsw: { dimension: 4 } })

Generated SurrealQL

DEFINE TABLE vh TYPE NORMAL SCHEMAFULL;
DEFINE FIELD emb ON TABLE vh TYPE array<float>;
DEFINE INDEX vec ON TABLE vh FIELDS emb HNSW DIMENSION 4;

Vector HNSW — tuned

schema.ts TypeScript

defineTable("vh2", { id: s.string(), emb: s.array(s.float()) }).index("vec", ["emb"], { hnsw: { dimension: 8, dist: "cosine", type: "f64", efc: 200, m: 16 } })

Generated SurrealQL

DEFINE TABLE vh2 TYPE NORMAL SCHEMAFULL;
DEFINE FIELD emb ON TABLE vh2 TYPE array<float>;
DEFINE INDEX vec ON TABLE vh2 FIELDS emb HNSW DIMENSION 8 DIST COSINE
  TYPE F64 EFC 200 M 16;

Vector DISKANN — minimal

schema.ts TypeScript

defineTable("vd", { id: s.string(), emb: s.array(s.float()) }).index("vec", ["emb"], { diskann: { dimension: 4 } })

Generated SurrealQL

DEFINE TABLE vd TYPE NORMAL SCHEMAFULL;
DEFINE FIELD emb ON TABLE vd TYPE array<float>;
DEFINE INDEX vec ON TABLE vd FIELDS emb DISKANN DIMENSION 4;

FULLTEXT search index + DEFINE ANALYZER

A FULLTEXT index deps on its analyzer (emitted first). Default BM25(1.2,0.75) is stripped.

schema.ts TypeScript

[
  defineAnalyzer("english", {
    tokenizers: ["blank"],
    filters: ["lowercase", "snowball(english)"],
  }),
  defineTable("doc", { id: s.string(), content: s.string() }).index("ft", ["content"], {
    fulltext: { analyzer: "english", highlights: true },
  }),
]

Generated SurrealQL

DEFINE ANALYZER english TOKENIZERS BLANK FILTERS LOWERCASE, SNOWBALL(ENGLISH);
DEFINE TABLE doc TYPE NORMAL SCHEMAFULL;
DEFINE FIELD content ON TABLE doc TYPE string;
DEFINE INDEX ft ON TABLE doc FIELDS content FULLTEXT ANALYZER english HIGHLIGHTS;

events

Inline event with WHEN + THEN

schema.ts TypeScript

defineTable("account", { id: s.string(), balance: s.float() }).event("overdraft", {
  when: surql`$after.balance < 0`,
  then: surql`CREATE alert SET account = $after.id, kind = 'overdraft'`,
})

Generated SurrealQL

DEFINE TABLE account TYPE NORMAL SCHEMAFULL;
DEFINE FIELD balance ON TABLE account TYPE float;
DEFINE EVENT overdraft ON TABLE account
  WHEN $after.balance < 0
  THEN CREATE alert SET account = $after.id, kind = 'overdraft';

Event without WHEN (fires on every change)

schema.ts TypeScript

defineTable("doc", { id: s.string() }).event("touch", {
  then: surql`UPDATE audit SET at = time::now()`,
})

Generated SurrealQL

DEFINE TABLE doc TYPE NORMAL SCHEMAFULL;
DEFINE EVENT touch ON TABLE doc THEN UPDATE audit SET at = time::now();

Standalone event with an ordered THEN array

defineEvent declares an event apart from its table; pull regenerates it inline.

schema.ts TypeScript

defineEvent("account", "onCreate", {
  when: surql`$event = "CREATE"`,
  then: [
    surql`CREATE log SET account = $after.id`,
    surql`UPDATE stats SET count += 1`,
  ],
})

Generated SurrealQL

DEFINE EVENT onCreate ON TABLE account
  WHEN $event = "CREATE"
  THEN (CREATE log SET account = $after.id), (UPDATE stats SET count += 1);

functions

Function with args, return type, body

schema.ts TypeScript

defineFunction("greet", { name: s.string() })
  .returns(s.string())
  .body(surql`RETURN "hi " + $name`)

Generated SurrealQL

DEFINE FUNCTION fn::greet($name: string) -> string { RETURN "hi " + $name };

Function with PERMISSIONS and COMMENT

schema.ts TypeScript

defineFunction("tax", { amount: s.float() })
  .returns(s.float())
  .body(surql`RETURN $amount * 0.21`)
  .permissions(surql`$auth.id != NONE`)
  .comment("VAT helper")

Generated SurrealQL

DEFINE FUNCTION fn::tax($amount: float) -> float { RETURN $amount * 0.21 }
  PERMISSIONS $auth.id != NONE
  COMMENT "VAT helper";

access

RECORD access (SIGNUP / SIGNIN / DURATION)

schema.ts TypeScript

defineAccess("user")
  .record()
  .signup(surql`CREATE user SET email = $email, pass = crypto::argon2::generate($pass)`)
  .signin(surql`SELECT * FROM user WHERE email = $email AND crypto::argon2::compare(pass, $pass)`)
  .duration({ token: "1h", session: "12h" })

Generated SurrealQL

DEFINE ACCESS user ON DATABASE
  TYPE RECORD
  SIGNUP { CREATE user SET email = $email, pass = crypto::argon2::generate($pass) }
  SIGNIN { SELECT * FROM user WHERE email = $email AND crypto::argon2::compare(pass, $pass) }
  DURATION
    FOR TOKEN 1h,
    FOR SESSION 12h;

RECORD access with AUTHENTICATE

schema.ts TypeScript

defineAccess("api")
  .record()
  .authenticate(surql`RETURN $auth`)
  .duration({ session: "1d" })

Generated SurrealQL

DEFINE ACCESS api ON DATABASE
  TYPE RECORD
  AUTHENTICATE { RETURN $auth }
  DURATION
    FOR SESSION 1d;

JWT access (validate external tokens)

Structure (alg + key/url) applies + introspects, but the signing KEY is redacted on pull.

schema.ts TypeScript

defineAccess("external").jwt({ alg: "HS512", key: "secret" }).onDatabase()

Generated SurrealQL

DEFINE ACCESS external ON DATABASE TYPE JWT ALGORITHM HS512 KEY "secret";

BEARER access (API-key grants)

Subject + duration round-trip; the grant secret is redacted on introspect.

schema.ts TypeScript

defineAccess("apikey").bearer({ for: "user" }).duration({ session: "30d" })

Generated SurrealQL

DEFINE ACCESS apikey ON DATABASE
  TYPE BEARER
    FOR USER
  DURATION
    FOR SESSION 30d;

analyzers

Analyzer with tokenizers + filters

schema.ts TypeScript

defineAnalyzer("english", {
  tokenizers: ["blank", "class"],
  filters: ["lowercase", "snowball(english)"],
})

Generated SurrealQL

DEFINE ANALYZER english TOKENIZERS BLANK, CLASS FILTERS LOWERCASE, SNOWBALL(ENGLISH);

Analyzer with tokenizers only

schema.ts TypeScript

defineAnalyzer("simple", { tokenizers: ["blank"] })

Generated SurrealQL

DEFINE ANALYZER simple TOKENIZERS BLANK;

escape-hatch

.$surreal(wire, codec) — store an instanceof type as a string

App type = Money, wire/DDL type = string; the codec maps both ways. Clears the no-DDL brand.

schema.ts TypeScript

defineTable("wallet", {
  id: s.string(),
  price: s.instanceof(Money).$surreal(s.string(), {
    encode: (m) => m.toString(),
    decode: (v) => new Money(Math.round(Number(v) * 100)),
  }),
})

Generated SurrealQL

DEFINE TABLE wallet TYPE NORMAL SCHEMAFULL;
DEFINE FIELD price ON TABLE wallet TYPE string;

.$surreal on s.custom — store an app-only type (URL) as a string

URL has no native SurrealQL type, so it needs a wire type + codec. (A JS Set, by contrast, is native: use s.set() -> set<T>.)

schema.ts TypeScript

defineTable("site", {
  id: s.string(),
  homepage: s.custom<URL>().$surreal(s.string(), {
    encode: (u) => u.href,
    decode: (v) => new URL(v),
  }),
})

Generated SurrealQL

DEFINE TABLE site TYPE NORMAL SCHEMAFULL;
DEFINE FIELD homepage ON TABLE site TYPE string;

.$internal() — DB-managed, client-hidden field (PERMISSIONS NONE)

schema.ts TypeScript

defineTable("account", { id: s.string(), passhash: s.string().$internal() })

Generated SurrealQL

DEFINE TABLE account TYPE NORMAL SCHEMAFULL;
DEFINE FIELD passhash ON TABLE account TYPE string PERMISSIONS NONE;

tables

SCHEMAFULL (the default)

defineTable is SCHEMAFULL by default — fields are constrained to the declared shape.

schema.ts TypeScript

defineTable("account", { id: s.string(), name: s.string() })

Generated SurrealQL

DEFINE TABLE account TYPE NORMAL SCHEMAFULL;
DEFINE FIELD name ON TABLE account TYPE string;

SCHEMALESS

Opt out with .schemaless() — records may carry undeclared fields.

schema.ts TypeScript

defineTable("blob", { id: s.string() }).schemaless()

Generated SurrealQL

DEFINE TABLE blob TYPE NORMAL SCHEMALESS;

TYPE ANY

Accept any record shape (.typeAny()).

schema.ts TypeScript

defineTable("misc", { id: s.string() }).typeAny()

Generated SurrealQL

DEFINE TABLE misc TYPE ANY SCHEMAFULL;

COMMENT

schema.ts TypeScript

defineTable("account", { id: s.string() }).comment("billing accounts")

Generated SurrealQL

DEFINE TABLE account TYPE NORMAL SCHEMAFULL COMMENT "billing accounts";

table PERMISSIONS

Per-operation row guards; an omitted op defaults to NONE.

schema.ts TypeScript

defineTable("doc", { id: s.string() }).permissions({
  select: surql`true`,
  create: surql`$auth.id != NONE`,
  update: surql`$auth.id = id.owner`,
  delete: surql`$auth.admin = true`,
})

Generated SurrealQL

DEFINE TABLE doc
  TYPE NORMAL
  SCHEMAFULL
  PERMISSIONS
    FOR select WHERE true
    FOR create WHERE $auth.id != NONE
    FOR update WHERE $auth.id = id.owner
    FOR delete WHERE $auth.admin = true;

CHANGEFEED

Retain a change feed for the table; INCLUDE ORIGINAL via the option.

schema.ts TypeScript

defineTable("audited", { id: s.string() }).changefeed("7d", { includeOriginal: true })

Generated SurrealQL

DEFINE TABLE audited
  TYPE NORMAL
  SCHEMAFULL
  CHANGEFEED 7d INCLUDE ORIGINAL;

DROP

A DROP table discards writes (TYPE NORMAL DROP) — useful to retire a table's data.

schema.ts TypeScript

defineTable("legacy", { id: s.string() }).drop(true)

Generated SurrealQL

DEFINE TABLE legacy TYPE NORMAL DROP SCHEMAFULL;

TYPE RELATION (edge table) with FROM / TO / ENFORCED

defineRelation + .from()/.to() restrict endpoints; .enforced() requires both to exist on RELATE.

schema.ts TypeScript

defineRelation("likes", { since: s.datetime() })
  .from(defineTable("user", { id: s.string() }))
  .to(defineTable("post", { id: s.string() }))
  .enforced()

Generated SurrealQL

DEFINE TABLE likes TYPE RELATION FROM user TO post ENFORCED SCHEMAFULL;
DEFINE FIELD since ON TABLE likes TYPE datetime;

TYPE RELATION — unrestricted endpoints

Endpoints are optional; a bare relation links any record to any record.

schema.ts TypeScript

defineRelation("touches", {})

Generated SurrealQL

DEFINE TABLE touches TYPE RELATION SCHEMAFULL;

Pre-computed VIEW (AS SELECT)

defineView emits TYPE ANY SCHEMALESS AS <query>; rows are kept in sync by SurrealDB. No authored fields.

schema.ts TypeScript

defineView("adults", surql`SELECT name, age FROM user WHERE age >= 18`)

Generated SurrealQL

DEFINE TABLE adults
  TYPE ANY
  SCHEMALESS AS SELECT name, age FROM user WHERE age >= 18;

field-types

Scalars

Plus aliases: s.int32/uint32/bigint, s.date (datetime).

schema.ts TypeScript

defineTable("scalars", {
  id: s.string(),
  str: s.string(),
  i: s.int(),
  f: s.float(),
  dec: s.decimal(),
  num: s.number(),
  b: s.boolean(),
  when: s.datetime(),
  uid: s.uuid(),
  raw: s.bytes(),
  dur: s.duration(),
  doc: s.file(),
})

Generated SurrealQL

DEFINE TABLE scalars TYPE NORMAL SCHEMAFULL;
DEFINE FIELD str ON TABLE scalars TYPE string;
DEFINE FIELD i ON TABLE scalars TYPE int;
DEFINE FIELD f ON TABLE scalars TYPE float;
DEFINE FIELD dec ON TABLE scalars TYPE decimal;
DEFINE FIELD num ON TABLE scalars TYPE number;
DEFINE FIELD b ON TABLE scalars TYPE bool;
DEFINE FIELD when ON TABLE scalars TYPE datetime;
DEFINE FIELD uid ON TABLE scalars TYPE uuid;
DEFINE FIELD raw ON TABLE scalars TYPE bytes;
DEFINE FIELD dur ON TABLE scalars TYPE duration;
DEFINE FIELD doc ON TABLE scalars TYPE file;

any / null

schema.ts TypeScript

defineTable("loose", { id: s.string(), anything: s.any(), nothing: s.null() })

Generated SurrealQL

DEFINE TABLE loose TYPE NORMAL SCHEMAFULL;
DEFINE FIELD anything ON TABLE loose TYPE any;
DEFINE FIELD nothing ON TABLE loose TYPE null;

Optionality — option<T> vs T | null vs option<T | null>

Kept distinct (absent vs present-null vs both), unlike SQL drivers that collapse them.

schema.ts TypeScript

defineTable("opt", {
  id: s.string(),
  maybe: s.string().optional(),
  nullable: s.string().nullable(),
  both: s.string().nullish(),
})

Generated SurrealQL

DEFINE TABLE opt TYPE NORMAL SCHEMAFULL;
DEFINE FIELD maybe ON TABLE opt TYPE option<string>;
DEFINE FIELD nullable ON TABLE opt TYPE string | null;
DEFINE FIELD both ON TABLE opt TYPE option<string | null>;

Containers — array / set (with max), object, tuple

schema.ts TypeScript

defineTable("containers", {
  id: s.string(),
  tags: s.array(s.string()),
  top3: s.array(s.string(), { max: 3 }),
  uniq: s.set(s.int()),
  coords: s.tuple([s.float(), s.float()]),
  meta: s.object({ k: s.string(), n: s.int() }),
})

Generated SurrealQL

DEFINE TABLE containers TYPE NORMAL SCHEMAFULL;
DEFINE FIELD tags ON TABLE containers TYPE array<string>;
DEFINE FIELD top3 ON TABLE containers TYPE array<string, 3>;
DEFINE FIELD uniq ON TABLE containers TYPE set<int>;
DEFINE FIELD coords ON TABLE containers TYPE [float, float];
DEFINE FIELD meta ON TABLE containers TYPE object;
DEFINE FIELD meta.k ON TABLE containers TYPE string;
DEFINE FIELD meta.n ON TABLE containers TYPE int;

Literals, enums, scalar unions

schema.ts TypeScript

defineTable("choice", {
  id: s.string(),
  kind: s.literal("a"),
  status: s.enum(["draft", "live", "archived"]),
  idOrName: s.union([s.int(), s.string()]),
})

Generated SurrealQL

DEFINE TABLE choice TYPE NORMAL SCHEMAFULL;
DEFINE FIELD kind ON TABLE choice TYPE "a";
DEFINE FIELD status ON TABLE choice TYPE "draft" | "live" | "archived";
DEFINE FIELD idOrName ON TABLE choice TYPE int | string;

Record links — record<table>, multi-table, array<record<…>>

schema.ts TypeScript

defineTable("links", {
  id: s.string(),
  author: s.recordId("user"),
  owner: s.recordId(["user", "org"]),
  tags: s.array(s.recordId("tag")),
})

Generated SurrealQL

DEFINE TABLE links TYPE NORMAL SCHEMAFULL;
DEFINE FIELD author ON TABLE links TYPE record<user>;
DEFINE FIELD owner ON TABLE links TYPE record<user | org>;
DEFINE FIELD tags ON TABLE links TYPE array<record<tag>>;

Geometry — bare and the 7 kinds

schema.ts TypeScript

defineTable("geo", {
  id: s.string(),
  any: s.geometry(),
  pt: s.geometry("point"),
  ln: s.geometry("line"),
  poly: s.geometry("polygon"),
  mpt: s.geometry("multipoint"),
  mln: s.geometry("multiline"),
  mpoly: s.geometry("multipolygon"),
  coll: s.geometry("collection"),
})

Generated SurrealQL

DEFINE TABLE geo TYPE NORMAL SCHEMAFULL;
DEFINE FIELD any ON TABLE geo TYPE geometry;
DEFINE FIELD pt ON TABLE geo TYPE geometry<point>;
DEFINE FIELD ln ON TABLE geo TYPE geometry<line>;
DEFINE FIELD poly ON TABLE geo TYPE geometry<polygon>;
DEFINE FIELD mpt ON TABLE geo TYPE geometry<multipoint>;
DEFINE FIELD mln ON TABLE geo TYPE geometry<multiline>;
DEFINE FIELD mpoly ON TABLE geo TYPE geometry<multipolygon>;
DEFINE FIELD coll ON TABLE geo TYPE geometry<collection>;

field-clauses

DEFAULT and DEFAULT ALWAYS

Literals stay bare; wrap an expression in surql`…`. ALWAYS re-applies on every update.

schema.ts TypeScript

defineTable("d", {
  id: s.string(),
  role: s.string().$default("member"),
  seen: s.int().$default(0),
  touched: s.datetime().$defaultAlways(surql`time::now()`),
})

Generated SurrealQL

DEFINE TABLE d TYPE NORMAL SCHEMAFULL;
DEFINE FIELD role ON TABLE d TYPE string DEFAULT "member";
DEFINE FIELD seen ON TABLE d TYPE int DEFAULT 0;
DEFINE FIELD touched ON TABLE d TYPE datetime DEFAULT ALWAYS time::now();

VALUE and COMPUTED

VALUE computes/coerces on write; COMPUTED is a derived (virtual) value.

schema.ts TypeScript

defineTable("c", {
  id: s.string(),
  email: s.string().$value(surql`string::lowercase($value)`),
  full: s.string().$computed(surql`name.first + ' ' + name.last`),
})

Generated SurrealQL

DEFINE TABLE c TYPE NORMAL SCHEMAFULL;
DEFINE FIELD email ON TABLE c
  TYPE string
  VALUE string::lowercase($value);
DEFINE FIELD full ON TABLE c
  TYPE string COMPUTED name.first + ' ' + name.last;

ASSERT — raw and baked $-constraints

$min/$max/$length/$gt/... bake into ASSERT; $assert(surql`…`) is the raw escape.

schema.ts TypeScript

defineTable("a", {
  id: s.string(),
  age: s.int().$min(0).$max(120),
  name: s.string().$length(64),
  score: s.float().$gte(0).$lte(1),
  slug: s.string().$assert(surql`$value = /^[a-z-]+$/`),
})

Generated SurrealQL

DEFINE TABLE a TYPE NORMAL SCHEMAFULL;
DEFINE FIELD age ON TABLE a
  TYPE int
  ASSERT $value >= 0 AND $value <= 120;
DEFINE FIELD name ON TABLE a
  TYPE string
  ASSERT string::len($value) == 64;
DEFINE FIELD score ON TABLE a
  TYPE float
  ASSERT $value >= 0 AND $value <= 1;
DEFINE FIELD slug ON TABLE a TYPE string ASSERT $value = /^[a-z-]+$/;

String-format builders (reverse to the builder on pull)

Each bakes an ASSERT; pull recovers the builder (s.email()) not the raw string ASSERT.

schema.ts TypeScript

defineTable("fmt", {
  id: s.string(),
  email: s.email(),
  site: s.url(),
  ip: s.ipv4(),
  id2: s.ulid(),
})

Generated SurrealQL

DEFINE TABLE fmt TYPE NORMAL SCHEMAFULL;
DEFINE FIELD email ON TABLE fmt
  TYPE string
  ASSERT string::is_email($value);
DEFINE FIELD site ON TABLE fmt TYPE string ASSERT string::is_url($value);
DEFINE FIELD ip ON TABLE fmt TYPE string ASSERT string::is_ipv4($value);
DEFINE FIELD id2 ON TABLE fmt TYPE string ASSERT string::is_ulid($value);

READONLY and field COMMENT

schema.ts TypeScript

defineTable("rc", {
  id: s.string(),
  createdAt: s.datetime().$readonly(),
  note: s.string().$comment("free-form note"),
})

Generated SurrealQL

DEFINE TABLE rc TYPE NORMAL SCHEMAFULL;
DEFINE FIELD createdAt ON TABLE rc TYPE datetime READONLY;
DEFINE FIELD note ON TABLE rc TYPE string COMMENT "free-form note";

FLEXIBLE object

.flexible() (alias .loose()) lets a typed object also carry undeclared keys.

schema.ts TypeScript

defineTable("fx", { id: s.string(), meta: s.object({ k: s.string() }).flexible() })

Generated SurrealQL

DEFINE TABLE fx TYPE NORMAL SCHEMAFULL;
DEFINE FIELD meta ON TABLE fx TYPE object FLEXIBLE;
DEFINE FIELD meta.k ON TABLE fx TYPE string;

Field PERMISSIONS

Field-level guards: select/create/update (no delete at field level, matching SurrealQL).

schema.ts TypeScript

defineTable("fp", {
  id: s.string(),
  secret: s.string().$permissions({
    select: surql`$auth.admin = true`,
    update: surql`false`,
  }),
})

Generated SurrealQL

DEFINE TABLE fp TYPE NORMAL SCHEMAFULL;
DEFINE FIELD secret ON TABLE fp
  TYPE string
  PERMISSIONS
    FOR select WHERE $auth.admin = true
    FOR update WHERE false;

REFERENCE — record link with ON DELETE

Reference integrity: REJECT | CASCADE | UNSET | IGNORE | THEN <expr>.

schema.ts TypeScript

defineTable("ref", {
  id: s.string(),
  author: s.recordId("user").reference({ onDelete: "cascade" }),
})

Generated SurrealQL

DEFINE TABLE ref TYPE NORMAL SCHEMAFULL;
DEFINE FIELD author ON TABLE ref
  TYPE record<user>
  REFERENCE ON DELETE CASCADE;

indexes

Plain and composite index

schema.ts TypeScript

defineTable("p", { id: s.string(), a: s.string(), b: s.string() }).index("ab", ["a", "b"])

Generated SurrealQL

DEFINE TABLE p TYPE NORMAL SCHEMAFULL;
DEFINE FIELD a ON TABLE p TYPE string;
DEFINE FIELD b ON TABLE p TYPE string;
DEFINE INDEX ab ON TABLE p FIELDS a, b;

UNIQUE (single field via field.unique() or table.index)

schema.ts TypeScript

defineTable("u", { id: s.string(), email: s.string() }).index("uq", ["email"], { unique: true })

Generated SurrealQL

DEFINE TABLE u TYPE NORMAL SCHEMAFULL;
DEFINE FIELD email ON TABLE u TYPE string;
DEFINE INDEX uq ON TABLE u FIELDS email UNIQUE;

COUNT (materialized row-count, no FIELDS)

schema.ts TypeScript

defineTable("ct", { id: s.string() }).index("rows", [], { count: true })

Generated SurrealQL

DEFINE TABLE ct TYPE NORMAL SCHEMAFULL;
DEFINE INDEX rows ON TABLE ct COUNT;

COMMENT

schema.ts TypeScript

defineTable("cm", { id: s.string(), email: s.string() }).index("uq", ["email"], { unique: true, comment: "email is unique" })

Generated SurrealQL

DEFINE TABLE cm TYPE NORMAL SCHEMAFULL;
DEFINE FIELD email ON TABLE cm TYPE string;
DEFINE INDEX uq ON TABLE cm FIELDS email UNIQUE
  COMMENT "email is unique";

Vector HNSW — minimal (defaults stripped)

Only DIMENSION authored; SurrealDB materializes DIST/TYPE/EFC/M/M0/LM — all stripped so it round-trips.

schema.ts TypeScript

defineTable("vh", { id: s.string(), emb: s.array(s.float()) }).index("vec", ["emb"], { hnsw: { dimension: 4 } })

Generated SurrealQL

DEFINE TABLE vh TYPE NORMAL SCHEMAFULL;
DEFINE FIELD emb ON TABLE vh TYPE array<float>;
DEFINE INDEX vec ON TABLE vh FIELDS emb HNSW DIMENSION 4;

Vector HNSW — tuned

schema.ts TypeScript

defineTable("vh2", { id: s.string(), emb: s.array(s.float()) }).index("vec", ["emb"], { hnsw: { dimension: 8, dist: "cosine", type: "f64", efc: 200, m: 16 } })

Generated SurrealQL

DEFINE TABLE vh2 TYPE NORMAL SCHEMAFULL;
DEFINE FIELD emb ON TABLE vh2 TYPE array<float>;
DEFINE INDEX vec ON TABLE vh2 FIELDS emb HNSW DIMENSION 8 DIST COSINE
  TYPE F64 EFC 200 M 16;

Vector DISKANN — minimal

schema.ts TypeScript

defineTable("vd", { id: s.string(), emb: s.array(s.float()) }).index("vec", ["emb"], { diskann: { dimension: 4 } })

Generated SurrealQL

DEFINE TABLE vd TYPE NORMAL SCHEMAFULL;
DEFINE FIELD emb ON TABLE vd TYPE array<float>;
DEFINE INDEX vec ON TABLE vd FIELDS emb DISKANN DIMENSION 4;

FULLTEXT search index + DEFINE ANALYZER

A FULLTEXT index deps on its analyzer (emitted first). Default BM25(1.2,0.75) is stripped.

schema.ts TypeScript

[
  defineAnalyzer("english", {
    tokenizers: ["blank"],
    filters: ["lowercase", "snowball(english)"],
  }),
  defineTable("doc", { id: s.string(), content: s.string() }).index("ft", ["content"], {
    fulltext: { analyzer: "english", highlights: true },
  }),
]

Generated SurrealQL

DEFINE ANALYZER english TOKENIZERS BLANK FILTERS LOWERCASE, SNOWBALL(ENGLISH);
DEFINE TABLE doc TYPE NORMAL SCHEMAFULL;
DEFINE FIELD content ON TABLE doc TYPE string;
DEFINE INDEX ft ON TABLE doc FIELDS content FULLTEXT ANALYZER english HIGHLIGHTS;

events

Inline event with WHEN + THEN

schema.ts TypeScript

defineTable("account", { id: s.string(), balance: s.float() }).event("overdraft", {
  when: surql`$after.balance < 0`,
  then: surql`CREATE alert SET account = $after.id, kind = 'overdraft'`,
})

Generated SurrealQL

DEFINE TABLE account TYPE NORMAL SCHEMAFULL;
DEFINE FIELD balance ON TABLE account TYPE float;
DEFINE EVENT overdraft ON TABLE account
  WHEN $after.balance < 0
  THEN CREATE alert SET account = $after.id, kind = 'overdraft';

Event without WHEN (fires on every change)

schema.ts TypeScript

defineTable("doc", { id: s.string() }).event("touch", {
  then: surql`UPDATE audit SET at = time::now()`,
})

Generated SurrealQL

DEFINE TABLE doc TYPE NORMAL SCHEMAFULL;
DEFINE EVENT touch ON TABLE doc THEN UPDATE audit SET at = time::now();

Standalone event with an ordered THEN array

defineEvent declares an event apart from its table; pull regenerates it inline.

schema.ts TypeScript

defineEvent("account", "onCreate", {
  when: surql`$event = "CREATE"`,
  then: [
    surql`CREATE log SET account = $after.id`,
    surql`UPDATE stats SET count += 1`,
  ],
})

Generated SurrealQL

DEFINE EVENT onCreate ON TABLE account
  WHEN $event = "CREATE"
  THEN (CREATE log SET account = $after.id), (UPDATE stats SET count += 1);

functions

Function with args, return type, body

schema.ts TypeScript

defineFunction("greet", { name: s.string() })
  .returns(s.string())
  .body(surql`RETURN "hi " + $name`)

Generated SurrealQL

DEFINE FUNCTION fn::greet($name: string) -> string { RETURN "hi " + $name };

Function with PERMISSIONS and COMMENT

schema.ts TypeScript

defineFunction("tax", { amount: s.float() })
  .returns(s.float())
  .body(surql`RETURN $amount * 0.21`)
  .permissions(surql`$auth.id != NONE`)
  .comment("VAT helper")

Generated SurrealQL

DEFINE FUNCTION fn::tax($amount: float) -> float { RETURN $amount * 0.21 }
  PERMISSIONS $auth.id != NONE
  COMMENT "VAT helper";

access

RECORD access (SIGNUP / SIGNIN / DURATION)

schema.ts TypeScript

defineAccess("user")
  .record()
  .signup(surql`CREATE user SET email = $email, pass = crypto::argon2::generate($pass)`)
  .signin(surql`SELECT * FROM user WHERE email = $email AND crypto::argon2::compare(pass, $pass)`)
  .duration({ token: "1h", session: "12h" })

Generated SurrealQL

DEFINE ACCESS user ON DATABASE
  TYPE RECORD
  SIGNUP { CREATE user SET email = $email, pass = crypto::argon2::generate($pass) }
  SIGNIN { SELECT * FROM user WHERE email = $email AND crypto::argon2::compare(pass, $pass) }
  DURATION
    FOR TOKEN 1h,
    FOR SESSION 12h;

RECORD access with AUTHENTICATE

schema.ts TypeScript

defineAccess("api")
  .record()
  .authenticate(surql`RETURN $auth`)
  .duration({ session: "1d" })

Generated SurrealQL

DEFINE ACCESS api ON DATABASE
  TYPE RECORD
  AUTHENTICATE { RETURN $auth }
  DURATION
    FOR SESSION 1d;

JWT access (validate external tokens)

Structure (alg + key/url) applies + introspects, but the signing KEY is redacted on pull.

schema.ts TypeScript

defineAccess("external").jwt({ alg: "HS512", key: "secret" }).onDatabase()

Generated SurrealQL

DEFINE ACCESS external ON DATABASE TYPE JWT ALGORITHM HS512 KEY "secret";

BEARER access (API-key grants)

Subject + duration round-trip; the grant secret is redacted on introspect.

schema.ts TypeScript

defineAccess("apikey").bearer({ for: "user" }).duration({ session: "30d" })

Generated SurrealQL

DEFINE ACCESS apikey ON DATABASE
  TYPE BEARER
    FOR USER
  DURATION
    FOR SESSION 30d;

analyzers

Analyzer with tokenizers + filters

schema.ts TypeScript

defineAnalyzer("english", {
  tokenizers: ["blank", "class"],
  filters: ["lowercase", "snowball(english)"],
})

Generated SurrealQL

DEFINE ANALYZER english TOKENIZERS BLANK, CLASS FILTERS LOWERCASE, SNOWBALL(ENGLISH);

Analyzer with tokenizers only

schema.ts TypeScript

defineAnalyzer("simple", { tokenizers: ["blank"] })

Generated SurrealQL

DEFINE ANALYZER simple TOKENIZERS BLANK;

escape-hatch

.$surreal(wire, codec) — store an instanceof type as a string

App type = Money, wire/DDL type = string; the codec maps both ways. Clears the no-DDL brand.

schema.ts TypeScript

defineTable("wallet", {
  id: s.string(),
  price: s.instanceof(Money).$surreal(s.string(), {
    encode: (m) => m.toString(),
    decode: (v) => new Money(Math.round(Number(v) * 100)),
  }),
})

Generated SurrealQL

DEFINE TABLE wallet TYPE NORMAL SCHEMAFULL;
DEFINE FIELD price ON TABLE wallet TYPE string;

.$surreal on s.custom — store an app-only type (URL) as a string

URL has no native SurrealQL type, so it needs a wire type + codec. (A JS Set, by contrast, is native: use s.set() -> set<T>.)

schema.ts TypeScript

defineTable("site", {
  id: s.string(),
  homepage: s.custom<URL>().$surreal(s.string(), {
    encode: (u) => u.href,
    decode: (v) => new URL(v),
  }),
})

Generated SurrealQL

DEFINE TABLE site TYPE NORMAL SCHEMAFULL;
DEFINE FIELD homepage ON TABLE site TYPE string;

.$internal() — DB-managed, client-hidden field (PERMISSIONS NONE)

schema.ts TypeScript

defineTable("account", { id: s.string(), passhash: s.string().$internal() })

Generated SurrealQL

DEFINE TABLE account TYPE NORMAL SCHEMAFULL;
DEFINE FIELD passhash ON TABLE account TYPE string PERMISSIONS NONE;

tables

implicit id primary key

no PK authored -> the driver adds `"id" text PRIMARY KEY` (mirrors Surreal's record id)

schema.ts TypeScript

defineTable("user", { name: s.text() })

Generated PostgreSQL

CREATE TABLE "user" (
  "id" text PRIMARY KEY,
  "name" text NOT NULL
);

composite primary key replaces the implicit id

schema.ts TypeScript

defineTable("member", { org: s.text(), person: s.text() }).primaryKey("org", "person")

Generated PostgreSQL

CREATE TABLE "member" (
  "org" text NOT NULL,
  "person" text NOT NULL,
  PRIMARY KEY ("org", "person")
);

table-level CHECK

emit-faithful; excluded from change-detection (PG rewrites expressions on read)

schema.ts TypeScript

defineTable("account", { balance: s.numeric(12, 2) }).check("balance >= 0")

Generated PostgreSQL

CREATE TABLE "account" (
  "id" text PRIMARY KEY,
  "balance" numeric(12, 2) NOT NULL,
  CHECK (balance >= 0)
);

field-types

canonical scalars

schema.ts TypeScript

defineTable("t", {
  name: s.text(),
  count: s.integer(),
  ratio: s.doublePrecision(),
  active: s.boolean(),
  created: s.timestamptz(),
  token: s.uuid(),
})

Generated PostgreSQL

CREATE TABLE "t" (
  "id" text PRIMARY KEY,
  "active" boolean NOT NULL,
  "count" integer NOT NULL,
  "created" timestamp with time zone NOT NULL,
  "name" text NOT NULL,
  "ratio" double precision NOT NULL,
  "token" uuid NOT NULL
);

pg-native parameterized types

varchar(n) / numeric(p,s) preserve their params; bigint/smallint/real are native

schema.ts TypeScript

defineTable("t", {
  label: s.varchar(255),
  price: s.numeric(10, 2),
  big: s.bigint(),
  small: s.smallint(),
  approx: s.real(),
})

Generated PostgreSQL

CREATE TABLE "t" (
  "id" text PRIMARY KEY,
  "approx" real NOT NULL,
  "big" bigint NOT NULL,
  "label" varchar(255) NOT NULL,
  "price" numeric(10, 2) NOT NULL,
  "small" smallint NOT NULL
);

array column

schema.ts TypeScript

defineTable("t", { tags: s.text().array() })

Generated PostgreSQL

CREATE TABLE "t" (
  "id" text PRIMARY KEY,
  "tags" text[] NOT NULL
);

jsonb (object collapses to an opaque jsonb column)

schema.ts TypeScript

defineTable("t", {
  meta: s.jsonb(),
  profile: s.object({ bio: s.text(), age: s.integer() }),
})

Generated PostgreSQL

CREATE TABLE "t" (
  "id" text PRIMARY KEY,
  "meta" jsonb NOT NULL,
  "profile" jsonb NOT NULL
);

field-clauses

nullability (.optional / .nullable both -> a nullable column)

schema.ts TypeScript

defineTable("t", { age: s.smallint().optional(), bio: s.text().nullable() })

Generated PostgreSQL

CREATE TABLE "t" (
  "id" text PRIMARY KEY,
  "age" smallint,
  "bio" text
);

identity column (GENERATED ... AS IDENTITY)

schema.ts TypeScript

defineTable("t", { seq: s.integer().$identity("by-default") })

Generated PostgreSQL

CREATE TABLE "t" (
  "id" text PRIMARY KEY,
  "seq" integer NOT NULL GENERATED BY DEFAULT AS IDENTITY
);

default: a JS literal vs sqlExpr() for a SQL expression

$default needs sqlExpr() to tell a SQL expr from a literal; emit-faithful, excluded from drift

schema.ts TypeScript

defineTable("t", {
  status: s.text().$default("active"),
  created: s.timestamptz().$default(sqlExpr("now()")),
})

Generated PostgreSQL

CREATE TABLE "t" (
  "id" text PRIMARY KEY,
  "created" timestamp with time zone NOT NULL DEFAULT now(),
  "status" text NOT NULL DEFAULT 'active'
);

field CHECK (a bare string expression)

schema.ts TypeScript

defineTable("t", { score: s.integer().$check("score >= 0") })

Generated PostgreSQL

CREATE TABLE "t" (
  "id" text PRIMARY KEY,
  "score" integer NOT NULL CHECK (score >= 0)
);

generated / computed column (STORED)

reference other columns by their (quoted) name; camelCase needs quotes: "unitPrice"

schema.ts TypeScript

defineTable("line", {
  quantity: s.integer(),
  unitPrice: s.numeric(10, 2),
  total: s.numeric(12, 2).$generated('quantity * "unitPrice"'),
})

Generated PostgreSQL

CREATE TABLE "line" (
  "id" text PRIMARY KEY,
  "quantity" integer NOT NULL,
  "total" numeric(12, 2) NOT NULL GENERATED ALWAYS AS (quantity * "unitPrice") STORED,
  "unitPrice" numeric(10, 2) NOT NULL
);

column comment (COMMENT ON COLUMN)

emit-only — not introspected back yet

schema.ts TypeScript

defineTable("t", { note: s.text().$comment("free text") })

Generated PostgreSQL

CREATE TABLE "t" (
  "id" text PRIMARY KEY,
  "note" text NOT NULL
);
COMMENT ON COLUMN "t"."note" IS 'free text';

indexes

column UNIQUE ($unique) -> CREATE UNIQUE INDEX

the $unique convention names it <table>_<col>_key

schema.ts TypeScript

defineTable("user", { email: s.text().$unique() })

Generated PostgreSQL

CREATE TABLE "user" (
  "id" text PRIMARY KEY,
  "email" text NOT NULL
);
CREATE UNIQUE INDEX "user_email_key" ON "user" ("email");

secondary index (.index)

emit-only: non-unique indexes are not introspected back yet, so they do not round-trip (see COVERAGE)

schema.ts TypeScript

defineTable("post", { title: s.text() }).index(["title"])

Generated PostgreSQL

CREATE TABLE "post" (
  "id" text PRIMARY KEY,
  "title" text NOT NULL
);
CREATE INDEX "post_title_idx" ON "post" ("title");

composite UNIQUE index

schema.ts TypeScript

defineTable("membership", { org: s.text(), user: s.text() }).index(["org", "user"], { unique: true })

Generated PostgreSQL

CREATE TABLE "membership" (
  "id" text PRIMARY KEY,
  "org" text NOT NULL,
  "user" text NOT NULL
);
CREATE UNIQUE INDEX "membership_org_user_idx" ON "membership" ("org", "user");

constraints

foreign key (s.references) -> text column + FK to ref(id)

the FK is its own kind (deps -> [table, refTable]), so it emits after both tables

schema.ts TypeScript

[
  defineTable("usr", { name: s.text() }),
  defineTable("post", { author: s.references("usr") }),
]

Generated PostgreSQL

CREATE TABLE "post" (
  "id" text PRIMARY KEY,
  "author" text NOT NULL
);
CREATE TABLE "usr" (
  "id" text PRIMARY KEY,
  "name" text NOT NULL
);
ALTER TABLE "post" ADD CONSTRAINT "post_author_fkey" FOREIGN KEY ("author") REFERENCES "usr" ("id");

FK with referential actions (ON DELETE / ON UPDATE)

actions canonicalize UPPERCASE; the default NO ACTION is omitted

schema.ts TypeScript

[
  defineTable("usr", { name: s.text() }),
  defineTable("post", {
    author: s.references("usr", { onDelete: "cascade", onUpdate: "restrict" }),
  }),
]

Generated PostgreSQL

CREATE TABLE "post" (
  "id" text PRIMARY KEY,
  "author" text NOT NULL
);
CREATE TABLE "usr" (
  "id" text PRIMARY KEY,
  "name" text NOT NULL
);
ALTER TABLE "post" ADD CONSTRAINT "post_author_fkey" FOREIGN KEY ("author") REFERENCES "usr" ("id") ON DELETE CASCADE ON UPDATE RESTRICT;

FK via table.record() (reference another table object)

schema.ts TypeScript

(() => {
  const usr = defineTable("usr", { name: s.text() });
  const post = defineTable("post", { author: usr.record({ onDelete: "cascade" }) });
  return [usr, post];
})()

Generated PostgreSQL

CREATE TABLE "post" (
  "id" text PRIMARY KEY,
  "author" text NOT NULL
);
CREATE TABLE "usr" (
  "id" text PRIMARY KEY,
  "name" text NOT NULL
);
ALTER TABLE "post" ADD CONSTRAINT "post_author_fkey" FOREIGN KEY ("author") REFERENCES "usr" ("id") ON DELETE CASCADE;

relations

customer / order: FK + CHECK + generated column + unique index

schema.ts TypeScript

(() => {
  const customer = defineTable("customer", {
    email: s.text().$unique().$check(sqlExpr("email ~* '^[^@]+@[^@]+$'")),
    name: s.text(),
  });
  const order = defineTable("order", {
    customer: customer.record({ onDelete: "cascade" }),
    quantity: s.integer().$check(sqlExpr("quantity > 0")),
    unitPrice: s.numeric(10, 2),
    total: s.numeric(12, 2).$generated('quantity * "unitPrice"'),
    createdAt: s.timestamptz().$default(sqlExpr("now()")),
  });
  return [customer, order];
})()

Generated PostgreSQL

CREATE TABLE "customer" (
  "id" text PRIMARY KEY,
  "email" text NOT NULL CHECK (email ~* '^[^@]+@[^@]+$'),
  "name" text NOT NULL
);
CREATE TABLE "order" (
  "id" text PRIMARY KEY,
  "createdAt" timestamp with time zone NOT NULL DEFAULT now(),
  "customer" text NOT NULL,
  "quantity" integer NOT NULL CHECK (quantity > 0),
  "total" numeric(12, 2) NOT NULL GENERATED ALWAYS AS (quantity * "unitPrice") STORED,
  "unitPrice" numeric(10, 2) NOT NULL
);
CREATE UNIQUE INDEX "customer_email_key" ON "customer" ("email");
ALTER TABLE "order" ADD CONSTRAINT "order_customer_fkey" FOREIGN KEY ("customer") REFERENCES "customer" ("id") ON DELETE CASCADE;

escape-hatch

factory: s.$postgres(pgType, codec) (from scratch)

schema.ts TypeScript

defineTable("blob", { raw: s.$postgres("text", z.string()) })

Generated PostgreSQL

CREATE TABLE "blob" (
  "id" text PRIMARY KEY,
  "raw" text NOT NULL
);

chainable: App value -> stored as the wire type (mirrors surreal .$surreal)

column emits as the wire type (varchar(32)); the codec maps app<->wire. `Money` is a demo App class

schema.ts TypeScript

defineTable("tx", {
  amount: new PgField(z.instanceof(Money), {}).$postgres(s.varchar(32), {
    encode: (m) => String(m.cents),
    decode: (v) => new Money(Number(v)),
  }),
})

Generated PostgreSQL

CREATE TABLE "tx" (
  "id" text PRIMARY KEY,
  "amount" varchar(32) NOT NULL
);