←Back to Blog
Node.js•25 min read

Prisma ORM Complete Guide: Database Operations with TypeScript

Prisma changed how I think about database operations. Instead of writing raw SQL or dealing with complex ORM abstractions, Prisma gives you a type-safe, intuitive API that feels natural. It's like having autocomplete for your entire database schema, and TypeScript catches errors before they reach production.

I've worked with a lot of database tools over the years—raw SQL, Sequelize, TypeORM, you name it. Each had its strengths, but they all felt like they were fighting against me in some way. Then I discovered Prisma, and everything clicked. The type safety, the intuitive API, the automatic migrations—it all just makes sense.

Prisma is a next-generation ORM that gives you a type-safe database client. You define your schema in a simple, declarative format, and Prisma generates a fully-typed client for you. No more guessing what fields exist, no more runtime errors from typos, no more manual migration management. It's database work the way it should be.

In this guide, I'll walk you through everything you need to know about Prisma. We'll cover schema definition, creating records, querying data, updating and deleting, working with relationships, and some advanced patterns. All with real examples from a working application.

Installation and Setup

First, let's install Prisma:

npm install prisma @prisma/client --save-dev
npm install @prisma/adapter-pg pg

Initialize Prisma in your project:

npx prisma init

This creates a `prisma` directory with a `schema.prisma` file and adds a `.env` file for your database connection string.

Defining Your Schema

The schema file is where you define your database structure. Here's a complete example with all relationship types:

// prisma/schema.prisma

generator client {
  provider = "prisma-client"
  output   = "../generated/prisma"
}

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

// User Model
model User {
  id               String          @id @default(uuid())
  age              Int
  name             String
  email            String          @unique
  role             Role            @default(BASIC)
  
  // One-to-Many: User has many Posts
  writtenPosts     Post[]          @relation("WrittenPosts")
  
  // One-to-Many: User can favorite many Posts
  favoritedPosts   Post[]          @relation("favoritedPosts")
  
  // One-to-One: User has one UserPreference
  userPreference   UserPreference? @relation(fields: [userPreferenceId], references: [id])
  userPreferenceId String?         @unique

  @@unique([age, name])  // Composite unique constraint
  @@index([email])       // Index on email
}

// One-to-One Relationship
model UserPreference {
  id           String  @id @default(uuid())
  emailUpdates Boolean
  user         User?   // One-to-one relationship with User
}

// One-to-Many Relationship
model Post {
  id            String   @id @default(uuid())
  title         String
  averageRating Float
  createdAt     DateTime @default(now())
  updatedAt     DateTime @updatedAt

  // Post belongs to User (author)
  author   User   @relation("WrittenPosts", fields: [authorId], references: [id])
  authorId String

  // Post can be favorited by one User
  favoritedBy   User?   @relation("favoritedPosts", fields: [favoritedById], references: [id])
  favoritedById String?

  // Many-to-Many: Post has many Categories
  categories Category[]
}

// Many-to-Many Relationship
model Category {
  id    String @id @default(uuid())
  name  String @unique
  posts Post[]  // Many-to-many relationship with Post
}

enum Role {
  BASIC
  ADMIN
  USER
}

After defining your schema, generate the Prisma Client and run migrations:

npx prisma generate
npx prisma migrate dev --name init

Setting Up Prisma Client

Create a singleton instance of Prisma Client to avoid multiple connections in development:

// lib/prisma.ts
import "dotenv/config";
import { PrismaPg } from '@prisma/adapter-pg';
import { PrismaClient } from '../generated/prisma/client';

const connectionString = process.env.NEON_POSTGRES_DATABASE_URL!;

// Prevent multiple instances in development
const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined;
};

// Use adapter for PostgreSQL connection pooling
const adapter = new PrismaPg({ connectionString });
const prisma = globalForPrisma.prisma ?? new PrismaClient({ adapter });

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

export { prisma };

This pattern ensures you only create one Prisma Client instance, which is important in development where hot reloading can create multiple instances.

