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.

📖 Want the complete guide with more examples and advanced patterns? Check out the full article on my blog for an in-depth tutorial with additional code examples, troubleshooting tips, and real-world use cases.

What is Prisma ORM?

Prisma is a next-generation ORM (Object-Relational Mapping) tool for Node.js and TypeScript. It provides:

• Type-safe database client - Full TypeScript support with autocomplete
• Automatic migrations - Schema changes are tracked and applied automatically
• Intuitive query API - Simple, chainable methods for all operations
• Multi-database support - PostgreSQL, MySQL, SQLite, SQL Server, and MongoDB
• Relationship handling - One-to-one, one-to-many, and many-to-many relationships
• Raw SQL support - Run raw queries when needed with SQL injection protection

Installation and Setup

First, let's install Prisma:

npm install prisma @prisma/client --save-dev
npm install @prisma/adapter-pg pg  # For PostgreSQL

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: '[e-mail]',
    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: '[e-mail]',
      age: 25,
    },
    {
      name: 'Jim Doe',
      email: '[e-mail]',
      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: '[e-mail]',
    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: '[e-mail]'
  }
});

// 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: '[e-mail]'
  },
  data: {
    email: '[e-mail]',
    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: '[e-mail]'
  },
  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: '[e-mail]'
  },
  data: {
    userPreference: {
      connect: {
        id: "preference-id-here"
      }
    }
  }
});

// Disconnect relationship
const user = await prisma.user.update({
  where: {
    email: '[e-mail]'
  },
  data: {
    userPreference: {
      disconnect: true
    }
  }
});

// Connect or create (upsert pattern)
const user = await prisma.user.update({
  where: {
    email: '[e-mail]'
  },
  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: '[e-mail]'
  }
});

// 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

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: '[e-mail]',
      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

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

Resources and Further Reading

• 📚 Full Prisma ORM Guide - Complete tutorial with advanced examples, troubleshooting, and best practices
• Sequelize ORM with MySQL Setup - Alternative ORM guide for comparison
• Prisma Documentation - Official Prisma documentation
• Prisma Client API Reference - Complete API reference
• Prisma Migrate Guide - Migration best practices
• Express.js REST API Setup - Learn how to integrate Prisma with Express.js
• TypeScript with React Best Practices - TypeScript patterns that work great with Prisma

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.

Key Takeaways:

• Prisma provides type-safe database operations with full TypeScript support
• Schema-first approach makes database design clear and maintainable
• Automatic migrations track and apply schema changes safely
• Intuitive query API simplifies complex database operations
• Relationship handling supports all common relationship patterns
• Raw SQL support when you need it, with SQL injection protection
• Transaction support for operations that must succeed or fail together

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.

---

What's your experience with Prisma? Share your tips and tricks in the comments below! 🚀

---

💡 Looking for more details? This is a condensed version of my comprehensive guide. Read the full article on my blog for additional examples, advanced patterns, troubleshooting tips, and more in-depth explanations.

If you found this guide helpful, consider checking out my other articles on Node.js development and database best practices.