Next.js Database Integration

Setting Up Prisma

• Install Prisma and initialize your project.

  npm install @prisma/client
  npm install -D prisma
  npx prisma init

• Set your database connection in the .env file.

  DATABASE_URL="postgresql://user:password@localhost:5432/mydb"

• Create a Prisma client instance for database queries.

  // lib/prisma.ts
  import { PrismaClient } from "@prisma/client";
  
  const globalForPrisma = global as unknown as {
    prisma: PrismaClient | undefined;
  };
  
  export const prisma =
    globalForPrisma.prisma ?? new PrismaClient();
  
  if (process.env.NODE_ENV !== "production") {
    globalForPrisma.prisma = prisma;
  }

• This pattern prevents multiple Prisma instances in development.

Defining Schemas

• Define your database schema in schema.prisma.

  // prisma/schema.prisma
  datasource db {
    provider = "postgresql"
    url      = env("DATABASE_URL")
  }
  
  model User {
    id    Int     @id @default(autoincrement())
    email String  @unique
    name  String?
    posts Post[]
  }
  
  model Post {
    id        Int   @id @default(autoincrement())
    title     String
    content   String?
    published Boolean @default(false)
    author    User  @relation(fields: [authorId], references: [id])
    authorId  Int
  }

• Define relationships between models clearly.
• Use @unique for fields that should be unique.
• Use @default for default values at creation time.

Running Migrations

• Create a new migration after changing the schema.

  npx prisma migrate dev --name add_posts

• This creates a migration file and applies it to the database.
• Review the SQL and commit the migration file to version control.

  # List all migrations
  npx prisma migrate status
  
  # Replay all migrations (for new databases)
  npx prisma migrate deploy

• Never manually modify the database — use migrations.
• Migrations are essential for team collaboration and deployments.

Querying Data

• Query data from server components or API routes safely.

  import { prisma } from "@/lib/prisma";
  
  export default async function Users() {
    const users = await prisma.user.findMany();
    return <div>{users.map(u => <div>{u.name}</div>)}</div>;
  }

• Fetch a single record by ID or unique field.

  const user = await prisma.user.findUnique({
    where: { email: "alice@example.com" }
  });

• Create new records with validation.

  const newUser = await prisma.user.create({
    data: {
      email: "bob@example.com",
      name: "Bob"
    }
  });

• Update existing records safely.

  const updated = await prisma.user.update({
    where: { id: 1 },
    data: { name: "Robert" }
  });

Common Patterns

• Include related data in queries to avoid N+1 problems.

  const users = await prisma.user.findMany({
    include: { posts: true }
  });

• Filter and sort results for complex queries.

  const published = await prisma.post.findMany({
    where: { published: true },
    orderBy: { createdAt: "desc" },
    take: 10
  });

• Use transactions for multiple operations that must succeed together.

  const [user, post] = await prisma.$transaction([
    prisma.user.create({ data: { email: "test@example.com" } }),
    prisma.post.create({ data: { title: "Hello", authorId: 1 } })
  ]);

• Use API routes for mutations from client components.

  // app/api/posts/route.ts
  export async function POST(request) {
    const data = await request.json();
    const post = await prisma.post.create({ data });
    return Response.json(post);
  }

Tip: Always use Prisma client from a singleton instance to avoid connection pool exhaustion, especially in serverless environments.

Warning: Never expose database credentials in client code — all queries must happen on the server or through secure API routes.