PostgreSQLPrismaTypeScriptDatabase

Designing Scalable PostgreSQL Schemas with Prisma ORM in TypeScript

การออกแบบ Database Schema ด้วย Prisma และ PostgreSQL ให้รองรับการขยายตัว

Author: Patipan Plonying···5 min read

Single Source of Truth Schema Modeling with Compile-Time Safety

Pairing Prisma ORM with PostgreSQL in a TypeScript codebase turns your database schema into a single source of truth that automatically generates strict TypeScript types across your backend—eliminating typos in column names and mismatched payload types.

In production systems built by Patipan Plonying, every core table establishes explicit audit timestamps (`createdAt`, `updatedAt`), bounded status Enums, and composite B-tree indexes aligned with real-world `WHERE` and `ORDER BY` query patterns.

prisma/schema.prismaprisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

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

enum InquiryStatus {
  PENDING
  REPLIED
  ARCHIVED
}

model ContactInquiry {
  id        String        @id @default(cuid())
  name      String
  email     String
  message   String        @db.Text
  status    InquiryStatus @default(PENDING)
  createdAt DateTime      @default(now()) @map("created_at")
  updatedAt DateTime      @updatedAt @map("updated_at")

  @@index([status, createdAt(sort: Desc)])
  @@map("contact_inquiries")
}

Preventing Connection Pool Exhaustion in Next.js

During Next.js development, Hot Module Replacement (HMR) re-imports server modules on file saves, which can inadvertently instantiate multiple `PrismaClient` pools and exhaust PostgreSQL connection limits. Attaching a singleton instance to `globalThis` prevents connection leaks cleanly.

src/lib/prisma.tstypescript
import { PrismaClient } from "@prisma/client";

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

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

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