Database Operations

Query and mutate PostgreSQL data with Prisma ORM - type-safe database operations with compile-time validation, relation handling, and transaction support.

The Next.js Prisma kit uses Prisma ORM for all database operations. Prisma generates a type-safe client from your schema, so TypeScript catches invalid field references and type mismatches before your code runs. You get IntelliSense for every query, automatic relation handling, and transactions when you need atomicity.

Prisma ORM is a type-safe database client that generates TypeScript types from your schema. It replaces raw SQL with an intuitive API while preserving the ability to write raw queries when needed.

Quick Reference

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();

Common Pitfalls

• Forgetting organization scope: Every tenant query must filter by organizationId. Without it, users can read other organizations' data.
• N+1 queries: Fetching records in a loop causes one query per iteration. Use include to fetch related data in a single query.
• Missing indexes: Queries on unindexed columns cause full table scans. Add indexes for organizationId, createdAt, and any column you filter by.
• Unbounded queries: findMany() without take returns all records. Always limit results, especially in production.
• Mixing tx and db: Inside $transaction(), use tx (the transaction client), not db. Using db bypasses the transaction.
• Ignoring null: findUnique() and findFirst() can return null. Always handle the missing-record case.

Performance Tips

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

Related

• Development Guide Overview - Full development patterns guide
• Database Schema - Schema definition and migrations
• Adding Features - Step-by-step feature tutorial

Back to: Development Guide Overview