Connect a REST API to PostgreSQL with Prisma and TypeScript

What you will build

By the end of this tutorial you will have a working REST API for a task list, built with Express and TypeScript, storing every task in a real PostgreSQL database through Prisma. It has the same five routes as most backends: list every task, create one, read one by id, update one, and delete one. That is the full create-read-update-delete (CRUD) set.

This is the direct follow-up to our in-memory REST API tutorial. That one keeps its tasks in a plain array, which resets every time the server restarts. Here you swap the array for Postgres, so your data survives a restart, a deploy, and a crash. If you have not built the in-memory version yet, you can still follow along; this page is self-contained.

Prisma is the piece that connects Express to Postgres. It is a TypeScript ORM: you describe your tables in a schema file, Prisma generates a typed client, and every query you write is checked against that schema as you type. No hand-written SQL strings, no guessing the shape of a row. Every file you need is in this page. Paste each one into a fresh project, follow the setup steps, and it runs. No repo to clone.

Setup: project, Prisma, and a Postgres database

You need three things: a fresh Node project, the Prisma tooling, and a PostgreSQL database to point it at. Start with the project and packages. Express and @prisma/client are the two runtime dependencies; TypeScript, tsx, the prisma CLI, and the @types/* packages are dev tooling for building and running the .ts files.

mkdir prisma-api
cd prisma-api
npm init -y
npm install express @prisma/client
npm install -D typescript tsx prisma @types/node @types/express
npx prisma init --datasource-provider postgresql

npx prisma init creates a prisma/ folder with a schema.prisma file and a .env file holding a DATABASE_URL placeholder. Now you need a real Postgres for that URL to point at. Any Postgres works. The quickest local option is Docker, one command:

docker run --name tasks-db -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=tasks -p 5432:5432 -d postgres:16

That starts Postgres 16 on localhost:5432 with a database named tasks. No Docker? A native Postgres install works the same way, and a free hosted database works too. Supabase's free tier is one option, and it happens to be the same Postgres this site runs on, though nothing here requires it. Whichever you pick, open the .env file Prisma created and set DATABASE_URL to your connection string. For the Docker database above, that is:

DATABASE_URL="postgresql://postgres:postgres@localhost:5432/tasks?schema=public"

Add a tsconfig.json at the project root. These settings run cleanly with tsx in development and compile with tsc for a production build:

{
  "compilerOptions": {
    "target": "ES2022",
    "module": "ESNext",
    "moduleResolution": "Bundler",
    "strict": true,
    "esModuleInterop": true,
    "skipLibCheck": true,
    "rootDir": "src",
    "outDir": "dist"
  },
  "include": ["src"]
}

Then open package.json, add "type": "module", and set these three scripts. dev runs the app straight from source and restarts on every save, build compiles to dist/, and start runs the compiled output the way a production host would:

{
  "type": "module",
  "scripts": {
    "dev": "tsx watch src/index.ts",
    "build": "tsc",
    "start": "node dist/index.js"
  }
}

Define the schema

The schema file is Prisma's source of truth for your database. You describe your models in it, and Prisma turns that into both the SQL that creates your tables and the typed client your code calls. Open prisma/schema.prisma and set its contents to one Task model:

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

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

model Task {
  id        Int      @id @default(autoincrement())
  title     String
  completed Boolean  @default(false)
  createdAt DateTime @default(now())
}

Each field maps to a column. @id @default(autoincrement()) makes id an auto-incrementing primary key, so Postgres hands out the next number on every insert. @default(false) and @default(now()) mean you never set completed or createdAt by hand; the database fills them in. Now turn this schema into a real table with a migration:

npx prisma migrate dev --name init

That one command does three things. It writes a SQL migration file under prisma/migrations/ (the exact CREATE TABLE it ran, versioned so a teammate or a deploy can replay it), it applies that SQL to your database, and it runs prisma generate to build the typed client from your schema. From here on, every time you change the schema you run npx prisma migrate dev again to create the next migration. If you ever need to rebuild the client without a schema change, npx prisma generate does just that part.

The five routes

Now the API itself. This is the whole thing in one file: create the Prisma client once, wire up Express with express.json() so req.body is a real object, and define the five routes. Each handler is async because every Prisma call returns a promise, and each one calls a typed prisma.task method instead of touching an array. Your editor autocompletes the fields on data and the shape of the returned task, because Prisma generated those types from your schema.

Create src/index.ts:

import express, { type Request, type Response } from "express";
import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();
const app = express();
app.use(express.json());

// GET /tasks: return every task, newest first.
app.get("/tasks", async (_req: Request, res: Response) => {
  const tasks = await prisma.task.findMany({ orderBy: { createdAt: "desc" } });
  res.json(tasks);
});

// POST /tasks: create a task from { title }. 400 if the title is missing.
app.post("/tasks", async (req: Request, res: Response) => {
  const { title } = req.body ?? {};
  if (typeof title !== "string" || title.trim() === "") {
    res.status(400).json({ error: "title is required and must be a non-empty string" });
    return;
  }
  const task = await prisma.task.create({ data: { title: title.trim() } });
  res.status(201).json(task);
});

// GET /tasks/:id: return one task or 404.
app.get("/tasks/:id", async (req: Request, res: Response) => {
  const id = Number(req.params.id);
  if (!Number.isInteger(id)) {
    res.status(400).json({ error: "id must be a number" });
    return;
  }
  const task = await prisma.task.findUnique({ where: { id } });
  if (!task) {
    res.status(404).json({ error: "task not found" });
    return;
  }
  res.json(task);
});

// PATCH /tasks/:id: update title and/or completed on an existing task, or 404.
app.patch("/tasks/:id", async (req: Request, res: Response) => {
  const id = Number(req.params.id);
  if (!Number.isInteger(id)) {
    res.status(400).json({ error: "id must be a number" });
    return;
  }
  const existing = await prisma.task.findUnique({ where: { id } });
  if (!existing) {
    res.status(404).json({ error: "task not found" });
    return;
  }

  const { title, completed } = req.body ?? {};
  const data: { title?: string; completed?: boolean } = {};
  if (title !== undefined) {
    if (typeof title !== "string" || title.trim() === "") {
      res.status(400).json({ error: "title must be a non-empty string" });
      return;
    }
    data.title = title.trim();
  }
  if (completed !== undefined) {
    if (typeof completed !== "boolean") {
      res.status(400).json({ error: "completed must be a boolean" });
      return;
    }
    data.completed = completed;
  }

  const task = await prisma.task.update({ where: { id }, data });
  res.json(task);
});

// DELETE /tasks/:id: remove a task. 204 on success, 404 if it was not there.
app.delete("/tasks/:id", async (req: Request, res: Response) => {
  const id = Number(req.params.id);
  if (!Number.isInteger(id)) {
    res.status(400).json({ error: "id must be a number" });
    return;
  }
  const existing = await prisma.task.findUnique({ where: { id } });
  if (!existing) {
    res.status(404).json({ error: "task not found" });
    return;
  }
  await prisma.task.delete({ where: { id } });
  res.status(204).end();
});

const port = process.env.PORT ?? 3000;
app.listen(port, () => {
  console.log(`API listening on http://localhost:${port}`);
});

A few decisions worth calling out. The Number.isInteger(id) guard on the :id routes matters more with a database than with an array: Number("abc") is NaN, and Prisma rejects a NaN id with a validation error, so you check first and return a clean 400. For update and delete, the handler reads the row with findUnique before writing, so a missing id returns 404 instead of surfacing Prisma's internal "record not found" error. And prisma.task.create only takes { title }: the database fills in id, completed, and createdAt from the defaults you set in the schema, so there is nothing else to pass.

Run it

Make sure your Postgres is running (the Docker container from earlier, or your own instance), then start the server in dev mode. It restarts automatically when you save a file:

npm run dev
# -> API listening on http://localhost:3000

In a second terminal, walk through the routes with curl. Create a task, read it back, mark it done, then delete it:

# Create a task
curl -X POST http://localhost:3000/tasks \
  -H "Content-Type: application/json" \
  -d '{"title":"Connect the API to Postgres"}'
# -> {"id":1,"title":"Connect the API to Postgres","completed":false,"createdAt":"..."}

# List every task
curl http://localhost:3000/tasks

# Mark it done
curl -X PATCH http://localhost:3000/tasks/1 \
  -H "Content-Type: application/json" \
  -d '{"completed":true}'

# Delete it (returns 204 No Content)
curl -i -X DELETE http://localhost:3000/tasks/1

Now the payoff. Stop the server with Ctrl-C, create a couple of tasks, then restart with npm run dev and list them again. They are still there, because Prisma wrote them to Postgres, not to memory. That persistence is the whole reason to reach for a database. The status codes are worth reading on purpose too: a create returns 201 Created, a POST with no title returns 400 Bad Request, an id that does not exist returns 404 Not Found, and a successful delete returns 204 No Content with an empty body. Want to see the rows directly? Run npx prisma studio for a browser UI over your tables, or connect with psql and run SELECT * FROM "Task";.

What's next

You now have a REST API backed by a real database, which is the shape of most production backends. Two moves take it further.

Add authentication. Right now anyone can read or delete any task. Our JWT authentication tutorial builds a register/login flow with a signed token and an Express middleware that protects routes, and it drops straight onto this Postgres setup: your users become another Prisma model.

Grow the schema. Add a Task relation to a User, or a priority enum, and each change is one edit to schema.prisma plus one npx prisma migrate dev. The typed client updates itself, so your editor flags every route that needs to change.

If you do not need a database yet and want the simplest possible version, start with the in-memory REST API tutorial instead. For the full course path around backend work, the backend developer roadmap shows what comes after this.