Database with Prisma

Managing a database is one of the most important parts of building a full-stack app. In this section, you will learn how to connect Prisma to your Next.js app, add and fetch data, and write smarter queries using filters, limits, joins, and more.

What is Prisma?

Prisma is a free, open-source tool that makes it easy to work with databases in your app. Instead of writing raw SQL queries, you write simple JavaScript/TypeScript code and Prisma handles the rest.

Here is what Prisma gives you:

• A type-safe query builder - your code editor will catch mistakes before you run the app
• An ORM (Object-Relational Mapper) - it maps your database tables to JavaScript objects
• A migration system - it automatically creates and updates your database tables based on your code

Prisma works with many popular databases like PostgreSQL, MySQL, SQLite, and more.

Setting Up a Database

Before using Prisma, you need a database to connect to. You can use a cloud database (like AWS RDS or Google Cloud SQL) or run one locally. Below is a simple MySQL database running inside Docker - great for local development.

services:
  db:
    image: mysql:8.0
    container_name: my-mysql-db
    environment:
      MYSQL_ROOT_PASSWORD: rootpassword
      MYSQL_DATABASE: mydatabase
    ports:
      - "3306:3306"
    volumes:
      - ./.db_data:/var/lib/mysql

  adminer:
    image: adminer
    container_name: my-adminer
    ports:
      - "8080:8080"
    depends_on:
      - db

• Connection URL: mysql://root:rootpassword@db:3306/mydatabase

Setting Up Prisma

Prisma Docs: https://www.prisma.io/docs

Follow these steps to add Prisma to your Next.js project:

1. Install Prisma CLI and Client: Run this command in your project folder to set up Prisma.

   npx prisma@latest init

2. Set Your Database URL: Open the .env file and update the connection string with your database details.

   DATABASE_URL="mysql://root:rootpassword@localhost:3306/mydatabase"

3. Install the VS Code Extension: This gives you auto-complete and formatting for Prisma files. Prisma VS Code Extension

4. Define Your Data Model: Open prisma/schema.prisma and describe what your data looks like. Think of a model like a table in your database. Below is a simple User model:

   datasource db {
     provider = "mysql"
     url      = env("DATABASE_URL")
   }
   
   generator client {
     provider = "prisma-client-js"
   }
   
   model User {
     id        Int      @id @default(autoincrement())
     name      String
     email     String   @unique
     createdAt DateTime @default(now())
   }

5. Run Prisma Migrate: This command looks at your schema and creates the actual tables in your database.

   npx prisma migrate dev --name init

6. Format Your Schema: Keep your schema file clean and readable with this command:

    npx prisma format

Tip

You can link models together in Prisma. For example, if each Post belongs to a User, you can set up that relationship like this:

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  authorId  Int
  author    User     @relation(fields: [authorId], references: [id])
}

For more details, check the Prisma Relations Documentation.

Prisma Client

The Prisma Client is what you use in your code to talk to the database. It is auto-generated from your schema file and gives you a clean API to add, read, update, and delete data.

One important thing - you should only ever create one Prisma Client instance in your app. Creating too many can cause connection problems. The trick below makes sure only one instance exists, even during hot-reloading in development:

import { PrismaClient } from "../prisma/generated/client";

const globalForPrisma = global as unknown as { prisma: PrismaClient };

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

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

This pattern stores the Prisma instance on the global object so it survives hot reloads during development. In production, a fresh instance is always created.

Performing CRUD Operations

CRUD stands for Create, Read, Update, Delete - the four basic things you do with data in any app. Prisma makes all of these simple with easy-to-read methods.

List Operation

This fetches all records from a table. Use it when you want to show a list of items.

// app/api/users/route.ts
import { NextRequest, NextResponse } from "next/server";
import { prisma } from "../prisma/client";

export async function GET(req: NextRequest) {
  try {
    const users = await prisma.user.findMany();
    if (!users) {
      return NextResponse.json({ error: "No users found" }, { status: 404 });
    }
    return NextResponse.json(users);
  } catch (error) {
    return NextResponse.json({ error: "Failed to fetch users" }, { status: 500 });
  }
}

