Prisma Development Guide

PostgreSQL + Prisma Database Design and Operations

Schema Design · Common Operations · Best Practices

🎯 Overview

NextJS Base uses PostgreSQL as the database and Prisma as the ORM. This guide explains how to design data models and perform database operations.

Tech Stack

Component	Version	Description
PostgreSQL	16+	Relational database
Prisma	5.x	Type-safe ORM
Prisma Client	Auto-generated	Database client

📐 Schema Design

File Location

prisma/
└── schema.prisma    # Data model definition

Basic Structure

// Generator Configuration
generator client {
  provider = "prisma-client-js"
  output   = "../lib/generated/prisma"
}

// Data Source Configuration
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

// Data Models
model User {
  id    String @id @default(cuid())
  email String @unique
  name  String?
  // ...
}

Common Field Types

Prisma Type	PostgreSQL Type	Description
`String`	`TEXT`	Text
`Int`	`INTEGER`	Integer
`Float`	`DOUBLE PRECISION`	Float
`Decimal`	`DECIMAL`	Precise decimal
`Boolean`	`BOOLEAN`	Boolean
`DateTime`	`TIMESTAMP`	DateTime
`Json`	`JSONB`	JSON data
`String[]`	`TEXT[]`	String array

Common Attributes

model Example {
  // Primary Key
  id        String    @id @default(cuid())
  
  // Unique Constraint
  email     String    @unique
  
  // Default Value
  enable    Boolean   @default(true)
  sort      Int       @default(0)
  
  // Optional Field
  remark    String?
  
  // Array Field
  tags      String[]  @default([])
  
  // Long Text
  content   String    @db.Text
  
  // Precise Decimal
  price     Decimal   @db.Decimal(10, 2)
  
  // Timestamps
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  
  // Soft Delete
  deletedAt DateTime?
  
  // Indexes
  @@index([createdAt])
  @@index([enable, sort])
}

📝 Model Design Examples

Example: Post Model

model Post {
  id          String    @id @default(cuid())
  
  // Basic Info
  title       String                        // Title
  slug        String    @unique             // URL alias
  content     String    @db.Text            // Content (Markdown)
  excerpt     String?                       // Excerpt
  cover       String?                       // Cover image
  
  // Category and Tags
  categoryId  String?                       // Category ID
  tags        String[]  @default([])        // Tag array
  
  // Status
  status      String    @default("draft")   // draft/published/archived
  enable      Boolean   @default(true)
  
  // Statistics
  viewCount   Int       @default(0)
  likeCount   Int       @default(0)
  
  // Author
  authorId    String
  
  // SEO
  metaTitle       String?
  metaDescription String?
  metaKeywords    String?
  
  // Time
  publishedAt DateTime?
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt
  deletedAt   DateTime?
  
  // Indexes
  @@index([status, enable])
  @@index([authorId])
  @@index([categoryId])
  @@index([publishedAt])
}

Example: Category Model (Tree Structure)

model Category {
  id        String    @id @default(cuid())
  
  name      String                        // Category name
  slug      String    @unique             // URL alias
  parentId  String?                       // Parent ID (supports tree)
  
  icon      String?                       // Icon
  cover     String?                       // Cover
  
  sort      Int       @default(0)
  enable    Boolean   @default(true)
  remark    String?
  
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  
  @@index([parentId])
  @@index([enable, sort])
}

🔧 Common Operations

Database Migration

# Create migration (development)
npx prisma migrate dev --name add_post_model

# Apply migration (production)
npx prisma migrate deploy

# Reset database (will delete all data)
npx prisma migrate reset

# Generate Prisma Client
npx prisma generate

Basic Queries

import { prisma } from '@/lib/database/prisma'

// Query Single
const user = await prisma.user.findUnique({
  where: { id: 'xxx' }
})

// Query Single (Multiple Conditions)
const user = await prisma.user.findFirst({
  where: { email: '[email protected]', enable: true }
})

// Query List
const users = await prisma.user.findMany({
  where: { enable: true },
  orderBy: { createdAt: 'desc' },
  take: 10,
  skip: 0,
})

// Query Count
const count = await prisma.user.count({
  where: { enable: true }
})

Create and Update

// Create Single
const user = await prisma.user.create({
  data: {
    email: '[email protected]',
    name: 'Test User',
  }
})

// Create Multiple
const users = await prisma.user.createMany({
  data: [
    { email: '[email protected]', name: 'User 1' },
    { email: '[email protected]', name: 'User 2' },
  ]
})

