Prisma Intro¶

ORMs get a bad reputation and most of it is deserved TypeORM makes you write decorators that look like a Java creepypasta , Sequelize has more gotchas than a trap-laden dungeon , and Knex is just SQL with extra steps. Prisma though? Prisma is the first ORM that doesn't make you want to drop-kick your laptop across the room because it generates a type-safe client from your schema definition , gives you autocomplete in your editor that actually works , and produces queries that don't make your DBA cry

What Prisma actually does¶

Prisma is three tools in one: * Prisma Schema - a declarative DSL where you define your database models, relations, indexes, and constraints in a single schema.prisma file * Prisma Migrate - generates and runs database migrations from your schema changes * Prisma Client - a type-safe auto-generated query builder that maps your schema to JavaScript/TypeScript objects with full IntelliSense and compile-time type checking

The key difference from other ORMs: Prisma doesn't use the active record pattern where models are objects that both represent data AND handle persistence. Prisma separates the data layer (Client) from business logic, giving you cleaner separation and avoiding the "model with 50 methods" antipattern

Schema definition¶

// schema.prisma - the single source of truth for your database

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

datasource db {
    provider = "postgresql"      // or "mysql", "sqlite", "mongodb", "sqlserver"
    url      = env("DATABASE_URL")  // Load from environment variable
}

model User {
    id        String   @id @default(uuid())  // UUID primary key
    email     String   @unique               // Unique constraint
    username  String   @map("user_name")     // Map to different column name in DB
    passwordHash String @map("password_hash")
    role      String   @default("user")
    isActive  Boolean  @default(true) @map("is_active")
    createdAt DateTime @default(now()) @map("created_at")
    updatedAt DateTime @updatedAt @map("updated_at")

    // Relations
    posts     Post[]
    profile   Profile?

    // Indexes
    @@index([role, isActive])  // Composite index
    @@map("users")             // Table name in database
}

model Post {
    id        String   @id @default(uuid())
    title     String
    content   String
    published Boolean  @default(false)
    authorId  String   @map("author_id")
    author    User     @relation(fields: [authorId], references: [id])
    createdAt DateTime @default(now()) @map("created_at")

    @@index([authorId])
    @@map("posts")
}

model Profile {
    id     String @id @default(uuid())
    bio    String
    avatar String?
    userId String @unique @map("user_id")
    user   User   @relation(fields: [userId], references: [id])

    @@map("profiles")
}

Relations in Prisma¶

Prisma supports all standard database relations with clear syntax:

One-to-One: User has one Profile, Profile belongs to one User - defined by @unique on the foreign key

One-to-Many: User has many Posts, Post belongs to one User - the most common relation

Many-to-Many: Post has many Tags, Tag has many Posts - requires an implicit or explicit join table

model Tag {
    id    String @id @default(uuid())
    name  String @unique
    posts PostTag[]

    @@map("tags")
}

model PostTag {
    postId String @map("post_id")
    tagId  String @map("tag_id")
    post   Post   @relation(fields: [postId], references: [id])
    tag    Tag    @relation(fields: [tagId], references: [id])

    @@id([postId, tagId])  // Composite primary key
    @@map("post_tags")
}

Or use Prisma's implicit many-to-many (auto-generated join table):

model Post {
    id   String @id @default(uuid())
    tags Tag[]  // No explicit join model needed
}

model Tag {
    id    String @id @default(uuid())
    posts Post[]
}

Migrations¶

# Create a migration from schema changes
npx prisma migrate dev --name add_user_table

# Apply migrations to production
npx prisma migrate deploy

# Reset database (dev only - drops and recreates)
npx prisma migrate reset

# Generate Prisma Client after schema changes
npx prisma generate

Each migration creates a timestamped SQL file in prisma/migrations/ that you can review, edit, and commit to version control. This gives you full control over the generated SQL while Prisma handles the boilerplate

Prisma Client CRUD¶

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

Create¶

// Create a single record
const user = await prisma.user.create({
    data: {
        email: 'omar@example.com',
        username: 'omar_hacker',
        passwordHash: hashedPassword,
        role: 'user'
    }
});

// Create with related records (nested create)
const userWithProfile = await prisma.user.create({
    data: {
        email: 'ali@example.com',
        username: 'ali_sec',
        passwordHash: hashedPassword,
        profile: {
            create: {
                bio: 'Security researcher and coffee enthusiast',
                avatar: 'https://cdn.example.com/ali.jpg'
            }
        },
        posts: {
            create: [
                { title: 'First Post', content: 'Hello world' },
                { title: 'Second Post', content: 'Security tips' }
            ]
        }
    },
    include: {
        profile: true,
        posts: true
    }
});

Read¶

// Find by unique field
const user = await prisma.user.findUnique({
    where: { email: 'omar@example.com' }
});

// Find first matching
const admin = await prisma.user.findFirst({
    where: { role: 'admin' },
    orderBy: { createdAt: 'desc' }
});

// Find many with filters
const activeUsers = await prisma.user.findMany({
    where: {
        isActive: true,
        role: { in: ['user', 'admin'] },
        email: { contains: '@example.com' }
    },
    select: {
        id: true,
        email: true,
        username: true,
        posts: {
            where: { published: true },
            select: { title: true, createdAt: true }
        }
    },
    orderBy: { createdAt: 'desc' },
    take: 20,
    skip: 0
});

Update¶

// Update single record
const updated = await prisma.user.update({
    where: { email: 'omar@example.com' },
    data: {
        username: 'omar_new',
        role: 'admin'
    }
});

// Update many records
const result = await prisma.user.updateMany({
    where: {
        lastLoginAt: { lt: new Date('2024-01-01') }
    },
    data: {
        isActive: false
    }
});
// result.count - number of records updated

Delete¶

// Delete single record
await prisma.user.delete({
    where: { email: 'spam@example.com' }
});

// Delete many records
await prisma.user.deleteMany({
    where: {
        isActive: false,
        posts: { none: {} }  // Users with no posts
    }
});

Prisma Studio - visual database browser¶

npx prisma studio
# Opens http://localhost:5555 - a visual editor for your database

Prisma Studio lets you browse, filter, create, edit, and delete records through a web UI without writing SQL. It's fantastic for debugging and quick data fixes during development but remember: it connects directly to your database with full read/write access - never expose it in production

Security - type safety is a security feature¶

Prisma's main security advantage is type safety: because the Client is generated from your schema, you can't accidentally pass a number where a string is expected, you can't misspell a field name, and you certainly can't concatenate user input into a SQL string because Prisma handles all query parameterization automatically

// SAFE - Prisma parameterizes everything
// User input is always treated as data, never as SQL
const user = await prisma.user.findUnique({
    where: { email: req.body.email }  // req.body.email is safely parameterized
});

// The generated SQL will always be:
// SELECT * FROM users WHERE email = $1
// Even if req.body.email = "'; DROP TABLE users; --"
// It's treated as a literal string value

But Prisma is not a magic shield against all database vulnerabilities: * Prisma can't prevent NoSQL injection if you use MongoDB provider with raw queries * Prisma's raw query API ($queryRaw, $executeRaw) can still be vulnerable if you interpolate user input * Prisma doesn't implement row-level security - you still need proper authorization checks * Prisma doesn't prevent mass assignment (use select or include to limit returned fields)

prerequisites¶

db_05_postgres_security.md - Prisma works with Postgres, SQLite, MySQL, and MongoDB. Understanding the underlying database helps you understand what Prisma is doing for you (and what it's hiding)

next → db_15_prisma_advanced.md