Database Operations

Learn how to query and mutate data using Prisma ORM.

Perform type-safe database operations with Prisma ORM.

Prisma provides an intuitive, type-safe query API that makes database operations a breeze. Every query is validated at compile time — if you reference a field that doesn't exist or pass the wrong type, TypeScript catches it before your code runs.

Query Data

Prisma's query API uses model methods like findMany(), findUnique(), and findFirst(). Results are always typed to your schema.

Find First

When you need a single record, use findFirst() with a where clause. If no record matches, the result is null:

import { db } from '@kit/database';
const user = await db.user.findFirst({
  where: { email: 'user@example.com' },
});

Or use findUnique() when querying by a unique field:

const user = await db.user.findUnique({
  where: { email: 'user@example.com' },
});

Find Many

For lists of records, use findMany() with orderBy for sorting and take for limiting:

import { db } from '@kit/database';
const projects = await db.project.findMany({
  where: { organizationId },
  orderBy: { createdAt: 'desc' },
  take: 10,
});

With Relations

Prisma makes fetching related data easy with include or select. This is much simpler than SQL joins:

import { db } from '@kit/database';
const organization = await db.organization.findUnique({
  where: { id: organizationId },
  include: {
    members: {
      include: { user: true },
    },
  },
});
// Access nested data
organization.members.forEach((member) => {
  console.log(member.user.name);
});

Complex Queries

Combine AND, OR, and comparison operators to build complex filters:

import { db } from '@kit/database';
const projects = await db.project.findMany({
  where: {
    AND: [
      { organizationId },
      { createdAt: { gt: startDate } },
      { name: { contains: 'search', mode: 'insensitive' } },
    ],
  },
});

Insert Data

Use create() to insert new records. Prisma automatically returns the created record with all fields:

Single Insert

const project = await db.project.create({
  data: {
    name: 'New Project',
    organizationId,
  },
});

Multiple Inserts

Use createMany() to insert multiple records efficiently:

const result = await db.project.createMany({
  data: [
    { name: 'Project 1', organizationId },
    { name: 'Project 2', organizationId },
  ],
});
console.log(`Created ${result.count} projects`);

With Relations

Create records with related data in a single operation:

const organization = await db.organization.create({
  data: {
    id: crypto.randomUUID(),
    name: 'New Organization',
    slug: 'new-org',
    createdAt: new Date(),
    members: {
      create: {
        id: crypto.randomUUID(),
        userId,
        role: 'owner',
        createdAt: new Date(),
      },
    },
  },
  include: { members: true },
});

Update Data

Use update() with where to identify the record and data for the new values:

Update by ID

const updated = await db.project.update({
  where: { id: projectId },
  data: {
    name: 'Updated Name',
  },
});

Conditional Update

Update multiple records matching criteria with updateMany():

const result = await db.project.updateMany({
  where: {
    organizationId,
    lastActivity: { lt: oneMonthAgo },
  },
  data: { status: 'archived' },
});
console.log(`Archived ${result.count} projects`);

Partial Update

Only fields you include in data are updated; others remain unchanged:

await db.project.update({
  where: { id: projectId },
  data: {
    name: 'New Name',
    // Other fields remain unchanged
  },
});

Delete Data

Use delete() for single records and deleteMany() for multiple:

Delete by ID

await db.project.delete({
  where: { id: projectId },
});

Conditional Delete

Delete multiple records matching criteria:

await db.invitation.deleteMany({
  where: {
    organizationId,
    expiresAt: { lt: new Date() },
  },
});

Transactions

When multiple operations must succeed or fail together, use $transaction():

await db.$transaction(async (tx) => {
  // Create organization
  const organization = await tx.organization.create({
    data: {
      id: crypto.randomUUID(),
      name: 'New Organization',
      slug: 'new-org',
      createdAt: new Date(),
    },
  });
  // Add owner as member
  await tx.member.create({
    data: {
      id: crypto.randomUUID(),
      organizationId: organization.id,
      userId,
      role: 'owner',
      createdAt: new Date(),
    },
  });
});

Raw SQL

For complex queries that are easier in SQL, use $queryRaw:

const result = await db.$queryRaw`
  SELECT
    p.*,
    COUNT(t.id) as task_count
  FROM projects p
  LEFT JOIN tasks t ON t.project_id = p.id
  WHERE p.organization_id = ${organizationId}
  GROUP BY p.id
`;

Counting Records

Use count() for efficient counting without fetching records:

const projectCount = await db.project.count({
  where: { organizationId },
});

Pagination

For large datasets, use skip and take for pagination:

import { db } from '@kit/database';
const PAGE_SIZE = 20;
export async function getPaginatedProjects(
  organizationId: string,
  page: number = 1
) {
  const skip = (page - 1) * PAGE_SIZE;
  const [projects, total] = await Promise.all([
    db.project.findMany({
      where: { organizationId },
      orderBy: { createdAt: 'desc' },
      take: PAGE_SIZE,
      skip,
    }),
    db.project.count({
      where: { organizationId },
    }),
  ]);
  return {
    projects,
    total,
    page,
    pageSize: PAGE_SIZE,
    totalPages: Math.ceil(total / PAGE_SIZE),
  };
}

Multi-tenancy Pattern

In a multi-tenant application, data isolation is critical. Every query that touches tenant data must filter by organizationId:

import { db } from '@kit/database';
// Good: Scoped to organization
const projects = await db.project.findMany({
  where: { organizationId: currentOrganizationId },
});
// Bad: Not scoped - security risk!
const allProjects = await db.project.findMany();

Performance Tips

Keep these practices in mind as your application grows:

Use indexes — Add database indexes on columns you frequently filter or sort by (like organizationId, createdAt)
Limit results — Always use take when you don't need every record
Select only needed fields — Use select to fetch only the fields you need
Use transactions — Group related operations to ensure data consistency
Avoid N+1 queries — Use include to fetch related data in one query instead of looping
Cache with React's cache() — Wrap loaders to prevent duplicate queries during a single request