Database Patterns & Best Practices
Schema Design Principles
1. Normalization (3NF minimum)
- No repeating groups
- No partial dependencies
- No transitive dependencies
2. Use UUIDs vs Auto-Increment
sql
1-- GOOD: UUID for distributed systems
2id UUID PRIMARY KEY DEFAULT gen_random_uuid()
3
4-- OK: Auto-increment for simple cases
5id SERIAL PRIMARY KEY
3. Timestamps on Every Table
sql
1CREATE TABLE users (
2 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
3 name VARCHAR(100) NOT NULL,
4 email VARCHAR(255) UNIQUE NOT NULL,
5 created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
6 updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
7);
Prisma ORM Patterns
Schema Definition
prisma
1// prisma/schema.prisma
2generator client {
3 provider = "prisma-client-js"
4}
5
6datasource db {
7 provider = "postgresql"
8 url = env("DATABASE_URL")
9}
10
11model User {
12 id String @id @default(uuid())
13 email String @unique
14 name String
15 role Role @default(USER)
16 posts Post[]
17 profile Profile?
18 createdAt DateTime @default(now())
19 updatedAt DateTime @updatedAt
20
21 @@index([email])
22 @@map("users")
23}
24
25model Post {
26 id String @id @default(uuid())
27 title String
28 content String?
29 published Boolean @default(false)
30 author User @relation(fields: [authorId], references: [id])
31 authorId String
32 tags Tag[]
33 createdAt DateTime @default(now())
34 updatedAt DateTime @updatedAt
35
36 @@index([authorId])
37 @@index([published])
38 @@map("posts")
39}
40
41enum Role {
42 USER
43 ADMIN
44}
CRUD Operations
typescript
1import { PrismaClient } from '@prisma/client'
2
3const prisma = new PrismaClient()
4
5// Create
6async function createUser(data: { name: string; email: string }) {
7 return prisma.user.create({
8 data,
9 select: {
10 id: true,
11 name: true,
12 email: true,
13 createdAt: true
14 }
15 })
16}
17
18// Read with relations
19async function getUserWithPosts(id: string) {
20 return prisma.user.findUnique({
21 where: { id },
22 include: {
23 posts: {
24 where: { published: true },
25 orderBy: { createdAt: 'desc' },
26 take: 10
27 },
28 profile: true
29 }
30 })
31}
32
33// Update
34async function updateUser(id: string, data: Partial<User>) {
35 return prisma.user.update({
36 where: { id },
37 data
38 })
39}
40
41// Delete (soft delete pattern)
42async function deleteUser(id: string) {
43 return prisma.user.update({
44 where: { id },
45 data: { deletedAt: new Date() }
46 })
47}
Transactions
typescript
1// Interactive transaction
2async function transferFunds(fromId: string, toId: string, amount: number) {
3 return prisma.$transaction(async (tx) => {
4 const from = await tx.account.update({
5 where: { id: fromId },
6 data: { balance: { decrement: amount } }
7 })
8
9 if (from.balance < 0) {
10 throw new Error('Insufficient funds')
11 }
12
13 await tx.account.update({
14 where: { id: toId },
15 data: { balance: { increment: amount } }
16 })
17
18 return tx.transaction.create({
19 data: { fromId, toId, amount }
20 })
21 })
22}
23
24// Sequential transaction
25async function createUserWithProfile(data: CreateUserInput) {
26 return prisma.$transaction([
27 prisma.user.create({ data: data.user }),
28 prisma.profile.create({ data: data.profile })
29 ])
30}
typescript
1// Cursor-based (recommended for large datasets)
2async function getUsers(cursor?: string, limit = 20) {
3 const users = await prisma.user.findMany({
4 take: limit + 1,
5 cursor: cursor ? { id: cursor } : undefined,
6 orderBy: { createdAt: 'desc' },
7 skip: cursor ? 1 : 0 // Skip the cursor itself
8 })
9
10 const hasMore = users.length > limit
11 const data = hasMore ? users.slice(0, -1) : users
12
13 return {
14 data,
15 nextCursor: hasMore ? data[data.length - 1].id : null
16 }
17}
18
19// Offset-based
20async function getUsersWithOffset(page = 1, limit = 20) {
21 const [users, total] = await Promise.all([
22 prisma.user.findMany({
23 skip: (page - 1) * limit,
24 take: limit
25 }),
26 prisma.user.count()
27 ])
28
29 return {
30 data: users,
31 meta: { total, page, limit, totalPages: Math.ceil(total / limit) }
32 }
33}
Query Optimization
Avoid N+1 Queries
typescript
1// BAD: N+1 problem
2const users = await prisma.user.findMany()
3for (const user of users) {
4 const posts = await prisma.post.findMany({
5 where: { authorId: user.id }
6 })
7}
8
9// GOOD: Include in single query
10const users = await prisma.user.findMany({
11 include: { posts: true }
12})
13
14// GOOD: Select only needed fields
15const users = await prisma.user.findMany({
16 select: {
17 id: true,
18 name: true,
19 posts: {
20 select: {
21 id: true,
22 title: true
23 }
24 }
25 }
26})
Use Indexes Properly
prisma
1model Post {
2 id String @id
3 authorId String
4 status Status
5 createdAt DateTime
6
7 // Compound index for common query patterns
8 @@index([authorId, status])
9 @@index([status, createdAt])
10}
sql
1-- For frequently queried columns
2CREATE INDEX idx_posts_author_status ON posts(author_id, status);
3
4-- For text search
5CREATE INDEX idx_posts_title_gin ON posts USING gin(to_tsvector('english', title));
6
7-- For JSON columns
8CREATE INDEX idx_metadata_gin ON posts USING gin(metadata);
Batch Operations
typescript
1// Batch create
2await prisma.user.createMany({
3 data: users,
4 skipDuplicates: true
5})
6
7// Batch update with raw SQL (when needed)
8await prisma.$executeRaw`
9 UPDATE posts
10 SET status = 'archived'
11 WHERE created_at < NOW() - INTERVAL '1 year'
12`
13
14// Batch delete
15await prisma.user.deleteMany({
16 where: {
17 deletedAt: { not: null },
18 deletedAt: { lt: thirtyDaysAgo }
19 }
20})
Migration Patterns
Prisma Migrations
bash
1# Create migration
2npx prisma migrate dev --name add_user_role
3
4# Apply in production
5npx prisma migrate deploy
6
7# Reset database (dev only)
8npx prisma migrate reset
Safe Schema Changes
typescript
1// Step 1: Add nullable column
2model User {
3 newField String? // nullable first
4}
5
6// Step 2: Backfill data
7await prisma.$executeRaw`
8 UPDATE users SET new_field = 'default' WHERE new_field IS NULL
9`
10
11// Step 3: Make non-nullable
12model User {
13 newField String @default("default")
14}
Rollback Strategy
sql
1-- Always create down migrations
2-- up.sql
3ALTER TABLE users ADD COLUMN phone VARCHAR(20);
4
5-- down.sql
6ALTER TABLE users DROP COLUMN phone;
Connection Management
Connection Pooling
typescript
1// Singleton pattern for Prisma
2const globalForPrisma = globalThis as unknown as {
3 prisma: PrismaClient | undefined
4}
5
6export const prisma = globalForPrisma.prisma ?? new PrismaClient({
7 log: ['error', 'warn'],
8 datasources: {
9 db: {
10 url: process.env.DATABASE_URL
11 }
12 }
13})
14
15if (process.env.NODE_ENV !== 'production') {
16 globalForPrisma.prisma = prisma
17}
Serverless Configuration
typescript
1// For serverless (Vercel, AWS Lambda)
2import { PrismaClient } from '@prisma/client'
3import { Pool } from '@neondatabase/serverless'
4import { PrismaNeon } from '@prisma/adapter-neon'
5
6const pool = new Pool({ connectionString: process.env.DATABASE_URL })
7const adapter = new PrismaNeon(pool)
8const prisma = new PrismaClient({ adapter })
Soft Delete Pattern
prisma
1model User {
2 id String @id @default(uuid())
3 email String @unique
4 deletedAt DateTime?
5
6 @@index([deletedAt])
7}
typescript
1// Middleware for automatic filtering
2prisma.$use(async (params, next) => {
3 if (params.model === 'User') {
4 if (params.action === 'findMany' || params.action === 'findFirst') {
5 params.args.where = {
6 ...params.args.where,
7 deletedAt: null
8 }
9 }
10 }
11 return next(params)
12})
13
14// Soft delete
15async function softDelete(id: string) {
16 return prisma.user.update({
17 where: { id },
18 data: { deletedAt: new Date() }
19 })
20}
21
22// Hard delete (permanent)
23async function hardDelete(id: string) {
24 return prisma.user.delete({ where: { id } })
25}
Audit Trail Pattern
prisma
1model AuditLog {
2 id String @id @default(uuid())
3 entityType String
4 entityId String
5 action String // CREATE, UPDATE, DELETE
6 changes Json?
7 userId String?
8 createdAt DateTime @default(now())
9
10 @@index([entityType, entityId])
11 @@index([userId])
12 @@index([createdAt])
13}
typescript
1// Middleware for automatic audit logging
2prisma.$use(async (params, next) => {
3 const result = await next(params)
4
5 if (['create', 'update', 'delete'].includes(params.action)) {
6 await prisma.auditLog.create({
7 data: {
8 entityType: params.model!,
9 entityId: result.id,
10 action: params.action.toUpperCase(),
11 changes: params.args.data,
12 userId: getCurrentUserId()
13 }
14 })
15 }
16
17 return result
18})
Multi-Tenant Pattern
prisma
1model Organization {
2 id String @id @default(uuid())
3 name String
4 users User[]
5 posts Post[]
6}
7
8model User {
9 id String @id @default(uuid())
10 organization Organization @relation(fields: [organizationId], references: [id])
11 organizationId String
12
13 @@index([organizationId])
14}
typescript
1// Row-level security with Prisma extension
2const prismaWithTenant = prisma.$extends({
3 query: {
4 $allModels: {
5 async $allOperations({ args, query, model }) {
6 const tenantId = getTenantId()
7
8 if (tenantId && hasTenantField(model)) {
9 args.where = { ...args.where, organizationId: tenantId }
10 }
11
12 return query(args)
13 }
14 }
15 }
16})
Raw SQL When Needed
typescript
1// Complex aggregations
2const stats = await prisma.$queryRaw<Stats[]>`
3 SELECT
4 DATE_TRUNC('day', created_at) as date,
5 COUNT(*) as count,
6 SUM(amount) as total
7 FROM transactions
8 WHERE created_at >= ${startDate}
9 GROUP BY DATE_TRUNC('day', created_at)
10 ORDER BY date DESC
11`
12
13// Full-text search
14const results = await prisma.$queryRaw<Post[]>`
15 SELECT * FROM posts
16 WHERE to_tsvector('english', title || ' ' || content)
17 @@ plainto_tsquery('english', ${searchTerm})
18 ORDER BY ts_rank(
19 to_tsvector('english', title || ' ' || content),
20 plainto_tsquery('english', ${searchTerm})
21 ) DESC
22 LIMIT ${limit}
23`
Checklist
