From schema to DDL

Schemic turns a table definition into Postgres DDL — the CREATE TABLE, CREATE INDEX, and ALTER TABLE statements that tell Postgres the shape of your data. This page explains how each part of a definition maps to DDL. It is the conceptual companion to the type mapping reference, which lists every type exactly.

DDL (Data Definition Language) is the subset of SQL that defines schema. There is no separate schema language and no code generation step: your defineTable is the schema, and the CLI derives the DDL from it.

A table becomes a CREATE TABLE

A defineTable produces a CREATE TABLE statement with one column per field:

database/schema/tables.ts TypeScript 
import { defineTable, s } from "@schemic/postgres";

export const user = defineTable("user", {
  name: s.text(),
});

produces:

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

Two things are visible here:

  • The id primary key is implicit. When you declare no primary key, Schemic adds "id" text PRIMARY KEY (mirroring SurrealDB’s record id). Declare your own with .primaryKey(...) on the table, or s.serial().$primaryKey() / s.uuid().$primaryKey() on a column, to replace it. The .$primaryKey() marker is what replaces the implicit id; a column without it leaves the implicit "id" in place.
  • Columns are NOT NULL by default. A required field emits NOT NULL; .optional() or .nullable() makes the column nullable.

How a field’s type is computed

The column type comes from the s.* field. Each builder carries a Postgres type; modifiers wrap it:

You writeColumn
s.text()text
s.varchar(255)varchar(255)
s.integer()integer
s.numeric(10, 2)numeric(10, 2)
s.timestamptz()timestamp with time zone
s.text().optional()text (nullable)
s.text().array()text[]
s.object({ ... })jsonb (opaque on disk)

.optional() and .nullable() both collapse to a single nullable column — Postgres has no column-level notion of “absent” distinct from NULL. A nested s.object({ ... }) becomes one jsonb column; its sub-structure is validated app-side by Zod, not stored as separate columns.

How clauses become DDL

The $-prefixed field methods add clauses to a column or emit companion statements:

You writeDDL
s.text().$unique()a companion CREATE UNIQUE INDEX
s.timestamptz().$default(sqlExpr("now()"))DEFAULT now()
s.integer().$check("score >= 0")CHECK (score >= 0)
s.integer().$identity()GENERATED BY DEFAULT AS IDENTITY
s.references("user")a text column + an ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY
s.numeric(12, 2).$generated('quantity * "unitPrice"')GENERATED ALWAYS AS (...) STORED

Foreign keys and indexes are emitted as their own statements after the tables they depend on, so a mutual reference between two tables resolves cleanly.

The DDL is what migrations diff

You rarely look at the emitted DDL by hand. The CLI generates it, diffs it against a snapshot of the last known state, and writes the difference into a migration. Understanding the mapping is what lets you predict what a schema change will do before you run schemic gen.

Where to go next