Database Schema to Prisma, Drizzle, and Zod: Design First, Generate Everything

A user submits a form. Your TypeScript compiles clean. Your Zod validator passes. Your tRPC procedure returns a 200. And somewhere in the response, a column that was supposed to be Date | null arrives as an ISO string — and your frontend renders NaN where a timestamp should be.

You've seen this bug. You've shipped this bug. The type system didn't catch it because the type was never derived from the database in the first place — it was written by hand, approximately correct, and silently wrong.

This is the source-of-truth problem. TypeScript doesn't solve it. Where you define your data model does.

The source-of-truth problem in TypeScript stacks

Here's the irony of TypeScript full-stack development: end-to-end type safety is the stated goal, but the data model — the one thing everything else derives from — usually lives in three places at once:

• Database — CREATE TABLE with NOT NULL, check constraints, and enum types.
• ORM — your schema.prisma or pgTable() definition, supposed to mirror the database but written and maintained by humans.
• Application — TypeScript interfaces, Zod validators, and tRPC input schemas describing what handlers accept and return.

When these three layers drift — and they always drift — you get runtime bugs TypeScript cannot catch. Not because TypeScript is inadequate, but because your types were never derived from the ground truth. They were asserted.

// The lie that compiles fine
interface Issue {
  id: string;
  assigneeId: string; // But the column is nullable in Postgres
  createdAt: Date; // But the driver returns a string
  priority: "high" | "medium" | "low"; // But there are 5 enum values in the DB
}

This is the stale-type problem. Every TypeScript project has a version of it. The fix isn't better linting — it's making your database schema the single source of truth and generating everything downstream from it.

Your database schema is the single source of truth. Generate Prisma, Drizzle, and Zod from it — not the other way around.

---

TalkingSchema: the design layer that feeds your stack

Every TypeScript stack has a layer where types originate. In the code-first workflow that layer is a developer typing into a schema file; in the design-first workflow it is an ERD tool that exports to every downstream format.

TalkingSchema is built for the second workflow. Describe your model — tables, relationships, enums, constraints, groups — in a visual designer that speaks DBML natively, and export:

• Prisma schema — complete schema.prisma with models, enums, relations, indexes
• Drizzle table definitions — pgTable() (or mysqlTable / sqliteTable) with full type inference
• Zod validators — create / update schemas derived from column constraints
• SQL DDL — PostgreSQL, MySQL, MSSQL, or Oracle
• DBML — portable database markup for docs and sharing

Throughout this post we'll work from one running example: a multi-tenant SaaS project management application using org_id shared-schema tenancy with indexes that support RLS. Every code excerpt below is generated from this single ERD.

Multi-Tenant SaaS Project Management — Full Schema

Open full screen ↗

Open in TalkingSchema to explore the schema, and export to Prisma, Drizzle, or SQL.

---

Prisma schema generation: what TalkingSchema produces and how to use it

Prisma uses a custom DSL — the .prisma schema file — as the declaration layer between your database and your TypeScript types. prisma generate emits a fully-typed client from it; prisma migrate dev diffs it against the live database to produce SQL.

Hand-writing schema.prisma is fine for 5–10 tables. By the time you have 25+ tables, complex FKs, several enums, and cross-schema references, drift starts the day someone forgets to add an index alongside the column.

Prompt TalkingSchema: "Generate a Prisma schema for a multi-tenant SaaS project tracker with organizations, users, projects, issues (status + priority enums), comments, and an event log. Use Postgres schema namespaces core, projects, events."

It returns the full schema.prisma, ready to drop in. Here is the output for three tables from the running example:

◇ TalkingSchema → schema.prisma (excerpt)

// Generated by TalkingSchema — schema.prisma excerpt

