1. Home
Technology / Next.js

Next.js Database Integration

Connect to databases using ORMs like Prisma, handle migrations, and query data safely.

TL;DR

  1. 01Use an ORM like Prisma to manage database connections safely.
  2. 02Define schemas and run migrations to evolve your database.
  3. 03Query data from server components or API routes securely.
Jump to section

Tips

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

Warnings

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

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

FAQ