> ## Documentation Index
> Fetch the complete documentation index at: https://resources.devweekends.com/llms.txt
> Use this file to discover all available pages before exploring further.

# 13. PostgreSQL & Prisma ORM

> Connect Node.js to PostgreSQL using Prisma ORM.

# 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

1. Install Prisma CLI as a dev dependency:
   ```bash theme={null}
   npm install prisma --save-dev
   ```

2. Initialize Prisma:
   ```bash theme={null}
   npx prisma init
   ```
   This creates a `prisma` directory with `schema.prisma` and creates a `.env` file.

3. 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.

```prisma theme={null}
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.

```bash theme={null}
# '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:

```bash theme={null}
npm install @prisma/client
```

Use it in your code:

```javascript theme={null}
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
}
```

<Warning>
  **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).
</Warning>

## CRUD Operations

### Create

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

### Read

```javascript theme={null}
// 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

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

### Delete

```javascript theme={null}
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 theme={null}
// 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

```javascript theme={null}
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

```javascript theme={null}
// 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.

```javascript theme={null}
// 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:

```bash theme={null}
npx prisma studio
```

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

## Best Practices

```javascript theme={null}
// 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();
});
```

<Tip>
  **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
</Tip>