Skip to main content

​
PostgreSQL & Prisma

PostgreSQL is a powerful, open-source relational database system. Prisma is a next-generation ORM that consists of the Prisma Client, Prisma Migrate, and Prisma Studio.

​
Setup

  1. Install Prisma CLI as a dev dependency: 
    npm install prisma --save-dev
  2. Initialize Prisma: 
    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. 
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

To map your data model to the database schema, run a migration. 
npx prisma migrate dev --name init
This creates the SQL tables and generates the Prisma Client.

​
Using Prisma Client

Install the client: 
npm install @prisma/client
Use it in your code: 
const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

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

​
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
const result = await prisma.$transaction(async (tx) => {
  // Decrease stock
  const product = await tx.product.update({
    where: { id: productId },
    data: { stock: { decrement: quantity } }
  });
  
  if (product.stock < 0) {
    throw new Error('Insufficient stock');
  }
  
  // Create order
  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

// Logging middleware
prisma.$use(async (params, next) => {
  const before = Date.now();
  const result = await next(params);
  const after = Date.now();
  
  console.log(`${params.model}.${params.action} took ${after - before}ms`);
  return result;
});

// Soft delete middleware
prisma.$use(async (params, next) => {
  if (params.model === 'User') {
    if (params.action === 'delete') {
      params.action = 'update';
      params.args.data = { deletedAt: new Date() };
    }
    
    if (params.action === 'findMany') {
      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
const { PrismaClient } = require('@prisma/client');

let prisma;

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

module.exports = prisma;

// Graceful shutdown
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