Next.js App Router + Prisma Practical Patterns: Type-Safe Database Operations

Introduction

Combining Next.js App Router with Prisma enables type-safe database operations directly from Server Components and Server Actions. This article covers practical design patterns with complete code examples.

For Prisma timezone configuration, see Prisma and MySQL Timezone Mismatch. For the theoretical background on transactions, see Transaction Isolation Levels and Data Inconsistencies.

Prerequisites: Prisma Schema Definition

This article uses the following schema as a running example.

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?
  role      Role     @default(USER)
  posts     Post[]
  profile   Profile?
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  @@map("users")
}

model Post {
  id        String   @id @default(cuid())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  String   @map("author_id")
  tags      Tag[]
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  @@map("posts")
}

model Profile {
  id     String  @id @default(cuid())
  bio    String?
  avatar String?
  user   User    @relation(fields: [userId], references: [id])
  userId String  @unique @map("user_id")

  @@map("profiles")
}

model Tag {
  id    String @id @default(cuid())
  name  String @unique
  posts Post[]

  @@map("tags")
}

enum Role {
  USER
  ADMIN
}

Prisma Client Singleton Pattern

In development, next dev reloads modules on every hot reload. If Prisma Client is instantiated normally, each reload creates a new connection, eventually exhausting the connection pool.

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

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined;
};

export const prisma = globalForPrisma.prisma ?? new PrismaClient();

if (process.env.NODE_ENV !== "production") {
  globalForPrisma.prisma = prisma;
}

Key points:

Storing the Prisma Client in globalThis ensures the same instance is reused across hot reloads
The globalThis assignment is skipped in production (hot reloads don’t occur in production)
This pattern is also recommended in the T3 Stack

Direct DB Access from Server Components

With App Router Server Components, you can call Prisma Client directly within components. No API endpoints required.

// app/users/page.tsx
import { prisma } from "@/lib/prisma";

export default async function UsersPage() {
  const users = await prisma.user.findMany({
    orderBy: { createdAt: "desc" },
    take: 20,
  });

  return (
    <main>
      <h1>Users</h1>
      <ul>
        {users.map((user) => (
          <li key={user.id}>{user.name ?? user.email}</li>
        ))}
      </ul>
    </main>
  );
}

Benefits of direct access from Server Components:

Eliminates the API layer, reducing code volume and latency
No need to design fetch caching strategies (direct DB access)
Fetch only the data the component needs

Cache Control

Be mindful of Next.js caching behavior when fetching data in Server Components.

// Force dynamic rendering
export const dynamic = "force-dynamic";

// Or set revalidation interval
export const revalidate = 60; // Revalidate every 60 seconds

CRUD Implementation with Server Actions

Server Actions allow form submissions and DB operations to be handled entirely on the server side.

Create

// app/users/actions.ts
"use server";

import { prisma } from "@/lib/prisma";
import { revalidatePath } from "next/cache";
import { z } from "zod";

const CreateUserSchema = z.object({
  email: z.string().email("Please enter a valid email address"),
  name: z.string().min(1, "Name is required").max(100),
  role: z.enum(["USER", "ADMIN"]).default("USER"),
});

export type ActionState = {
  success: boolean;
  message: string;
  errors?: Record<string, string[]>;
};

export async function createUser(
  _prevState: ActionState,
  formData: FormData,
): Promise<ActionState> {
  const rawData = {
    email: formData.get("email"),
    name: formData.get("name"),
    role: formData.get("role"),
  };

  const validated = CreateUserSchema.safeParse(rawData);

  if (!validated.success) {
    return {
      success: false,
      message: "Validation error",
      errors: validated.error.flatten().fieldErrors,
    };
  }

  try {
    await prisma.user.create({
      data: validated.data,
    });

    revalidatePath("/users");
    return { success: true, message: "User created successfully" };
  } catch (error) {
    if (error instanceof Error && error.message.includes("Unique constraint")) {
      return {
        success: false,
        message: "This email address is already registered",
      };
    }
    return { success: false, message: "Failed to create user" };
  }
}