Create Operation

This adds a new record to the database.

// app/api/users/route.ts
import { NextRequest, NextResponse } from "next/server";
import { prisma } from "../prisma/client";

export async function POST(req: NextRequest) {
  try {
    const { name, email } = await req.json();
    const newUser = await prisma.user.create({ data: { name, email } });
    return NextResponse.json(newUser, { status: 201 });
  } catch (error) {
    return NextResponse.json({ error: "Failed to create user" }, { status: 500 });
  }
}

Read Operation

This fetches one specific record using its unique ID.

// app/api/users/[id]/route.ts
import { NextRequest, NextResponse } from "next/server";
import { prisma } from "../prisma/client";

export async function GET(req: NextRequest, { params }: { params: { id: string } }) {
  try {
    const userId = parseInt(params.id, 10);
    const user = await prisma.user.findUnique({ where: { id: userId } });
    if (!user) {
      return NextResponse.json({ error: "User not found" }, { status: 404 });
    }
    return NextResponse.json(user);
  } catch (error) {
    return NextResponse.json({ error: "Failed to fetch user" }, { status: 500 });
  }
}

Update Operation

This changes the data of an existing record.

// app/api/users/[id]/route.ts
import { NextRequest, NextResponse } from "next/server";
import { prisma } from "../prisma/client";

export async function PUT(req: NextRequest, { params }: { params: { id: string } }) {
  try {
    const userId = parseInt(params.id, 10);
    const { name, email } = await req.json();
    const updatedUser = await prisma.user.update({ where: { id: userId }, data: { name, email } });
    return NextResponse.json(updatedUser);
  } catch (error) {
    return NextResponse.json({ error: "Failed to update user" }, { status: 500 });
  }
}

Delete Operation

This removes a record from the database permanently.

// app/api/users/[id]/route.ts
import { NextRequest, NextResponse } from "next/server";
import { prisma } from "../prisma/client";

export async function DELETE(req: NextRequest, { params }: { params: { id: string } }) {
  try {
    const userId = parseInt(params.id, 10);
    const deletedUser = await prisma.user.delete({ where: { id: userId } });
    return NextResponse.json(deletedUser);
  } catch (error) {
    return NextResponse.json({ error: "Failed to delete user" }, { status: 500 });
  }
}

Filtering with where

The where clause lets you fetch only the records that match certain conditions. Think of it like a search filter.

Basic where Filter

// Find a user by their email address
const user = await prisma.user.findFirst({ where: { email: "john@example.com" } });

Comparison Operators

Prisma gives you helpful operators to compare values:

Operator	Meaning	Example
equals	Exact match (default)	{ age: { equals: 25 } }
not	Does not equal	{ age: { not: 25 } }
gt	Greater than	{ age: { gt: 18 } }
gte	Greater than or equal	{ age: { gte: 18 } }
lt	Less than	{ age: { lt: 60 } }
lte	Less than or equal	{ age: { lte: 60 } }
in	Value is in a list	{ id: { in: [1, 2, 3] } }
notIn	Value is not in a list	{ id: { notIn: [4, 5] } }
contains	String contains a value	{ name: { contains: "john" } }
startsWith	String starts with a value	{ name: { startsWith: "J" } }
endsWith	String ends with a value	{ email: { endsWith: "@gmail.com" } }

// Fetch all users older than 18
const adults = await prisma.user.findMany({ where: { age: { gt: 18 } } });

// Fetch users whose name contains "john" (case-insensitive)
const johns = await prisma.user.findMany({ where: { name: { contains: "john", mode: "insensitive" } } });

// Fetch users with specific IDs
const selectedUsers = await prisma.user.findMany({ where: { id: { in: [1, 2, 3] } } });

Limiting Results with take and skip

When you have thousands of records, you do not want to fetch all of them at once. Use take and skip to control how many records you get back - this is called pagination.

