Prisma | CodeWorlds

Prisma - Complete guide to the next-generation ORM for TypeScript

What is Prisma and why does it change the approach to databases?

Prisma is a next-generation ORM (Object-Relational Mapping) for Node.js and TypeScript. Unlike traditional ORMs like Sequelize or TypeORM, Prisma generates a TypeScript client directly from your database schema, providing full type safety and excellent autocomplete in your IDE.

Prisma consists of three main components:

Prisma Client - Auto-generated, type-safe query builder
Prisma Migrate - Schema-based migration system
Prisma Studio - GUI for browsing and editing data

Why did Prisma win over the alternatives?

Type safety on steroids

Code

TypeScript

// Traditional ORM - no type safety
const user = await User.findOne({ where: { id: 1 } })
user.nmae // Typo - no error at compile time!

// Prisma - full type safety
const user = await prisma.user.findUnique({ where: { id: 1 } })
user.nmae // ❌ Error: Property 'nmae' does not exist on type 'User'
user.name // ✅ Works with autocomplete

Intuitive API

Code

TypeScript

// Prisma has a readable, chainable API
const posts = await prisma.post.findMany({
  where: {
    author: {
      email: { contains: '@example.com' }
    },
    published: true
  },
  include: {
    author: true,
    comments: {
      take: 5,
      orderBy: { createdAt: 'desc' }
    }
  },
  orderBy: { createdAt: 'desc' },
  take: 10
})

Installation and setup

New project

Code

Bash

# Project initialization
npm init -y
npm install typescript ts-node @types/node --save-dev
npm install prisma --save-dev
npm install @prisma/client

# Prisma initialization
npx prisma init

Structure after initialization

Code

TEXT

my-project/
├── prisma/
│   └── schema.prisma    # Database schema
├── .env                 # Environment variables (DATABASE_URL)
└── package.json

Prisma Schema - The heart of the project

Basic structure

prisma/schema.prisma

Prisma

// prisma/schema.prisma

// Generator - how to generate the client
generator client {
  provider = "prisma-client-js"
}

// Datasource - database connection
datasource db {
  provider = "postgresql" // postgresql, mysql, sqlite, sqlserver, mongodb
  url      = env("DATABASE_URL")
}

// Models - tables in the database
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  role      Role     @default(USER)
  posts     Post[]
  profile   Profile?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

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

  @@index([authorId])
}

model Profile {
  id     Int    @id @default(autoincrement())
  bio    String
  user   User   @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId Int    @unique
}

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

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

enum Role {
  USER
  ADMIN
  MODERATOR
}

Field types

Code

Prisma

model Example {
  // Basic types
  stringField    String
  intField       Int
  floatField     Float
  boolField      Boolean
  dateField      DateTime

  // Optional field (can be null)
  optionalField  String?

  // List
  tags           String[]

  // JSON (PostgreSQL, MySQL)
  metadata       Json

  // Decimal for financial data
  price          Decimal  @db.Decimal(10, 2)

  // BigInt for large numbers
  bigNumber      BigInt

  // Bytes for binary files
  data           Bytes
}

Field attributes

Code

Prisma

model User {
  id        Int      @id @default(autoincrement())  // Primary key + auto increment
  uuid      String   @id @default(uuid())           // UUID as primary key
  cuid      String   @id @default(cuid())           // CUID as primary key

  email     String   @unique                         // Unique
  role      Role     @default(USER)                  // Default value

  createdAt DateTime @default(now())                 // Timestamp
  updatedAt DateTime @updatedAt                      // Auto-update timestamp

  // Column name mapping in the database
  firstName String   @map("first_name")

  // Table name mapping
  @@map("users")

  // Indexes
  @@index([email])
  @@index([firstName, lastName])

  // Composite unique
  @@unique([email, tenantId])
}

Relations

Code

Prisma

// One-to-One
model User {
  id      Int      @id @default(autoincrement())
  profile Profile?
}

model Profile {
  id     Int  @id @default(autoincrement())
  user   User @relation(fields: [userId], references: [id])
  userId Int  @unique // unique = one-to-one
}

// One-to-Many
model User {
  id    Int    @id @default(autoincrement())
  posts Post[]
}

model Post {
  id       Int  @id @default(autoincrement())
  author   User @relation(fields: [authorId], references: [id])
  authorId Int
}

// Many-to-Many (implicit)
model Post {
  id   Int   @id @default(autoincrement())
  tags Tag[]
}

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

// Many-to-Many (explicit - with additional fields)
model Post {
  id   Int        @id @default(autoincrement())
  tags PostTag[]
}

model Tag {
  id    Int       @id @default(autoincrement())
  posts PostTag[]
}

