Part 4: CRUD Operations and Prisma Client Fundamentals

Introduction

Now that we have our schema designed and migrations in place, it's time to interact with our database. In this part, I'll walk you through the CRUD (Create, Read, Update, Delete) operations using Prisma Client—the type-safe database client that Prisma generates from your schema.

I remember the satisfaction when I first replaced hundreds of lines of raw SQL queries with clean, type-safe Prisma operations in a microservices project. Not only did my code become more readable, but TypeScript caught errors before they reached production.

Understanding Prisma Client

What is Prisma Client?

Prisma Client is an auto-generated, type-safe database client. Every time you run npx prisma migrate dev or npx prisma generate, Prisma reads your schema and generates TypeScript types and methods.

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

const prisma = new PrismaClient();

// TypeScript knows exactly what fields exist
const user = await prisma.user.findUnique({
  where: { email: '[email protected]' }
});

// ✅ TypeScript autocomplete works
console.log(user?.name);

// ❌ TypeScript error - 'emial' doesn't exist
console.log(user?.emial);  // Error: Property 'emial' does not exist

Setting Up Prisma Client

Here's how I structure Prisma Client in my applications:

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

// Prevent multiple instances in development (hot reload)
declare global {
  var prisma: PrismaClient | undefined;
}

export const prisma = global.prisma || new PrismaClient({
  log: process.env.NODE_ENV === 'development' 
    ? ['query', 'error', 'warn'] 
    : ['error'],
});

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

Now import it anywhere:

// src/index.ts
import { prisma } from './lib/prisma';

async function main() {
  const users = await prisma.user.findMany();
  console.log(users);
}

Create Operations

Creating a Single Record

Basic create operation:

const user = await prisma.user.create({
  data: {
    email: '[email protected]',
    name: 'Hassan',
    password: 'hashed_password',  // Use bcrypt in production
  }
});

console.log('Created user:', user);
// Output includes auto-generated id, createdAt, etc.

Creating with Related Data

In a blog application I built, I needed to create users with their profile:

const userWithProfile = await prisma.user.create({
  data: {
    email: '[email protected]',
    name: 'Hassan',
    password: 'hashed_password',
    // Create related profile
    profile: {
      create: {
        bio: 'Backend developer passionate about TypeScript and databases',
        avatar: 'https://example.com/avatar.jpg',
        website: 'https://hassan.dev'
      }
    }
  },
  // Include profile in response
  include: {
    profile: true
  }
});

console.log(userWithProfile);
// Returns user with profile nested

Creating Multiple Records

Bulk insert when seeding database:

const users = await prisma.user.createMany({
  data: [
    { email: '[email protected]', name: 'User One', password: 'hash1' },
    { email: '[email protected]', name: 'User Two', password: 'hash2' },
    { email: '[email protected]', name: 'User Three', password: 'hash3' },
  ],
  skipDuplicates: true, // Skip records if email already exists
});

console.log(`Created ${users.count} users`);

Note: createMany doesn't return created records, only count.

Creating with Many-to-Many Relationships

Creating a post with tags:

const post = await prisma.post.create({
  data: {
    title: 'Understanding Prisma ORM',
    slug: 'understanding-prisma-orm',
    content: 'Prisma makes database interactions type-safe...',
    published: true,
    authorId: 1,
    // Connect to existing tags
    tags: {
      connect: [
        { id: 1 },  // Connect existing tag with id 1
        { id: 2 },  // Connect existing tag with id 2
      ]
    }
  },
  include: {
    tags: true,
    author: true
  }
});

Or create tags if they don't exist:

const post = await prisma.post.create({
  data: {
    title: 'Understanding Prisma ORM',
    slug: 'understanding-prisma-orm',
    content: 'Prisma makes database interactions type-safe...',
    published: true,
    authorId: 1,
    tags: {
      connectOrCreate: [
        {
          where: { slug: 'typescript' },
          create: { name: 'TypeScript', slug: 'typescript' }
        },
        {
          where: { slug: 'database' },
          create: { name: 'Database', slug: 'database' }
        }
      ]
    }
  }
});

Read Operations

Finding a Single Record

Most common: finding by unique field:

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

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

// Returns null if not found
if (!user) {
  throw new Error('User not found');
}

Finding First Match

Find first record matching condition:

const publishedPost = await prisma.post.findFirst({
  where: { 
    published: true 
  },
  orderBy: {
    createdAt: 'desc'  // Most recent first
  }
});

Finding Multiple Records

// Get all users
const users = await prisma.user.findMany();