• take - how many records to return (like a “page size”)
• skip - how many records to skip from the beginning (like “which page”)

// Get the first 10 users
const firstPage = await prisma.user.findMany({
  take: 10,
  skip: 0, // start from the beginning
});

// Get the next 10 users (page 2)
const secondPage = await prisma.user.findMany({
  take: 10,
  skip: 10, // skip the first 10
});

Here is a reusable helper for pagination:

const page = 2; // which page you want
const pageSize = 10; // how many items per page

const users = await prisma.user.findMany({
  take: pageSize,
  skip: (page - 1) * pageSize,
  orderBy: { createdAt: "desc" }, // newest first
});

Sorting Results with orderBy

Use orderBy to sort your results. You can sort by any field in ascending (asc) or descending (desc) order.

// Get users sorted by name A to Z
const usersAZ = await prisma.user.findMany({ orderBy: { name: "asc" } });

// Get the most recently created users first
const newest = await prisma.user.findMany({ orderBy: { createdAt: "desc" } });

// Sort by multiple fields - first by name, then by email
const sorted = await prisma.user.findMany({ orderBy: [{ name: "asc" }, { email: "asc" }] });

Logical Operators: AND, OR, NOT

Sometimes you need to combine multiple conditions. Prisma gives you AND, OR, and NOT for this.

AND - All conditions must be true

// Find users who are both active AND have a gmail address
const users = await prisma.user.findMany({ where: { AND: [{ isActive: true }, { email: { endsWith: "@gmail.com" } }] } });

Tip

Writing multiple conditions inside a single where object automatically applies AND. So AND is the default behaviour when you list conditions side by side.

// These two queries do the same thing:

// Using AND explicitly
where: { AND: [{ isActive: true }, { email: { endsWith: "@gmail.com" } }] }

// Using shorthand (same result)
where: { isActive: true, email: { endsWith: "@gmail.com" } }

OR - At least one condition must be true

// Find users who are either admins OR have a verified email
const users = await prisma.user.findMany({ where: { OR: [{ role: "admin" }, { emailVerified: true }] } });

NOT - Exclude records that match the condition

// Find all users who are NOT banned
const users = await prisma.user.findMany({ where: { NOT: { isBanned: true } } });

Combining AND, OR, and NOT Together

You can nest these operators to build complex filters:

// Find active users who are either admins OR have verified emails,
// but are NOT banned
const users = await prisma.user.findMany({
  where: { AND: [{ isActive: true }, { OR: [{ role: "admin" }, { emailVerified: true }] }, { NOT: { isBanned: true } }] },
});

Selecting Only Specific Fields with select

By default, Prisma returns all fields from a record. If you only need a few fields (for performance or privacy), use select.

// Only return the id, name, and email - skip createdAt and other fields
const users = await prisma.user.findMany({ select: { id: true, name: true, email: true } });

Tip

Never return sensitive fields (like passwords or tokens) from API routes. Use select to be specific about what you send back.

Including Related Data (Joins)

In SQL, a “join” means combining data from two related tables. In Prisma, you do this using include or select with nested fields - no raw SQL needed.

Setup: Models with a Relationship

For the examples below, assume this schema:

model User {
  id        Int      @id @default(autoincrement())
  name      String
  email     String   @unique
  posts     Post[]
  createdAt DateTime @default(now())
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  authorId  Int
  author    User     @relation(fields: [authorId], references: [id])
  createdAt DateTime @default(now())
}

Here, one User can have many Post records (one-to-many relationship).

include - Fetch Related Data Alongside the Main Record

// Fetch a user AND all their posts
const userWithPosts = await prisma.user.findUnique({ where: { id: 1 }, include: { posts: true } });

// Result looks like:
// {
//   id: 1,
//   name: "John",
//   email: "john@example.com",
//   posts: [
//     { id: 1, title: "Hello World", ... },
//     { id: 2, title: "My Second Post", ... }
//   ]
// }

