Chapter 10 made our database tests deterministic, so every test now starts from a clean, shared urlshortener_test database. In this chapter we introduce Prisma and stand up the real Url table, while the app keeps serving requests from the in-memory Map. We build the schema, the migration, and the typed client here; wiring them into the request path is the next chapter.

schema.prisma file describing the Url table, the single source of truth for both the database shape and our TypeScript types.Url row.Prisma comes with its own vocabulary, and three terms carry the rest of the chapter. Here they are in plain English.
ORM (Object-Relational Mapper). A library that lets you work with database rows as ordinary code objects instead of hand-writing SQL. Example: prisma.url.create({ data }) instead of an INSERT INTO urls ... string. Prisma is the specific ORM and migration toolkit we use.
Migration. A versioned, timestamped SQL file recording one change to the database shape, committed to git. For example, creates the table.
prisma/migrations/20260613091431_init/migration.sqlurls@unique constraint. A database-level rule that no two rows may share a value in a column. We put it on shortCode, so the database itself rejects a duplicate, not just our app code.
So far the database story has been hand-written SQL through the pg driver. That works, but it leaves two gaps. First, nothing keeps the table's shape and the TypeScript types in sync; pool.query<{ id: number }>(...) is an assertion, and the compiler trusts it whether or not the table actually has an id column. Second, there is no record of how the schema came to be: you run CREATE TABLE somewhere and hope every environment ran the same statements in the same order. Prisma closes both gaps from one file, the schema.prisma that declares your tables, columns, and types. The versioned migrations and a typed client are both derived from that single source, so they can never drift apart.
The tradeoffs are worth spelling out, because "use an ORM" is not automatically the right call:
pg, what we used in chapter 9): maximum control, zero abstraction. You hand-write every query and there is no compile-time guarantee the query shape matches the table. Great when you need exotic SQL; easy to let types drift.generate step), a query engine, and dropping to $queryRaw when you need SQL Prisma's API doesn't express.We pick Prisma because the typed client (the generated PrismaClient, whose methods know your schema and turn a field mismatch into a compile error) makes our integration assertions type-safe, and the migration workflow is precisely what a production service and a CI pipeline need. That same workflow reappears in the database swap and the collisions chapter later in the course.
One practical note: this chapter only stands up the schema, the migration, and the typed client. The app still serves every request from the in-memory Map. The storage swap, where the routes actually start reading from Postgres, is the next chapter.
A word on versions: we pin Prisma to 6.19.3, the current stable release, so everyone runs the exact same setup and the commands and output match what you see here. The version isn't the point. The concepts of one schema file, versioned migrations, and a generated typed client carry straight over to newer releases.
As always, we start from a test that fails for the right reason. Check out the start branch with the Postgres container running:
bashgit checkout 11-prisma-and-schema-startnpm install
The start branch ships one new integration test, url-model.test.ts. It uses the typed Prisma client to create a Url row and read it back, asserting the persisted shape: shortCode and originalUrl round-trip, clicks defaults to 0, id and createdAt are populated by the database, and a duplicate shortCode is rejected.
typescript// __tests__/integration/url-model.test.tsimport { prisma } from "../../src/db/prisma";import { pool } from "../../src/db/pool";describe("Url model (Prisma)", () => {afterAll(async () => {await prisma.$disconnect();await pool.end();});it("creates a Url and reads back the persisted fields", async () => {const created = await prisma.url.create({data: {shortCode: "abc123",originalUrl: "https://example.com/some/very/long/path",},});expect(created.id).toEqual(expect.any(Number));expect(created.shortCode).toBe("abc123");expect(created.originalUrl).toBe("https://example.com/some/very/long/path");expect(created.clicks).toBe(0);expect(created.createdAt).toBeInstanceOf(Date);const found = await prisma.url.findUnique({where: { shortCode: "abc123" },});expect(found).not.toBeNull();expect(found?.originalUrl).toBe("https://example.com/some/very/long/path");expect(found?.clicks).toBe(0);});it("rejects a duplicate shortCode (unique constraint)", async () => {await prisma.url.create({data: { shortCode: "dup", originalUrl: "https://example.com/first" },});await expect(prisma.url.create({data: { shortCode: "dup", originalUrl: "https://example.com/second" },})).rejects.toThrow();});});
On the start branch, none of the machinery this test needs exists yet: Prisma is not installed, there is no src/db/prisma module, no generated client, and no urls table. So the test fails to even load. Bring the container up and run the integration suite:
bashcp .env.example .env # first time onlydocker compose up -d --wait # starts Postgres, blocks until healthynpm run test:integration