// Get users with filters
const adminUsers = await prisma.user.findMany({
  where: {
    role: 'ADMIN'
  }
});

// Get users created in last 7 days
const recentUsers = await prisma.user.findMany({
  where: {
    createdAt: {
      gte: new Date(Date.now() - 7 * 24 * 60 * 60 * 1000)
    }
  }
});

Filtering with Complex Conditions

Real-world example from a blog I built:

const posts = await prisma.post.findMany({
  where: {
    AND: [
      { published: true },
      { 
        OR: [
          { title: { contains: 'TypeScript', mode: 'insensitive' } },
          { content: { contains: 'TypeScript', mode: 'insensitive' } }
        ]
      },
      {
        createdAt: {
          gte: new Date('2024-01-01')
        }
      }
    ]
  },
  orderBy: {
    viewCount: 'desc'
  },
  take: 10  // Limit to 10 results
});

Selecting Specific Fields

Reduce data transfer by selecting only needed fields:

const users = await prisma.user.findMany({
  select: {
    id: true,
    email: true,
    name: true,
    // Exclude password and other sensitive fields
  }
});

// Result: [{ id: 1, email: '...', name: '...' }]

Including Related Data

One of Prisma's best features—eager loading relationships:

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

// Nested includes
const post = await prisma.post.findUnique({
  where: { id: 1 },
  include: {
    author: {
      include: {
        profile: true  // Include author's profile
      }
    },
    tags: true,
    comments: {
      include: {
        author: true   // Include comment authors
      }
    }
  }
});

Filtering Related Records

In a comments system I built, I needed posts with only approved comments:

const postsWithApprovedComments = await prisma.post.findMany({
  include: {
    comments: {
      where: {
        approved: true
      },
      orderBy: {
        createdAt: 'desc'
      },
      take: 5  // Latest 5 comments per post
    },
    _count: {
      select: {
        comments: true  // Total comment count
      }
    }
  }
});

Pagination

Two approaches I use:

Offset-based pagination:

const page = 2;
const pageSize = 20;

const posts = await prisma.post.findMany({
  skip: (page - 1) * pageSize,
  take: pageSize,
  where: { published: true },
  orderBy: { createdAt: 'desc' }
});

// Get total count for pagination UI
const totalPosts = await prisma.post.count({
  where: { published: true }
});

const totalPages = Math.ceil(totalPosts / pageSize);

Cursor-based pagination (better for large datasets):

const posts = await prisma.post.findMany({
  take: 20,
  skip: 1,  // Skip the cursor itself
  cursor: {
    id: lastPostId  // ID of last post from previous page
  },
  where: { published: true },
  orderBy: { id: 'asc' }
});

Update Operations

Updating a Single Record

const updatedUser = await prisma.user.update({
  where: { id: 1 },
  data: {
    name: 'Hassan Tunn',
    updatedAt: new Date()  // Usually automatic with @updatedAt
  }
});

Updating with Increments

Useful for view counts, likes, etc.:

const post = await prisma.post.update({
  where: { id: 1 },
  data: {
    viewCount: {
      increment: 1  // Atomic increment
    }
  }
});

Updating Related Records

const user = await prisma.user.update({
  where: { id: 1 },
  data: {
    profile: {
      update: {
        bio: 'Updated bio',
        website: 'https://newsite.com'
      }
    }
  },
  include: {
    profile: true
  }
});

Updating or Creating (Upsert)

Extremely useful pattern I use constantly:

const user = await prisma.user.upsert({
  where: { email: '[email protected]' },
  update: {
    name: 'Hassan Tunn',
    updatedAt: new Date()
  },
  create: {
    email: '[email protected]',
    name: 'Hassan Tunn',
    password: 'hashed_password'
  }
});

Updating Multiple Records

// Publish all posts by a specific author
const result = await prisma.post.updateMany({
  where: {
    authorId: 1,
    published: false
  },
  data: {
    published: true,
    publishedAt: new Date()
  }
});

console.log(`Published ${result.count} posts`);

Delete Operations

Deleting a Single Record

const deletedUser = await prisma.user.delete({
  where: { id: 1 }
});

// Throws error if user doesn't exist

Safe Delete (Check if Exists)

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

if (user) {
  await prisma.user.delete({
    where: { id: 1 }
  });
}

Deleting Multiple Records

const result = await prisma.post.deleteMany({
  where: {
    published: false,
    createdAt: {
      lt: new Date(Date.now() - 30 * 24 * 60 * 60 * 1000) // Older than 30 days
    }
  }
});

console.log(`Deleted ${result.count} unpublished posts`);

