From Raw SQL Strings to Type-Safe Queries: How I Learned Prisma ORM

I'm a web design agency CEO teaching myself full-stack development in public.
This is week 7 of a 16-week roadmap I built to go from React-only to full React + Express + PostgreSQL. Last week I finished Express core — routing, middleware, CORS, error handling, full CRUD with in-memory arrays. This week the arrays are gone. I'm talking to a real database now.

This post covers everything I learned about Prisma ORM — what it is, why it exists, how migrations work, how the client works, and how I wired it all into Express. I'll show you the actual code I wrote, the mistakes I made, and what clicked for me along the way.

The problem Prisma solves

Before I touch any Prisma code, I need to explain why it exists. Because if you don't understand the problem, the solution feels like unnecessary abstraction.

When you talk to PostgreSQL from Node.js without any tools, you write raw SQL as strings:

js
const result = await db.query(`SELECT * FROM projects WHERE client_id = ${clientId}`)

This works. But it has real problems that become painful at scale.
No type safety. You typed client_id — but what if the column is actually called clientId? You won't find out until runtime. Your editor has no idea what your database looks like, so it can't warn you. Every query is a guess.

No autocomplete. The result you get back is a plain JavaScript object. You're guessing the shape. Is it result.rows? result.data? Depends on the library. And the object itself carries no type information — autocomplete is completely dead.
No database history. You add a column in development, forget to add it in production, things break at the worst possible time. You need a way to track database structure changes the same way you track code changes — with version control.

Prisma solves all three. Type safety, autocomplete, and a migration system that versions your database schema like git versions your code.

What Prisma actually is

Prisma is an ORM — Object Relational Mapper. It sits between your Express app and your PostgreSQL database. Instead of writing SQL strings, you write JavaScript. Prisma translates that JavaScript into the correct SQL and executes it.
But it's not just a query translator. It has three distinct parts that work together:

1. Prisma Schema — a single file where you describe your entire database structure. Tables, columns, data types, relationships. Your entire database in one place.

2. Prisma Migrate — a CLI tool that reads your schema, compares it to the current state of your database, and generates the SQL needed to bring them in sync. Every change is saved as a migration file — a permanent record of how your database evolved.

3. Prisma Client — an auto-generated, fully typed JavaScript client. Once your schema is defined, Prisma generates a client that knows exactly what your database looks like. Your editor knows your tables, your columns, their types. Full autocomplete, full type safety.

Project setup

I'm using Node.js with CommonJS (require/module.exports), Express, and Supabase for my PostgreSQL database.

bash
mkdir prisma-project && cd prisma-project
npm init -y
npm install express dotenv cors
npm install prisma @prisma/client
npx prisma init

npx prisma init creates two things: a prisma/ folder with a schema.prisma file, and a .env file at the root with a DATABASE_URL placeholder.

I replaced that placeholder with my Supabase connection string from Settings → Database → Connection string → URI.

One issue I hit: I had a prisma.config.ts file auto-generated by Prisma 6 that was intercepting my .env loading. The error was: "Prisma config detected, skipping environment variable loading." Deleting prisma.config.ts fixed it immediately.

The Schema

Everything in Prisma starts with prisma/schema.prisma. This file is your single source of truth for the database.

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

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

model Client {
  id        Int       @id @default(autoincrement())
  name      String
  email     String    @unique
  createdAt DateTime  @default(now())
  projects  Project[]
}

model Project {
  id        Int      @id @default(autoincrement())
  title     String
  status    String   @default("active")
  createdAt DateTime @default(now())
  clientId  Int
  client    Client   @relation(fields: [clientId], references: [id])
}

Let me break this down piece by piece.
model maps to a database table. Each field inside is a column. The type after the field name (String, Int, DateTime) is Prisma's type system — it maps to the correct PostgreSQL type automatically.

The @ decorators are modifiers:

@id — marks this field as the primary key
@default(autoincrement()) — the database auto-generates an incrementing integer
@unique — no two rows can share this value
@default(now()) — auto-fills with the current timestamp on insert
@default("active") — sets a literal string default

The last two lines of the Project model are the foreign key relationship:

prisma
clientId  Int
client    Client   @relation(fields: [clientId], references: [id])

clientId is the actual column in the database — an integer that stores the id of the related client. The client field below it is not a column — it's a virtual relation field that tells Prisma how to do the join. fields: [clientId] is the foreign key on this model. references: [id] is the primary key it points to on Client.

The projects Project[] field on the Client model is the other side of the same relation — it tells Prisma that one client can have many projects. Again, not a column. Just a relation definition.

Migrations

Once the schema is written, I ran:

bash
npx prisma migrate dev --name init