Filtering Included Relations

You can filter which related records are included:

// Fetch a user and only their PUBLISHED posts
const userWithPublishedPosts = await prisma.user.findUnique({
  where: { id: 1 },
  include: { posts: { where: { published: true }, orderBy: { createdAt: "desc" } } },
});

Nested include - Go Multiple Levels Deep

If your schema has deeper relationships (for example, Posts have Comments), you can go as deep as needed:

// Fetch a user, their posts, and each post's comments
const userWithPostsAndComments = await prisma.user.findUnique({
  where: { id: 1 },
  include: { posts: { include: { comments: true } } },
});

Using select with Relations

select gives you more control - you can pick specific fields from both the main record and the related records:

// Only return the user's name, and just the titles of their posts
const user = await prisma.user.findUnique({ where: { id: 1 }, select: { name: true, posts: { select: { title: true } } } });

Tip

Use select instead of include when you want to control which fields come back from related models too. include returns all fields of the related model, but select lets you pick exactly what you need. This is better for performance and avoids over-fetching data.

Query Optimisation

As your app grows and your database gets larger, slow queries can hurt your users’ experience. Here are practical tips to keep your queries fast.

1. Always Use select to Avoid Over-fetching

Fetching all fields when you only need a few wastes time and memory.

// Bad - fetches all fields even if you only need name
const users = await prisma.user.findMany();

// Good - only fetch what you need
const users = await prisma.user.findMany({ select: { id: true, name: true } });

2. Use Pagination Instead of Fetching All Records

Never fetch thousands of records at once. Always use take and skip.

// Bad - fetches every single user from the database
const allUsers = await prisma.user.findMany();

// Good - fetch only what fits on one page
const pageUsers = await prisma.user.findMany({ take: 20, skip: 0 });

3. Use findUnique Instead of findFirst When You Have an ID

findUnique is faster because Prisma knows it only needs to look up one row using a unique index (like id or email).

// Slower - scans more rows
const user = await prisma.user.findFirst({ where: { id: 1 } });

// Faster - uses the unique index directly
const user = await prisma.user.findUnique({ where: { id: 1 } });

4. Avoid Deep or Large include Chains

Including lots of related data in one query can be slow. Only include what you actually use.

// Potentially slow - loads everything
const user = await prisma.user.findUnique({
  where: { id: 1 },
  include: { posts: { include: { comments: { include: { likes: true } } } } },
});

// Better - only include what you need on this page
const user = await prisma.user.findUnique({ where: { id: 1 }, include: { posts: { take: 5, orderBy: { createdAt: "desc" } } } });

5. Count Records Without Fetching Them

If you just need to know how many records exist, use count instead of fetching all records and checking the length.

// Bad - loads all users into memory just to count them
const users = await prisma.user.findMany();
const count = users.length;

// Good - just asks the database for the number
const count = await prisma.user.count({ where: { isActive: true } });

6. Run Independent Queries in Parallel

If you need data from multiple unrelated queries, run them at the same time using Promise.all instead of one after the other.

// Slow - waits for each query to finish before starting the next
const users = await prisma.user.findMany();
const posts = await prisma.post.findMany();

// Fast - runs both queries at the same time
const [users, posts] = await Promise.all([prisma.user.findMany(), prisma.post.findMany()]);

7. Use Prisma Transactions for Multiple Writes

If you need to do several writes together (for example, create a user and their default settings), wrap them in a transaction. This makes sure either all of them succeed, or none of them do - so your data stays consistent.

const result = await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({ data: { name: "Alice", email: "alice@example.com" } });

  const settings = await tx.settings.create({ data: { userId: user.id, theme: "dark" } });

  return { user, settings };
});

8. Use @@index in Your Schema for Frequently Filtered Fields

If you often filter by a field (like email or createdAt), add a database index to that field in your schema. This makes lookups much faster.

