Modeling Data with Prisma

npx prisma init creates one file that becomes the source of truth for your entire data layer: prisma/schema.prisma. Everything downstream — the SQL in your migrations and the TypeScript types in your app — is derived from it, which is why a typo in this file is a compile error instead of a 3 a.m. production surprise.

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

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

enum OrderStatus { PENDING PAID SHIPPED CANCELLED }

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  createdAt DateTime @default(now())
  orders    Order[]
}

model Order {
  id        String      @id @default(cuid())
  total     Decimal     @db.Money
  status    OrderStatus @default(PENDING)
  user      User        @relation(fields: [userId], references: [id])
  userId    String
  items     OrderItem[]
  createdAt DateTime    @default(now())

  @@index([userId, createdAt])
}

Read it as a table definition with extra safety. Each model becomes a table (User → "User"), each scalar field becomes a column with a database type. @id @default(cuid()) makes the primary key generated client-side — no database sequence, ids sort chronologically, and two concurrent inserts never collide the way sequential integers can. @unique creates a unique index, which is both a performance structure and an integrity guarantee: a duplicate insert fails with P2002: Unique constraint failed on the fields: ("email") instead of silently storing two accounts.

Relations are declared once and enforced by a foreign key. @relation(fields: [userId], references: [id]) says Order.userId points at User.id, and the matching scalar field userId String is mandatory — Prisma refuses to generate a relation without it. The back-relation orders Order[] lives on User. Declaring it on both sides is not redundancy: it is how the client knows whether a relation is one-to-many or one-to-one.

Small details that save large repairs:

• Optional vs required maps to nullability. deletedAt DateTime? is a nullable column; email String is NOT NULL. Getting this wrong is a migration, not an edit.
• @@index([userId, createdAt]) is a composite index matching your most common WHERE userId ... ORDER BY createdAt query. Indexes belong next to the query patterns, not sprinkled after incidents.
• @db.Money maps to Postgres money/numeric — Prisma's Decimal avoids the float rounding bug that turns $0.10 + $0.20 into 0.30000000000000004 in accounting code.
• @map / @@map rename fields and tables for databases that already exist under different naming conventions, without renaming your code.

Before touching a database, run npx prisma format (canonical layout, catches syntax errors) and npx prisma validate (the schema is legal against your provider). Both run in seconds and belong in pre-commit. The schema being valid is the precondition for everything in the next chapter, where Connecting a Database turns into actual migrations.

Before all of that, npx prisma init scaffolds the prisma/ directory, writes a starter schema.prisma, creates an .env with DATABASE_URL placeholder, and appends .env to .gitignore — the connection-string discipline from the previous topic is set up by the tool itself.

The type surface is small enough to learn in one sitting, and each choice lands in DDL:

Relation shapes vary by one annotation: one-to-one adds @unique to the scalar foreign key; many-to-many needs only a relation field on both models, and Prisma creates the join table for you.

Editing schema.prisma changes nothing by itself. The bridge between the file and the database is migrate dev:

npx prisma format
npx prisma migrate dev --name add_order_items

Environment variables loaded from .env
Datasource "db": PostgreSQL database "shop", schema "public" at "localhost:5432"

Applying migration `20250304101500_add_order_items`

Your database is now in sync with your schema.

✔ Generated Prisma Client (v5.22.0) in 121ms

Three artifacts appear. prisma/migrations/20250304101500_add_order_items/migration.sql is plain SQL you can read, review in a pull request, and commit to git. The _prisma_migrations table in your database records which migrations have been applied. And the client is regenerated, so TypeScript immediately reflects the new fields.

How Prisma produces the SQL matters for understanding its safety rails. It spins up a temporary shadow database, replays every committed migration from scratch, then diffs that result against your schema.prisma. The diff is your new migration. This is why migrations are files rather than instructions: history is a recorded sequence, and any environment replays the same sequence in the same order.

The failure modes are worth knowing before you meet them:

