Database & Prisma Guide

PostgreSQL schema design, Prisma ORM setup, API route patterns, and the data seeding strategy.

Overview

The application uses PostgreSQL as its primary database, accessed via Prisma ORM. The schema is defined in prisma/schema.prisma and includes four models: User, Post, Course, and Contact.

Prisma provides type-safe database access, automatic migration generation, a built-in data browser (Prisma Studio), and a seed script for initial data population.

Schema Models

The schema uses the postgresql provider with the connection string from the DATABASE_URL environment variable.

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

generator client {
  provider = "prisma-client-js"
}

User Model

Field	Type	Notes
`id`	String (cuid)	Primary key
`email`	String	Unique, used for login
`password`	String	Bcrypt hashed
`name`	String?	Display name
`role`	Role enum	USER or ADMIN (default USER)
`posts`	Post[]	One-to-many relation
`createdAt`	DateTime	Auto-set
`updatedAt`	DateTime	Auto-updated

The Role enum has two values: USER and ADMIN. The admin middleware checks role === "ADMIN" for protected routes.

Post Model

Field	Type	Notes
`id`	String (cuid)	Primary key
`title`	String	Post title
`slug`	String	Unique, URL-friendly identifier
`excerpt`	String	Short summary for listings
`content`	String	Full content (plain text or HTML)
`category`	String	Category for filtering (e.g. "Javascript", "CSS")
`author`	String	Author name (display)
`coverImage`	String?	Optional cover image URL
`published`	Boolean	Published status (default false)
`publishedAt`	DateTime?	Publication date
`userId`	String?	FK to User (optional)

Content format: The content field supports both plain text (paragraphs separated by \n\n) and HTML. When HTML is detected (<[a-z] regex), the RichContent component renders it with syntax highlighting and sanitization.

Course Model

Field	Type	Notes
`id`	String (cuid)	Primary key
`title`	String	Course title
`description`	String	Full description
`instructor`	String	Instructor name
`duration`	String	e.g. "4 Months"
`price`	Float	Price (0 for free courses)
`originalPrice`	Float?	Strikethrough price (optional)
`startDate`	String?	Start date text
`schedule`	String?	Schedule text
`image`	String?	Course image URL
`registrationLink`	String?	External registration URL
`published`	Boolean	Published status (default true)

Contact Model

Field	Type	Notes
`id`	String (cuid)	Primary key
`name`	String	Submitter's name
`email`	String	Submitter's email
`subject`	String	Message subject
`message`	String	Full message text
`isRead`	Boolean	Read status (default false)
`createdAt`	DateTime	Submission timestamp

Prisma Client Singleton

To prevent multiple Prisma Client instances in Next.js development (hot reload), the client is exported as a singleton from src/lib/prisma.ts:

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

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

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

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

All database access imports prisma from this file.

API Routes

CRUD operations are exposed as Next.js API routes under src/app/api/:

Endpoint	Methods	Description
`/api/posts`	GET, POST	List all posts / create a post
`/api/posts/[id]`	GET, PUT, DELETE	Read / update / delete a post
`/api/courses`	GET, POST	List all courses / create a course
`/api/courses/[id]`	GET, PUT, DELETE	Read / update / delete a course
`/api/contact`	GET, POST	List contacts / submit contact form
`/api/auth/[...nextauth]`	GET, POST	NextAuth.js handler

POST/PUT routes validate input with Zod schemas before database writes. Validation errors return 400 with error details.

Server Component Queries

Public pages query the database directly in Server Components without an API round-trip:

// src/app/posts/page.tsx (Server Component)
import { prisma } from "@/lib/prisma";

async function getPosts() {
  try {
    return await prisma.post.findMany({
      where: { published: true },
      orderBy: { publishedAt: "desc" },
    });
  } catch {
    return []; // fallback: hardcoded data
  }
}

Each public page includes a try/catch with static fallback data so the site renders even without a database connection.

Seed Script

The seed script at prisma/seed.ts uses upsert for idempotent seeding. It creates:

1 admin user (from env vars)
6 blog posts across 3 categories
5 training courses with pricing

npm run db:seed   # Runs: tsx prisma/seed.ts