model User {
  id        Int      @id @default(autoincrement())
  name      String
  email     String   @unique
  isActive  Boolean  @default(true)
  createdAt DateTime @default(now())

  @@index([isActive])     // add an index on isActive since we filter by it often
  @@index([createdAt])    // useful for sorting by newest
}

After updating your schema, run the migration command to apply the index:

npx prisma migrate dev --name add-indexes

Tip

You can use npx prisma studio to open a visual browser-based dashboard for your database. It lets you view, add, edit, and delete records without writing any code - great for debugging during development.

Uploading Files

Generally, you do not store files (like images or PDFs) directly in your database. Instead, you upload them to a file storage service (like AWS S3, Google Cloud Storage, Cloudflare R2, or Cloudinary) and save only the file’s URL in your database.

Here we will use Cloudinary as an example. The key thing we will do is use signed uploads - this means only users who are logged in (authorised) can upload files. An API route on your server signs each upload request, so random people cannot just upload files to your Cloudinary account.

Here is how the flow works:

1. User clicks “Upload” in the browser
2. The browser asks your Next.js API route to sign the upload request
3. Your API route uses your secret Cloudinary key to create a signature
4. The browser sends the file to Cloudinary along with that signature
5. Cloudinary accepts the file and returns the file URL
6. You save that URL to your database using Prisma

Set Up Cloudinary

1. Create a free Cloudinary account at https://cloudinary.com/.

2. In your Cloudinary dashboard, find your Cloud Name, API Key, and API Secret on the home page or under Settings > API Keys.

3. Add your Cloudinary credentials to your .env file. Note: only the cloud name and API key use the NEXT_PUBLIC_ prefix (safe to expose to the browser). The API secret must never be exposed to the browser.

   NEXT_PUBLIC_CLOUDINARY_CLOUD_NAME="<Your Cloud Name>"
   NEXT_PUBLIC_CLOUDINARY_API_KEY="<Your API Key>"
   CLOUDINARY_API_SECRET="<Your API Secret>"

4. Install the packages you need:

   npm install next-cloudinary cloudinary

   Docs:

   • https://next.cloudinary.dev/
   • https://cloudinary.com/documentation

5. Create the signature API route. This is the server-side route that signs each upload request. Only your server knows the API secret, so this keeps things secure.

   Create the file app/api/sign-cloudinary-params/route.ts:

   // app/api/sign-cloudinary-params/route.ts
   import { v2 as cloudinary } from "cloudinary";
   import { NextRequest } from "next/server";
   
   cloudinary.config({
     cloud_name: process.env.NEXT_PUBLIC_CLOUDINARY_CLOUD_NAME,
     api_key: process.env.NEXT_PUBLIC_CLOUDINARY_API_KEY,
     api_secret: process.env.CLOUDINARY_API_SECRET,
   });
   
   export async function POST(request: NextRequest) {
     // Check if the user is logged in before signing
     // Replace this with your actual auth check (e.g. from next-auth or your session)
     const isAuthenticated = true; // <- swap this with real auth logic
   
     if (!isAuthenticated) {
       return Response.json({ error: "Unauthorised" }, { status: 401 });
     }
   
     const body = await request.json();
     const { paramsToSign } = body;
   
     const signature = cloudinary.utils.api_sign_request(
       paramsToSign,
       process.env.CLOUDINARY_API_SECRET!
     );
   
     return Response.json({ signature });
   }

   The isAuthenticated check is where you plug in your real auth logic. For example, if you are using NextAuth, you would call getServerSession() here and return 401 if there is no session. This ensures only logged-in users can trigger uploads.

