Prisma: schema, client and migrations

Prisma sits between your code and PostgreSQL. You describe your tables in one file, it generates a fully typed client, and a misspelled column becomes a compile error rather than a runtime one.

Installing

cd apps/api
npm install prisma --save-dev
npm install @prisma/client
npx prisma init --datasource-provider postgresql

That creates prisma/schema.prisma and adds DATABASE_URL to .env.

# apps/api/.env
DATABASE_URL="postgresql://kirana:localdev@localhost:5432/kirana"

That is the container from module 1. Make sure it is running:

docker compose up -d

The schema

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

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

model Product {
  id          String   @id @default(cuid())
  slug        String   @unique
  name        String
  description String?
  pricePaise  Int
  unit        String
  stock       Int      @default(0)
  isActive    Boolean  @default(true)
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  @@index([isActive, name])
  @@map("products")
}

Reading it line by line, because every part matters later:

String @id @default(cuid()) — the primary key. cuid() generates a collision-resistant id in your application rather than the database. Compared with an auto-incrementing integer, it does not leak how many products you have and does not conflict when data is created in more than one place.

@unique on slug — the database enforces it, so two products cannot share a URL even if two requests arrive at once. A check in application code cannot promise that.

description String? — the ? makes it nullable. Everything else is required, and the database will refuse a row without it.

pricePaise Int — the decision from module 1. An integer count of paise, never a float. Prisma's Decimal is the alternative when you need fractional currency; integers are simpler and exact.

@default(now()) and @updatedAt — set automatically. @updatedAt changes on every update without you remembering.

@@index([isActive, name]) — a database index for the query the product list will run. Without it that query scans every row.

@@map("products") — the table is products while the model is Product. Prisma's convention is singular PascalCase models and snake_case plural tables, and @@map bridges them.

Migrations

npx prisma migrate dev --name add_products

That generates SQL, applies it, and regenerates the client:

prisma/migrations/20260927120000_add_products/migration.sql

Read that SQL. It is checked into Git and it is what will run in production. Reading it is how you notice that a change you thought was additive drops a column.

Commit migrations. They are the history of your database, and the only thing that lets a colleague reproduce your schema.

In production, never migrate dev:

npx prisma migrate deploy

deploy applies pending migrations and nothing else. dev may reset the database, which is fine locally and catastrophic otherwise.

The client

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

const product = await prisma.product.findUnique({ where: { slug: "atta-5kg" } });

product.name autocompletes. product.nmae is a compile error. Change the schema, run npx prisma generate, and every place using a removed field fails to compile — which is the whole reason for using it.

migrate dev regenerates automatically. After pulling somebody else's migration, run npx prisma generate yourself.

If your editor shows errors on valid fields, restart the TypeScript server. It caches the generated types aggressively, and this wastes people a surprising amount of time.

Wiring it into NestJS

The provider from module 5:

// apps/api/src/prisma/prisma.service.ts
import { Injectable, OnModuleInit, OnModuleDestroy } from "@nestjs/common";
import { PrismaClient } from "@prisma/client";

@Injectable()
export class PrismaService extends PrismaClient implements OnModuleInit, OnModuleDestroy {
  constructor() {
    super({
      log: process.env.NODE_ENV === "development" ? ["query", "warn", "error"] : ["error"],
    });
  }

  async onModuleInit() {
    await this.$connect();
  }

  async onModuleDestroy() {
    await this.$disconnect();
  }
}

// apps/api/src/prisma/prisma.module.ts
@Global()
@Module({
  providers: [PrismaService],
  exports: [PrismaService],
})
export class PrismaModule {}

log: ["query"] in development prints every SQL statement. Leave it on while you learn — watching what a Prisma call actually sends is the fastest way to understand both, and it is how you will spot the N+1 problem in the queries lesson.

Prisma Studio

npx prisma studio

A browser interface at localhost:5555 for browsing and editing your data. Genuinely useful while developing — far quicker than writing a query to check whether a row was created.

The commands

Command	Does
`prisma migrate dev --name x`	create and apply a migration (dev only)
`prisma migrate deploy`	apply pending migrations (production)
`prisma migrate reset`	drop everything and re-run migrations
`prisma generate`	regenerate the client
`prisma studio`	browse data
`prisma db push`	push schema without a migration — prototyping only
`prisma format`	tidy the schema file

migrate reset deletes all data. Fine locally, never anywhere else.

db push skips migrations, which means no history and no way to reproduce the change. Acceptable while sketching a schema on day one; switch to migrations before anyone else pulls your work.

When a migration goes wrong

"Drift detected" — the database does not match the migration history. Usually because somebody changed the schema by hand or used db push. Locally: migrate reset. In production this needs care and is not something to rush.

A failed migration leaves the database partly changed. prisma migrate resolve marks it applied or rolled back once you have fixed the state by hand.

A migration that loses data — Prisma warns when a change would drop a column or make a nullable column required. Read those warnings. Making a column required when existing rows are null fails, and dropping a column is irreversible.

The safe pattern for a rename is three deploys: add the new column, copy the data and write to both, then drop the old one. Doing it in one migration means downtime and a risk of loss.

Check your work

Why cuid() over auto-increment: ids can be generated in the application, they do not reveal how many rows exist, and they do not collide across sources.

What @unique gives you that a code check does not: the database enforces it even when two requests arrive simultaneously.

Why pricePaise Int: money as an integer count of the smallest unit is exact; floats are not.

What @@index is for: making the queries you actually run fast. Without it the database scans every row.

migrate dev versus migrate deploy: dev creates and applies a migration and may reset the database; deploy only applies pending ones and is the production command.

Why migrations are committed: they are the schema's history and the only way somebody else reproduces it.

What db push costs: no migration file, so no history and no reproducible change.

Editor showing errors on valid fields: restart the TypeScript server; the generated types are cached.

Practice

Install Prisma and initialise it. Confirm DATABASE_URL points at your container.
Write the Product model. Run migrate dev and read the generated SQL.
Open Prisma Studio and add a product by hand.
Query it from a NestJS service and return it from an endpoint.
Misspell a field in a query. Confirm it fails to compile.
Add a field to the schema, migrate, and use it immediately with autocomplete.
Turn on query logging and watch the SQL for a findMany.
Try to create two products with the same slug. Read the constraint error.
Add a required field to a model that already has rows. Read Prisma's warning and work out why it cannot proceed.
Run migrate reset and confirm your data is gone. Understand why this is a development-only command.

Next: modelling the catalogue properly.