model PostTag {
  post      Post     @relation(fields: [postId], references: [id])
  postId    Int
  tag       Tag      @relation(fields: [tagId], references: [id])
  tagId     Int
  createdAt DateTime @default(now())

  @@id([postId, tagId])
}

// Self-relation (e.g. followers)
model User {
  id        Int    @id @default(autoincrement())
  followers User[] @relation("UserFollows")
  following User[] @relation("UserFollows")
}

Migrations

Basic commands

Code

Bash

# Create a migration from the current schema
npx prisma migrate dev --name init

# Apply migrations in production
npx prisma migrate deploy

# Reset the database (WARNING: deletes all data)
npx prisma migrate reset

# Generate the client without migrations
npx prisma generate

# Format the schema
npx prisma format

# Validate the schema
npx prisma validate

# Open Prisma Studio
npx prisma studio

Migration workflow

Code

Bash

# 1. Modify schema.prisma
# 2. Create a migration
npx prisma migrate dev --name add_user_avatar

# 3. Review the generated SQL in prisma/migrations/
# 4. Commit the changes to the repository

Prisma Client - Query Builder

Client initialization

TSlib/prisma.ts

TypeScript

// lib/prisma.ts
import { PrismaClient } from '@prisma/client'

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined
}

export const prisma = globalForPrisma.prisma ?? new PrismaClient({
  log: process.env.NODE_ENV === 'development'
    ? ['query', 'error', 'warn']
    : ['error'],
})

if (process.env.NODE_ENV !== 'production') {
  globalForPrisma.prisma = prisma
}

CRUD Operations

Code

TypeScript

import { prisma } from '@/lib/prisma'

// CREATE
const user = await prisma.user.create({
  data: {
    email: 'alice@example.com',
    name: 'Alice',
    profile: {
      create: { bio: 'Software developer' }
    },
    posts: {
      create: [
        { title: 'Hello World' },
        { title: 'My Second Post' }
      ]
    }
  },
  include: {
    profile: true,
    posts: true
  }
})

// CREATE MANY
const users = await prisma.user.createMany({
  data: [
    { email: 'bob@example.com', name: 'Bob' },
    { email: 'charlie@example.com', name: 'Charlie' }
  ],
  skipDuplicates: true
})

// READ - findUnique (by unique field)
const user = await prisma.user.findUnique({
  where: { email: 'alice@example.com' }
})

// READ - findFirst
const firstAdmin = await prisma.user.findFirst({
  where: { role: 'ADMIN' },
  orderBy: { createdAt: 'asc' }
})

// READ - findMany
const users = await prisma.user.findMany({
  where: {
    email: { contains: '@example.com' },
    role: { in: ['USER', 'ADMIN'] },
    posts: { some: { published: true } }
  },
  orderBy: [
    { role: 'asc' },
    { name: 'asc' }
  ],
  skip: 0,
  take: 10
})

// UPDATE
const updatedUser = await prisma.user.update({
  where: { email: 'alice@example.com' },
  data: {
    name: 'Alice Smith',
    posts: {
      updateMany: {
        where: { published: false },
        data: { published: true }
      }
    }
  }
})

// UPDATE MANY
const result = await prisma.post.updateMany({
  where: { authorId: 1, published: false },
  data: { published: true }
})

// UPSERT
const user = await prisma.user.upsert({
  where: { email: 'alice@example.com' },
  update: { name: 'Alice Updated' },
  create: { email: 'alice@example.com', name: 'Alice' }
})

// DELETE
const deletedUser = await prisma.user.delete({
  where: { id: 1 }
})

// DELETE MANY
const result = await prisma.post.deleteMany({
  where: { published: false }
})

Advanced filters

Code

TypeScript

// Comparison operators
const posts = await prisma.post.findMany({
  where: {
    viewCount: { gt: 100 },        // greater than
    likes: { gte: 10 },            // greater than or equal
    comments: { lt: 50 },          // less than
    shares: { lte: 5 },            // less than or equal
    title: { not: 'Draft' },       // not equal
    authorId: { in: [1, 2, 3] },   // in array
    status: { notIn: ['DELETED'] } // not in array
  }
})

// String filters
const users = await prisma.user.findMany({
  where: {
    email: { contains: '@gmail.com' },
    name: { startsWith: 'A' },
    bio: { endsWith: 'developer' },
    // Case insensitive (PostgreSQL)
    name: { contains: 'alice', mode: 'insensitive' }
  }
})

// Relations
const usersWithPosts = await prisma.user.findMany({
  where: {
    // Has at least one post
    posts: { some: { published: true } },
    // All posts are published
    posts: { every: { published: true } },
    // Has no posts
    posts: { none: {} },
    // Check fields in a relation
    posts: {
      some: {
        title: { contains: 'Prisma' },
        comments: { some: {} }
      }
    }
  }
})

