Build a URL Shortener: Database Design & Storage

Our URL shortener works — but it forgets everything the moment the server restarts. Every shortened URL, every click count, gone. That's fine for prototyping, but completely useless in production.

In this post, we'll add PostgreSQL as our database and Prisma as our ORM. By the end, URLs survive server restarts, queries are type-safe, and our schema is version-controlled through migrations. The best part? Thanks to the layered architecture from Post #2, we only need to replace one file.

Time commitment: 1–2 hours
Prerequisites: Phase 1: Project Setup & URL Shortening API

What we'll build in this post:
✅ PostgreSQL running in Docker with Docker Compose
✅ Prisma ORM with type-safe database client
✅ Schema design with proper indexes for fast lookups
✅ Database migrations for version-controlled schema changes
✅ Replace memoryStore with a Prisma-based store
✅ Connection pooling configuration for production readiness

Why PostgreSQL + Prisma?

Before we start, let's clarify why we're picking these tools over the dozens of alternatives.

Why PostgreSQL?

Feature	PostgreSQL	MySQL	SQLite
ACID compliance	Full	Full	Full
JSON support	Native `jsonb` type	Limited	None
Full-text search	Built-in `tsvector`	Requires plugin	Basic
Concurrent writes	MVCC (excellent)	Lock-based	Single writer
Analytics	Window functions, CTEs	Limited	Basic
Scalability	Handles millions of rows easily	Good	Small datasets

For a URL shortener, PostgreSQL gives us:

Fast indexed lookups on short codes (critical for redirect speed)
JSONB columns for flexible analytics data in later posts
Window functions for click analytics and reporting
Battle-tested concurrency when multiple users shorten URLs simultaneously

Why Prisma?

Prisma is a TypeScript-first ORM that generates a fully typed database client from your schema:

// Type-safe — TypeScript knows the exact shape of the result
const url = await prisma.url.findUnique({
  where: { shortCode: 'aBc1X9z' }
});
// url is typed as Url | null — not `any`

Compared to raw SQL or query builders:

Type safety — catch query errors at compile time, not runtime
Auto-generated client — no hand-written types that drift from your schema
Migration system — version-controlled schema changes with rollback
Introspection — generate schema from existing databases

Setting Up PostgreSQL with Docker

We'll use Docker Compose to run PostgreSQL locally. This keeps your machine clean and makes the setup reproducible.

Create Docker Compose File

# docker-compose.yml
 
services:
  postgres:
    image: postgres:16-alpine
    container_name: url-shortener-db
    restart: unless-stopped
    environment:
      POSTGRES_USER: urlshortener
      POSTGRES_PASSWORD: urlshortener123
      POSTGRES_DB: urlshortener
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U urlshortener"]
      interval: 5s
      timeout: 5s
      retries: 5
 
volumes:
  postgres_data:

Let's break this down:

postgres:16-alpine — Alpine-based image (much smaller than the default image, ~80MB vs ~400MB)
restart: unless-stopped — auto-restart if the container crashes (but not if you stop it manually)
volumes — named volume postgres_data persists data across container restarts. Without this, your data disappears when the container stops
healthcheck — Docker knows when PostgreSQL is actually ready to accept connections, not just when the container starts. This matters for dependent services

Start PostgreSQL

docker compose up -d

Verify it's running:

docker compose ps

NAME                 STATUS              PORTS
url-shortener-db     Up (healthy)        0.0.0.0:5432->5432/tcp

You can also connect directly to check:

docker exec -it url-shortener-db psql -U urlshortener -d urlshortener -c "SELECT version();"

Update Environment Variables

Add the database connection string to .env:

PORT=3000
BASE_URL=http://localhost:3000
NODE_ENV=development
DATABASE_URL=postgresql://urlshortener:urlshortener123@localhost:5432/urlshortener

And update .env.example:

PORT=3000
BASE_URL=http://localhost:3000
NODE_ENV=development
DATABASE_URL=postgresql://user:password@localhost:5432/urlshortener

Security note: Never commit .env to git. The .env.example file uses placeholder values and is safe to commit.

Installing and Initializing Prisma

Install Dependencies

# Runtime dependencies
npm install @prisma/client @prisma/adapter-pg
 
# Development dependencies
npm install -D prisma @types/pg

Four packages:

