Prisma ORM Setup & Queries

Prisma ORM — Type-Safe Database Access

Writing raw SQL works, but as your schema grows it becomes hard to maintain — no autocomplete, no type safety, no automatic protection against common mistakes. Prisma solves this by generating a type-safe client from your database schema. You get full IntelliSense on every query, TypeScript errors before runtime errors, and automatic migration management.

Setup

npm install prisma @prisma/client
npx prisma init

This creates:

• prisma/schema.prisma — your data model
• .env — with a DATABASE_URL placeholder

Set your database URL:

# .env
DATABASE_URL="postgresql://postgres:password@localhost:5432/myapp"

The Schema

Define your data models in prisma/schema.prisma:

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

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

model User {
    id        String   @id @default(cuid())
    email     String   @unique
    name      String
    password  String
    role      Role     @default(USER)
    createdAt DateTime @default(now())
    updatedAt DateTime @updatedAt
    
    // Relations
    enrollments Enrollment[]
    courses     Course[]     @relation("CourseAuthor")
    
    @@map("users")
}

enum Role {
    USER
    ADMIN
    INSTRUCTOR
}

model Course {
    id          String   @id @default(cuid())
    slug        String   @unique
    title       String
    description String?
    price       Float    @default(0)
    published   Boolean  @default(false)
    createdAt   DateTime @default(now())
    
    authorId    String?
    author      User?    @relation("CourseAuthor", fields: [authorId], references: [id], onDelete: SetNull)
    
    enrollments Enrollment[]
    lessons     Lesson[]
    
    @@map("courses")
}

model Lesson {
    id       String @id @default(cuid())
    slug     String
    title    String
    content  String
    order    Int
    
    courseId String
    course   Course @relation(fields: [courseId], references: [id], onDelete: Cascade)
    
    @@unique([courseId, slug])
    @@map("lessons")
}

model Enrollment {
    id         String   @id @default(cuid())
    enrolledAt DateTime @default(now())
    completed  Boolean  @default(false)
    
    userId   String
    courseId String
    
    user   User   @relation(fields: [userId], references: [id], onDelete: Cascade)
    course Course @relation(fields: [courseId], references: [id], onDelete: Cascade)
    
    @@unique([userId, courseId])
    @@map("enrollments")
}

Migrations

After defining your schema, create and apply a migration:

# Create and apply migration
npx prisma migrate dev --name init

# Apply migrations without creating new ones (production)
npx prisma migrate deploy

# View your data in Prisma Studio (local GUI)
npx prisma studio

Every migrate dev creates a SQL migration file in prisma/migrations/ — check these into git.

Generate the Client

npx prisma generate

This creates a fully-typed client based on your schema. Run this after every schema change.

Using the Prisma Client

// src/lib/db.ts
import { PrismaClient } from "@prisma/client";

const globalForPrisma = global as unknown as { prisma: PrismaClient };

export const db =
    globalForPrisma.prisma ??
    new PrismaClient({
        log: process.env.NODE_ENV === "development" ? ["query"] : [],
    });

if (process.env.NODE_ENV !== "production") globalForPrisma.prisma = db;

This pattern prevents creating multiple Prisma instances during Next.js hot reloading.

CRUD Operations

Create

// Create a user
const user = await db.user.create({
    data: {
        email: "alice@example.com",
        name: "Alice Johnson",
        password: hashedPassword,
    },
});

// Create with nested relation
const course = await db.course.create({
    data: {
        slug: "react-complete",
        title: "React Complete Course",
        price: 49.00,
        authorId: user.id,
        lessons: {
            create: [
                { slug: "intro", title: "Introduction", content: "...", order: 1 },
                { slug: "setup", title: "Setup", content: "...", order: 2 },
            ],
        },
    },
    include: {
        lessons: true,
        author: { select: { name: true, email: true } },
    },
});

Read

// Find one by unique field
const user = await db.user.findUnique({
    where: { email: "alice@example.com" },
});

// Find with relations
const course = await db.course.findUnique({
    where: { slug: "react-complete" },
    include: {
        author: true,
        lessons: { orderBy: { order: "asc" } },
        _count: { select: { enrollments: true } },
    },
});

// Find many with filtering
const courses = await db.course.findMany({
    where: {
        published: true,
        price: { lte: 50 },        // less than or equal
        title: { contains: "React", mode: "insensitive" },
    },
    orderBy: { createdAt: "desc" },
    take: 20,
    skip: 0,
    select: {
        id: true,
        slug: true,
        title: true,
        price: true,
        author: { select: { name: true } },
    },
});

// Count
const totalCourses = await db.course.count({
    where: { published: true },
});

Update

// Update a record
const updated = await db.user.update({
    where: { id: userId },
    data: { name: "Alice Williams" },
});

// Update or create (upsert)
const enrollment = await db.enrollment.upsert({
    where: { userId_courseId: { userId, courseId } },
    update: { completed: true },
    create: { userId, courseId },
});

// Update many
await db.course.updateMany({
    where: { authorId: userId },
    data: { published: false },
});

Delete

// Delete one
await db.user.delete({ where: { id: userId } });

// Delete many
await db.enrollment.deleteMany({
    where: { userId, completed: true },
});

Transactions

// Sequential operations — all succeed or all fail
const result = await db.$transaction(async (tx) => {
    const enrollment = await tx.enrollment.create({
        data: { userId, courseId },
    });
    
    await tx.course.update({
        where: { id: courseId },
        data: { studentCount: { increment: 1 } },
    });
    
    return enrollment;
});

// Batch operations (faster — single round trip to DB)
const [users, coursesCount] = await db.$transaction([
    db.user.findMany({ take: 10 }),
    db.course.count(),
]);

Pagination with Cursor

Cursor-based pagination is faster than offset pagination for large datasets:

async function getCourses(cursor?: string, limit = 20) {
    const courses = await db.course.findMany({
        take: limit + 1,   // take one extra to determine if there's a next page
        cursor: cursor ? { id: cursor } : undefined,
        orderBy: { createdAt: "desc" },
    });
    
    const hasMore = courses.length > limit;
    const items = hasMore ? courses.slice(0, -1) : courses;
    const nextCursor = hasMore ? items[items.length - 1].id : null;
    
    return { items, nextCursor, hasMore };
}

Seeding the Database

// prisma/seed.ts
import { PrismaClient } from "@prisma/client";
import bcrypt from "bcryptjs";

const db = new PrismaClient();

async function main() {
    // Create admin user
    const admin = await db.user.upsert({
        where: { email: "admin@example.com" },
        update: {},
        create: {
            email: "admin@example.com",
            name: "Admin",
            password: await bcrypt.hash("password123", 10),
            role: "ADMIN",
        },
    });
    
    // Create sample courses
    await db.course.upsert({
        where: { slug: "react-complete" },
        update: {},
        create: {
            slug: "react-complete",
            title: "React Complete Course",
            price: 49.00,
            published: true,
            authorId: admin.id,
        },
    });
    
    console.log("Database seeded");
}

main().catch(console.error).finally(() => db.$disconnect());

// package.json — add seed script
{
    "prisma": {
        "seed": "ts-node prisma/seed.ts"
    }
}

npx prisma db seed

Next lesson: Database design — structuring your schema correctly from the start.