Back to all articles
DatabaseORMTypeScriptNode.jsDocumentation

Complete Guide to Prisma ORM: Modern Database Toolkit for TypeScript and Node.js

Comprehensive documentation covering Prisma ORM setup, schema design, queries, migrations, and best practices for modern web applications.

Complete Guide to Prisma ORM: Modern Database Toolkit for TypeScript and Node.js

Prisma is a next-generation ORM (Object-Relational Mapping) that provides a type-safe database client for Node.js and TypeScript applications. It simplifies database operations while maintaining full type safety and excellent developer experience.

Table of Contents

What is Prisma?

Prisma consists of three main tools:

  1. Prisma Client: Auto-generated and type-safe query builder
  2. Prisma Migrate: Migration system for database schema changes
  3. Prisma Studio: Visual database browser and editor

Key Benefits

  • Type Safety: Full TypeScript support with auto-generated types
  • Auto-completion: Intelligent IntelliSense for queries
  • Database Agnostic: Supports PostgreSQL, MySQL, SQLite, SQL Server, and MongoDB
  • Migration System: Version control for your database schema
  • Performance: Optimized queries and connection pooling

Installation and Setup

1. Install Prisma

# Install Prisma CLI
npm install prisma --save-dev

# Install Prisma Client
npm install @prisma/client

# Initialize Prisma in your project
npx prisma init

2. Project Structure

After initialization, you'll have:

your-project/
├── prisma/
│   ├── schema.prisma
│   └── migrations/
├── .env
└── package.json

3. Environment Configuration

Configure your database connection in .env:

# PostgreSQL
DATABASE_URL="postgresql://username:password@localhost:5432/mydb?schema=public"

# MySQL
DATABASE_URL="mysql://username:password@localhost:3306/mydb"

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

Prisma Schema

The schema.prisma file is the heart of your Prisma setup. It defines your database schema, data sources, and generators.

Basic Schema Structure

// This is your Prisma schema file
// learn more about it in the docs: https://pris.ly/d/prisma-schema

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

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

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  posts     Post[]

  @@map("users")
}

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

  @@map("posts")
}

Database Connections

Supported Databases

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

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

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

// SQL Server
datasource db {
  provider = "sqlserver"
  url      = env("DATABASE_URL")
}

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

Connection Pooling

For production applications, use connection pooling:

# PostgreSQL with connection pooling
DATABASE_URL="postgresql://username:password@localhost:5432/mydb?schema=public&connection_limit=20&pool_timeout=20"

Models and Fields

Field Types

model ExampleModel {
  // Scalar types
  id          Int       @id @default(autoincrement())
  title       String
  content     String?   // Optional field
  count       Int       @default(0)
  price       Float
  isActive    Boolean   @default(true)
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt

  // Arrays (PostgreSQL)
  tags        String[]

  // JSON fields
  metadata    Json?

  // Enums
  status      Status    @default(DRAFT)
}

enum Status {
  DRAFT
  PUBLISHED
  ARCHIVED
}

Field Attributes

model User {
  id       Int    @id @default(autoincrement())
  email    String @unique
  username String @unique @db.VarChar(30)
  bio      String @db.Text
  age      Int    @db.SmallInt

  // Composite unique constraint
  @@unique([email, username])

  // Index
  @@index([email])

  // Custom table name
  @@map("app_users")
}

Relations

One-to-One Relations

model User {
  id      Int      @id @default(autoincrement())
  email   String   @unique
  profile Profile?
}

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

One-to-Many Relations

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

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

Many-to-Many Relations

model Post {
  id         Int        @id @default(autoincrement())
  title      String
  categories Category[]
}

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

Explicit Many-to-Many

model User {
  id             Int              @id @default(autoincrement())
  name           String
  projectMembers ProjectMember[]
}

model Project {
  id      Int             @id @default(autoincrement())
  name    String
  members ProjectMember[]
}

model ProjectMember {
  userId    Int
  projectId Int
  role      String
  user      User    @relation(fields: [userId], references: [id])
  project   Project @relation(fields: [projectId], references: [id])

  @@id([userId, projectId])
}

Prisma Client

Generating the Client

# Generate Prisma Client
npx prisma generate

Basic Client Setup

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

const prisma = new PrismaClient();

// Optional: Enable logging
const prisma = new PrismaClient({
  log: ['query', 'info', 'warn', 'error'],
});

// Graceful shutdown
async function gracefulShutdown() {
  await prisma.$disconnect();
}

process.on('SIGINT', gracefulShutdown);
process.on('SIGTERM', gracefulShutdown);

CRUD Operations

Create Operations

// Create single record
const user = await prisma.user.create({
  data: {
    email: 'john@example.com',
    name: 'John Doe',
  },
});

// Create with nested relations
const userWithPosts = await prisma.user.create({
  data: {
    email: 'jane@example.com',
    name: 'Jane Smith',
    posts: {
      create: [
        { title: 'First Post', content: 'Hello World!' },
        { title: 'Second Post', content: 'Another post' },
      ],
    },
  },
  include: {
    posts: true,
  },
});