prisma (dev dependency) — CLI tool for migrations, schema management, and client generation
@prisma/client (runtime dependency) — the generated client you use in your code
@prisma/adapter-pg (runtime dependency) — Prisma 7's driver adapter for PostgreSQL, connects Prisma to the pg driver
@types/pg (dev dependency) — TypeScript types for the pg driver

Why an adapter? Prisma 7 uses a new "client" engine that delegates database communication to a JavaScript driver (in our case, pg) rather than embedding its own binary query engine. This makes the Prisma Client smaller and faster to start, and gives you direct control over the connection pool.

Initialize Prisma

npx prisma init

This creates:

prisma/schema.prisma — your database schema definition
prisma.config.ts — Prisma configuration file (new in Prisma 7)
Updates .env with a DATABASE_URL placeholder (we already set ours)

Your project structure now looks like:

url-shortener/
├── prisma/
│   └── schema.prisma        # NEW — database schema
├── src/
│   ├── config/env.ts
│   ├── middleware/
│   ├── routes/
│   ├── services/
│   ├── store/
│   │   └── memoryStore.ts   # Will be replaced
│   ├── types/
│   ├── app.ts
│   └── index.ts
├── docker-compose.yml        # NEW — PostgreSQL
├── prisma.config.ts          # NEW — Prisma configuration
├── .env                      # UPDATED — DATABASE_URL added
└── ...

Designing the Schema

This is the most important part of the post. A well-designed schema prevents painful migrations later and makes queries fast.

The Prisma Schema

// prisma/schema.prisma
 
generator client {
  provider = "prisma-client"
}
 
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}
 
model Url {
  id          String   @id @default(uuid())
  shortCode   String   @unique @map("short_code")
  originalUrl String   @map("original_url")
  createdAt   DateTime @default(now()) @map("created_at")
  updatedAt   DateTime @updatedAt @map("updated_at")
  clickCount  Int      @default(0) @map("click_count")
  isActive    Boolean  @default(true) @map("is_active")
  expiresAt   DateTime? @map("expires_at")
 
  @@index([originalUrl], name: "idx_original_url")
  @@index([createdAt], name: "idx_created_at")
  @@index([isActive, shortCode], name: "idx_active_short_code")
  @@map("urls")
}

Let's examine every decision in detail.

Field-by-Field Breakdown

id — UUID primary key

id String @id @default(uuid())

We use UUID instead of auto-increment integers for two reasons:

Security — sequential IDs leak information (users can guess id=1, id=2, id=3 to enumerate all URLs)
Distributed systems — UUIDs can be generated without hitting the database, which matters when you scale to multiple servers

shortCode — the URL slug

shortCode String @unique @map("short_code")

The @unique constraint is critical. It:

Creates a unique index automatically (fast lookups)
Prevents duplicate short codes at the database level (even if application logic has a bug)
Is the primary lookup field for redirects

originalUrl — the destination URL

originalUrl String @map("original_url")

No length constraint in Prisma — PostgreSQL's TEXT type has no practical limit. URLs can be absurdly long (browsers support up to ~2000 characters, but some server-generated URLs are longer).

createdAt / updatedAt — timestamps

createdAt DateTime @default(now()) @map("created_at")
updatedAt DateTime @updatedAt @map("updated_at")

@default(now()) sets createdAt at insert time. @updatedAt is a Prisma feature that automatically sets the timestamp whenever the record is updated.

clickCount — analytics counter

clickCount Int @default(0) @map("click_count")

Starts at 0. Incremented on every redirect. In a high-traffic system, you'd use a separate analytics table or Redis for this — but for now, an atomic increment operation is fine.

isActive — soft delete

isActive Boolean @default(true) @map("is_active")

Instead of deleting rows, we set isActive to false. This lets us:

