Prisma ORM

Difficulty: Intermediate

Prisma is a modern, type-safe ORM (Object-Relational Mapper) for Node.js and TypeScript. Unlike traditional ORMs that map classes to tables, Prisma uses a declarative schema language to define your database models, and it generates a fully typed client that provides auto-completion and compile-time error checking. This 'schema-first' approach means your database structure is defined in one place (the schema.prisma file), and everything else - types, client, and migrations - is generated from it.

The Prisma schema file (schema.prisma) is the single source of truth for your database. It defines the data source (which database to connect to), the generator (which client to produce), and your models (which correspond to database tables). Each model lists its fields with types, attributes like @id, @unique, @default, and @relation. When you change the schema, you create a migration with `prisma migrate dev`, which generates SQL migration files and applies them to your database.

Prisma Client is an auto-generated, type-safe query builder. After running `prisma generate`, you import PrismaClient and use it to perform CRUD operations. Every query is fully typed - your IDE knows exactly which fields exist on each model, what types they are, and which relations are available. This catches errors at compile time instead of runtime, which is a massive productivity and reliability boost compared to raw SQL or loosely typed ORMs.

Relations in Prisma are defined using the @relation attribute. A one-to-many relation (e.g., a user has many posts) is defined by adding a field on both models: the 'many' side gets an array field and the 'one' side gets a scalar field with a foreign key. Many-to-many relations can be implicit (Prisma creates the join table) or explicit (you define it). When querying, you use the `include` option to eagerly load related data or `select` to pick specific fields.

Prisma's migration system tracks changes to your schema over time. Each migration is a timestamped SQL file stored in the `prisma/migrations` directory. In development, `prisma migrate dev` creates and applies migrations interactively. In production, `prisma migrate deploy` applies pending migrations without prompting. This ensures your database schema evolves safely and reproducibly across all environments.

Code examples

Prisma Schema Definition

// prisma/schema.prisma

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

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

// model User {
//   id        Int       @id @default(autoincrement())
//   email     String    @unique
//   name      String
//   role      Role      @default(STUDENT)
//   posts     Post[]
//   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      String[]
//   createdAt DateTime @default(now())
// }

// enum Role {
//   STUDENT
//   RECRUITER
//   ADMIN
// }

console.log('Prisma schema defines:');
console.log('- Data source (PostgreSQL)');
console.log('- Models with typed fields and relations');
console.log('- Enums for constrained values');
console.log('- Automatic timestamps with @default(now()) and @updatedAt');

The schema defines two models with a one-to-many relation (User has many Posts). Each field has a type and optional attributes. The ? after String makes it nullable. String[] is a PostgreSQL array type. The @relation attribute defines the foreign key relationship.

CRUD Operations with Prisma Client

const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

async function crudExample() {
  // CREATE
  const user = await prisma.user.create({
    data: {
      email: 'alice@example.com',
      name: 'Alice',
      role: 'STUDENT'
    }
  });
  console.log('Created:', user.name, user.id);

  // CREATE with nested relation
  const userWithPost = await prisma.user.create({
    data: {
      email: 'bob@example.com',
      name: 'Bob',
      posts: {
        create: [
          { title: 'First Post', content: 'Hello world' },
          { title: 'Second Post', content: 'Prisma is great', published: true }
        ]
      }
    },
    include: { posts: true }
  });
  console.log('Bob has', userWithPost.posts.length, 'posts');

  // READ with filtering and pagination
  const users = await prisma.user.findMany({
    where: { role: 'STUDENT' },
    orderBy: { createdAt: 'desc' },
    take: 10,
    skip: 0,
    select: { id: true, name: true, email: true }
  });
  console.log('Found', users.length, 'students');

  // UPDATE
  const updated = await prisma.user.update({
    where: { email: 'alice@example.com' },
    data: { name: 'Alice Smith', role: 'RECRUITER' }
  });
  console.log('Updated:', updated.name, updated.role);

  // DELETE
  await prisma.user.delete({ where: { email: 'alice@example.com' } });
  console.log('User deleted');
}

console.log('Prisma CRUD functions defined');
// crudExample().catch(console.error).finally(() => prisma.$disconnect());

Prisma Client provides intuitive methods for every operation. The nested create inside user.create automatically sets the foreign key. The include option eagerly loads relations. The select option returns only specified fields. All operations are fully type-safe.

Advanced Queries and Transactions

const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

// Complex filtering
async function searchPosts(query) {
  return prisma.post.findMany({
    where: {
      OR: [
        { title: { contains: query, mode: 'insensitive' } },
        { content: { contains: query, mode: 'insensitive' } }
      ],
      published: true
    },
    include: {
      author: { select: { name: true, email: true } }
    },
    orderBy: { createdAt: 'desc' }
  });
}

// Aggregation
async function getUserStats() {
  const stats = await prisma.user.groupBy({
    by: ['role'],
    _count: { id: true },
    _max: { createdAt: true }
  });
  console.log('Stats by role:', stats);
  return stats;
}

// Transaction - multiple operations that must all succeed
async function transferPost(postId, fromUserId, toUserId) {
  return prisma.$transaction(async (tx) => {
    const post = await tx.post.findUnique({ where: { id: postId } });

    if (!post || post.authorId !== fromUserId) {
      throw new Error('Post not found or not owned by sender');
    }

    const updatedPost = await tx.post.update({
      where: { id: postId },
      data: { authorId: toUserId }
    });

    await tx.user.update({
      where: { id: fromUserId },
      data: { updatedAt: new Date() }
    });

    return updatedPost;
  });
}

console.log('Advanced Prisma queries defined');
console.log('Features: OR filters, insensitive search, groupBy, transactions');

Prisma supports complex WHERE conditions with OR/AND/NOT, case-insensitive search with mode: 'insensitive', aggregation with groupBy, and interactive transactions with $transaction. The transaction callback receives a tx client - if any operation fails or you throw, all changes are rolled back.

Key points

Run `npx prisma generate` after every schema change to regenerate the client with updated types.
Use `npx prisma migrate dev` in development and `npx prisma migrate deploy` in production.
Prisma Client is a singleton - create one instance and reuse it across your application.
Use prisma.$disconnect() in serverless functions and script cleanup to properly close connections.

Concepts covered

Prisma, schema, migrations, Prisma Client, CRUD, relations, type safety