Read

// app/users/queries.ts
import { prisma } from "@/lib/prisma";
import { Prisma } from "@prisma/client";

type GetUsersParams = {
  page?: number;
  perPage?: number;
  search?: string;
  role?: "USER" | "ADMIN";
};

export async function getUsers({
  page = 1,
  perPage = 20,
  search,
  role,
}: GetUsersParams = {}) {
  const where: Prisma.UserWhereInput = {
    ...(search && {
      OR: [
        { name: { contains: search, mode: "insensitive" } },
        { email: { contains: search, mode: "insensitive" } },
      ],
    }),
    ...(role && { role }),
  };

  const [users, total] = await Promise.all([
    prisma.user.findMany({
      where,
      skip: (page - 1) * perPage,
      take: perPage,
      orderBy: { createdAt: "desc" },
    }),
    prisma.user.count({ where }),
  ]);

  return {
    users,
    pagination: {
      page,
      perPage,
      total,
      totalPages: Math.ceil(total / perPage),
    },
  };
}

Update

// Add to app/users/actions.ts
export async function updateUser(
  userId: string,
  _prevState: ActionState,
  formData: FormData,
): Promise<ActionState> {
  const rawData = {
    name: formData.get("name"),
    role: formData.get("role"),
  };

  const UpdateUserSchema = z.object({
    name: z.string().min(1).max(100),
    role: z.enum(["USER", "ADMIN"]),
  });

  const validated = UpdateUserSchema.safeParse(rawData);
  if (!validated.success) {
    return {
      success: false,
      message: "Validation error",
      errors: validated.error.flatten().fieldErrors,
    };
  }

  try {
    await prisma.user.update({
      where: { id: userId },
      data: validated.data,
    });

    revalidatePath("/users");
    return { success: true, message: "User updated successfully" };
  } catch (error) {
    return { success: false, message: "Failed to update user" };
  }
}

Delete

// Add to app/users/actions.ts
export async function deleteUser(userId: string): Promise<ActionState> {
  try {
    await prisma.user.delete({
      where: { id: userId },
    });

    revalidatePath("/users");
    return { success: true, message: "User deleted successfully" };
  } catch (error) {
    return { success: false, message: "Failed to delete user" };
  }
}

Error Handling Best Practices

Prisma errors are typed as PrismaClientKnownRequestError. Handle them based on error codes.

// lib/errors.ts
import { Prisma } from "@prisma/client";

export type DbResult<T> =
  | { success: true; data: T }
  | { success: false; error: string };

export async function withDbError<T>(
  operation: () => Promise<T>,
): Promise<DbResult<T>> {
  try {
    const data = await operation();
    return { success: true, data };
  } catch (error) {
    if (error instanceof Prisma.PrismaClientKnownRequestError) {
      switch (error.code) {
        case "P2002":
          return {
            success: false,
            error: `Unique constraint violation: ${(error.meta?.target as string[])?.join(", ")}`,
          };
        case "P2025":
          return {
            success: false,
            error: "Record not found",
          };
        case "P2003":
          return {
            success: false,
            error:
              "Foreign key constraint violation: related record does not exist",
          };
        default:
          return {
            success: false,
            error: `Database error: ${error.code}`,
          };
      }
    }
    return { success: false, error: "An unexpected error occurred" };
  }
}

Usage example:

const result = await withDbError(() =>
  prisma.user.create({
    data: { email: "test@example.com", name: "Test" },
  }),
);

if (!result.success) {
  console.error(result.error);
  return;
}

// result.data is inferred as User type
console.log(result.data.id);

Type-Safe Query Patterns

Leveraging Prisma’s type inference ensures query result types are automatically determined.

Partial Retrieval with `select`

// Using select narrows the return type automatically
const userEmails = await prisma.user.findMany({
  select: {
    id: true,
    email: true,
    // name is not included, so userEmails[0].name causes a compile error
  },
});

// Type: { id: string; email: string }[]

Reusable Query Definitions with `Prisma.validator`

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