// Create many
const users = await prisma.user.createMany({
  data: [
    { email: 'user1@example.com', name: 'User 1' },
    { email: 'user2@example.com', name: 'User 2' },
  ],
  skipDuplicates: true,
});

Read Operations

// Find unique
const user = await prisma.user.findUnique({
  where: { email: 'john@example.com' },
});

// Find first
const firstUser = await prisma.user.findFirst({
  where: { name: { contains: 'John' } },
});

// Find many
const users = await prisma.user.findMany({
  where: {
    posts: {
      some: {
        published: true,
      },
    },
  },
  include: {
    posts: {
      where: { published: true },
    },
  },
  orderBy: { createdAt: 'desc' },
  take: 10,
  skip: 0,
});

// Count
const userCount = await prisma.user.count({
  where: { posts: { some: {} } },
});

Update Operations

// Update single record
const updatedUser = await prisma.user.update({
  where: { id: 1 },
  data: { name: 'Updated Name' },
});

// Update many
const updateResult = await prisma.user.updateMany({
  where: { createdAt: { lt: new Date('2023-01-01') } },
  data: { isActive: false },
});

// Upsert (update or create)
const user = await prisma.user.upsert({
  where: { email: 'john@example.com' },
  update: { name: 'John Updated' },
  create: {
    email: 'john@example.com',
    name: 'John Doe',
  },
});

Delete Operations

// Delete single record
const deletedUser = await prisma.user.delete({
  where: { id: 1 },
});

// Delete many
const deleteResult = await prisma.user.deleteMany({
  where: { isActive: false },
});

Advanced Queries

Filtering and Conditions

// Complex where conditions
const users = await prisma.user.findMany({
  where: {
    OR: [{ email: { contains: '@gmail.com' } }, { name: { startsWith: 'John' } }],
    AND: [{ isActive: true }, { createdAt: { gte: new Date('2023-01-01') } }],
    NOT: {
      email: { in: ['banned1@example.com', 'banned2@example.com'] },
    },
    posts: {
      some: {
        title: { contains: 'prisma' },
        published: true,
      },
    },
  },
});

Aggregations

// Group by and aggregate
const result = await prisma.post.groupBy({
  by: ['authorId'],
  _count: {
    id: true,
  },
  _avg: {
    viewCount: true,
  },
  _sum: {
    viewCount: true,
  },
  having: {
    _count: {
      id: {
        gt: 5,
      },
    },
  },
});

// Aggregate functions
const aggregations = await prisma.post.aggregate({
  _count: true,
  _avg: { viewCount: true },
  _sum: { viewCount: true },
  _min: { createdAt: true },
  _max: { createdAt: true },
});

Raw Queries

// Raw SQL query
const users = await prisma.$queryRaw`
  SELECT * FROM users 
  WHERE email LIKE ${`%${searchTerm}%`}
`;

// Execute raw SQL
await prisma.$executeRaw`
  UPDATE users 
  SET last_login = NOW() 
  WHERE id = ${userId}
`;

// Type-safe raw queries
import { Prisma } from '@prisma/client';

const users: User[] = await prisma.$queryRaw(Prisma.sql`SELECT * FROM users WHERE active = true`);

Transactions

// Interactive transactions
const result = await prisma.$transaction(async (prisma) => {
  const user = await prisma.user.create({
    data: { email: 'john@example.com', name: 'John' },
  });

  const post = await prisma.post.create({
    data: {
      title: 'First Post',
      authorId: user.id,
    },
  });

  return { user, post };
});

// Batch transactions
const [userCount, postCount] = await prisma.$transaction([
  prisma.user.count(),
  prisma.post.count(),
]);

Migrations

Creating Migrations

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

# Create migration without applying
npx prisma migrate dev --create-only --name add-user-profile

# Apply pending migrations
npx prisma migrate dev

# Reset database (development only)
npx prisma migrate reset

Production Migrations

# Apply migrations in production
npx prisma migrate deploy

# Check migration status
npx prisma migrate status

Migration Files

-- CreateTable
CREATE TABLE "User" (
    "id" SERIAL NOT NULL,
    "email" TEXT NOT NULL,
    "name" TEXT,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "User_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");

Best Practices

Schema Design

// Use meaningful model names
model BlogPost {  // ✅ Good
  id       Int    @id @default(autoincrement())
  title    String
  content  String
  authorId Int
  author   User   @relation(fields: [authorId], references: [id])

  // Use proper constraints
  @@index([authorId])
  @@map("blog_posts")
}

// Use enums for fixed sets of values
enum PostStatus {
  DRAFT
  PUBLISHED
  ARCHIVED
}

Client Usage Patterns

// Singleton pattern for Prisma Client
class PrismaService {
  private static instance: PrismaClient;

  public static getInstance(): PrismaClient {
    if (!PrismaService.instance) {
      PrismaService.instance = new PrismaClient();
    }
    return PrismaService.instance;
  }
}

// Repository pattern
class UserRepository {
  constructor(private prisma: PrismaClient) {}

  async findById(id: number): Promise<User | null> {
    return this.prisma.user.findUnique({
      where: { id },
      include: {
        profile: true,
        posts: {
          where: { published: true },
          orderBy: { createdAt: 'desc' },
        },
      },
    });
  }

