Indexes

Schemic emits indexes — unique and non-unique, btree or another access method, and partial — as CREATE INDEX statements alongside the table. This guide covers the forms you reach for most.

A unique column index

$unique() on a column emits a CREATE UNIQUE INDEX. The index is named <table>_<column>_key:

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

export const user = defineTable("user", {
  email: s.text().$unique(),
});
Generated DDL PostgreSQL 
CREATE TABLE "user" (
  "id" text PRIMARY KEY,
  "email" text NOT NULL
);
CREATE UNIQUE INDEX "user_email_key" ON "user" ("email");

A secondary index

For a non-unique index, use the table-level .index([...]). It is named <table>_<columns>_idx:

TypeScript 
export const post = defineTable("post", {
  title: s.text(),
}).index(["title"]);
Generated DDL PostgreSQL 
CREATE TABLE "post" (
  "id" text PRIMARY KEY,
  "title" text NOT NULL
);
CREATE INDEX "post_title_idx" ON "post" ("title");

A composite index

Pass several columns, and { unique: true } for a multi-column unique constraint:

TypeScript 
export const membership = defineTable("membership", {
  org: s.text(),
  user: s.text(),
}).index(["org", "user"], { unique: true });
Generated DDL 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");

Access methods and partial indexes

.index(cols, { method, where }) covers the rest. method picks the access method — gin, gist, brin, or hash (btree is the default and emits no USING); where makes the index partial.

TypeScript 
export const doc = defineTable("doc", { meta: s.jsonb() })
  .index(["meta"], { method: "gin" });
Generated DDL PostgreSQL 
CREATE INDEX "doc_meta_idx" ON "doc" USING gin ("meta");

A where predicate makes the index partial — it covers only the matching rows:

TypeScript 
export const task = defineTable("task", { priority: s.integer(), status: s.text() })
  .index(["priority"], { where: "status = 'open'" });
Generated DDL PostgreSQL 
CREATE INDEX "task_priority_idx" ON "task" ("priority") WHERE status = 'open';

Pick the method to match the column: gin for jsonb, arrays, and full-text; brin for large, naturally-ordered columns; hash for equality. (gist needs a type with a gist operator class.) Methods and columns round-trip cleanly; a partial where predicate emits and round-trips on presence, but — like DEFAULT and CHECK — a predicate-only edit is not auto-detected.

Where to go next