Prisma & PostgreSQL — Practical Guide
A concise, dark-themed TSX article showing setup, CRUD, transactions and best practices.
1. Installation & Setup
Install Prisma CLI and the client, then initialize the project. Keep sensitive details in .env and never commit them.
Terminal
"color:#FBBF24;font-weight:600">npm install "color:#34D399;font-weight:600">prisma "color:#FBBF24;font-weight:600">--save-dev
"color:#FBBF24;font-weight:600">npm install @"color:#34D399;font-weight:600">prisma/client.env
DATABASE_URL="postgresql://username:password@localhost:5432/mydb?schema=public"2. Schema & Models
Your schema.prisma defines generators, datasource and models. Use Prisma's expressive model syntax to represent relations and constraints.
schema.prisma
"color:#F472B6;font-weight:600">generator client {
"color:#F472B6;font-weight:600">provider = ""color:#34D399;font-weight:600">prisma-client-js"
}
"color:#F472B6;font-weight:600">datasource db {
"color:#F472B6;font-weight:600">provider = "postgresql"
url = "color:#F472B6;font-weight:600">env("DATABASE_URL")
}
"color:#F472B6;font-weight:600">model User {
id Int @id @default(autoincrement())
email String @unique
name String?
posts Post[]
createdAt "color:#F472B6;font-weight:600">DateTime @default(now())
updatedAt "color:#F472B6;font-weight:600">DateTime @updatedAt
}
"color:#F472B6;font-weight:600">model Post {
id Int @id @default(autoincrement())
title String
content String?
published Boolean @default(false)
author User @relation(fields: [authorId], references: [id])
authorId Int
createdAt "color:#F472B6;font-weight:600">DateTime @default(now())
tags String[]
}
"color:#F472B6;font-weight:600">model Profile {
id Int @id @default(autoincrement())
bio String?
user User @relation(fields: [userId], references: [id])
userId Int @unique
}3. Migrate & Generate
Create a migration and generate the client. Migrations are your source of truth for schema changes.
Commands
"color:#FBBF24;font-weight:600">npx "color:#34D399;font-weight:600">prisma migrate dev "color:#FBBF24;font-weight:600">--name init
"color:#FBBF24;font-weight:600">npx "color:#34D399;font-weight:600">prisma generate4. Basic CRUD
Use a singleton Prisma client to avoid creating many connections in development servers.
lib/prisma.ts
"color:#60A5FA;font-weight:600">import { "color:#34D399;font-weight:600">PrismaClient } from '@"color:#34D399;font-weight:600">prisma/client'
"color:#60A5FA;font-weight:600">const globalForPrisma = global as unknown as { "color:#34D399;font-weight:600">prisma: "color:#34D399;font-weight:600">PrismaClient }
"color:#60A5FA;font-weight:600">export "color:#60A5FA;font-weight:600">const "color:#34D399;font-weight:600">prisma = globalForPrisma."color:#34D399;font-weight:600">prisma || "color:#60A5FA;font-weight:600">new "color:#34D399;font-weight:600">PrismaClient()
if (process."color:#F472B6;font-weight:600">env.NODE_ENV !== 'production') globalForPrisma."color:#34D399;font-weight:600">prisma = "color:#34D399;font-weight:600">prismaExample: Create a User with Posts
This example demonstrates nested writes and returning related records via include.
Create (TypeScript)
"color:#60A5FA;font-weight:600">const user = "color:#60A5FA;font-weight:600">await "color:#34D399;font-weight:600">prisma.user.create({
data: {
email: 'alice@"color:#34D399;font-weight:600">prisma.io',
name: 'Alice',
posts: {
create: [
{ title: 'Hello World', content: 'Welcome to my blog' },
{ title: 'Prisma is awesome', content: 'Learn about Prisma' }
]
}
},
include: { posts: true }
})Example: Read & Paginate Posts
A common pattern: filter, paginate and sort results.
Posts Query
"color:#60A5FA;font-weight:600">const posts = "color:#60A5FA;font-weight:600">await "color:#34D399;font-weight:600">prisma.post.findMany({
where: {
title: { contains: 'Prisma' },
published: true
},
skip: 0,
take: 10,
orderBy: { createdAt: 'desc' }
})5. Advanced Features
Prisma offers transactions, raw SQL and aggregations. Transactions keep multiple related writes atomic.
Transactions (concept)
const result = await prisma.$transaction(async (tx) => {
const user = await tx.user.create({ data: { email: 'user@example.com', name: 'User' } })
const post = await tx.post.create({ data: { title: 'Transaction Post', authorId: user.id } })
return { user, post }
})For raw SQL, prefer parameterized queries to avoid injection risks.
6. Best Practices
- Use a singleton Prisma Client to prevent excessive DB connections.
- Handle known Prisma errors (example: P2002 unique constraint).
- Log and monitor slow queries via middleware.
Error Handling
"color:#60A5FA;font-weight:600">try {
"color:#60A5FA;font-weight:600">const user = "color:#60A5FA;font-weight:600">await "color:#34D399;font-weight:600">prisma.user.create({ data: { email: 'existing@email.com' } })
} "color:#60A5FA;font-weight:600">catch (error) {
if (error instanceof Prisma.PrismaClientKnownRequestError) {
if (error.code === 'P2002') {
console.log('Unique constraint violation')
}
}
throw error
}Middleware
"color:#34D399;font-weight:600">prisma.$use("color:#60A5FA;font-weight:600">async (params, next) => {
"color:#60A5FA;font-weight:600">const before = Date.now()
"color:#60A5FA;font-weight:600">const result = "color:#60A5FA;font-weight:600">await next(params)
"color:#60A5FA;font-weight:600">const after = Date.now()
console.log(`Query took ${after - before}ms`)
"color:#60A5FA;font-weight:600">return result
})7. Useful Commands
Cheat Sheet
# Generate Prisma Client
"color:#FBBF24;font-weight:600">npx "color:#34D399;font-weight:600">prisma generate
# Create migration
"color:#FBBF24;font-weight:600">npx "color:#34D399;font-weight:600">prisma migrate dev "color:#FBBF24;font-weight:600">--name add_feature
# Reset database
"color:#FBBF24;font-weight:600">npx "color:#34D399;font-weight:600">prisma migrate reset
# View database in browser
"color:#FBBF24;font-weight:600">npx "color:#34D399;font-weight:600">prisma studio
# Check schema
"color:#FBBF24;font-weight:600">npx "color:#34D399;font-weight:600">prisma validate
# Format schema
"color:#FBBF24;font-weight:600">npx "color:#34D399;font-weight:600">prisma format