  async create(data: Prisma.UserCreateInput): Promise<User> {
    return this.prisma.user.create({ data });
  }
}

Error Handling

import { Prisma } from '@prisma/client';

try {
  const user = await prisma.user.create({
    data: { email: 'duplicate@example.com' },
  });
} catch (error) {
  if (error instanceof Prisma.PrismaClientKnownRequestError) {
    // Handle known Prisma errors
    if (error.code === 'P2002') {
      console.log('Unique constraint violation');
    }
  } else if (error instanceof Prisma.PrismaClientUnknownRequestError) {
    // Handle unknown database errors
    console.log('Unknown database error');
  } else if (error instanceof Prisma.PrismaClientRustPanicError) {
    // Handle Prisma engine panics
    console.log('Prisma engine panic');
  } else if (error instanceof Prisma.PrismaClientInitializationError) {
    // Handle initialization errors
    console.log('Prisma initialization error');
  } else if (error instanceof Prisma.PrismaClientValidationError) {
    // Handle validation errors
    console.log('Prisma validation error');
  }
}

Performance Optimization

Query Optimization

// Use select to fetch only needed fields
const users = await prisma.user.findMany({
  select: {
    id: true,
    email: true,
    name: true,
  },
});

// Use include judiciously
const usersWithPosts = await prisma.user.findMany({
  include: {
    posts: {
      select: {
        id: true,
        title: true,
        published: true,
      },
      where: { published: true },
      take: 5, // Limit related records
    },
  },
});

// Pagination
const posts = await prisma.post.findMany({
  take: 20,
  skip: (page - 1) * 20,
  orderBy: { createdAt: 'desc' },
});

// Cursor-based pagination (more efficient for large datasets)
const posts = await prisma.post.findMany({
  take: 20,
  cursor: lastPostId ? { id: lastPostId } : undefined,
  skip: lastPostId ? 1 : 0,
  orderBy: { id: 'desc' },
});

Connection Pooling

// Configure connection pooling
const prisma = new PrismaClient({
  datasources: {
    db: {
      url: process.env.DATABASE_URL + '?connection_limit=20&pool_timeout=20',
    },
  },
});

Monitoring and Debugging

// Enable query logging
const prisma = new PrismaClient({
  log: [
    {
      emit: 'event',
      level: 'query',
    },
    {
      emit: 'stdout',
      level: 'error',
    },
    {
      emit: 'stdout',
      level: 'info',
    },
    {
      emit: 'stdout',
      level: 'warn',
    },
  ],
});

prisma.$on('query', (e) => {
  console.log('Query: ' + e.query);
  console.log('Params: ' + e.params);
  console.log('Duration: ' + e.duration + 'ms');
});

Integration Examples

Express.js Integration

import express from 'express';
import { PrismaClient } from '@prisma/client';

const app = express();
const prisma = new PrismaClient();

app.use(express.json());

// Get all users
app.get('/users', async (req, res) => {
  try {
    const users = await prisma.user.findMany({
      include: { posts: true },
    });
    res.json(users);
  } catch (error) {
    res.status(500).json({ error: 'Failed to fetch users' });
  }
});

// Create user
app.post('/users', async (req, res) => {
  try {
    const { email, name } = req.body;
    const user = await prisma.user.create({
      data: { email, name },
    });
    res.status(201).json(user);
  } catch (error) {
    res.status(400).json({ error: 'Failed to create user' });
  }
});

// Graceful shutdown
process.on('SIGINT', async () => {
  await prisma.$disconnect();
  process.exit(0);
});

app.listen(3000, () => {
  console.log('Server running on port 3000');
});

Next.js Integration

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

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

export const prisma = globalForPrisma.prisma ?? new PrismaClient();

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

// pages/api/users.ts
import type { NextApiRequest, NextApiResponse } from 'next';
import { prisma } from '../../lib/prisma';

export default async function handler(req: NextApiRequest, res: NextApiResponse) {
  if (req.method === 'GET') {
    const users = await prisma.user.findMany();
    res.status(200).json(users);
  } else if (req.method === 'POST') {
    const { email, name } = req.body;
    const user = await prisma.user.create({
      data: { email, name },
    });
    res.status(201).json(user);
  } else {
    res.setHeader('Allow', ['GET', 'POST']);
    res.status(405).end(`Method ${req.method} Not Allowed`);
  }
}

Conclusion

Prisma ORM provides a modern, type-safe approach to database management in Node.js applications. Its combination of intuitive schema definition, powerful query capabilities, and excellent TypeScript support makes it an excellent choice for building scalable applications.

Key takeaways:

  • Always use type-safe queries with Prisma Client
  • Design your schema with proper relations and constraints
  • Implement proper error handling and connection management
  • Use migrations for database schema versioning
  • Optimize queries for production performance
  • Follow best practices for security and scalability

For more advanced features and use cases, refer to the official Prisma documentation.

Read more

Advanced React Hooks: Mastering Modern State Management and Side Effects

Deep dive into React hooks including custom hooks, performance optimization, and advanced patterns for building scalable React applications.

Instagram
Spotify
Steam