SQL Databases with Prisma

Not every Express project wants a document database. Relational databases such as PostgreSQL, MySQL and SQLite give you transactions, foreign keys and mature tooling, and Prisma makes them easy to use from Node: describe tables in a schema file, generate a type-aware client, and query with plain JavaScript objects. After this lesson you will be able to set up Prisma in an Express project, run migrations, perform CRUD with relations, and handle Prisma errors in your error middleware.

Installation and schema

This lesson targets Prisma 6, the most widely deployed major version. Start with SQLite so there is nothing to install; switching to PostgreSQL later is a one-line change.

bash

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

prisma init creates prisma/schema.prisma and adds DATABASE_URL="file:./dev.db" to .env. Define your models:

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
  name      String
  posts     Post[]
  createdAt DateTime @default(now())
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  body      String
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
  createdAt DateTime @default(now())

  @@index([authorId, createdAt])
}

@relation declares the foreign key; User.posts is the reverse side of the same relation.

Migrations and the generated client

bash

npx prisma migrate dev --name init

This command creates a SQL migration in prisma/migrations/, applies it to the database and regenerates @prisma/client so it knows about your models. Run it again with a new --name whenever the schema changes; in production apply pending migrations with npx prisma migrate deploy.

Create one shared client; each PrismaClient owns a connection pool:

javascript

// src/lib/prisma.js
import { PrismaClient } from "@prisma/client";
export const prisma = new PrismaClient();

CRUD and relations in a service

javascript

// src/services/post.service.js
import { prisma } from "../lib/prisma.js";

export const listPublished = ({ page = 1, limit = 20 }) =>
  prisma.post.findMany({
    where: { published: true },
    include: { author: { select: { id: true, name: true } } },
    orderBy: { createdAt: "desc" },
    skip: (page - 1) * limit,
    take: limit,
  });

export const findById = (id) =>
  prisma.post.findUnique({ where: { id }, include: { author: true } }); // null if missing

export const create = ({ title, body, authorId }) =>
  prisma.post.create({ data: { title, body, author: { connect: { id: authorId } } } });

export const update = (id, data) => prisma.post.update({ where: { id }, data });
export const remove = (id) => prisma.post.delete({ where: { id } });

| Method | SQL equivalent | | --- | --- | | findMany({ where, orderBy, skip, take }) | SELECT ... WHERE ... ORDER BY ... LIMIT/OFFSET | | findUnique({ where: { id } }) | SELECT ... WHERE id = ? (unique columns only) | | create({ data }) / createMany | INSERT | | update({ where, data }) / upsert | UPDATE / insert-or-update | | delete({ where }) | DELETE | | count({ where }), aggregate, groupBy | COUNT, SUM/AVG, GROUP BY |

Route parameters arrive as strings, so convert with Number(req.params.id) before passing an Int id.

Transactions and error handling

When two writes must succeed or fail together, wrap them in await prisma.$transaction(async (tx) => { ... }) and call tx.user.create(...), tx.post.create(...) inside the callback; any thrown error rolls both back.

Prisma throws PrismaClientKnownRequestError with a stable code. Map the common ones in your error middleware:

javascript

import { Prisma } from "@prisma/client";

if (err instanceof Prisma.PrismaClientKnownRequestError) {
  if (err.code === "P2002") return res.status(409).json({ error: "Value already exists" });
  if (err.code === "P2025") return res.status(404).json({ error: "Record not found" });
}

P2002 is a unique-constraint violation (duplicate email); P2025 means update or delete targeted a row that does not exist.

Tips

Change provider = "postgresql" and DATABASE_URL to move to Postgres; the client API stays identical.
Prisma 7 moves the connection URL into prisma.config.ts, generates the client with the prisma-client generator and connects through driver adapters; the query API shown here is unchanged.

Key Takeaways

Prisma describes tables in schema.prisma, migrates them with prisma migrate dev, and generates a client with methods per model.
findMany, findUnique, create, update and delete accept where, data, include, select, orderBy, skip and take objects.
Relations are declared with @relation and loaded with include or written with connect.
Use one shared PrismaClient, $transaction for multi-step writes, and map P2002/P2025 to 409/404.
Swapping SQLite for PostgreSQL only changes the datasource provider and URL.

Next lesson: Building a REST API with Express — combine routing, controllers and a database into a complete CRUD API with proper status codes.