Prisma ORM

Prisma is an ORM for Node.js/TypeScript that helps you work with a SQL database (often Postgres) using generated, typed queries.

What Prisma Gives You

• A schema file (schema.prisma) where you define models
• Migrations generated from schema changes
• A generated Prisma Client you use in code

Example: Prisma Schema (Postgres)

schema.prisma

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

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

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

model Post {
  id     Int    @id @default(autoincrement())
  title  String
  body   String
  userId Int
  user   User   @relation(fields: [userId], references: [id], onDelete: Cascade)
}

Example: Basic Prisma Queries

const users = await prisma.user.findMany();

const user = await prisma.user.findUnique({ where: { email: 'a@b.com' } });

const posts = await prisma.post.findMany({
  where: { userId: 123 },
  orderBy: { id: 'desc' },
  take: 10,
});

Raw SQL vs Prisma (Same Idea, Two Approaches)

Even if you use Prisma, you should be able to read SQL.

Example: “Get the latest 20 posts with author name”

Raw SQL:

SELECT p.id, p.title, u.name AS author
FROM posts p
JOIN users u ON u.id = p.user_id
ORDER BY p.id DESC
LIMIT 20;

Prisma:

const posts = await prisma.post.findMany({
  orderBy: { id: 'desc' },
  take: 20,
  include: { user: { select: { name: true } } },
});

When You Still Use Raw SQL

• Complex reporting queries
• Vendor-specific features (Postgres jsonb, full-text search, advanced aggregations)
• Performance tuning (custom SQL + careful indexing)

Prisma supports parameterized raw SQL (safe against SQL injection):

const userId = 123;
const posts = await prisma.$queryRaw`
  SELECT id, title
  FROM posts
  WHERE user_id = ${userId}
  ORDER BY id DESC
  LIMIT 10
`;

Express Example: Prisma vs Raw SQL

Two endpoints returning the same kind of data:

• one uses Prisma ORM queries
• one uses a raw SQL query

Setup

import express from 'express';
import { PrismaClient } from '@prisma/client';

const app = express();
const prisma = new PrismaClient();

app.use(express.json());

Endpoint 1: Prisma ORM Query

app.get('/api/users/:id/posts', async (req, res) => {
  const userId = Number(req.params.id);
  if (Number.isNaN(userId))
    return res.status(400).json({ error: 'Invalid user id' });

  const posts = await prisma.post.findMany({
    where: { userId },
    orderBy: { id: 'desc' },
    take: 10,
    select: { id: true, title: true },
  });

  res.json({ posts });
});

Endpoint 2: Raw SQL Query (Same Result)

app.get('/api/users/:id/posts-raw', async (req, res) => {
  const userId = Number(req.params.id);
  if (Number.isNaN(userId))
    return res.status(400).json({ error: 'Invalid user id' });

  const rows = await prisma.$queryRaw`
    SELECT id, title
    FROM posts
    WHERE user_id = ${userId}
    ORDER BY id DESC
    LIMIT 10
  `;

  res.json({ posts: rows });
});

Prisma Migrations (Typical Workflow)

• Edit schema.prisma
• Run a migration to update your database schema
• Prisma generates migration files and updates the client

References

• Prisma ORM