Introduction to Prisma & Database Schema

@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.

Why Prisma Instead of Raw SQL

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:

• Raw SQL (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.
• Prisma: one schema is the single source of truth. It generates both the migrations and the typed client, so the database shape and the TypeScript types stay locked together. The cost is a generated client (a generate step), a query engine, and dropping to $queryRaw when you need SQL Prisma's API doesn't express.
• Other ORMs (TypeORM, Sequelize, Drizzle, Knex): TypeORM and Sequelize lean on decorator/model-class patterns; Drizzle is a thinner SQL-like typed query builder; Knex is a query builder with no typed-model layer. Prisma's distinguishing feature here is schema-as-source-of-truth plus generated types plus first-class migrations, the combination that makes the TDD loop catch schema/code drift for us.

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.

Step 1: The Failing Test (Red)

As always, we start from a test that fails for the right reason. Check out the start branch with the Postgres container running:

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.

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:

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.

Step 2: Install and Model the Schema (Green)

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.

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:

--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.

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.

The schema

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.

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.

The shared client

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.

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.

Step 3: Run the First Migration

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:

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.

Inspect the generated SQL

Open the migration Prisma wrote. This is the entire file:

Every decision from the schema shows up here in plain SQL, just generated DDL you can read and review:

• The table is 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.
• The @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:

Step 4: Migrate the Test Database, Then Go Green

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.

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:

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:

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.

One change to truncate: keep migration history

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:

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.

Retiring the chapter 10 demo table

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.

The suite goes green

Now run the integration suite against a clean test database. The globalSetup applies the migration first, then the tests run:

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:

What's Next

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.