6. Create the upload component. This is a client component that shows the upload button and handles the result.

   Create the file components/FileUploader.tsx:

   // components/FileUploader.tsx
   "use client";
   
   import { useState } from "react";
   import { CldUploadWidget } from "next-cloudinary";
   
   interface UploadResult {
     public_id: string;
     secure_url: string;
   }
   
   interface FileUploaderProps {
     onUploadSuccess: (url: string) => void;
   }
   
   export default function FileUploader({ onUploadSuccess }: FileUploaderProps) {
     const [uploading, setUploading] = useState(false);
   
     return (
       <CldUploadWidget
         signatureEndpoint="/api/sign-cloudinary-params"
         onSuccess={(result) => {
           const info = result?.info as UploadResult;
           if (info?.secure_url) {
             setUploading(false);
             onUploadSuccess(info.secure_url);
           }
         }}
         onQueuesEnd={(result, { widget }) => {
           widget.close();
         }}
       >
         {({ open }) => (
           <button
             type="button"
             disabled={uploading}
             onClick={() => {
               setUploading(true);
               open();
             }}
           >
             {uploading ? "Uploading..." : "Upload File"}
           </button>
         )}
       </CldUploadWidget>
     );
   }

   The signatureEndpoint prop points to the API route you created in the previous step. Before every upload, the widget automatically calls that route to get a signature - so the upload is always authenticated on your server before the file goes to Cloudinary.

7. Use the upload component in a page and save the URL to your database after a successful upload.

   // app/upload/page.tsx
   "use client";
   
   import { useState } from "react";
   import FileUploader from "@/components/FileUploader";
   
   export default function UploadPage() {
     const [fileUrl, setFileUrl] = useState<string | null>(null);
   
     async function handleUploadSuccess(url: string) {
       setFileUrl(url);
   
       // Save the URL to your database via your API route
       await fetch("/api/users/avatar", {
         method: "PUT",
         headers: { "Content-Type": "application/json" },
         body: JSON.stringify({ avatarUrl: url }),
       });
     }
   
     return (
       <div>
         <h1>Upload a File</h1>
         <FileUploader onUploadSuccess={handleUploadSuccess} />
   
         {fileUrl && (
           <div>
             <p>Uploaded successfully!</p>
             <img src={fileUrl} alt="Uploaded file" width={300} />
           </div>
         )}
       </div>
     );
   }

8. In your database API route, save the file URL using Prisma:

   // app/api/users/avatar/route.ts
   import { NextRequest, NextResponse } from "next/server";
   import { prisma } from "@/prisma/client";
   
   export async function PUT(req: NextRequest) {
     try {
       // Replace 1 with the actual logged-in user's ID from your auth session
       const userId = 1;
       const { avatarUrl } = await req.json();
   
       const updatedUser = await prisma.user.update({
         where: { id: userId },
         data: { avatarUrl },
       });
   
       return NextResponse.json(updatedUser);
     } catch (error) {
       return NextResponse.json({ error: "Failed to update avatar" }, { status: 500 });
     }
   }

   Make sure your User model in prisma/schema.prisma has an avatarUrl field:

   model User {
     id        Int      @id @default(autoincrement())
     name      String
     email     String   @unique
     avatarUrl String?  // nullable - not every user has a photo yet
     createdAt DateTime @default(now())
   }

   After adding the field, run npx prisma migrate dev --name add-avatar-url to update your database.

Tip

Never use unsigned uploads in a real app where you care about who can upload files. Unsigned uploads let anyone with your upload preset name upload files to your Cloudinary account without any check. Always use signed uploads (with signatureEndpoint) and protect your signature route with a proper auth check.

Important Security Note

Uploading files with a signed upload only controls who is allowed to upload the file. It does not make the uploaded file private.

If your image is uploaded with the default Cloudinary settings, anyone who has the file URL can access it directly. For example:

https://res.cloudinary.com/demo/image/upload/v123456/avatar.jpg

Anyone who receives or discovers this URL can open it in their browser without logging in or authenticating.

How to Protect Sensitive Files

If you are storing confidential files (such as passports, identity documents, medical reports, invoices, or other private user data), do not use public delivery URLs.

Instead:

• Upload the file as an authenticated/private asset.
• Generate signed delivery URLs on your server after verifying that the requesting user is authorised.
• Return the signed URL to the client only after successful authentication.

This ensures that even if someone knows the public ID of the file, they cannot access it without a valid signed URL generated by your server.