Keep analytics data for deactivated URLs
Restore accidentally deleted URLs
Prevent short code reuse (a deleted code shouldn't be reassigned)

expiresAt — link expiration

expiresAt DateTime? @map("expires_at")

Nullable — not all URLs expire. When set, redirects should check this before redirecting. We'll implement expiration logic in a later post.

Naming Conventions

Notice the @map() decorators:

shortCode String @map("short_code")  // TypeScript: camelCase
                                      // Database: snake_case

This gives us the best of both worlds:

TypeScript code uses camelCase (JavaScript convention)
Database columns use snake_case (SQL convention)
Table name @@map("urls") — plural, lowercase (SQL convention)

Indexing Strategy

Indexes are what make database queries fast. Without them, PostgreSQL has to scan every row in the table (called a "sequential scan"). With indexes, it can jump directly to matching rows.

@@index([originalUrl], name: "idx_original_url")
@@index([createdAt], name: "idx_created_at")
@@index([isActive, shortCode], name: "idx_active_short_code")

Here's why each index exists:

Index	Speeds Up	Used When
`shortCode` (unique)	`WHERE short_code = ?`	Every redirect (`GET /:code`)
`idx_original_url`	`WHERE original_url = ?`	Deduplication check on shorten
`idx_created_at`	`ORDER BY created_at DESC`	Listing recent URLs
`idx_active_short_code`	`WHERE is_active = true AND short_code = ?`	Active URL lookup

The composite index idx_active_short_code is important. When we check if a short code is active during redirects, PostgreSQL can satisfy the query entirely from the index — no table access needed. This is called an "index-only scan" and it's significantly faster.

Rule of thumb: Create indexes for columns that appear in WHERE clauses, ORDER BY, and JOIN conditions. But don't over-index — each index slows down writes because PostgreSQL has to update the index on every INSERT/UPDATE.

Running Migrations

Prisma migrations are version-controlled SQL files that track your schema changes over time.

Create the Initial Migration

npx prisma migrate dev --name init

This command does three things:

Generates SQL — creates a migration file in prisma/migrations/
Runs the SQL — applies the migration to your database
Generates the client — creates the type-safe Prisma Client

Output:

Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "urlshortener", schema "public" at "localhost:5432"
 
Applying migration `20260321100000_init`
 
The following migration(s) have been created and applied from new schema changes:
 
migrations/
  └─ 20260321100000_init/
    └─ migration.sql
 
✔ Generated Prisma Client to ./node_modules/.prisma/client

Examine the Generated SQL

-- prisma/migrations/20260321100000_init/migration.sql
 
CREATE TABLE "urls" (
    "id" TEXT NOT NULL,
    "short_code" TEXT NOT NULL,
    "original_url" TEXT NOT NULL,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,
    "click_count" INTEGER NOT NULL DEFAULT 0,
    "is_active" BOOLEAN NOT NULL DEFAULT true,
    "expires_at" TIMESTAMP(3),
 
    CONSTRAINT "urls_pkey" PRIMARY KEY ("id")
);
 
CREATE UNIQUE INDEX "urls_short_code_key" ON "urls"("short_code");
CREATE INDEX "idx_original_url" ON "urls"("original_url");
CREATE INDEX "idx_created_at" ON "urls"("created_at");
CREATE INDEX "idx_active_short_code" ON "urls"("is_active", "short_code");

This is the actual SQL that Prisma ran against your database. It's committed to git, so your team can see exactly what changed and when. If something goes wrong, you can write a down migration to reverse it.

Updated Project Structure

url-shortener/
├── prisma/
│   ├── migrations/
│   │   └── 20260321100000_init/
│   │       └── migration.sql         # Generated SQL
│   └── schema.prisma                 # Schema definition
├── src/
│   ├── config/env.ts
│   ├── lib/
│   │   └── prisma.ts                 # NEW — Prisma client singleton
│   ├── store/
│   │   ├── memoryStore.ts            # OLD — will be replaced
│   │   └── prismaStore.ts            # NEW — database-backed store
│   └── ...
├── docker-compose.yml
├── prisma.config.ts                   # Prisma 7 configuration
└── ...

Prisma Client Singleton

In a Node.js application, you want exactly one Prisma Client instance. Creating multiple instances wastes database connections and can cause connection pool exhaustion.

Prisma 7 uses a driver adapter pattern — instead of embedding its own query engine binary, it delegates to a JavaScript PostgreSQL driver (pg). This means we control the connection pool directly.

// src/lib/prisma.ts
 
import 'dotenv/config';
import { PrismaClient } from '@prisma/client';
import { PrismaPg } from '@prisma/adapter-pg';
 
const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL });
 
const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined;
};
 
export const prisma =
  globalForPrisma.prisma ??
  new PrismaClient({
    adapter,
    log: process.env.NODE_ENV === 'development'
      ? ['query', 'warn', 'error']
      : ['warn', 'error'],
  });
 
if (process.env.NODE_ENV !== 'production') {
  globalForPrisma.prisma = prisma;
}

What's Different in Prisma 7?

In older Prisma versions (5/6), new PrismaClient() was enough — it used an embedded binary query engine that handled the database connection. In Prisma 7, the query engine runs in JavaScript, and you provide a driver adapter that wraps the pg driver:

