4. Supercharge Performance with select and include

A common performance pitfall is over-fetching—retrieving more data from the database than you actually need. By default, Prisma fetches all scalar fields of a model. Masterful Prisma use involves surgical data retrieval.

• Use select to specify exactly which fields you want.
• Use include to load data from related models.

You cannot use both select and include in the same query. Think of it this way: select is for whitelisting fields, including relations, while include is for grabbing all base model fields *plus* specified relations.

// Bad: Over-fetches all user fields, including hashed password
const user = await prisma.user.findUnique({ where: { id: 1 } });

// Good: Only fetches the id, name, and email
const partialUser = await prisma.user.findUnique({
  where: { id: 1 },
  select: { id: true, name: true, email: true },
});

// Great: Fetches specific user fields AND their post titles
const userWithPostTitles = await prisma.user.findUnique({
  where: { id: 1 },
  select: {
    id: true,
    name: true,
    posts: {
      select: {
        title: true,
      }
    }
  }
});

5. Customizing the Client with Extensions

Since Prisma 5, client extensions have been a revelation. They allow you to add custom logic directly to your Prisma Client, making your data access layer more powerful and expressive. You can add computed fields, custom model methods, and even new top-level queries.

Let's create an extension that adds a fullName computed field to our User model and a custom method to find users by their domain.

const prisma = new PrismaClient().$extends({
  result: {
    user: {
      fullName: {
        needs: { firstName: true, lastName: true },
        compute(user) {
          return `${user.firstName} ${user.lastName}`;
        },
      },
    },
  },
  model: {
    user: {
      async findByDomain(domain: string) {
        return prisma.user.findMany({ where: { email: { endsWith: domain } } });
      },
    },
  },
});

// Now you can do this!
const user = await prisma.user.findFirst();
console.log(user.fullName); // e.g., "Ada Lovelace"

const domainUsers = await prisma.user.findByDomain('@example.com');

6. Advanced Filtering and Full-Text Search

Your filtering logic can go far beyond simple equality checks. Prisma's where clause is a powerhouse of operators.

• Case-insensitivity: Set mode: 'insensitive' for case-less matching (on supported databases like PostgreSQL).
• Pattern matching: Use contains, startsWith, and endsWith.
• List filtering: Use in and notIn to check against an array of values.

For true full-text search, Prisma leverages the database's native capabilities. For PostgreSQL, you can use the search operator:

// Find posts that mention "prisma" or "graphql" in a case-insensitive way
const posts = await prisma.post.findMany({
  where: {
    title: {
      contains: 'prisma',
      mode: 'insensitive',
    },
    OR: [
      { content: { contains: 'graphql' } },
      { published: true },
    ],
  },
});

// Full-text search example for PostgreSQL
const searchResults = await prisma.post.findMany({
  where: {
    _search: 'optimistic & UI',
  },
});

7. Taming Concurrency with $runCommandRaw

This is an advanced technique for when you need to run database-specific commands that don't map to a standard CRUD operation. It's particularly useful for tasks like optimistic concurrency control or complex aggregations in document databases like MongoDB.

For example, using MongoDB's findAndModify command to atomically find and update a document, ensuring another process doesn't interfere:

// Example for MongoDB
const result = await prisma.$runCommandRaw({
  findAndModify: 'Product',
  query: { _id: { $oid: 'product-id' }, version: 1 },
  update: { $inc: { stock: -1 }, $set: { version: 2 } },
  new: true, // Return the updated document
});

if (!result.value) {
  throw new Error('Concurrency conflict: Product was modified by another process.');
}

8. Efficient Pagination: Cursor vs. Offset

As your data grows, you'll need to paginate results. While offset pagination (skip/take) is simple, it becomes slow and unreliable with large, frequently changing datasets. Cursor-based pagination is the mastery-level solution.

It uses a unique, ordered column (like an id or createdAt) to bookmark your position in the dataset, making it highly performant and stable.

// Get the first page
const firstPage = await prisma.post.findMany({
  take: 10,
  orderBy: { id: 'asc' },
});

// Get the last post's ID from the first page
const lastPostId = firstPage[9].id;

// Get the second page using the cursor
const secondPage = await prisma.post.findMany({
  take: 10,
  skip: 1, // Skip the cursor itself
  cursor: {
    id: lastPostId,
  },
  orderBy: { id: 'asc' },
});

9. Leveraging Prisma Accelerate & Pulse for Real-Time Apps

Looking towards 2025, building modern, scalable applications means thinking about global performance and real-time capabilities. Prisma's Data Platform offers two powerful tools for this:

• Prisma Accelerate: A global database connection pool and cache. It dramatically speeds up your database queries by caching results at the edge, close to your users, and eliminates the headache of managing connection limits in serverless environments.
• Prisma Pulse: A service that allows you to subscribe to real-time database change events. Instead of polling your database for changes, Pulse pushes them to your application. It's the perfect foundation for building features like live notifications, real-time dashboards, and collaborative apps.

Adopting these services is a sign of a forward-thinking developer who understands that application performance is not just about efficient queries, but also about infrastructure.

10. The Art of Graceful Schema Migrations

In development, prisma migrate dev is your best friend. In production, it can be your worst enemy if used carelessly. Mastery of migrations means understanding how to evolve your schema without causing downtime or data loss.

The Production Workflow

1. Generate a migration file: Use prisma migrate dev --create-only to generate the SQL file without applying it.
2. Review the SQL: Manually inspect the generated SQL. Is it a non-blocking change? For example, adding a nullable column is safe. Renaming a column is a multi-step, breaking change that requires a more careful rollout strategy (e.g., add new column, backfill data, switch application code, drop old column).
3. Deploy the migration: Use prisma migrate deploy in your CI/CD pipeline. This command simply applies all pending migration files and will never prompt for input, making it safe for production environments.

For complex data transformations, write a separate script using the Prisma Client to run *after* the schema migration. This separates schema changes from data changes, making the process more robust.