PostgreSQL & Prisma

PostgreSQL is a powerful, open-source relational database system known for its standards compliance, extensibility, and rock-solid data integrity. If MongoDB is a flexible filing cabinet, PostgreSQL is a meticulously designed spreadsheet where every column type, constraint, and relationship is enforced by the system itself. Prisma is a next-generation ORM (Object-Relational Mapper) that consists of three tools: the Prisma Client (a type-safe query builder auto-generated from your schema), Prisma Migrate (a migration system that keeps your database schema in sync with your code), and Prisma Studio (a visual database browser). Unlike traditional ORMs that map classes to tables, Prisma takes a schema-first approach — you define your data model in a .prisma file, and it generates everything else.

Setup

Install Prisma CLI as a dev dependency:
npm install prisma --save-dev
Initialize Prisma:
npx prisma init
This creates a prisma directory with schema.prisma and creates a .env file.

Update .env with your PostgreSQL connection string:

DATABASE_URL="postgresql://user:password@localhost:5432/mydb?schema=public"

Defining the Schema

Edit prisma/schema.prisma to define your data model.

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

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

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  posts     Post[]
  createdAt DateTime @default(now())
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
}

Migrations

Migrations are versioned SQL scripts that evolve your database schema over time. Think of them as git commits for your database structure — each migration captures the exact changes needed to go from one schema version to the next, and they can be replayed in order on any database to arrive at the same state.

# 'dev' is for development -- it creates the migration, applies it,
# and regenerates the Prisma Client in one step.
# The --name flag is a human-readable label for the migration.
npx prisma migrate dev --name init

# In production, use 'deploy' instead -- it only applies pending
# migrations without generating new ones or resetting data
npx prisma migrate deploy

This creates the SQL tables and generates the Prisma Client. Each migration lives as a .sql file inside prisma/migrations/, giving you a full audit trail of every schema change.

Using Prisma Client

Install the client:

npm install @prisma/client

Use it in your code:

const { PrismaClient } = require('@prisma/client');

// Create a single instance -- every method on prisma (prisma.user, prisma.post, etc.)
// is auto-generated from your schema.prisma file
const prisma = new PrismaClient();

async function main() {
  // ... database queries
}

Common mistake: Creating a new PrismaClient() on every request or in every file. Each instance opens its own connection pool, so spawning many instances will exhaust your database connections. Always use a singleton pattern (see Best Practices at the bottom of this chapter).

CRUD Operations

Create

const newUser = await prisma.user.create({
  data: {
    name: 'Alice',
    email: 'alice@prisma.io',
    posts: {
      create: { title: 'Hello World' },
    },
  },
});

Read

// Find all users
const users = await prisma.user.findMany({
  include: {
    posts: true, // Include relations
  },
});

// Find unique
const user = await prisma.user.findUnique({
  where: {
    email: 'alice@prisma.io',
  },
});

Update

const updatedUser = await prisma.user.update({
  where: { id: 1 },
  data: { name: 'Alice Wonderland' },
});

Delete

const deletedUser = await prisma.user.delete({
  where: { id: 1 },
});

Summary

Prisma provides a type-safe database client
schema.prisma is the single source of truth for your data model
Migrations keep your database schema in sync
Prisma Client’s auto-completion makes queries easy and less error-prone

Advanced Schema Features

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

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

enum Role {
  USER
  ADMIN
  MODERATOR
}

enum OrderStatus {
  PENDING
  PROCESSING
  SHIPPED
  DELIVERED
  CANCELLED
}

model User {
  id        Int       @id @default(autoincrement())
  email     String    @unique
  name      String?
  role      Role      @default(USER)
  posts     Post[]
  profile   Profile?
  orders    Order[]
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt

  @@index([email])
  @@map("users") // Custom table name
}

model Profile {
  id       Int     @id @default(autoincrement())
  bio      String?
  avatar   String?
  userId   Int     @unique
  user     User    @relation(fields: [userId], references: [id], onDelete: Cascade)
}

model Post {
  id          Int        @id @default(autoincrement())
  title       String
  content     String?
  published   Boolean    @default(false)
  authorId    Int
  author      User       @relation(fields: [authorId], references: [id])
  categories  Category[]
  createdAt   DateTime   @default(now())
  updatedAt   DateTime   @updatedAt

  @@index([authorId])
  @@fulltext([title, content])
}

model Category {
  id    Int    @id @default(autoincrement())
  name  String @unique
  posts Post[]
}

model Order {
  id        Int         @id @default(autoincrement())
  status    OrderStatus @default(PENDING)
  total     Decimal     @db.Decimal(10, 2)
  userId    Int
  user      User        @relation(fields: [userId], references: [id])
  items     OrderItem[]
  createdAt DateTime    @default(now())
}

model OrderItem {
  id        Int   @id @default(autoincrement())
  quantity  Int
  price     Decimal @db.Decimal(10, 2)
  productId Int
  orderId   Int
  order     Order @relation(fields: [orderId], references: [id], onDelete: Cascade)
}

Advanced Queries

const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