Creating Records

Creating records with Prisma is straightforward. Here are examples for different scenarios:

Creating a Single Record

import { prisma } from './lib/prisma';

// Create a user with nested UserPreference
const user = await prisma.user.create({
  data: {
    name: 'John Doe',
    email: 'john.doe@example.com',
    age: 25,
    userPreference: {
      create: {
        emailUpdates: true
      }
    }
  },
  // Select specific fields
  select: {
    name: true,
    email: true,
    userPreference: {
      select: {
        id: true,
        emailUpdates: true
      }
    }
  }
});

console.log('User created:', user);

Creating Multiple Records

// Create multiple users at once
const users = await prisma.user.createMany({
  data: [
    {
      name: 'John Doe',
      email: 'john.doe@example.com',
      age: 25,
    },
    {
      name: 'Jim Doe',
      email: 'jim.doe@example.com',
      age: 27,
    }
  ]
});

console.log('Users created:', users);

Creating Records with Relationships

// Create user with posts and categories
const user = await prisma.user.create({
  data: {
    age: 25,
    name: 'Test User',
    email: 'test.user@example.com',
    writtenPosts: {
      create: {
        title: 'My First Post',
        averageRating: 5.0,
        categories: {
          create: {
            name: 'Technology'
          }
        }
      }
    },
    userPreference: {
      create: {
        emailUpdates: true
      }
    }
  }
});

console.log('User with posts created:', user);

Connecting Existing Records

// Create post and connect to existing author
const post = await prisma.post.create({
  data: {
    title: 'New Post',
    averageRating: 4.5,
    author: {
      connect: {
        id: "user-id-here"
      }
    },
    favoritedBy: {
      connect: {
        id: "another-user-id"
      }
    }
  }
});

// Or use the ID directly
const post2 = await prisma.post.create({
  data: {
    title: 'Another Post',
    averageRating: 4.8,
    authorId: "user-id-here"
  }
});

Querying Data

Prisma provides several methods for querying data. Here are the most common patterns:

Find Unique Record

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

// Find by composite unique constraint
const user2 = await prisma.user.findUnique({
  where: {
    age_name: {
      age: 25,
      name: 'John Doe'
    }
  }
});

Find First Record

const user = await prisma.user.findFirst({
  where: {
    name: 'Sam Smith'
  },
  orderBy: {
    age: 'asc'
  }
});

Find Many Records

// Basic findMany
const users = await prisma.user.findMany({
  where: {
    name: "Sam Smith"
  }
});

// With pagination and ordering
const users = await prisma.user.findMany({
  where: {
    age: {
      gte: 25  // Greater than or equal
    }
  },
  skip: 0,      // Offset
  take: 5,      // Limit
  orderBy: {
    name: "desc"  // asc or desc
  }
});

// Get distinct values
const users = await prisma.user.findMany({
  where: {
    name: "Sam Smith"
  },
  distinct: ["name"]
});

Filtering with Operators

// Equals and not equals
const users = await prisma.user.findMany({
  where: {
    name: {
      equals: "Sam Smith",
      not: "John Doe"
    }
  }
});

// In and notIn operators
const users = await prisma.user.findMany({
  where: {
    name: {
      in: ["Sam Smith", "Jim Doe"],
      notIn: ["John Doe", "Jane Doe"]
    }
  }
});

// String operators
const users = await prisma.user.findMany({
  where: {
    email: {
      startsWith: "sam",
      endsWith: "@example.com",
      contains: "@example.com"
    }
  }
});

// Comparison operators
const users = await prisma.user.findMany({
  where: {
    age: {
      gt: 25,   // Greater than
      gte: 25,  // Greater than or equal
      lt: 30,   // Less than
      lte: 30   // Less than or equal
    }
  }
});

AND, OR, and NOT Operators

// AND operator
const users = await prisma.user.findMany({
  where: {
    AND: [
      {
        name: "Sam Smith"
      },
      {
        email: {
          contains: "@example.com"
        }
      }
    ]
  }
});