// Define type-safe query arguments
const userWithPosts = Prisma.validator<Prisma.UserDefaultArgs>()({
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: "desc" },
    },
    profile: true,
  },
});

// Derive the return type
type UserWithPosts = Prisma.UserGetPayload<typeof userWithPosts>;

// Reusable function
export async function getUserWithPosts(
  userId: string,
): Promise<UserWithPosts | null> {
  return prisma.user.findUnique({
    where: { id: userId },
    ...userWithPosts,
  });
}

Type-Safe Dynamic `where` Conditions

function buildUserFilter(params: {
  search?: string;
  role?: Role;
  createdAfter?: Date;
}): Prisma.UserWhereInput {
  const conditions: Prisma.UserWhereInput[] = [];

  if (params.search) {
    conditions.push({
      OR: [
        { name: { contains: params.search, mode: "insensitive" } },
        { email: { contains: params.search, mode: "insensitive" } },
      ],
    });
  }

  if (params.role) {
    conditions.push({ role: params.role });
  }

  if (params.createdAfter) {
    conditions.push({ createdAt: { gte: params.createdAfter } });
  }

  return conditions.length > 0 ? { AND: conditions } : {};
}

Avoiding the N+1 Problem

The N+1 problem occurs when fetching relational data issues an additional query for each parent record. Prisma prevents this with include and select.

Problematic Pattern (N+1)

// BAD: Fetching posts individually for each user (N+1 queries)
const users = await prisma.user.findMany();
for (const user of users) {
  const posts = await prisma.post.findMany({
    where: { authorId: user.id },
  });
  // ...
}

Solution with `include`

// OK: Fetch relations in a single query
const users = await prisma.user.findMany({
  include: {
    posts: true, // Issues a JOIN query
    profile: true,
  },
});

Fetching Only Required Fields with `select`

// BETTER: Fetch only needed fields to reduce data transfer
const users = await prisma.user.findMany({
  select: {
    id: true,
    name: true,
    posts: {
      select: {
        id: true,
        title: true,
        published: true,
      },
      where: { published: true },
      take: 5,
    },
    _count: {
      select: { posts: true }, // Count posts only
    },
  },
});

Optimizing Deeply Nested Relations

// Avoid N+1 in multi-level relations with include/select
const posts = await prisma.post.findMany({
  select: {
    id: true,
    title: true,
    author: {
      select: {
        name: true,
        profile: {
          select: { avatar: true },
        },
      },
    },
    tags: {
      select: { name: true },
    },
  },
});

Transaction Handling

Use Prisma’s transaction features to execute multiple DB operations atomically. For details on transaction isolation levels, see Transaction Isolation Levels and Data Inconsistencies.

Sequential Transactions

Pass multiple operations as an array. All succeed or all are rolled back.

const [user, post] = await prisma.$transaction([
  prisma.user.create({
    data: { email: "new@example.com", name: "New User" },
  }),
  prisma.post.create({
    data: {
      title: "My First Post",
      content: "Hello, world!",
      authorId: "existing-user-id",
    },
  }),
]);

Interactive Transactions

Use for complex transaction logic with conditional branching.

// Create user with profile and initial post atomically
async function createUserWithProfile(data: {
  email: string;
  name: string;
  bio?: string;
}) {
  return prisma.$transaction(async (tx) => {
    // 1. Create user
    const user = await tx.user.create({
      data: {
        email: data.email,
        name: data.name,
      },
    });

    // 2. Create profile
    await tx.profile.create({
      data: {
        userId: user.id,
        bio: data.bio ?? "",
      },
    });

    // 3. Create welcome post
    await tx.post.create({
      data: {
        title: "Hello!",
        content: `Hi, I'm ${data.name}. Nice to meet you!`,
        authorId: user.id,
        published: true,
      },
    });

    return user;
  });
}

Transaction Timeout and Isolation Level Configuration

await prisma.$transaction(
  async (tx) => {
    // Heavy operations
    const users = await tx.user.findMany();
    for (const user of users) {
      await tx.user.update({
        where: { id: user.id },
        data: { role: "USER" },
      });
    }
  },
  {
    maxWait: 5000, // Max wait time to start transaction (ms)
    timeout: 10000, // Max transaction execution time (ms)
    isolationLevel: Prisma.TransactionIsolationLevel.Serializable,
  },
);

