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

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