Prisma ORM: Modern Database Access for TypeScript

Prisma is a modern ORM that makes working with databases easy for TypeScript and Node.js developers. This comprehensive guide covers everything you need to know about Prisma.

What is Prisma?

Prisma is a next-generation ORM that provides type-safe database access with a great developer experience.

// Define schema
model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  name  String?
  posts Post[]
}

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

// Type-safe queries
const users = await prisma.user.findMany({
  where: { email: { contains: 'test' } },
  include: { posts: true }
});

Key Features

Type-safe - Full TypeScript support
- Intuitive - Clean, readable API
- Migrations - Version-controlled schema
- Relations - Easy relational queries
- Multi-database - PostgreSQL, MySQL, SQLite
- Studio - Visual database browser

Installation

Setup

# Initialize Prisma
npx prisma init

# Install Prisma Client
npm install @prisma/client

# Install dev dependency for CLI
npm install prisma --save-dev

Basic Configuration

// prisma/schema.prisma

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

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

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

Schema Definition

Models

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

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

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

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

Field Types

model Types {
  // String
  name     String
  
  // Integer
  count    Int
  
  // Float
  price    Float
  
  // Boolean
  active   Boolean
  
  // DateTime
  created  DateTime @default(now())
  
  // UUID (with extension)
  id       String   @id @default(uuid())
  
  // Json
  metadata Json?
  
  // Bytes
  data     Bytes?
}

Attributes

model Example {
  // Primary key
  id        Int       @id
  uuid      String    @id @default(uuid())
  
  // Unique
  email     String    @unique
  composite String    @unique
  
  // Index
  title     String    @index
  // Composite index
  @@index([field1, field2])
  
  // Default values
  active    Boolean   @default(true)
  count     Int       @default(0)
  
  // Optional
  name      String?
  
  // Required
  title     String
  
  // Timestamp
  created   DateTime  @default(now())
  updated   DateTime @updatedAt
}

Migrations

Creating Migrations

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

# Apply migrations (production)
npx prisma migrate deploy

# Reset database
npx prisma migrate reset

# Create SQL without applying
npx prisma migrate diff \
  --from-empty \
  --to-schema-datamodel prisma/schema.prisma \
  --script > migration.sql

Migration History

# View migration status
npx prisma migrate status

# Verify migration
npx prisma migrate verify

CRUD Operations

Create

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

const prisma = new PrismaClient();

// Create single record
const user = await prisma.user.create({
  data: {
    email: '[email protected]',
    name: 'John Doe'
  }
});

// Create with relations
const post = await prisma.post.create({
  data: {
    title: 'Hello World',
    content: 'My first post',
    author: {
      connect: { email: '[email protected]' }
    }
  }
});

// Create multiple
const users = await prisma.user.createMany({
  data: [
    { email: '[email protected]', name: 'John' },
    { email: '[email protected]', name: 'Jane' }
  ]
});

Read

// Find one
const user = await prisma.user.findUnique({
  where: { email: '[email protected]' }
});

// Find by ID
const user = await prisma.user.findUnique({
  where: { id: 1 }
});

// Find many
const users = await prisma.user.findMany();

// With pagination
const users = await prisma.user.findMany({
  skip: 0,
  take: 10
});

// Where clause
const users = await prisma.user.findMany({
  where: {
    name: { not: null },
    email: { contains: '@example.com' }
  },
  orderBy: { createdAt: 'desc' }
});

Update

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

// Update many
await prisma.user.updateMany({
  where: { role: 'guest' },
  data: { role: 'member' }
});

// Upsert
const user = await prisma.user.upsert({
  where: { email: '[email protected]' },
  update: { name: 'Updated' },
  create: { email: '[email protected]', name: 'New User' }
});

Delete

// Delete single
await prisma.user.delete({
  where: { id: 1 }
});

// Delete many
await prisma.user.deleteMany({
  where: { email: { contains: 'test' } }
});

