ORM with Prisma

Raw SQL helpers taught you what the database is doing. Prisma is the typed client FE brains usually like - schema in git, migrations, prisma.user.create(...) instead of stringly SQL.

When an ORM helps

Hand-rolled better-sqlite3 is great for learning. It gets noisy when you have:

• relations (user → notes → attachments)
• migrations across machines
• TypeScript types that should match the schema automatically

Prisma (or Drizzle) sits in that gap. Same Express routes - different data layer.

Mental map

Raw SQL	Prisma
CREATE TABLE users (...)	model User { ... } in schema.prisma
db.prepare('INSERT…').run(...)	prisma.user.create({ data })
SELECT … JOIN	include: { notes: true }
manual migrations	prisma migrate dev

FE analogy: Prisma Client ≈ a generated typed API client for your database - like OpenAPI codegen, but for tables.

Setup (SQLite, same spirit as before)

npm install prisma @prisma/client
npx prisma init --datasource-provider sqlite

prisma/schema.prisma sketch matching our auth arc:

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

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

model User {
  id               Int       @id @default(autoincrement())
  email            String    @unique
  passwordHash     String    @map("password_hash")
  emailVerifiedAt  DateTime? @map("email_verified_at")
  createdAt        DateTime  @default(now()) @map("created_at")
  notes            Note[]
  files            File[]
}

model Note {
  id        Int      @id @default(autoincrement())
  title     String
  body      String   @default("")
  createdAt DateTime @default(now()) @map("created_at")
  userId    Int      @map("user_id")
  user      User     @relation(fields: [userId], references: [id])
}

model File {
  id           Int      @id @default(autoincrement())
  originalName String   @map("original_name")
  storedName   String   @map("stored_name")
  mimeType     String   @map("mime_type")
  size         Int
  createdAt    DateTime @default(now()) @map("created_at")
  userId       Int      @map("user_id")
  user         User     @relation(fields: [userId], references: [id])
}

.env:

DATABASE_URL="file:./dev.db"

npx prisma migrate dev --name init
npx prisma generate

Client singleton

// src/prisma.ts
import { PrismaClient } from '@prisma/client'

export const prisma = new PrismaClient()

Don't new PrismaClient() in every route file - connection pool drama.

CRUD that replaces our helpers

import bcrypt from 'bcryptjs'
import { prisma } from './prisma.js'

export async function createUser(email: string, password: string) {
  const passwordHash = await bcrypt.hash(password, 10)
  return prisma.user.create({
    data: { email: email.toLowerCase(), passwordHash },
  })
}

export async function findUserByEmail(email: string) {
  return prisma.user.findUnique({ where: { email: email.toLowerCase() } })
}

export async function listNotesForUser(userId: number) {
  return prisma.note.findMany({
    where: { userId },
    orderBy: { id: 'desc' },
  })
}

export async function createNote(userId: number, title: string, body: string) {
  return prisma.note.create({
    data: { userId, title, body },
  })
}

Comments for the code above:

• Field names in TS are camelCase; @map keeps snake_case columns if you care
• findUnique / findMany / create / update / delete cover most Express CRUD
• Unique email violations throw - catch and map to HttpError(409, …) like before

Relations (the nice part)

const userWithNotes = await prisma.user.findUnique({
  where: { id: userId },
  include: { notes: true },
})
// userWithNotes.notes === Note[]

FE map: nested include ≈ GraphQL nesting or { notes: true } in a tRPC procedure - one round-trip, shaped JSON.

Watch N+1: looping findMany then per-row queries. Prefer include / select.

In an Express route

notesRouter.get('/', requireAuth, async (req: AuthedRequest, res, next) => {
  try {
    const notes = await prisma.note.findMany({
      where: { userId: req.user!.id },
      orderBy: { id: 'desc' },
    })
    res.json({ notes })
  } catch (err) {
    next(err)
  }
})

Same middleware, same JWT - only db.ts got replaced.

Companion sketch

See examples/prisma-sketch/ for a minimal Prisma + Express hello that lists/creates notes. Our main examples/notes-api stays on better-sqlite3 so the auth chapters don't require a generate step.

Prisma vs Drizzle (honest take)

	Prisma	Drizzle
Feel	Client API, migrations first-class	SQL-ish, lighter
Types	Generated heavily	Schema-as-TS
Learning curve	Friendly for FE	Friendly if you liked raw SQL

Either beats string-concat SQL. I reach for Prisma when the team wants "just ship the schema"; Drizzle when we want to stay closer to SQL.

What doesn't change

• Password hashing still yours
• JWT / sessions still yours
• Validation (Zod) still at the edge

ORM ≠ auth framework. 🙂

Next: cookie sessions - the other auth style we skipped during the JWT arc.

Best,

-- ł.