prisma migrate reset is the escape hatch in development: it drops the schema, replays every migration, and re-runs seed scripts. It is also the single most dangerous command in the toolchain — there is no confirmation that protects a production database from it, so it never runs there.

Two commands are not interchangeable:

db push feels faster because it skips files — and that is exactly why shared databases drift out of sync with no record of what changed. The production half of this story, migrate deploy and baselining an existing database, is the subject of Applying Schema Migrations.

Two workflow refinements earn their place in daily use. npx prisma migrate dev --create-only writes the migration file without applying it, so you can review or hand-edit the SQL — the right move before a NOT NULL backfill or anything touching large tables — and then apply it by running migrate dev again. And npx prisma migrate status answers the question you ask when a colleague's build behaves differently:

4 migrations found in prisma/migrations

20250211090312_init       Applied
20250304101500_add_orders Applied
20250318141207_add_index  Pending        <-- their dev DB is behind

A migration nobody has pulled yet is edited like any other file — change migration.sql before it reaches a second machine. Once it has been applied elsewhere, treat it as immutable: amend the schema and create a follow-up migration instead, because two databases already carrying different SQL under the same timestamp is drift you cannot detect with migrate status.

The generator client block in your schema is Prisma's quietly most valuable feature. It emits a class whose shape is derived from your models, so the database schema becomes part of your compile-time contract.

Generation runs itself in the places that matter: @prisma/client's postinstall script generates on npm install, and migrate dev regenerates after every schema change. The output lands under node_modules/.prisma/client and is imported as:

import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();   // in practice: the singleton from lib/prisma.ts

Now the payoff. Because User exists as a model, prisma.user exists as a property — autocomplete at the call site lists every model, every field, and every relation you declared — and because emial does not, prisma.user.findUnique({ where: { emial } }) fails in your editor with a red squiggle, not in production with a 500:

const user = await prisma.user.findUnique({ where: { email } });
user.email;        // string — non-nullable column, so the type is non-nullable
user.deletedAt;    // Date | null — the `?` in the schema survives into TypeScript
user.orders;      // Order[] — back-relation is always an array unless you select it away

Result inference follows the query. Add select: { id: true } and the returned object has exactly { id } — no other key exists, so accessing user.email afterwards is a compile error. This is the practical answer to the interview question "how do you get type-safe database access?": one schema file is the source of both the SQL and the types, so they cannot drift apart.

The stale-client failure mode is the one you will actually hit. Edit a model, forget that a background process still runs the old client, and you get a runtime error that reads like nonsense:

PrismaClientKnownRequestError: Invalid prisma.user() invocation.
Unknown argument `createdAt`. Available options are marked with *.

Nothing is wrong with your data — the generated client no longer matches the schema. npx prisma generate fixes it; if it happens in CI or a deploy, add the generate step explicitly after install, because a cached node_modules can ship without re-running postinstall.

Two boundaries keep the illusion honest. The client is not a validator for user input: a POST body is unknown, so parse it with Zod before it reaches a create call. And the client is a Node artifact — instantiating it in middleware.ts or an export const runtime = 'edge' route fails, which is the problem Querying with Prisma Client steps around by keeping queries on the server runtime.

Three configuration details round out the picture. npx prisma generate --watch regenerates on every schema save, which removes the stale-client failure mode while you develop; in CI, run prisma generate explicitly after install because a cached node_modules can skip the postinstall hook. Enums arrive in TypeScript as union types ('PAID' | 'SHIPPED' | ...), so a wrong status is caught at compile time like any other field. And query logging is declared in code, which is how you will see the SQL in the next topic:

const prisma = new PrismaClient({
  log: [{ emit: 'event', level: 'query' }],
});
prisma.$on('query', (e) => console.log(`${e.duration}ms  ${e.query}`));

$queryRaw and $executeRaw are the escape hatch when the query builder cannot express something — a window function, a CTE, an upsert with a conflict target — and they take tagged templates so interpolated values are bound as parameters rather than pasted into SQL. The client you just configured is about to be exercised hard in Querying with Prisma Client.