Real-World Example: User Management API

Here is a practical user management implementation that integrates all the patterns covered above.

Directory Structure

app/
  users/
    page.tsx          # User list (Server Component)
    [id]/
      page.tsx        # User detail (Server Component)
    actions.ts        # Server Actions (CRUD)
    queries.ts        # Data fetching functions
lib/
  prisma.ts           # Prisma Client singleton
  errors.ts           # Error handling utilities
prisma/
  schema.prisma       # Schema definition

User Detail Page

// app/users/[id]/page.tsx
import { prisma } from "@/lib/prisma";
import { notFound } from "next/navigation";

type Props = {
  params: Promise<{ id: string }>;
};

export default async function UserDetailPage({ params }: Props) {
  const { id } = await params;

  const user = await prisma.user.findUnique({
    where: { id },
    include: {
      profile: true,
      posts: {
        where: { published: true },
        orderBy: { createdAt: "desc" },
        take: 10,
      },
      _count: {
        select: { posts: true },
      },
    },
  });

  if (!user) {
    notFound();
  }

  return (
    <main>
      <h1>{user.name}</h1>
      <p>{user.email}</p>
      {user.profile?.bio && <p>{user.profile.bio}</p>}
      <h2>Posts ({user._count.posts})</h2>
      <ul>
        {user.posts.map((post) => (
          <li key={post.id}>{post.title}</li>
        ))}
      </ul>
    </main>
  );
}

Calling Server Actions from Client Components

// app/users/create-form.tsx
"use client";

import { useActionState } from "react";
import { createUser, type ActionState } from "./actions";

const initialState: ActionState = {
  success: false,
  message: "",
};

export function CreateUserForm() {
  const [state, formAction, isPending] = useActionState(
    createUser,
    initialState,
  );

  return (
    <form action={formAction}>
      <div>
        <label htmlFor="email">Email</label>
        <input type="email" id="email" name="email" required />
        {state.errors?.email && (
          <p className="error">{state.errors.email[0]}</p>
        )}
      </div>
      <div>
        <label htmlFor="name">Name</label>
        <input type="text" id="name" name="name" required />
        {state.errors?.name && (
          <p className="error">{state.errors.name[0]}</p>
        )}
      </div>
      <div>
        <label htmlFor="role">Role</label>
        <select id="role" name="role">
          <option value="USER">User</option>
          <option value="ADMIN">Admin</option>
        </select>
      </div>
      <button type="submit" disabled={isPending}>
        {isPending ? "Creating..." : "Create User"}
      </button>
      {state.message && (
        <p className={state.success ? "success" : "error"}>{state.message}</p>
      )}
    </form>
  );
}

Summary

Here is a summary of the patterns covered in this article.

Pattern	Purpose
Singleton	Prevent connection pool exhaustion in dev
Server Components direct fetch	Eliminate API layer to reduce code and latency
Server Actions + Zod	Type-safe form handling and validation
`withDbError` wrapper	Unified Prisma error handling
`Prisma.validator`	Reusable, type-safe query definitions
`include` / `select`	Avoid N+1 problem and optimize data transfer
Interactive transactions	Atomic execution of multiple operations

Combining these patterns enables you to build a type-safe, performant data access layer.

Prisma and MySQL Timezone Mismatch - Explains the causes and solutions for JST/UTC mismatches in Prisma.
Transaction Isolation Levels and Data Inconsistencies - Covers the theoretical background of transaction isolation levels.
Technical Challenges of Sharing prom-client Metrics Between Next.js App Router and Pages Router - Discusses global state sharing challenges between App Router and Pages Router.

JSON Formatter (DevToolBox) - JSON formatting and validation tool
JSON to TypeScript (DevToolBox) - Auto-generate TypeScript type definitions from JSON
SQL Formatter (DevToolBox) - SQL formatting tool