ORM with Prisma — Database Design | Sabaoon Academy

Prisma is a modern ORM (Object-Relational Mapper) for Node.js and TypeScript. It replaces raw SQL queries with a type-safe, auto-completed API that catches errors at build time instead of runtime.

Why Prisma?

Feature	Raw SQL	Prisma
Type safety	None	Full TypeScript support
Auto-complete	None	Schema-aware IDE completions
Migrations	Manual SQL files	Auto-generated from schema
Query building	String concatenation	Fluent JavaScript API
Database support	One at a time	PostgreSQL, MySQL, SQLite, etc.

Setting Up Prisma

npm install prisma @prisma/client
npx prisma init

This creates a prisma/ directory with a schema.prisma file and a .env file for your database URL.

Configure the Database

# .env
DATABASE_URL="file:./dev.db"

For PostgreSQL, the URL would look like:

DATABASE_URL="postgresql://user:password@localhost:5432/mydb"

The Prisma Schema

The schema file defines your data models, relationships, and database connection:

// prisma/schema.prisma

generator client {
  provider = "prisma-client-js"
}

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

model User {
  id        Int       @id @default(autoincrement())
  email     String    @unique
  name      String
  role      String    @default("user")
  posts     Post[]
  comments  Comment[]
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
}

model Post {
  id          Int       @id @default(autoincrement())
  title       String
  slug        String    @unique
  content     String
  published   Boolean   @default(false)
  author      User      @relation(fields: [authorId], references: [id])
  authorId    Int
  tags        Tag[]
  comments    Comment[]
  publishedAt DateTime?
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt
}

model Tag {
  id    Int    @id @default(autoincrement())
  name  String @unique
  posts Post[]
}

model Comment {
  id        Int      @id @default(autoincrement())
  body      String
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
  post      Post     @relation(fields: [postId], references: [id], onDelete: Cascade)
  postId    Int
  createdAt DateTime @default(now())
}

Running Migrations

After defining or modifying your schema, create and run a migration:

# Create a migration
npx prisma migrate dev --name init

# Apply migrations in production
npx prisma migrate deploy

# Reset database (development only)
npx prisma migrate reset

Each migration is stored as a SQL file in prisma/migrations/, giving you a full history of schema changes.

Generating the Client

Prisma generates a type-safe client based on your schema:

npx prisma generate

This runs automatically after prisma migrate dev, but you can run it manually when needed.

CRUD Operations

Create

Ctrl+Enter

HTML

CSS

Preview

Read

Ctrl+Enter

HTML

CSS

Preview

Filtering

Ctrl+Enter

HTML

CSS

Preview

Update

Ctrl+Enter

HTML

CSS

Preview

Delete

await prisma.user.delete({ where: { id: 1 } });
await prisma.user.deleteMany({ where: { role: "inactive" } });

Transactions

Run multiple operations atomically:

Ctrl+Enter

HTML

CSS

Preview

Prisma with Express

Ctrl+Enter

HTML

CSS

Preview

Prisma Studio

Prisma includes a visual database browser:

npx prisma studio

This opens a web interface at http://localhost:5555 where you can browse, filter, and edit your data.

Practical Exercise

Build a task management API with Prisma:

model Project {
  id          Int      @id @default(autoincrement())
  name        String
  description String?
  tasks       Task[]
  createdAt   DateTime @default(now())
}

model Task {
  id        Int      @id @default(autoincrement())
  title     String
  status    String   @default("todo")
  priority  Int      @default(0)
  project   Project  @relation(fields: [projectId], references: [id], onDelete: Cascade)
  projectId Int
  createdAt DateTime @default(now())
  dueDate   DateTime?
}

Ctrl+Enter

HTML

CSS

Preview

Key Takeaways

Prisma generates a type-safe client from your schema, catching errors before runtime.
The schema file defines models, fields, relationships, and constraints in a declarative format.
Migrations track schema changes over time and can be applied to any environment.
Use include to load relations and select to pick specific fields.
Transactions ensure multiple operations succeed or fail together.