Define a table

defineTable(name, fields) creates a table definition: a set of columns whose types and clauses lower to Postgres DDL. This guide covers the everyday choices you make when defining one. For the conceptual background see from schema to DDL.

Define the columns

Pass the table name and an object of columns built with s, the Postgres-native schema vocabulary:

TypeScript 
import { defineTable, s } from "@schemic/postgres";

export const user = defineTable("user", {
  name: s.text(),
  count: s.integer(),
  ratio: s.doublePrecision(),
  active: s.boolean(),
  created: s.timestamptz(),
  token: s.uuid(),
});

Each column carries a Postgres type and is NOT NULL by default:

Generated DDL PostgreSQL 
CREATE TABLE "user" (
  "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
);

Use .optional() (or .nullable()) to make a column nullable. See the type mapping for every s.* type and the column it emits.

The implicit id

You did not declare id above — Schemic adds an implicit "id" text PRIMARY KEY to every table that has no primary key of its own. It mirrors SurrealDB’s record id and gives you a stable key without ceremony.

To use your own key, declare it. A text column literally named id is treated as the implicit one, so name a custom text id otherwise.

Custom and composite primary keys

Declare a primary key at the column level with .$primaryKey(), or at the table level with .primaryKey(...). Either replaces the implicit id.

A composite key uses the table-level form:

TypeScript 
export const member = defineTable("member", {
  org: s.text(),
  person: s.text(),
}).primaryKey("org", "person");
Generated DDL PostgreSQL 
CREATE TABLE "member" (
  "org" text NOT NULL,
  "person" text NOT NULL,
  PRIMARY KEY ("org", "person")
);

For an auto-incrementing key use s.serial().$primaryKey() (an integer identity) or s.bigserial().$primaryKey(); for a uuid key use s.uuid().$primaryKey(). The .$primaryKey() is required — it makes the column the primary key and replaces the implicit id. Without it you keep the implicit "id" column and get the new one.

Table-level CHECK

.check(expr) attaches a table-level CHECK constraint — useful when a rule spans more than one column:

TypeScript 
export const account = defineTable("account", {
  balance: s.numeric(12, 2),
}).check("balance >= 0");
Generated DDL PostgreSQL 
CREATE TABLE "account" (
  "id" text PRIMARY KEY,
  "balance" numeric(12, 2) NOT NULL,
  CHECK (balance >= 0)
);

Verify it

Preview the structural change, then generate the migration to see the DDL your definition produces:

Shell 
npx schemic diff
npx schemic gen add_user

Open the written .sql file before applying anything.

Where to go next