// AND, OR, NOT
const posts = await prisma.post.findMany({
  where: {
    AND: [
      { published: true },
      { authorId: 1 }
    ],
    OR: [
      { title: { contains: 'Prisma' } },
      { content: { contains: 'Prisma' } }
    ],
    NOT: {
      status: 'DELETED'
    }
  }
})

Select and Include

Code

TypeScript

// Select - choose only specific fields
const users = await prisma.user.findMany({
  select: {
    id: true,
    name: true,
    email: true,
    _count: {
      select: { posts: true }
    }
  }
})

// Include - attach relations
const users = await prisma.user.findMany({
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: 'desc' },
      take: 5,
      include: {
        comments: true
      }
    },
    profile: true
  }
})

// Nested select within include
const users = await prisma.user.findMany({
  include: {
    posts: {
      select: {
        id: true,
        title: true
      }
    }
  }
})

Aggregations

Code

TypeScript

// Count
const userCount = await prisma.user.count({
  where: { role: 'ADMIN' }
})

// Aggregate
const stats = await prisma.post.aggregate({
  _count: { _all: true },
  _avg: { viewCount: true },
  _sum: { viewCount: true },
  _min: { viewCount: true },
  _max: { viewCount: true },
  where: { published: true }
})

// Group by
const postsByAuthor = await prisma.post.groupBy({
  by: ['authorId'],
  _count: { _all: true },
  _avg: { viewCount: true },
  having: {
    viewCount: { _avg: { gt: 100 } }
  },
  orderBy: {
    _count: { _all: 'desc' }
  }
})

Transactions

Code

TypeScript

// Interactive transaction
const [user, post] = await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({
    data: { email: 'new@example.com', name: 'New User' }
  })

  const post = await tx.post.create({
    data: {
      title: 'Welcome Post',
      authorId: user.id
    }
  })

  // If anything fails, everything will be rolled back
  return [user, post]
})

// Sequential transaction (array of operations)
const [deletedPosts, updatedUser] = await prisma.$transaction([
  prisma.post.deleteMany({ where: { authorId: 1 } }),
  prisma.user.update({
    where: { id: 1 },
    data: { postCount: 0 }
  })
])

Raw Queries

Code

TypeScript

// Raw SQL query
const users = await prisma.$queryRaw`
  SELECT * FROM "User"
  WHERE email LIKE ${`%@example.com`}
  ORDER BY "createdAt" DESC
  LIMIT 10
`

// Raw SQL execute
const result = await prisma.$executeRaw`
  UPDATE "Post"
  SET "viewCount" = "viewCount" + 1
  WHERE id = ${postId}
`

// Tagged template literal (safe from SQL injection)
const email = 'alice@example.com'
const user = await prisma.$queryRaw`
  SELECT * FROM "User" WHERE email = ${email}
`

Integration with Next.js

Server Components

TSapp/users/page.tsx

TypeScript

// app/users/page.tsx
import { prisma } from '@/lib/prisma'

export default async function UsersPage() {
  const users = await prisma.user.findMany({
    include: { _count: { select: { posts: true } } }
  })

  return (
    <ul>
      {users.map(user => (
        <li key={user.id}>
          {user.name} ({user._count.posts} posts)
        </li>
      ))}
    </ul>
  )
}

Server Actions

TSapp/users/actions.ts

TypeScript

// app/users/actions.ts
'use server'

import { prisma } from '@/lib/prisma'
import { revalidatePath } from 'next/cache'

export async function createUser(formData: FormData) {
  const email = formData.get('email') as string
  const name = formData.get('name') as string

  await prisma.user.create({
    data: { email, name }
  })

  revalidatePath('/users')
}

export async function deleteUser(id: number) {
  await prisma.user.delete({ where: { id } })
  revalidatePath('/users')
}

API Routes

TSapp/api/users/route.ts

TypeScript

// app/api/users/route.ts
import { prisma } from '@/lib/prisma'
import { NextResponse } from 'next/server'

export async function GET(request: Request) {
  const { searchParams } = new URL(request.url)
  const page = parseInt(searchParams.get('page') || '1')
  const limit = 10

  const [users, total] = await prisma.$transaction([
    prisma.user.findMany({
      skip: (page - 1) * limit,
      take: limit,
      orderBy: { createdAt: 'desc' }
    }),
    prisma.user.count()
  ])

  return NextResponse.json({
    users,
    pagination: {
      page,
      limit,
      total,
      pages: Math.ceil(total / limit)
    }
  })
}