// Delete all
await prisma.user.deleteMany();

Relations

Include Relations

// Include single relation
const user = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    posts: true,
    profile: true
  }
});

// Include nested relations
const posts = await prisma.post.findMany({
  include: {
    author: {
      include: { profile: true }
    },
    categories: true
  }
});

Filter by Relation

// Filter users with posts
const users = await prisma.user.findMany({
  where: {
    posts: {
      some: {
        published: true
      }
    }
  }
});

// Filter posts with specific author
const posts = await prisma.post.findMany({
  where: {
    author: {
      email: '[email protected]'
    }
  }
});

Nested Writes

// Create post with author
const post = await prisma.post.create({
  data: {
    title: 'New Post',
    author: {
      create: {
        email: '[email protected]',
        name: 'New Author'
      }
    }
  }
});

// Connect existing user to post
await prisma.post.update({
  where: { id: 1 },
  data: {
    author: { connect: { id: 2 } }
  }
});

// Disconnect relation
await prisma.post.update({
  where: { id: 1 },
  data: {
    author: { disconnect: true }
  }
});

Queries

Filtering

// Operators
const users = await prisma.user.findMany({
  where: {
    // Equality
    name: 'John',
    
    // Not
    name: { not: 'John' },
    
    // In
    id: { in: [1, 2, 3] },
    
    // Contains
    email: { contains: '@example.com' },
    
    // Starts with
    name: { startsWith: 'J' },
    
    // Ends with
    name: { endsWith: 'n' },
    
    // Less than / greater than
    age: { lt: 30, gt: 18 },
    
    // Null checks
    name: { not: null }
  }
});

Aggregation

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

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

// Group by
const groups = await prisma.user.groupBy({
  by: ['role'],
  _count: { id: true }
});

Raw SQL

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

// Raw mutation
await prisma.$executeRaw`
  UPDATE users SET role = 'admin' 
  WHERE id = ${userId}
`;

// Raw query with parameters
const users = await prisma.$queryRawUnsafe(
  'SELECT * FROM users WHERE name = $1',
  name
);

TypeScript Integration

Generated Types

// Prisma generates full TypeScript types
type User = {
  id: number;
  email: string;
  name: string | null;
  createdAt: Date;
  updatedAt: Date;
};

// Include types are auto-generated
type UserWithPosts = Prisma.UserGetPayload<{
  include: { posts: true }
}>;

Type-Safe Queries

// Full autocomplete support
const users = await prisma.user.findMany({
  where: {
    // IDE autocomplete works here
    email: { contains: '' }
  }
});

Transactions

// Sequential transactions
const [user, post] = await prisma.$transaction([
  prisma.user.create({ data: { email: '[email protected]' } }),
  prisma.post.create({ data: { title: 'Post' } })
]);

// Interactive transaction
const result = await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({
    data: { email: '[email protected]' }
  });
  
  await tx.post.create({
    data: {
      title: 'Post',
      authorId: user.id
    }
  });
  
  return user;
});

Prisma Studio

# Open visual database browser
npx prisma studio

Advanced

Middleware

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

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

// Custom middleware
prisma.$use(async (params, next) => {
  const before = Date.now();
  const result = await next(params);
  const after = Date.now();
  
  console.log(`Query ${params.model}.${params.action} took ${after - before}ms`);
  
  return result;
});

Extensions

const prisma = new PrismaClient().$extends(
  prisma => ({
    model: {
      user: {
        async findAdmin() {
          return prisma.user.findMany({
            where: { role: 'admin' }
          });
        }
      }
    }
  })
);

External Resources

Conclusion

Prisma provides an excellent developer experience for database access. Key points:

Define schema in Prisma schema file
Generate type-safe Prisma Client
Use intuitive CRUD API
Handle relations with include/connect
Use migrations for schema changes
Leverage Prisma Studio for visual debugging

For TypeScript/Node.js projects, Prisma offers a modern, type-safe alternative to traditional ORMs.