Prisma reads the schema, generates the SQL to create both tables with the correct columns, constraints, and foreign keys, executes it against my Supabase database, and saves the generated SQL as a migration file in prisma/migrations/.

That migration file is committed to git alongside your code. Every subsequent schema change — adding a column, renaming a field, adding a new model — gets its own migration. You always have a full history of how your database structure evolved.
After the migration ran, both tables appeared in my Supabase dashboard with the correct structure.

The Prisma Client singleton

After migration, I ran npx prisma generate (migrate dev does this automatically). This generates the typed client based on my schema.

The critical thing about Prisma Client: it should be a singleton. Creating a new PrismaClient() in every file that needs database access will open a new connection pool each time, which exhausts your database connections quickly.

The solution is one file that creates the instance and exports it:

js
// lib/prisma.js
const { PrismaClient } = require("@prisma/client")
const prisma = new PrismaClient()

module.exports = prisma

Every route file imports from here. One instance, shared across the whole app.

The routes

My project structure:

prisma-project/
├── lib/
│   └── prisma.js
├── routes/
│   ├── clients.js
│   └── projects.js
└── index.js

clients.js

js
const prisma = require("../lib/prisma")
const express = require("express")
const router = express.Router()

router.post("/", async (req, res, next) => {
  try {
    const { name, email } = req.body
    const newClient = await prisma.client.create({
      data: { name, email }
    })
    res.status(201).json(newClient)
  } catch (err) {
    next(err)
  }
})

router.get("/", async (req, res, next) => {
  try {
    const clients = await prisma.client.findMany()
    res.json(clients)
  } catch (err) {
    next(err)
  }
})

module.exports = router

projects.js

js
const express = require("express")
const prisma = require("../lib/prisma")
const router = express.Router()

router.post("/", async (req, res, next) => {
  try {
    const { title, clientId } = req.body
    const newProject = await prisma.project.create({
      data: { title, clientId }
    })
    res.status(201).json(newProject)
  } catch (err) {
    next(err)
  }
})

router.get("/", async (req, res, next) => {
  try {
    const projects = await prisma.project.findMany({
      include: { client: true }
    })
    res.json(projects)
  } catch (err) {
    next(err)
  }
})

router.put("/:id", async (req, res, next) => {
  const { status } = req.body
  const projectId = Number(req.params.id)
  try {
    const updated = await prisma.project.update({
      where: { id: projectId },
      data: { status }
    })
    res.json(updated)
  } catch (err) {
    next(err)
  }
})

router.delete("/:id", async (req, res, next) => {
  const projectId = Number(req.params.id)
  try {
    await prisma.project.delete({
      where: { id: projectId }
    })
    res.json({ message: `Project ${projectId} deleted successfully` })
  } catch (err) {
    next(err)
  }
})

module.exports = router

A few things worth noting:
prisma.project.create takes a data object — the fields you want to write. Fields with defaults (status, createdAt) don't need to be included.

prisma.project.findMany with include: { client: true } performs a JOIN under the hood and returns the client object nested inside each project. One line of JavaScript, no SQL join syntax.

prisma.project.update and prisma.project.delete both take a where clause to identify which record to target. where: { id: projectId } maps to WHERE id = $1 in SQL.

req.params.id is always a string — I wrapped it with Number() before passing it to Prisma since the schema expects an Int.

index.js

js
require("dotenv").config()
const cors = require("cors")
const express = require("express")
const clientRouter = require("./routes/clients")
const projectRouter = require("./routes/projects")

const app = express()
const PORT = process.env.PORT || 5000

app.use(express.json())
app.use(cors({ origin: "*" }))
app.use("/clients", clientRouter)
app.use("/projects", projectRouter)

function errMiddleware(err, req, res, next) {
  res.status(err.status || 500).json({
    statusCode: err.status || 500,
    message: err.message || "Something went wrong"
  })
}

app.use(errMiddleware)

app.listen(PORT, () => {
  console.log(`App running on port ${PORT}`)
})

One routing mistake I made: I defined routes as router.post("/clients", ...) inside the router file while also mounting at app.use("/clients", router) in index.js. That made the actual endpoint /clients/clients. The fix is to use "/" inside the router file since the prefix already lives in

index.js.

The error middleware has the 4-argument signature (err, req, res, next) — Express identifies error middleware by that fourth argument. It must be registered after all routes. err.status || 500 ensures you always send a valid status code even if the error object doesn't carry one.

Testing in Postman

All 6 routes tested and working:

POST /clients → returns created client with auto-generated id and timestamp
GET /clients → returns array of all clients
POST /projects → returns created project with status: "active" auto-filled
GET /projects → returns all projects with nested client object from the JOIN
PUT /projects/1 → returns updated project with new status
DELETE /projects/1 → returns success message

Building in public. Sharing everything.