// Prisma 5/6 — embedded query engine (old way)
const prisma = new PrismaClient();
 
// Prisma 7 — driver adapter (new way)
const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL });
const prisma = new PrismaClient({ adapter });

The PrismaPg adapter creates and manages a pg.Pool under the hood. You can also pass pool configuration:

const adapter = new PrismaPg({
  connectionString: process.env.DATABASE_URL,
  pool: { max: 10 },  // max connections in the pool
});

Why import 'dotenv/config'? The adapter reads DATABASE_URL at import time, before Express's startup code runs. This ensures environment variables are loaded before we create the adapter.

Why the `globalThis` Trick?

During development, tools like nodemon and tsx hot-reload your code by re-importing modules. Each re-import creates a new PrismaClient instance, opening a fresh connection pool. After a few reloads, you've exhausted the database's connection limit.

Storing the instance on globalThis ensures it survives module reloads. In production, there's only one load — so the guard is only needed in development.

Logging Configuration

log: process.env.NODE_ENV === 'development'
  ? ['query', 'warn', 'error']
  : ['warn', 'error'],

In development, we log every SQL query. This helps you:

See what queries Prisma generates
Spot N+1 query problems
Verify indexes are being used

In production, we only log warnings and errors to keep logs clean.

Building the Prisma Store

Now for the main event — replacing memoryStore.ts with a Prisma-based implementation that uses the same interface.

// src/store/prismaStore.ts
 
import { prisma } from '../lib/prisma';
import { UrlRecord } from '../types/url';
 
export const prismaStore = {
  async save(record: UrlRecord): Promise<UrlRecord> {
    const url = await prisma.url.create({
      data: {
        id: record.id,
        shortCode: record.shortCode,
        originalUrl: record.originalUrl,
        createdAt: record.createdAt,
        clickCount: record.clickCount,
      },
    });
 
    return this.toUrlRecord(url);
  },
 
  async findByShortCode(shortCode: string): Promise<UrlRecord | null> {
    const url = await prisma.url.findUnique({
      where: { shortCode },
    });
 
    return url ? this.toUrlRecord(url) : null;
  },
 
  async findByOriginalUrl(originalUrl: string): Promise<UrlRecord | null> {
    const url = await prisma.url.findFirst({
      where: {
        originalUrl,
        isActive: true,
      },
    });
 
    return url ? this.toUrlRecord(url) : null;
  },
 
  async incrementClickCount(shortCode: string): Promise<void> {
    await prisma.url.update({
      where: { shortCode },
      data: {
        clickCount: { increment: 1 },
      },
    });
  },
 
  async exists(shortCode: string): Promise<boolean> {
    const count = await prisma.url.count({
      where: { shortCode },
    });
 
    return count > 0;
  },
 
  toUrlRecord(url: {
    id: string;
    shortCode: string;
    originalUrl: string;
    createdAt: Date;
    clickCount: number;
  }): UrlRecord {
    return {
      id: url.id,
      shortCode: url.shortCode,
      originalUrl: url.originalUrl,
      createdAt: url.createdAt,
      clickCount: url.clickCount,
    };
  },
};

Key Differences from Memory Store

All methods are now async

The memory store was synchronous — Map.get() returns immediately. Database queries are I/O operations that return Promises. This is why we made the service layer async from the start.

findUnique vs findFirst

// findUnique — for columns with @unique constraint
await prisma.url.findUnique({ where: { shortCode } });
 
// findFirst — for non-unique columns, returns first match
await prisma.url.findFirst({ where: { originalUrl } });

findUnique is faster because PostgreSQL knows there's at most one matching row (thanks to the unique index) and stops scanning immediately.

Atomic increment

await prisma.url.update({
  where: { shortCode },
  data: {
    clickCount: { increment: 1 },
  },
});

This translates to UPDATE urls SET click_count = click_count + 1 WHERE short_code = ?. The increment happens atomically in the database — no race conditions when multiple requests click the same URL simultaneously.

Compare this to the memory store approach:

// MEMORY STORE — race condition possible
const record = urls.get(shortCode);
if (record) {
  record.clickCount += 1;  // Two concurrent reads could both see clickCount=5
}                           // Both write 6, losing one click

count for existence checks

async exists(shortCode: string): Promise<boolean> {
  const count = await prisma.url.count({
    where: { shortCode },
  });
  return count > 0;
}

Using count() instead of findUnique() is slightly more efficient for existence checks — we don't need to transfer the full row from the database, just a number.