// Update Single
const user = await prisma.user.update({
  where: { id: 'xxx' },
  data: { name: 'New Name' }
})

// Update Multiple
const result = await prisma.user.updateMany({
  where: { enable: false },
  data: { enable: true }
})

Delete Operations

// Delete Single
await prisma.user.delete({
  where: { id: 'xxx' }
})

// Delete Multiple
await prisma.user.deleteMany({
  where: { enable: false }
})

// Soft Delete (Recommended)
await prisma.user.update({
  where: { id: 'xxx' },
  data: { deletedAt: new Date() }
})

Advanced Queries

// Fuzzy Search
const users = await prisma.user.findMany({
  where: {
    name: { contains: 'John' }
  }
})

// Range Query
const posts = await prisma.post.findMany({
  where: {
    createdAt: {
      gte: new Date('2024-01-01'),
      lte: new Date('2024-12-31'),
    }
  }
})

// Array Contains
const posts = await prisma.post.findMany({
  where: {
    tags: { has: 'javascript' }
  }
})

// OR Condition
const users = await prisma.user.findMany({
  where: {
    OR: [
      { name: { contains: 'John' } },
      { email: { contains: 'john' } },
    ]
  }
})

// Relation Query
const posts = await prisma.post.findMany({
  include: {
    author: true,
    category: true,
  }
})

🏗️ Integration with BaseDAO

BaseDAO Configuration

const postConfig = {
  modelName: 'post',
  primaryKey: 'id',
  softDelete: true,
  
  fields: {
    creatable: ['title', 'slug', 'content', 'categoryId', 'tags', 'status'],
    updatable: ['title', 'slug', 'content', 'categoryId', 'tags', 'status'],
    searchable: ['title', 'content', 'tags'],
  },
  
  query: {
    defaultSort: { createdAt: 'desc' },
    defaultPageSize: 20,
  },
}

Search Condition Conversion

BaseDAO automatically converts whereJson to Prisma query conditions:

// whereJson from frontend
{
  title: { contains: 'Tutorial' },
  status: 'published',
  createdAt: {
    gte: '2024-01-01',
    lte: '2024-12-31'
  }
}

// Prisma where converted by BaseDAO
{
  AND: [
    { title: { contains: 'Tutorial' } },
    { status: 'published' },
    { createdAt: { gte: new Date('2024-01-01'), lte: new Date('2024-12-31') } },
    { deletedAt: null }  // Soft delete filter
  ]
}

✅ Best Practices

1. Naming Conventions

Type	Convention	Example
Model Name	PascalCase, singular	`User`, `Post`, `Category`
Field Name	camelCase	`createdAt`, `userId`, `isPublished`
Table Name	Auto-converted to snake_case	`users`, `posts`, `categories`

2. Required Fields

model Example {
  id        String    @id @default(cuid())  // Primary key
  createdAt DateTime  @default(now())       // Created time
  updatedAt DateTime  @updatedAt            // Updated time
  deletedAt DateTime?                       // Soft delete (recommended)
}

3. Use Indexes

model Post {
  // ... field definitions
  
  // Single Field Index
  @@index([createdAt])
  
  // Composite Index (common query conditions)
  @@index([status, enable, createdAt])
  
  // Foreign Key Index
  @@index([authorId])
  @@index([categoryId])
}

4. Soft Delete

model Post {
  // ...
  deletedAt DateTime?  // Soft delete field
}

// BaseDAO automatically handles soft delete
const postConfig = {
  softDelete: true,  // Enable soft delete
  // ...
}

// Automatically filter deleted records when querying
// Automatically set deletedAt instead of physical delete when deleting

5. Array Fields

model Post {
  tags String[] @default([])  // Tag array
}

// Query posts containing specific tag
await prisma.post.findMany({
  where: {
    tags: { has: 'javascript' }
  }
})

// Query posts containing multiple tags
await prisma.post.findMany({
  where: {
    tags: { hasEvery: ['javascript', 'react'] }
  }
})

Prisma Official Documentation

Deep dive into Prisma models and query capabilities

BaseDAO API

Abstraction layer built on database operations

Server Actions Development

Action encapsulation combined with database operations

Database Design Template

Standardized database design and naming conventions

SmartCrudPage Guide

End-to-end connection from model to interface

Prisma Official Documentation

BaseDAO API

Server Actions Development

Database Design Template

SmartCrudPage Guide

On this page