Skip to content
TanStackIntent
TanStack Intent/Registry/wcz-layout

wcz-layout

db-schema

99 linesSource

Use when changing PostgreSQL tables, columns, constraints, indexes, enums, foreign keys, relations, migrations, or Drizzle-derived Zod schemas in a wcz-layout app.

Database schema patterns

Rules

  • One table per file, named after the table (e.g. bookTable lives in book.ts). Never put multiple tables in one file, even when they are related — a parent and its children each get their own file.
  • Use uuid for application-table primary keys and snakeCase for generating tables.
  • For uuid columns, do not generate random values in the schema. The client generates the id with uuidv7() from wcz-layout/utils when creating the record.
  • Always add withTimezone: true to timestamp columns.
  • Always declare onDelete explicitly on every foreign key — never rely on the default. Pick the behavior from the business rule: "cascade" when the child is meaningless without its parent, "restrict" when the parent must not be deleted while children exist, "set null" for optional references.
  • Index every foreign key column. Postgres does not create one for you.
  • Define relations in a central relations.ts file.
  • Declare enums with pgEnum in the table's own file, export them, and reference enumValues everywhere else — never re-declare the values.
  • Generate the full client record schema with createSelectSchema from drizzle-orm/zod. Use createInsertSchema; override fields with stricter rules (trim, min/max) where needed.
  • The second argument of createSelectSchema refines existing columns only. Adding a key that is not a column is a type error. Attach related data with .extend() after it.
  • Do not auto-generate migrations unless the user explicitly asks. When asked, run npm run db:generate; migrations apply automatically at app startup while DATABASE_AUTO_MIGRATE=true.

Examples

ts
// src/server/db/schemas/library.ts
import { snakeCase, text, uuid } from "drizzle-orm/pg-core";

export const libraryTable = snakeCase.table("libraries", {
  id: uuid().primaryKey(),
  name: text().notNull(),
});
ts
// src/server/db/schemas/book.ts
import { index, pgEnum, snakeCase, text, timestamp, uuid } from "drizzle-orm/pg-core";
import { libraryTable } from "./library";

export const bookStatusEnum = pgEnum("book_status", ["draft", "published"]);

export const bookTable = snakeCase.table(
  "books",
  {
    id: uuid().primaryKey(),
    title: text().notNull(),
    status: bookStatusEnum().notNull().default("draft"),
    libraryId: uuid()
      .notNull()
      .references(() => libraryTable.id, { onDelete: "cascade" }),
    createdAt: timestamp({ withTimezone: true }).defaultNow().notNull(),
  },
  (table) => [index().on(table.libraryId)],
);
ts
// src/server/db/schemas/relations.ts
import { defineRelations } from "drizzle-orm";
import { bookTable } from "./book";
import { libraryTable } from "./library";

export const relations = defineRelations(
  { libraryTable, bookTable },
  ({ one, many, libraryTable: library, bookTable: book }) => ({
    libraryTable: {
      books: many.bookTable(),
    },
    bookTable: {
      library: one.libraryTable({ from: book.libraryId, to: library.id }),
    },
  }),
);
ts
// src/lib/schemas/<table>.ts
import { createSelectSchema } from "drizzle-orm/zod";
import { t } from "wcz-layout/utils";
import type { z } from "zod";
import { bookTable } from "~/server/db/schemas/book";
import { PageSchema } from "./page";

export const BookSchema = createSelectSchema(bookTable, {
  title: (schema) =>
    schema
      .trim()
      .min(1, t("Validation.Required"))
      .max(255, t("Validation.MaxLength", { length: 255 })),
}).extend({
  pages: PageSchema.array().min(1, t("Validation.Required")),
});

export type Book = z.infer<typeof BookSchema>;