export async function POST(request: Request) {
  const body = await request.json()

  const user = await prisma.user.create({
    data: body
  })

  return NextResponse.json(user, { status: 201 })
}

// app/api/users/[id]/route.ts
export async function GET(
  request: Request,
  { params }: { params: { id: string } }
) {
  const user = await prisma.user.findUnique({
    where: { id: parseInt(params.id) },
    include: { posts: true, profile: true }
  })

  if (!user) {
    return NextResponse.json({ error: 'User not found' }, { status: 404 })
  }

  return NextResponse.json(user)
}

Seeding

TSprisma/seed.ts

TypeScript

// prisma/seed.ts
import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function main() {
  // Delete existing data
  await prisma.post.deleteMany()
  await prisma.user.deleteMany()

  // Create users
  const alice = await prisma.user.create({
    data: {
      email: 'alice@example.com',
      name: 'Alice',
      role: 'ADMIN',
      profile: {
        create: { bio: 'Admin user' }
      },
      posts: {
        create: [
          { title: 'Hello World', published: true },
          { title: 'Draft Post', published: false }
        ]
      }
    }
  })

  const bob = await prisma.user.create({
    data: {
      email: 'bob@example.com',
      name: 'Bob',
      posts: {
        create: { title: 'My First Post', published: true }
      }
    }
  })

  console.log({ alice, bob })
}

main()
  .catch((e) => {
    console.error(e)
    process.exit(1)
  })
  .finally(async () => {
    await prisma.$disconnect()
  })

package.json

JSON

// package.json
{
  "prisma": {
    "seed": "ts-node --compiler-options {\"module\":\"CommonJS\"} prisma/seed.ts"
  }
}

Code

Bash

npx prisma db seed

Prisma vs Alternatives

Aspect	Prisma	TypeORM	Drizzle
Type Safety	Full (generated)	Partial	Full
API	Query builder	Active Record + Query builder	SQL-like
Migrations	Declarative	Imperative	SQL
Performance	Good	Good	Very good
Learning Curve	Low	Medium	Low
Flexibility	Medium	High	High

Best Practices

1. Singleton pattern for the client

Code

TypeScript

// ALWAYS use the singleton pattern in Next.js
// Prevents creating multiple connections in development
export const prisma = globalForPrisma.prisma ?? new PrismaClient()

2. Soft delete instead of hard delete

Code

Prisma

model Post {
  id        Int       @id @default(autoincrement())
  deletedAt DateTime?
  // ...
}

Code

TypeScript

// "Delete" a post
await prisma.post.update({
  where: { id: 1 },
  data: { deletedAt: new Date() }
})

// Fetch only non-deleted records
const posts = await prisma.post.findMany({
  where: { deletedAt: null }
})

3. Middleware for audit logging

Code

TypeScript

const prisma = new PrismaClient().$extends({
  query: {
    $allModels: {
      async $allOperations({ model, operation, args, query }) {
        const start = Date.now()
        const result = await query(args)
        const duration = Date.now() - start

        console.log(`${model}.${operation} took ${duration}ms`)

        return result
      }
    }
  }
})

4. Pagination helper

Code

TypeScript

interface PaginationParams {
  page?: number
  limit?: number
}

async function paginate<T>(
  model: any,
  { page = 1, limit = 10 }: PaginationParams,
  args: any = {}
) {
  const [data, total] = await prisma.$transaction([
    model.findMany({
      ...args,
      skip: (page - 1) * limit,
      take: limit
    }),
    model.count({ where: args.where })
  ])

  return {
    data,
    pagination: {
      page,
      limit,
      total,
      pages: Math.ceil(total / limit),
      hasNext: page * limit < total,
      hasPrev: page > 1
    }
  }
}

// Usage
const result = await paginate(prisma.user, { page: 2, limit: 20 }, {
  where: { role: 'USER' },
  orderBy: { createdAt: 'desc' }
})

FAQ

Does Prisma support MongoDB?

Yes, since version 3.12. The syntax is very similar, but some features (like transactions) require a replica set.

How to debug queries?

Code

TypeScript

const prisma = new PrismaClient({
  log: ['query', 'info', 'warn', 'error']
})

How to perform batch operations?

Code

TypeScript

// createMany, updateMany, deleteMany
const result = await prisma.user.createMany({
  data: users,
  skipDuplicates: true
})

Is Prisma fast?

Prisma is fast enough for most use cases. For extremely performance-critical queries, you can use $queryRaw.

Summary

Prisma changes the way we work with databases in Node.js/TypeScript:

Type Safety - Errors caught at compile time
DX - Excellent autocomplete and inline documentation
Migrations - Simple, declarative migrations
Prisma Studio - GUI without writing code