Cascading Deletes

Remember our schema with onDelete: Cascade:

model Post {
  authorId Int
  author   User @relation(fields: [authorId], references: [id], onDelete: Cascade)
}

When we delete a user:

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

// All posts by this user are automatically deleted

Transactions

When multiple operations must succeed or fail together.

Sequential Transactions

const result = await prisma.$transaction(async (tx) => {
  // Create user
  const user = await tx.user.create({
    data: {
      email: '[email protected]',
      name: 'Hassan',
      password: 'hashed'
    }
  });
  
  // Create profile
  const profile = await tx.profile.create({
    data: {
      userId: user.id,
      bio: 'New user'
    }
  });
  
  // Create initial post
  const post = await tx.post.create({
    data: {
      title: 'My First Post',
      content: 'Hello World',
      authorId: user.id
    }
  });
  
  return { user, profile, post };
});

// If any operation fails, all are rolled back

Batch Transactions

const [user, posts, comments] = await prisma.$transaction([
  prisma.user.create({ data: userData }),
  prisma.post.findMany({ where: { published: true } }),
  prisma.comment.deleteMany({ where: { spam: true } })
]);

Real-World Transaction Example

From an e-commerce project—transferring points between users:

async function transferPoints(fromUserId: number, toUserId: number, amount: number) {
  return await prisma.$transaction(async (tx) => {
    // Deduct from sender
    const sender = await tx.user.update({
      where: { id: fromUserId },
      data: {
        points: {
          decrement: amount
        }
      }
    });
    
    // Verify sender has enough points
    if (sender.points < 0) {
      throw new Error('Insufficient points');
    }
    
    // Add to receiver
    await tx.user.update({
      where: { id: toUserId },
      data: {
        points: {
          increment: amount
        }
      }
    });
    
    // Log transaction
    await tx.pointsTransaction.create({
      data: {
        fromUserId,
        toUserId,
        amount,
        timestamp: new Date()
      }
    });
    
    return { success: true };
  });
}

Error Handling

Common Prisma Errors

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

try {
  await prisma.user.create({
    data: {
      email: '[email protected]',
      name: 'Test',
      password: 'hash'
    }
  });
} catch (error) {
  if (error instanceof Prisma.PrismaClientKnownRequestError) {
    // Unique constraint violation
    if (error.code === 'P2002') {
      console.error('User with this email already exists');
    }
    
    // Foreign key constraint violation
    if (error.code === 'P2003') {
      console.error('Referenced record does not exist');
    }
    
    // Record not found
    if (error.code === 'P2025') {
      console.error('Record to update/delete does not exist');
    }
  }
  
  throw error;
}

Handling Not Found

// findUnique returns null if not found
const user = await prisma.user.findUnique({
  where: { id: 999 }
});

if (!user) {
  throw new Error('User not found');
}

// Or use findUniqueOrThrow (throws if not found)
const user = await prisma.user.findUniqueOrThrow({
  where: { id: 999 }
});
// Throws PrismaClientKnownRequestError with code P2025

Performance Tips

Use Select to Limit Data

// ❌ Fetches all fields including large content
const posts = await prisma.post.findMany();

// ✅ Fetch only needed fields
const posts = await prisma.post.findMany({
  select: {
    id: true,
    title: true,
    excerpt: true,  // Use excerpt instead of full content
    createdAt: true
  }
});

Batch Operations

// ❌ Multiple queries
for (const userId of userIds) {
  await prisma.user.delete({ where: { id: userId } });
}

// ✅ Single query
await prisma.user.deleteMany({
  where: {
    id: {
      in: userIds
    }
  }
});

Connection Pooling

// src/lib/prisma.ts
export const prisma = new PrismaClient({
  datasources: {
    db: {
      url: process.env.DATABASE_URL
    }
  },
  // Configure connection pool (defaults are usually fine)
});

What's Next?

We've covered the fundamentals of CRUD operations with Prisma. In Part 5, we'll explore advanced topics:

• Complex filtering and aggregations

• Raw SQL queries when needed

• Full-text search

• Database functions

• Real-time features with Prisma Pulse

• Deployment and production considerations

Conclusion

Prisma Client transforms database operations from error-prone string manipulation into type-safe, intuitive API calls. The type safety alone has saved me countless hours of debugging.

The operations we covered here—create, read, update, delete, and transactions—form the foundation of any database-backed application. I use these patterns daily in production systems handling thousands of requests.

---

This article is part of my personal knowledge sharing based on real projects I've built. All examples are adapted from actual code I've written and deployed to production.