// Complex filtering
const users = await prisma.user.findMany({
  where: {
    AND: [
      { email: { contains: '@gmail.com' } },
      { role: 'USER' },
      {
        OR: [
          { name: { startsWith: 'A' } },
          { posts: { some: { published: true } } }
        ]
      }
    ]
  },
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: 'desc' },
      take: 5
    },
    profile: true
  },
  orderBy: { createdAt: 'desc' },
  skip: 0,
  take: 10
});

// Aggregations
const stats = await prisma.post.aggregate({
  _count: { id: true },
  _avg: { views: true },
  _sum: { views: true },
  where: { published: true }
});

// Group by
const postsByAuthor = await prisma.post.groupBy({
  by: ['authorId'],
  _count: { id: true },
  _sum: { views: true },
  having: {
    id: { _count: { gt: 5 } }
  },
  orderBy: {
    _count: { id: 'desc' }
  }
});

// Raw SQL when needed
const users = await prisma.$queryRaw`
  SELECT * FROM users
  WHERE created_at > ${new Date('2024-01-01')}
  ORDER BY created_at DESC
`;

Transactions

// Interactive transaction -- the callback receives a transaction client (tx)
// that you use instead of prisma. If ANY operation fails or you throw an error,
// ALL changes within this block are rolled back automatically.
const result = await prisma.$transaction(async (tx) => {
  // Decrease stock -- decrement is an atomic operation
  const product = await tx.product.update({
    where: { id: productId },
    data: { stock: { decrement: quantity } }
  });
  
  // Business logic check: if stock went negative, throw to trigger rollback.
  // The decrement already happened in the DB, but throwing here rolls it back.
  if (product.stock < 0) {
    throw new Error('Insufficient stock');
  }
  
  // Create order -- this only persists if we reach the end without errors
  const order = await tx.order.create({
    data: {
      userId,
      total: product.price * quantity,
      items: {
        create: {
          productId,
          quantity,
          price: product.price
        }
      }
    }
  });
  
  return order;
});

// Batch transaction
const [updatedUser, newPost] = await prisma.$transaction([
  prisma.user.update({
    where: { id: 1 },
    data: { postsCount: { increment: 1 } }
  }),
  prisma.post.create({
    data: { title: 'New Post', authorId: 1 }
  })
]);

Middleware

Prisma middleware intercepts every database operation before and after it executes. This is the same concept as Express middleware, but for database queries instead of HTTP requests. You can use them for logging, soft deletes, automatic auditing, or transforming results.

// Logging middleware -- wraps every query with timing information.
// Extremely useful for identifying slow queries during development.
prisma.$use(async (params, next) => {
  const before = Date.now();
  const result = await next(params); // Execute the actual query
  const after = Date.now();
  
  console.log(`${params.model}.${params.action} took ${after - before}ms`);
  return result;
});

// Soft delete middleware -- intercepts delete operations and converts
// them into updates that set a deletedAt timestamp instead of actually
// removing the row. This lets you "undelete" data later if needed.
prisma.$use(async (params, next) => {
  if (params.model === 'User') {
    if (params.action === 'delete') {
      // Rewrite the delete into an update
      params.action = 'update';
      params.args.data = { deletedAt: new Date() };
    }
    
    if (params.action === 'findMany') {
      // Automatically filter out soft-deleted records
      params.args.where = {
        ...params.args.where,
        deletedAt: null
      };
    }
  }
  return next(params);
});

Prisma Studio

Prisma provides a visual database browser:

npx prisma studio

This opens a web interface at http://localhost:5555 to browse and edit data.

Best Practices

// Singleton pattern for Prisma Client
// lib/prisma.js
//
// Why a singleton? In development, tools like ts-node-dev or nodemon
// restart your server frequently. Each restart creates a new PrismaClient,
// but the old one's connection pool may not be fully closed yet.
// After several restarts you can hit the database connection limit.
// Storing the instance on `global` survives hot-reloads.
const { PrismaClient } = require('@prisma/client');

let prisma;

if (process.env.NODE_ENV === 'production') {
  prisma = new PrismaClient();
} else {
  // Prevent multiple instances in development (hot-reload safe)
  if (!global.prisma) {
    global.prisma = new PrismaClient();
  }
  prisma = global.prisma;
}

module.exports = prisma;

// Graceful shutdown -- close the connection pool when the process exits
// to avoid leaving dangling connections on the database server
process.on('beforeExit', async () => {
  await prisma.$disconnect();
});

Prisma vs Mongoose:

Use Prisma for PostgreSQL/MySQL with TypeScript (better type safety)
Use Mongoose for MongoDB with flexible schemas
Both are excellent choices for their respective databases

​PostgreSQL & Prisma

​Setup

​Defining the Schema

​Migrations

​Using Prisma Client

​CRUD Operations

​Create

​Read

​Update

​Delete

​Summary

​Advanced Schema Features

​Advanced Queries

​Transactions

​Middleware

​Prisma Studio

​Best Practices

PostgreSQL & Prisma

Setup

Defining the Schema

Migrations

Using Prisma Client

CRUD Operations

Create

Read

Update

Delete

Summary

Advanced Schema Features

Advanced Queries

Transactions

Middleware

Prisma Studio

Best Practices