// OR operator
const users = await prisma.user.findMany({
  where: {
    OR: [
      {
        name: "Sam Smith"
      },
      {
        email: {
          contains: "@example.com"
        }
      }
    ]
  }
});

// NOT operator
const users = await prisma.user.findMany({
  where: {
    NOT: {
      email: {
        startsWith: "sam"
      }
    }
  }
});

Including Relations

// Include related data
const categories = await prisma.category.findMany({
  include: {
    posts: true
  }
});

// Nested includes
const categories = await prisma.category.findMany({
  include: {
    posts: {
      include: {
        author: {
          include: {
            userPreference: true
          }
        },
        favoritedBy: {
          include: {
            userPreference: true
          }
        }
      }
    }
  }
});

Filtering by Relations

// Find users with specific user preference
const users = await prisma.user.findMany({
  where: {
    userPreference: {
      emailUpdates: true
    }
  },
  include: {
    userPreference: true
  }
});

// Find users with posts matching criteria
const users = await prisma.user.findMany({
  where: {
    writtenPosts: {
      some: {
        title: {
          startsWith: "Post"
        }
      }
    }
  },
  include: {
    writtenPosts: true
  }
});

// Find users where ALL posts match criteria
const users = await prisma.user.findMany({
  where: {
    writtenPosts: {
      every: {
        title: "Post 1"
      }
    }
  }
});

// Find users with NO posts matching criteria
const users = await prisma.user.findMany({
  where: {
    writtenPosts: {
      none: {
        title: {
          startsWith: "Post"
        }
      }
    }
  }
});

// Find posts by author's age
const posts = await prisma.post.findMany({
  where: {
    author: {
      is: {
        age: 27
      }
    }
  },
  include: {
    author: true
  }
});

Updating Records

Updating records is just as intuitive as creating them:

Update Single Record

const user = await prisma.user.update({
  where: {
    email: 'john.doe@example.com'
  },
  data: {
    email: 'john.doe.updated@example.com',
    age: 26,
    name: 'John Doe Updated'
  }
});

console.log('User updated:', user);

Update Many Records

const users = await prisma.user.updateMany({
  where: {
    name: 'Sam Smith'
  },
  data: {
    name: 'Sam Smith Updated'
  }
});

console.log('Users updated:', users);

Numeric Operations

// Increment, decrement, multiply, divide
const user = await prisma.user.update({
  where: {
    email: 'jim.doe@example.com'
  },
  data: {
    age: {
      increment: 10,  // Add 10
      // decrement: 5,  // Subtract 5
      // multiply: 2,   // Multiply by 2
      // divide: 2      // Divide by 2
    }
  }
});

Updating Relationships

// Connect existing relationship
const user = await prisma.user.update({
  where: {
    email: 'sam.smith01@example.com'
  },
  data: {
    userPreference: {
      connect: {
        id: "preference-id-here"
      }
    }
  }
});

// Disconnect relationship
const user = await prisma.user.update({
  where: {
    email: 'sam.smith01@example.com'
  },
  data: {
    userPreference: {
      disconnect: true
    }
  }
});

// Connect or create (upsert pattern)
const user = await prisma.user.update({
  where: {
    email: 'sam.smith01@example.com'
  },
  data: {
    userPreference: {
      connectOrCreate: {
        where: {
          id: "preference-id"
        },
        create: {
          emailUpdates: true
        }
      }
    }
  }
});

Deleting Records

Deleting records follows the same pattern as other operations:

// Delete single record
const deletedUser = await prisma.user.delete({
  where: {
    email: 'sam.smith02@example.com'
  }
});

// Delete many records
const deletedUsers = await prisma.user.deleteMany({
  where: {
    age: {
      gt: 26
    }
  }
});

// Delete all records (use with caution!)
const deletedPosts = await prisma.post.deleteMany({});
const deletedUsers = await prisma.user.deleteMany({});
const deletedUserPreferences = await prisma.userPreference.deleteMany({});