Red, with Cannot find module '../../src/db/prisma', the module this test imports doesn't exist yet. This is an honest red: the container is up and chapter 9's db.test.ts still passes its two tests, proving the database connection itself works. The failure is the missing Prisma module, client, and table, exactly the gap we are about to close.
You'll see the same gap from the type checker. npm run typecheck reports error TS2307: Cannot find module '../../src/db/prisma'. Both the test runner and the compiler agree the module this test imports doesn't exist yet.
Switch to the finish branch. The interesting part is what's on it, so let's check it out and then walk through each piece.
bashgit checkout 11-prisma-and-schema-finishnpm install
Installing Prisma is a single pinned command. This is already in package.json on the finish branch, so npm install is enough, but for reference, here is how the dependency was added:
bashnpm install --save-exact prisma@6.19.3 @prisma/client@6.19.3
--save-exact writes 6.19.3 with no caret, so a later npm install can't quietly drift onto a different 6.x. The package.json ends up with @prisma/client in dependencies (the app needs it at runtime), prisma in devDependencies (the CLI is a build-time tool), and a postinstall hook.
json// package.json (excerpt)"scripts": {"prisma:generate": "prisma generate","prisma:migrate": "prisma migrate dev","prisma:deploy": "prisma migrate deploy","prisma:studio": "prisma studio","postinstall": "prisma generate"},"dependencies": {"@prisma/client": "6.19.3"},"devDependencies": {"prisma": "6.19.3"}
The postinstall: "prisma generate" matters: the generated client lives in node_modules and is not committed, so a fresh npm install (yours, a teammate's, or CI's) rebuilds it automatically. That is why the run sequence never calls prisma generate by hand.
Here is the heart of the chapter, prisma/schema.prisma. Three blocks: generator (how to build the client), datasource (which database), and the Url model itself.
prisma// prisma/schema.prismagenerator client {provider = "prisma-client-js"}datasource db {provider = "postgresql"url = env("DATABASE_URL")}model Url {id Int @id @default(autoincrement())shortCode String @unique @map("short_code")originalUrl String @map("original_url")clicks Int @default(0)createdAt DateTime @default(now()) @map("created_at")@@map("urls")}
A few decisions in that model deserve their why.
The datasource never hard-codes a connection string; it reads the single env var DATABASE_URL. That is the whole reason the same schema can target the dev database normally and the test database under Jest: we just point DATABASE_URL somewhere different. Nothing about the database is committed to the repo.
The id is an internal Int @default(autoincrement()), not the public handle. It's an auto-incrementing integer (Postgres SERIAL) and is never exposed to API clients. It's small, ordered, and cheap to index, which also makes the createdAt desc, id tiebreaking we'll use for pagination later inexpensive. The public identifier is shortCode. We deliberately did not reach for a cuid()/uuid() string primary key here: UUIDs are larger and non-sequential (worse index locality) and add nothing when the public handle is already shortCode. They make sense when IDs are exposed externally or generated client-side or offline, which is not our case.
The @unique on shortCode is load-bearing. It creates a database-level unique index, which means the database itself rejects a duplicate short code, not just our application code. That is exactly what the collisions chapter relies on: retry-on-conflict only works if the constraint guarantees no two rows can share a code, even under concurrent inserts. We set it up now, on purpose, and the duplicate-shortCode test above already exercises it.
For naming, @@map("urls") maps the Url model to a table named urls; @map("short_code"), @map("original_url"), and @map("created_at") map each camelCase field to a snake_case column. This keeps the SQL idiomatic (snake_case tables and columns are the Postgres convention) while keeping the TypeScript idiomatic (camelCase fields). One convention each side, and you'll see both halves in the migration SQL next.
schema.prisma describes the database; src/db/prisma.ts is the runtime handle we actually import. It's tiny, and it mirrors the single-pool pattern from chapter 9.
typescript// src/db/prisma.tsimport { PrismaClient } from "@prisma/client";export const prisma = new PrismaClient();
The PrismaClient manages its own connection pool, so exactly like the pg Pool, we create one instance and export it rather than new-ing a client per query. Importing it from one place also gives the next chapter a single seam to swap the in-memory store for a Prisma-backed repository. For now this client exists so we can prove the schema, migration, and typed client are real. The routes still serve from the Map.
With the schema written, prisma migrate dev turns it into SQL and applies it to the dev database. This was run once during authoring against urlshortener:
bashnpx prisma migrate dev --name init
Two things happened in one command. First, Prisma wrote a timestamped migration directory, prisma/migrations/20260613091431_init/, and applied it to the dev database. Second, it ran prisma generate for us, rebuilding the typed client so the new Url model is immediately available in code. That migration directory is committed to git; it's the durable, reviewable record of how the schema reached its current shape.
Open the migration Prisma wrote. This is the entire file:
sql-- prisma/migrations/20260613091431_init/migration.sql-- CreateTableCREATE TABLE "urls" ("id" SERIAL NOT NULL,"short_code" TEXT NOT NULL,"original_url" TEXT NOT NULL,"clicks" INTEGER NOT NULL DEFAULT 0,"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,CONSTRAINT "urls_pkey" PRIMARY KEY ("id"));-- CreateIndexCREATE UNIQUE INDEX "urls_short_code_key" ON "urls"("short_code");
Every decision from the schema shows up here in plain SQL, just generated DDL you can read and review:
urls (@@map), and the columns are short_code, original_url, created_at (each @map). The camelCase-to-snake_case translation is exactly what we asked for.id is SERIAL, Postgres's auto-incrementing integer, and the primary key (urls_pkey).clicks carries DEFAULT 0, which is why the test never sets it and still reads 0 back.created_at is TIMESTAMP(3) with DEFAULT CURRENT_TIMESTAMP, populated by the database on insert.@unique on shortCode becomes CREATE UNIQUE INDEX "urls_short_code_key". This is the constraint the duplicate-shortCode test trips, and the one the collisions chapter leans on.Alongside it, Prisma writes a migration_lock.toml that records the provider so the migration history can't be replayed against the wrong database engine:
toml# prisma/migrations/migration_lock.toml# Please do not edit this file manually# It should be added in your version-control system (e.g., Git)provider = "postgresql"
The dev database is migrated, but our integration test runs against the test database, urlshortener_test. That table has to exist before the first test runs, and we don't want every test author to remember to migrate it by hand. So we apply the migration to the test database automatically, once, before the whole suite, with a Jest globalSetup.
typescript// __tests__/integration/global-setup.tsimport { execSync } from "node:child_process";import { config } from "dotenv";export default async function globalSetup(): Promise<void> {config({ quiet: true });const testDatabaseUrl = process.env.TEST_DATABASE_URL;if (!testDatabaseUrl) {throw new Error("Missing TEST_DATABASE_URL. Copy .env.example to .env and start Postgres with `docker compose up -d`.");}execSync("npx prisma migrate deploy", {stdio: "inherit",env: { ...process.env, DATABASE_URL: testDatabaseUrl },});}
Two design choices here are worth calling out.
The command is migrate deploy, not migrate dev. deploy is the non-interactive, production-style command: it applies every committed migration in prisma/migrations/ exactly as written and never generates new ones or resets the database. That is precisely what a test database, and a CI pipeline, want. migrate dev is for authoring (it diffs the schema, creates new migrations, and may prompt); it has no business running unattended against a test database.
We also override DATABASE_URL to TEST_DATABASE_URL just for this command. Prisma always reads its connection string from the single DATABASE_URL env var. The execSync call passes a modified env so this one migration targets the test database, while the dev DATABASE_URL in .env is left untouched. We wire it into the integration config with one line:
typescript// jest.integration.config.ts (excerpt)const config: Config = {preset: "ts-jest",testEnvironment: "node",roots: ["<rootDir>/__tests__/integration"],globalSetup: "<rootDir>/__tests__/integration/global-setup.ts",setupFiles: ["<rootDir>/__tests__/integration/setup-env.ts"],setupFilesAfterEnv: ["<rootDir>/__tests__/integration/setup-isolation.ts"],testMatch: ["<rootDir>/__tests__/integration/**/*.test.ts"],};
There's a matching half. globalSetup migrates the test database, but the test processes also need their Prisma client pointed at the test database. Chapter 9's setup-env.ts already loaded .env; we add one block so the client inside the tests targets the test database too:
typescript// __tests__/integration/setup-env.ts (added block)if (process.env.TEST_DATABASE_URL) {process.env.DATABASE_URL = process.env.TEST_DATABASE_URL;}
The pg pool picks dev-vs-test itself based on NODE_ENV, but Prisma only knows DATABASE_URL, so we override it before the Prisma client is first imported. The result: the migration in globalSetup and the queries inside the tests all hit the same dedicated test database, with no change to src/db/prisma.ts.
There's a subtle interaction with chapter 10's centralized cleanup. The beforeEach(truncateAllTables) hook discovers every table in the public schema and truncates it. But the test database now has a second table we don't own: _prisma_migrations, Prisma's bookkeeping table recording which migrations have been applied. If truncateAllTables wiped it between tests, Prisma would think the database is un-migrated. So the discovery query now excludes it:
typescript// __tests__/integration/helpers/truncate.ts (excerpt)export const getPublicTableNames = async (): Promise<string[]> => {const result = await pool.query<{ tablename: string }>(`SELECT tablenameFROM pg_tablesWHERE schemaname = 'public'AND tablename <> '_prisma_migrations'`);return result.rows.map((row) => row.tablename);};
The migration is applied once per run in globalSetup; the cleanup runs before every test. Excluding _prisma_migrations lets the cleanup empty urls between tests while leaving the migration history intact. truncateAllTables itself is unchanged, still TRUNCATE ... RESTART IDENTITY CASCADE.
Chapter 10 created a throwaway urls_demo table (with helpers/urls-demo.ts and urls-isolation.test.ts) purely to teach isolation, because no real table existed yet. Now that a real, migrated urls table exists, both of those files are deleted on this branch. Test isolation is now demonstrated through the real Url table: url-model.test.ts relies on the same centralized beforeEach(truncateAllTables) to start from an empty urls. Keeping a parallel throwaway table alongside the real one would be confusing and redundant. The demo table did its job in chapter 10, and the real table tells the isolation story from here on.
Now run the integration suite against a clean test database. The globalSetup applies the migration first, then the tests run:
bashnpm run test:integration

Green: the model test creates a Url, reads it back with clicks defaulting to 0 and createdAt populated by the database, and the @unique constraint rejects the duplicate shortCode. globalSetup applied 20260613091431_init to urlshortener_test first, then both suites passed: chapter 9's db.test.ts (two tests) plus our new url-model.test.ts (two tests), four total.
Run it a second time and the Applying migration block is replaced by No pending migrations to apply., because migrate deploy is idempotent, so a test database that's already migrated is left exactly as it is. The fast unit suite is untouched and still green at six suites and thirty-two tests, and npm run typecheck now passes with no output: the src/db/prisma module the start branch was missing is real, generated, and fully typed.
When you're done, stop the container:
bashdocker compose down -v # stop + remove the container and delete the data volume
The schema and typed client are real, but the routes still serve every request from the in-memory Map. Next we migrate to the database, swapping the Map for a Prisma-backed repository behind the same interface.