Lesson 14: Database Integration & Prisma

React & Next.js: From Basics to Advanced

Part 4: Next.js Advanced

xdev.asia

1. Setup Prisma

npm install prisma @prisma/client
npx prisma init --datasource-provider postgresql

2. Schema Design

// 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())
  updatedAt DateTime @updatedAt

  @@index([email])
}

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

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

  @@index([authorId])
  @@index([published, createdAt])
}

model Category {
  id    String @id @default(cuid())
  name  String @unique
  slug  String @unique
  posts Post[]
}

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

enum Role {
  USER
  ADMIN
  EDITOR
}

3. Migration & Seeding

# Create migration
npx prisma migrate dev --name init

# Apply in production
npx prisma migrate deploy

# Reset database
npx prisma migrate reset

// prisma/seed.ts
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

async function main() {
  await prisma.user.create({
    data: {
      email: '[email protected]',
      name: 'Admin',
      role: 'ADMIN',
      profile: { create: { bio: 'Administrator' } },
      posts: {
        create: [
          { title: 'First Post', content: 'Hello!', published: true },
          { title: 'Draft', content: 'WIP' },
        ],
      },
    },
  });
}

main()
  .catch(console.error)
  .finally(() => prisma.$disconnect());

4. Prisma Client Singleton

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

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

export const db =
  globalForPrisma.prisma ??
  new PrismaClient({
    log: process.env.NODE_ENV === 'development' ? ['query'] : [],
  });

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

5. Advanced Queries

// Pagination + Filter + Sort
const posts = await db.post.findMany({
  where: {
    published: true,
    OR: [
      { title: { contains: search, mode: 'insensitive' } },
      { content: { contains: search, mode: 'insensitive' } },
    ],
    categories: { some: { slug: categorySlug } },
  },
  include: {
    author: { select: { name: true, email: true } },
    categories: true,
    _count: { select: { tags: true } },
  },
  orderBy: { createdAt: 'desc' },
  skip: (page - 1) * limit,
  take: limit,
});

// Count
const total = await db.post.count({ where: { published: true } });

// Transaction
const [post, user] = await db.$transaction([
  db.post.create({ data: { title: 'New', authorId: userId } }),
  db.user.update({
    where: { id: userId },
    data: { postCount: { increment: 1 } },
  }),
]);

// Interactive transaction
await db.$transaction(async (tx) => {
  const user = await tx.user.findUnique({ where: { id: userId } });
  if (!user) throw new Error('User not found');
  await tx.post.create({ data: { title: 'Post', authorId: user.id } });
});

6. Use in Server Component

import { db } from '@/lib/db';
import { Suspense } from 'react';

async function PostList() {
  const posts = await db.post.findMany({
    where: { published: true },
    include: { author: true },
    orderBy: { createdAt: 'desc' },
  });

  return (
    <div className="grid gap-4">
      {posts.map(post => (
        <article key={post.id}>
          <h2>{post.title}</h2>
          <p>By {post.author.name}</p>
        </article>
      ))}
    </div>
  );
}

export default function PostsPage() {
  return (
    <Suspense fallback={<div>Loading...</div>}>
      <PostList />
    </Suspense>
  );
}

Next article: Internationalization & SEO — multilingual, metadata, structured data.