Updating the URL Service

Now we swap the import from memoryStore to prismaStore. Because both stores implement the same interface, the changes are minimal:

// src/services/urlService.ts
 
import { prismaStore } from '../store/prismaStore';
import { generateShortCode, isValidCustomAlias } from '../utils/shortCode';
import { env } from '../config/env';
import { CreateUrlResponse, UrlStatsResponse } from '../types/url';
 
export class UrlService {
  async shortenUrl(originalUrl: string, customAlias?: string): Promise<CreateUrlResponse> {
    // Check if URL was already shortened
    const existing = await prismaStore.findByOriginalUrl(originalUrl);
    if (existing && !customAlias) {
      return this.toCreateResponse(existing.shortCode, existing.originalUrl, existing.createdAt);
    }
 
    // Use custom alias or generate a short code
    let shortCode: string;
 
    if (customAlias) {
      if (!isValidCustomAlias(customAlias)) {
        throw new AppError('Invalid custom alias. Use 3-30 alphanumeric characters, hyphens, or underscores.', 400);
      }
 
      if (await prismaStore.exists(customAlias)) {
        throw new AppError('Custom alias is already taken', 409);
      }
 
      shortCode = customAlias;
    } else {
      shortCode = await this.generateUniqueCode();
    }
 
    // Save the record
    const record = await prismaStore.save({
      id: crypto.randomUUID(),
      shortCode,
      originalUrl,
      createdAt: new Date(),
      clickCount: 0,
    });
 
    return this.toCreateResponse(record.shortCode, record.originalUrl, record.createdAt);
  }
 
  async resolveAndTrack(shortCode: string): Promise<string> {
    const record = await prismaStore.findByShortCode(shortCode);
 
    if (!record) {
      throw new AppError('Short URL not found', 404);
    }
 
    // Increment click count (fire and forget — don't slow down the redirect)
    prismaStore.incrementClickCount(shortCode).catch((err) => {
      console.error('Failed to increment click count:', err);
    });
 
    return record.originalUrl;
  }
 
  async getUrlStats(shortCode: string): Promise<UrlStatsResponse> {
    const record = await prismaStore.findByShortCode(shortCode);
 
    if (!record) {
      throw new AppError('Short URL not found', 404);
    }
 
    return {
      shortCode: record.shortCode,
      shortUrl: `${env.baseUrl}/${record.shortCode}`,
      originalUrl: record.originalUrl,
      createdAt: record.createdAt,
      clickCount: record.clickCount,
    };
  }
 
  private async generateUniqueCode(): Promise<string> {
    const MAX_RETRIES = 5;
 
    for (let i = 0; i < MAX_RETRIES; i++) {
      const code = generateShortCode();
      if (!(await prismaStore.exists(code))) {
        return code;
      }
    }
 
    throw new AppError('Failed to generate unique short code. Please try again.', 500);
  }
 
  private toCreateResponse(shortCode: string, originalUrl: string, createdAt: Date): CreateUrlResponse {
    return {
      shortCode,
      shortUrl: `${env.baseUrl}/${shortCode}`,
      originalUrl,
      createdAt,
    };
  }
}
 
export class AppError extends Error {
  constructor(
    message: string,
    public statusCode: number
  ) {
    super(message);
    this.name = 'AppError';
  }
}

What Changed?

Let's diff the key changes:

1. Import swap

// Before
import { memoryStore } from '../store/memoryStore';
 
// After
import { prismaStore } from '../store/prismaStore';

2. All store calls now use await

// Before (synchronous)
const existing = memoryStore.findByOriginalUrl(originalUrl);
 
// After (asynchronous)
const existing = await prismaStore.findByOriginalUrl(originalUrl);

3. Fire-and-forget click tracking

// Don't await the click count increment — the user shouldn't wait for analytics
prismaStore.incrementClickCount(shortCode).catch((err) => {
  console.error('Failed to increment click count:', err);
});

This is a deliberate optimization. The redirect is time-critical — users expect it in milliseconds. Click counting can happen in the background. If it fails, we log it but don't slow down the redirect.

Graceful Shutdown with Prisma

We need to disconnect from the database when the server shuts down. Update the entry point:

// src/index.ts
 
import app from './app';
import { env } from './config/env';
import { prisma } from './lib/prisma';
 