generator client {
  provider = "prisma-client"
  output   = "../src/generated/prisma"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

enum UserRole {
  owner
  admin
  member
  guest
}

enum UserStatus {
  active
  invited
  suspended
  deactivated
}

enum IssueStatus {
  backlog
  todo
  in_progress
  in_review
  done
  cancelled
}

enum IssuePriority {
  urgent
  high
  medium
  low
  no_priority
}

/// Core organization / tenant root
model Organization {
  id        String   @id @default(cuid())
  name      String
  slug      String   @unique
  plan      String   @default("free")
  seats     Int      @default(5)
  createdAt DateTime @default(now()) @map("created_at")
  settings  Json     @default("{}")

  users       User[]
  teams       Team[]
  projects    Project[]
  invitations Invitation[]
  apiKeys     ApiKey[]

  @@map("organizations")
  @@schema("core")
}

/// Per-org user record. role/status drive all access control.
model User {
  id           String     @id @default(cuid())
  orgId        String     @map("org_id")
  email        String
  displayName  String     @map("display_name")
  avatarUrl    String?    @map("avatar_url")
  role         UserRole   @default(member)
  status       UserStatus @default(invited)
  createdAt    DateTime   @default(now()) @map("created_at")
  lastActiveAt DateTime?  @map("last_active_at")

  organization    Organization     @relation(fields: [orgId], references: [id], onDelete: Cascade)
  teamMemberships TeamMembership[]
  assignedIssues  Issue[]          @relation("IssueAssignee")
  reportedIssues  Issue[]          @relation("IssueReporter")
  comments        IssueComment[]
  attachments     IssueAttachment[]
  apiKeys         ApiKey[]

  @@unique([orgId, email])
  @@index([orgId])
  @@map("users")
  @@schema("core")
}

/// Issue is the primary work unit. parent_id enables sub-issues.
model Issue {
  id             String        @id @default(cuid())
  projectId      String        @map("project_id")
  assigneeId     String?       @map("assignee_id")
  reporterId     String        @map("reporter_id")
  title          String
  description    String?
  status         IssueStatus   @default(backlog)
  priority       IssuePriority @default(no_priority)
  labels         String[]
  estimate       Int?
  cycleId        String?       @map("cycle_id")
  parentId       String?       @map("parent_id")
  sequenceNumber Int           @map("sequence_number")
  createdAt      DateTime      @default(now()) @map("created_at")

  project     Project      @relation(fields: [projectId], references: [id], onDelete: Cascade)
  assignee    User?        @relation("IssueAssignee", fields: [assigneeId], references: [id])
  reporter    User         @relation("IssueReporter", fields: [reporterId], references: [id])
  cycle       Cycle?       @relation(fields: [cycleId], references: [id])
  parent      Issue?       @relation("IssueChildren", fields: [parentId], references: [id])
  children    Issue[]      @relation("IssueChildren")
  comments    IssueComment[]
  attachments IssueAttachment[]
  events      IssueEvent[]

  @@index([projectId])
  @@index([assigneeId])
  @@index([status, priority])
  @@map("issues")
  @@schema("projects")
}

After prisma generate, every table becomes a fully-typed TypeScript type, query builder, and relationship accessor — prisma.issue.findMany({ where: { assigneeId: null } }) respects the column's real nullability, and prisma.user.create({ data: { role: "superuser" } }) is a compile error because superuser is not in UserRole.

▸ Why Prisma v7 matters here

v6 made the generated client plain TypeScript (no Rust engine in node_modules). v7 finished the job. The numbers that move for serverless:

• Bundle size: ~14 MB → ~1.6 MB
• Cold start: ~800 ms → ~80–150 ms

That removes most of the reason teams used to reach for Drizzle on edge runtimes — now the choice is mostly about query style and migration ergonomics, not platform support.

---

Drizzle ORM: table definitions as TypeScript, generated from your ERD

Drizzle takes the opposite philosophical stance: no custom DSL, your schema is TypeScript.

Prompt TalkingSchema: "Export the same schema as Drizzle (drizzle-orm/pg-core) with pgSchema namespaces, pgEnum declarations, and references() for every FK."

◇ TalkingSchema → schema.ts (Drizzle excerpt)

// Generated by TalkingSchema — schema.ts excerpt (Drizzle)
import {
  pgTable,
  pgSchema,
  pgEnum,
  uuid,
  text,
  varchar,
  integer,
  boolean,
  jsonb,
  timestamp,
  index,
  uniqueIndex,
} from "drizzle-orm/pg-core";

// Schema namespaces
export const coreSchema = pgSchema("core");
export const projectsSchema = pgSchema("projects");
export const eventsSchema = pgSchema("events");

// Enums
export const userRoleEnum = pgEnum("user_role", [
  "owner",
  "admin",
  "member",
  "guest",
]);

export const userStatusEnum = pgEnum("user_status", [
  "active",
  "invited",
  "suspended",
  "deactivated",
]);

export const issueStatusEnum = pgEnum("issue_status", [
  "backlog",
  "todo",
  "in_progress",
  "in_review",
  "done",
  "cancelled",
]);

export const issuePriorityEnum = pgEnum("issue_priority", [
  "urgent",
  "high",
  "medium",
  "low",
  "no_priority",
]);

// Core schema tables
export const organizations = coreSchema.table(
  "organizations",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    name: text("name").notNull(),
    slug: varchar("slug", { length: 63 }).notNull().unique(),
    plan: varchar("plan", { length: 32 }).notNull().default("free"),
    seats: integer("seats").notNull().default(5),
    createdAt: timestamp("created_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
    settings: jsonb("settings").notNull().default({}),
  },
  (t) => [uniqueIndex("organizations_slug_idx").on(t.slug)],
);

export const users = coreSchema.table(
  "users",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    orgId: uuid("org_id")
      .notNull()
      .references(() => organizations.id, { onDelete: "cascade" }),
    email: varchar("email", { length: 320 }).notNull(),
    displayName: text("display_name").notNull(),
    avatarUrl: text("avatar_url"),
    role: userRoleEnum("role").notNull().default("member"),
    status: userStatusEnum("status").notNull().default("invited"),
    createdAt: timestamp("created_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
    lastActiveAt: timestamp("last_active_at", { withTimezone: true }),
  },
  (t) => [
    uniqueIndex("users_org_email_idx").on(t.orgId, t.email),
    index("users_org_idx").on(t.orgId),
  ],
);

// Projects schema tables
export const issues = projectsSchema.table(
  "issues",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    projectId: uuid("project_id")
      .notNull()
      .references(() => projects.id, { onDelete: "cascade" }),
    assigneeId: uuid("assignee_id").references(() => users.id, {
      onDelete: "set null",
    }),
    reporterId: uuid("reporter_id")
      .notNull()
      .references(() => users.id),
    title: text("title").notNull(),
    description: text("description"),
    status: issueStatusEnum("status").notNull().default("backlog"),
    priority: issuePriorityEnum("priority").notNull().default("no_priority"),
    labels: text("labels").array().notNull().default([]),
    estimate: integer("estimate"),
    cycleId: uuid("cycle_id").references(() => cycles.id),
    parentId: uuid("parent_id"), // self-reference resolved post-declaration
    sequenceNumber: integer("sequence_number").notNull(),
    createdAt: timestamp("created_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
  },
  (t) => [
    index("issues_project_idx").on(t.projectId),
    index("issues_assignee_idx").on(t.assigneeId),
    index("issues_status_priority_idx").on(t.status, t.priority),
  ],
);

▸ Types come straight from the table

Drizzle's inferred types come directly from the table definition — no separate generation step:

import { type InferSelectModel, type InferInsertModel } from "drizzle-orm";

export type Issue = InferSelectModel<typeof issues>;
// Inferred: { id: string; projectId: string; assigneeId: string | null; ... }

export type NewIssue = InferInsertModel<typeof issues>;
// Inferred: { projectId: string; reporterId: string; title: string; ... }
// (id, createdAt are optional — they have database defaults)

The schema definition and the TypeScript types are the same object. Flip assigneeId from nullable to NOT NULL and TypeScript immediately knows everywhere that assignment was optional — no build step required.

▸ Queries that read like SQL

const openIssues = await db
  .select({
    id: issues.id,
    title: issues.title,
    assignee: { id: users.id, displayName: users.displayName },
  })
  .from(issues)
  .leftJoin(users, eq(issues.assigneeId, users.id))
  .where(and(eq(issues.projectId, projectId), eq(issues.status, "in_progress")))
  .orderBy(desc(issues.createdAt));

---

TypeScript + Zod: from column constraints to runtime validators

Here is the dividend neither Prisma nor Drizzle docs talk about enough: your database schema already encodes your validation rules. Each constraint maps to a Zod fragment:

Database constraint	Zod equivalent
NOT NULL	z.string() (not .optional())
VARCHAR(320)	z.string().max(320)
CHECK (seats > 0)	z.number().positive()
Enum type	z.enum(["active", "invited", "suspended", "deactivated"])
Unique constraint	documented in the validator's error message

Generate both ORM types and Zod validators from the same ERD and constraint violations that would otherwise surface as database errors get caught at the API boundary instead.

Prompt TalkingSchema: "Export Zod create / update schemas for every table — derive min/max/email/uuid from column types, defaults from DEFAULT, and enums from Postgres enum types."

◇ TalkingSchema → validators.ts (excerpt)

// Generated by TalkingSchema — validators.ts excerpt
import { z } from "zod";

// Derived from core.users column constraints
export const CreateUserSchema = z.object({
  orgId: z.string().uuid(),
  email: z.string().email().max(320),
  displayName: z.string().min(1).max(255),
  avatarUrl: z.string().url().optional(),
  role: z.enum(["owner", "admin", "member", "guest"]).default("member"),
});

// Derived from projects.issues column constraints
export const CreateIssueSchema = z.object({
  projectId: z.string().uuid(),
  assigneeId: z.string().uuid().optional(),
  reporterId: z.string().uuid(),
  title: z.string().min(1).max(500),
  description: z.string().optional(),
  status: z
    .enum(["backlog", "todo", "in_progress", "in_review", "done", "cancelled"])
    .default("backlog"),
  priority: z
    .enum(["urgent", "high", "medium", "low", "no_priority"])
    .default("no_priority"),
  labels: z.array(z.string()).default([]),
  estimate: z.number().int().positive().optional(),
  cycleId: z.string().uuid().optional(),
  parentId: z.string().uuid().optional(),
});

export const UpdateIssueSchema = CreateIssueSchema.partial().extend({
  id: z.string().uuid(),
});

// Inferred types — these are your application-layer types
export type CreateIssueInput = z.infer<typeof CreateIssueSchema>;
export type UpdateIssueInput = z.infer<typeof UpdateIssueSchema>;

There are code-first variants of this too — drizzle-zod reads your pgTable() definitions, prisma-zod-generator (a.k.a. zod-prisma-types) reads your schema.prisma. Both work, both add another generation step downstream of the ORM. Generating from the ERD instead skips a hop: Prisma, Drizzle, and Zod are all peers of the same source, not derivatives of each other.

▸ How this lands in your API layer

Both approaches plug into the usual TypeScript framework spots:

• tRPC — the procedure input is the Zod schema.
• Hono — validator middleware accepts the Zod schema as-is.
• Elysia — has its own schema integration but can wrap Zod when needed.

Same upstream definition, three different network boundaries.

---

Multi-tenant SaaS: how schema design decisions cascade into ORM complexity

The multi-tenant SaaS domain makes this concrete: schema design choices have non-linear effects on ORM complexity later. The central question — how do you isolate tenant data? — has three common answers, each with very different downstream cost.

◯ Option A — Shared schema with org_id everywhere

Every table has an org_id foreign key. Tenant isolation is enforced at the query level (always WHERE org_id = ?), and optionally at the database via Row-Level Security.

• Schema cost: every table grows an org_id column.
• Drizzle: each query needs .where(eq(table.orgId, orgId)) or a typed helper.
• Prisma: middleware can inject the filter for you:

// Prisma tenant middleware — inject org filter on every query
prisma.$use(async (params, next) => {
  if (params.model && TENANT_SCOPED_MODELS.includes(params.model)) {
    params.args.where = {
      ...params.args.where,
      orgId: getCurrentOrgId(),
    };
  }
  return next(params);
});

◐ Option B — PostgreSQL schema-per-tenant

Each tenant gets a dedicated PostgreSQL schema (tenant_abc.issues, tenant_xyz.issues). Tables are identical; data is physically separated.

• Schema cost: every migration must run against every tenant schema.
• Drizzle: drizzle-kit migrate once per tenant — scriptable but heavier.
• Prisma: prisma migrate deploy with a dynamic schema URL per tenant.

● Option C — Row-Level Security at the database

PostgreSQL RLS policies enforce tenant isolation transparently — no WHERE org_id = ? in application code, no middleware injection. The policy lives in the database:

-- Applied to every tenant-scoped table
ALTER TABLE projects.issues ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON projects.issues
  USING (
    project_id IN (
      SELECT id FROM projects.projects WHERE org_id = current_setting('app.org_id')::uuid
    )
  );

Then set the session variable on every connection before querying:

// With Drizzle — set RLS context per request
await db.execute(sql`SELECT set_config('app.org_id', ${orgId}, true)`);
const issues = await db.select().from(issuesTable);
// RLS policy enforces tenant filter — no WHERE clause needed

The tenancy model you choose ripples through your entire ORM layer. A schema designed for shared-table tenancy generates very different Prisma/Drizzle boilerplate than one designed for RLS. Decide this at the ERD level, before writing a single line of ORM code.

---

The design-first workflow: ER diagram → Prisma/Drizzle → migrations → types

✓ Design-first

1. Design the full schema in TalkingSchema (ERD)
      ↓
2. Export Prisma schema OR Drizzle table definitions
      ↓
3. Run prisma migrate dev / drizzle-kit generate
      ↓
4. prisma generate (Prisma) / infer types (Drizzle)
      ↓
5. Generate Zod validators from ORM schema
      ↓
6. Build tRPC procedures / API handlers against generated types

Every downstream artifact derives from step 1. Schema changes go through the ERD first; re-export and regenerate, and types, validators, and migrations all update together.

✗ Code-first (what most teams actually do)

1. Write schema.prisma or pgTable() by hand
2. Write Zod validators by hand (usually approximate)
3. Write TypeScript interfaces by hand (sometimes exact, sometimes stale)
4. Notice runtime mismatch after shipping
5. Fix in two of three places
6. Repeat

Code-first is fine for simple schemas. It breaks predictably around the 15-table mark, when keeping three definitions in sync exceeds one developer's working memory — and it breaks earliest on cross-cutting concerns (tenancy, soft deletes, audit columns) that are obvious at the diagram level but invisible when you're typing pgTable() blocks one at a time. The classic giveaway: writing the first non-trivial join query and realizing the relational model needs a redesign.

---

Design the schema once, generate everything

The Prisma vs Drizzle decision is real — bundle size, migration safety, edge compatibility, and SQL transparency all vary meaningfully. But it sits downstream of a more important question: where does your data model live?

✗ When the schema lives in your head

Transcribed into ORM syntax as needed. Type system approximately correct. Validators slightly stale. Migrations occasionally surprising. Runtime mismatches inevitable.

✓ When the schema lives in a design artifact

An ERD that exports to any ORM format. Single source of truth. Every type, validator, and migration derives from it. Prisma, Drizzle, Zod, and your migration runner become consumers of the design, not co-authors of it.

▸ Pick your tools — but design the schema first

• Choose Drizzle if you live on serverless/edge, prioritize bundle size, or want SQL-transparent queries.
• Choose Prisma if you want Prisma Studio, the broadest ecosystem, or have T3 Stack muscle memory.
• Use TalkingSchema to design your schema before writing either.

The multi-tenant SaaS schema above is open in → Try TalkingSchema — fork it, adapt it, export to whichever ORM you choose. The schema IS the system. Design it first.