Raw SQL Queries

Sometimes you need to run raw SQL. Prisma provides safe ways to do this:

// Method 1: $queryRaw with template literals (SAFE - prevents SQL injection)
const users = await prisma.$queryRaw`SELECT * FROM "User"`;

// Method 2: $queryRaw with parameters (SAFE)
const minAge = 25;
const users = await prisma.$queryRaw`
  SELECT * FROM "User" 
  WHERE age > ${minAge}
`;

// Method 3: Complex query with LIKE
const searchName = 'Sam';
const pattern = `%${searchName}%`;
const users = await prisma.$queryRaw`
  SELECT * FROM "User" 
  WHERE name LIKE ${pattern}
  ORDER BY age DESC
`;

// Method 4: JOIN query
const postsWithAllData = await prisma.$queryRaw`
  SELECT 
    p.id AS post_id,
    p.title AS post_title,
    p."averageRating" AS post_rating,
    author.id AS author_id,
    author.name AS author_name,
    author.email AS author_email,
    c.id AS category_id,
    c.name AS category_name
  FROM "Post" p
  INNER JOIN "User" author ON p."authorId" = author.id
  LEFT JOIN "_CategoryToPost" ctp ON ctp."B" = p.id
  LEFT JOIN "Category" c ON c.id = ctp."A"
  ORDER BY p."createdAt" DESC
`;

// WARNING: $queryRawUnsafe is vulnerable to SQL injection!
// Only use with trusted input
// const users = await prisma.$queryRawUnsafe('SELECT * FROM "User"');

Transactions

Prisma supports transactions for operations that need to succeed or fail together:

// Sequential operations (one after another)
const result = await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({
    data: {
      name: 'John Doe',
      email: 'john@example.com',
      age: 25
    }
  });

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

  return { user, post };
});

// Interactive transactions (with timeout)
const result = await prisma.$transaction(
  async (tx) => {
    // Your operations here
    const user = await tx.user.create({ data: {...} });
    const post = await tx.post.create({ data: {...} });
    return { user, post };
  },
  {
    maxWait: 5000,  // Maximum time to wait for a transaction slot
    timeout: 10000 // Maximum time the transaction can run
  }
);

Best Practices

  • Always use a singleton pattern for Prisma Client to avoid connection issues
  • Use `select` instead of `include` when you only need specific fields for better performance
  • Use indexes on frequently queried fields (defined in schema with @@index)
  • Use transactions for operations that must succeed or fail together
  • Always use `$queryRaw` with template literals, never `$queryRawUnsafe` with user input
  • Use `findUnique` for unique fields, `findFirst` for non-unique queries
  • Leverage TypeScript types generated by Prisma for type safety
  • Use migrations for all schema changes—never modify the database directly
  • Disconnect Prisma Client when shutting down your application
  • Use connection pooling adapters in production for better performance

Conclusion

Prisma has become my go-to tool for database work in Node.js and TypeScript projects. The type safety alone saves me hours of debugging, and the intuitive API makes complex queries feel simple. Whether you're building a simple CRUD app or a complex system with multiple relationships, Prisma handles it all elegantly.

The examples in this guide cover everything from basic operations to advanced patterns. Start with simple creates and queries, then gradually explore relationships and more complex operations. Once you get comfortable with Prisma, you'll wonder how you ever worked without it.

Related Articles

Sequelize ORM with MySQL Setup: Complete Guide for Node.js

Complete guide with connection pooling, migrations, and best practices for database setup.

Sequelize Associations and Relationships: Complete Guide

Learn how to define and use Sequelize associations (hasMany, belongsTo, hasOne) in Node.js.

Express.js REST API Setup: Complete Guide with Error Handling

Learn how to set up a production-ready Express.js REST API with CORS and error handling.

TypeScript with React: Best Practices and Patterns

Learn TypeScript best practices for React development with type definitions and interfaces.

View All Blog Posts→