const server = app.listen(env.port, () => {
  console.log(`🚀 URL Shortener running at ${env.baseUrl}`);
  console.log(`   Environment: ${env.nodeEnv}`);
  console.log(`   Health check: ${env.baseUrl}/health`);
});
 
// Graceful shutdown
async function shutdown() {
  console.log('Shutting down gracefully...');
 
  server.close(async () => {
    await prisma.$disconnect();
    console.log('Database disconnected');
    process.exit(0);
  });
 
  // Force shutdown after 10 seconds
  setTimeout(() => {
    console.error('Forced shutdown after timeout');
    process.exit(1);
  }, 10_000);
}
 
process.on('SIGTERM', shutdown);
process.on('SIGINT', shutdown);

Without prisma.$disconnect(), the process might exit with open database connections. PostgreSQL has a limited number of connections (default: 100), and leaked connections can exhaust that limit over time.

Updating the Health Check

Let's make the health check verify the database connection too:

// src/app.ts (updated health check)
 
import express from 'express';
import urlRoutes from './routes/urlRoutes';
import redirectRoutes from './routes/redirectRoutes';
import { errorHandler } from './middleware/errorHandler';
import { prisma } from './lib/prisma';
 
const app = express();
 
// Middleware
app.use(express.json({ limit: '10kb' }));
 
// Health check — includes database connectivity
app.get('/health', async (_req, res) => {
  try {
    await prisma.$queryRaw`SELECT 1`;
    res.json({
      status: 'ok',
      timestamp: new Date().toISOString(),
      database: 'connected',
    });
  } catch {
    res.status(503).json({
      status: 'error',
      timestamp: new Date().toISOString(),
      database: 'disconnected',
    });
  }
});
 
// API routes (must come before redirect catch-all)
app.use('/api', urlRoutes);
 
// Redirect catch-all (must be last)
app.use(redirectRoutes);
 
// Error handler (must be after all routes)
app.use(errorHandler);
 
export default app;

A health check that always returns ok is useless. By pinging the database, load balancers and monitoring systems can detect when the database is down and route traffic away.

Testing Everything

Make sure PostgreSQL is running:

docker compose up -d

Start the dev server:

npm run dev

Test 1: Health Check with Database

curl http://localhost:3000/health

{
  "status": "ok",
  "timestamp": "2026-03-21T10:00:00.000Z",
  "database": "connected"
}

Test 2: Shorten a URL (Persisted!)

curl -X POST http://localhost:3000/api/shorten \
  -H "Content-Type: application/json" \
  -d '{"url": "https://github.com/prisma/prisma"}'

{
  "shortCode": "xK9mP2q",
  "shortUrl": "http://localhost:3000/xK9mP2q",
  "originalUrl": "https://github.com/prisma/prisma",
  "createdAt": "2026-03-21T10:00:05.000Z"
}

Test 3: Verify Persistence

Restart the server (Ctrl+C, then npm run dev), and the URL should still work:

curl http://localhost:3000/api/urls/xK9mP2q

{
  "shortCode": "xK9mP2q",
  "shortUrl": "http://localhost:3000/xK9mP2q",
  "originalUrl": "https://github.com/prisma/prisma",
  "createdAt": "2026-03-21T10:00:05.000Z",
  "clickCount": 0
}

Data survives restarts — that's the whole point of a database.

Test 4: Redirect and Click Counting

# Redirect (follow with -L to see the final destination)
curl -v http://localhost:3000/xK9mP2q 2>&1 | grep "Location:"

< Location: https://github.com/prisma/prisma

# Check click count increased
curl http://localhost:3000/api/urls/xK9mP2q | jq .clickCount

Test 5: Duplicate URL Deduplication

# Shorten the same URL again — returns existing short code
curl -X POST http://localhost:3000/api/shorten \
  -H "Content-Type: application/json" \
  -d '{"url": "https://github.com/prisma/prisma"}'

{
  "shortCode": "xK9mP2q",
  "shortUrl": "http://localhost:3000/xK9mP2q",
  "originalUrl": "https://github.com/prisma/prisma",
  "createdAt": "2026-03-21T10:00:05.000Z"
}

Same shortCode — no duplicate created.

Test 6: Verify Data in PostgreSQL Directly

docker exec -it url-shortener-db psql -U urlshortener -d urlshortener \
  -c "SELECT short_code, original_url, click_count FROM urls;"

 short_code |            original_url            | click_count
------------+------------------------------------+-------------
 xK9mP2q    | https://github.com/prisma/prisma   |           1

Connection Pooling

Every database query needs a connection. Opening a connection takes time (TCP handshake, authentication, TLS negotiation). A connection pool keeps a set of connections open and reuses them across queries.

How Prisma 7 Connection Pooling Works

In Prisma 7, the PrismaPg adapter manages a pg.Pool under the hood. You configure the pool through the adapter, not through URL parameters:

// src/lib/prisma.ts
 
const adapter = new PrismaPg({
  connectionString: process.env.DATABASE_URL,
  pool: {
    max: 10,                // Max connections (default: 10)
    idleTimeoutMillis: 30000, // Close idle connections after 30s
    connectionTimeoutMillis: 5000, // Timeout waiting for a connection
  },
});

Pool Configuration Parameters

Parameter	Default	Description
`max`	10	Max connections in the pool
`idleTimeoutMillis`	10000 (10s)	How long an idle connection stays open
`connectionTimeoutMillis`	0 (no timeout)	How long to wait for a free connection
`min`	0	Min connections to keep open

Note: In older Prisma versions (5/6), you configured the pool via URL parameters like ?connection_limit=10&pool_timeout=10. In Prisma 7, use the adapter's pool option instead — URL parameters are ignored by the pg driver for pool settings.

How Many Connections Do You Need?

A common mistake is setting the pool too large. More connections ≠ better performance. Each connection consumes memory on the database server (~10MB per connection in PostgreSQL).

Rules of thumb:

Development: 2-5 connections is fine
Production (single server): num_cpus * 2 + 1 is a good starting point
Production (multiple servers): Divide the total pool across all servers. If PostgreSQL allows 100 connections and you have 4 servers, each gets 25

// For production with 4 CPU cores:
// Pool = 4 * 2 + 1 = 9 connections
const adapter = new PrismaPg({
  connectionString: process.env.DATABASE_URL,
  pool: { max: 9 },
});

Monitoring Connection Usage

You can check active connections directly in PostgreSQL:

docker exec -it url-shortener-db psql -U urlshortener -d urlshortener \
  -c "SELECT count(*) FROM pg_stat_activity WHERE datname = 'urlshortener';"

Prisma Studio — Visual Database Browser

Prisma includes a built-in database GUI:

npx prisma studio

This opens a web interface at http://localhost:5555 where you can:

Browse all records in your tables
Edit data directly
Filter and sort records
See relationships between tables

It's incredibly useful for debugging during development.

Adding Prisma to Your Workflow

Useful Prisma Commands

# Generate client after schema changes (without migration)
npx prisma generate
 
# Create and apply a migration
npx prisma migrate dev --name describe_your_change
 
# Apply migrations in production (no prompts)
npx prisma migrate deploy
 
# Reset database (drops all data — development only!)
npx prisma migrate reset
 
# Open database GUI
npx prisma studio
 
# Format schema file
npx prisma format
 
# Validate schema
npx prisma validate

Update .gitignore

The .gitignore from Post #2 already covers what we need (node_modules/, dist/, .env, *.log). No changes necessary.

The prisma/migrations/ directory should be committed — it's your database version history. The generated client in node_modules/.prisma/client is regenerated on npm install via the postinstall hook.

Update Package Scripts

{
  "scripts": {
    "dev": "nodemon --watch src --ext ts --exec tsx src/index.ts",
    "build": "tsc",
    "start": "node dist/index.js",
    "lint": "tsc --noEmit",
    "db:migrate": "prisma migrate dev",
    "db:push": "prisma db push",
    "db:studio": "prisma studio",
    "db:reset": "prisma migrate reset",
    "postinstall": "prisma generate"
  }
}

The postinstall script ensures the Prisma Client is generated whenever someone runs npm install — essential for CI/CD pipelines and new team members.

Understanding the Data Flow

Let's trace a complete shorten → redirect flow with the database:

Common Mistakes to Avoid

1. Creating Multiple Prisma Client Instances

// WRONG — new adapter + client on every import
export function getDb() {
  const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL });
  return new PrismaClient({ adapter });
}
 
// CORRECT — singleton pattern
const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL });
const prisma = new PrismaClient({ adapter });
export { prisma };

Each PrismaClient instance creates its own connection pool via the adapter. Five instances = five pools = five times the connections. Use the singleton pattern from our lib/prisma.ts.

2. Not Awaiting Prisma Queries

// WRONG — returns a Promise, not the result
const url = prismaStore.findByShortCode(code);
console.log(url.shortCode); // Error: shortCode doesn't exist on Promise
 
// CORRECT
const url = await prismaStore.findByShortCode(code);

This is why TypeScript is valuable — it catches this at compile time if your types are right.

3. Forgetting to Run Migrations in Production

# Development — interactive, generates migration files
npx prisma migrate dev --name add_expires_at
 
# Production — non-interactive, applies pending migrations
npx prisma migrate deploy

In production, always use migrate deploy. It applies pending migrations without generating new ones or prompting for confirmation.

4. Missing Indexes on Frequently Queried Columns

// SLOW — no index on originalUrl means full table scan
model Url {
  originalUrl String
}
 
// FAST — index enables O(log n) lookup
model Url {
  originalUrl String
  @@index([originalUrl])
}

Check your query patterns and add indexes accordingly. You can use EXPLAIN ANALYZE in PostgreSQL to verify that your queries use indexes:

EXPLAIN ANALYZE SELECT * FROM urls WHERE short_code = 'xK9mP2q';

Look for "Index Scan" in the output — if you see "Seq Scan", your index isn't being used.

5. Not Handling Connection Failures

import { Prisma } from '@prisma/client';
 
// WRONG — crashes on database error
const record = await prisma.url.findUnique({ where: { shortCode } });
 
// CORRECT — handle gracefully
try {
  const record = await prisma.url.findUnique({ where: { shortCode } });
} catch (error) {
  if (error instanceof Prisma.PrismaClientKnownRequestError) {
    // Handle specific Prisma errors (e.g., unique constraint violation P2002)
  }
  throw error; // Re-throw unknown errors
}

6. Using `db push` in Production

# Development only — syncs schema without creating migration files
npx prisma db push
 
# Production — always use migrations for auditable, reversible changes
npx prisma migrate deploy

db push is great for rapid prototyping but doesn't create migration files. In production, you need the audit trail that migrations provide.

Project State After This Post

url-shortener/
├── prisma/
│   ├── migrations/
│   │   └── 20260321100000_init/
│   │       └── migration.sql
│   └── schema.prisma
├── src/
│   ├── config/
│   │   └── env.ts
│   ├── lib/
│   │   └── prisma.ts              # NEW — Prisma client singleton
│   ├── middleware/
│   │   ├── errorHandler.ts
│   │   └── validateRequest.ts
│   ├── routes/
│   │   ├── urlRoutes.ts
│   │   └── redirectRoutes.ts
│   ├── services/
│   │   └── urlService.ts          # UPDATED — uses prismaStore
│   ├── store/
│   │   ├── memoryStore.ts         # Kept for reference (unused)
│   │   └── prismaStore.ts         # NEW — database-backed store
│   ├── types/
│   │   └── url.ts
│   ├── utils/
│   │   └── shortCode.ts
│   ├── app.ts                     # UPDATED — health check with DB
│   └── index.ts                   # UPDATED — graceful shutdown
├── docker-compose.yml             # NEW — PostgreSQL
├── prisma.config.ts               # NEW — Prisma 7 configuration
├── .env                           # UPDATED — DATABASE_URL
├── .env.example
├── .gitignore
├── package.json                   # UPDATED — db scripts + dependencies
└── tsconfig.json

What Changed from Post #2

File	Change	Why
`docker-compose.yml`	New	Run PostgreSQL locally
`prisma/schema.prisma`	New	Database schema definition
`prisma.config.ts`	New	Prisma 7 configuration (auto-generated)
`src/lib/prisma.ts`	New	Prisma client singleton with pg adapter
`src/store/prismaStore.ts`	New	Database-backed store
`src/services/urlService.ts`	Updated	Uses prismaStore instead of memoryStore
`src/app.ts`	Updated	Health check pings database
`src/index.ts`	Updated	Graceful Prisma disconnect
`.env`	Updated	Added DATABASE_URL
`package.json`	Updated	Added db scripts, adapter-pg, and postinstall

What's Next?

We've got persistent storage — URLs survive restarts, clicks are counted atomically, and our schema is version-controlled. But our short code generation is still basic. In Post #4, we'll tackle:

Base62 encoding vs nanoid — which approach to use and when
Collision detection and resolution strategies
Custom alias validation and reserved words
Guaranteed uniqueness with database constraints

The short code generator is the heart of a URL shortener, and getting it right matters for scale.

Series: Build a URL Shortener
Previous: Phase 1: Project Setup & URL Shortening API
Next: Phase 3: Short Code Generation