Build a URL Shortener: Database Design & Storage

// Type-safe — TypeScript biết chính xác shape của result
const url = await prisma.url.findUnique({
  where: { shortCode: 'aBc1X9z' }
});
// url có type là Url | null — không phải `any`

# 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:

docker compose up -d

docker compose ps

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

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

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

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

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

npx prisma init

url-shortener/
├── prisma/
│   └── schema.prisma        # MỚI — database schema
├── src/
│   ├── config/env.ts
│   ├── middleware/
│   ├── routes/
│   ├── services/
│   ├── store/
│   │   └── memoryStore.ts   # Sẽ được thay thế
│   ├── types/
│   ├── app.ts
│   └── index.ts
├── docker-compose.yml        # MỚI — PostgreSQL
├── prisma.config.ts          # MỚI — cấu hình Prisma
├── .env                      # ĐÃ CẬP NHẬT — thêm DATABASE_URL
└── ...

// 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")
}

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")

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

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

npx prisma migrate dev --name init

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

-- 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");

url-shortener/
├── prisma/
│   ├── migrations/
│   │   └── 20260321100000_init/
│   │       └── migration.sql         # SQL được generate
│   └── schema.prisma                 # Định nghĩa schema
├── src/
│   ├── config/env.ts
│   ├── lib/
│   │   └── prisma.ts                 # MỚI — Prisma client singleton
│   ├── store/
│   │   ├── memoryStore.ts            # CŨ — sẽ được thay thế
│   │   └── prismaStore.ts            # MỚI — database-backed store
│   └── ...
├── docker-compose.yml
├── prisma.config.ts                   # Cấu hình Prisma 7
└── ...

// 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;
}

// Prisma 5/6 — embedded query engine (cách cũ)
const prisma = new PrismaClient();
 
// Prisma 7 — driver adapter (cách mới)
const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL });
const prisma = new PrismaClient({ adapter });

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

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

// 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,
    };
  },
};

// findUnique — cho column có @unique constraint
await prisma.url.findUnique({ where: { shortCode } });
 
// findFirst — cho column không unique, trả kết quả đầu tiên
await prisma.url.findFirst({ where: { originalUrl } });

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

// MEMORY STORE — có thể race condition
const record = urls.get(shortCode);
if (record) {
  record.clickCount += 1;  // Hai request đọc cùng lúc đều thấy clickCount=5
}                           // Cả hai ghi 6, mất một click

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

// 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 URL đã được shorten chưa
    const existing = await prismaStore.findByOriginalUrl(originalUrl);
    if (existing && !customAlias) {
      return this.toCreateResponse(existing.shortCode, existing.originalUrl, existing.createdAt);
    }
 
    // Dùng custom alias hoặc generate 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();
    }
 
    // Lưu 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 — không làm chậm 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';
  }
}

// Trước
import { memoryStore } from '../store/memoryStore';
 
// Sau
import { prismaStore } from '../store/prismaStore';

// Trước (synchronous)
const existing = memoryStore.findByOriginalUrl(originalUrl);
 
// Sau (asynchronous)
const existing = await prismaStore.findByOriginalUrl(originalUrl);

// Không await increment click count — user không nên chờ analytics
prismaStore.incrementClickCount(shortCode).catch((err) => {
  console.error('Failed to increment click count:', err);
});

// 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 sau 10 giây
  setTimeout(() => {
    console.error('Forced shutdown after timeout');
    process.exit(1);
  }, 10_000);
}
 
process.on('SIGTERM', shutdown);
process.on('SIGINT', shutdown);

// src/app.ts (health check đã update)
 
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 — bao gồm 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 (phải trước redirect catch-all)
app.use('/api', urlRoutes);
 
// Redirect catch-all (phải cuối cùng)
app.use(redirectRoutes);
 
// Error handler (phải sau tất cả routes)
app.use(errorHandler);
 
export default app;

docker compose up -d

npm run dev

curl http://localhost:3000/health

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

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"
}

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
}

# Redirect (dùng -L để thấy destination cuối)
curl -v http://localhost:3000/xK9mP2q 2>&1 | grep "Location:"

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

# Check click count đã tăng
curl http://localhost:3000/api/urls/xK9mP2q | jq .clickCount

1

# Shorten cùng URL lần nữa — trả về short code đã có
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"
}

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

// src/lib/prisma.ts
 
const adapter = new PrismaPg({
  connectionString: process.env.DATABASE_URL,
  pool: {
    max: 10,                // Max connection (default: 10)
    idleTimeoutMillis: 30000, // Đóng idle connection sau 30s
    connectionTimeoutMillis: 5000, // Timeout chờ connection
  },
});

// Cho production với 4 CPU core:
// Pool = 4 * 2 + 1 = 9 connections
const adapter = new PrismaPg({
  connectionString: process.env.DATABASE_URL,
  pool: { max: 9 },
});

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

npx prisma studio

# Generate client sau khi thay đổi schema (không migration)
npx prisma generate
 
# Tạo và apply migration
npx prisma migrate dev --name describe_your_change
 
# Apply migration trong production (không prompt)
npx prisma migrate deploy
 
# Reset database (xóa hết data — chỉ dùng khi develop!)
npx prisma migrate reset
 
# Mở database GUI
npx prisma studio
 
# Format schema file
npx prisma format
 
# Validate schema
npx prisma validate

{
  "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"
  }
}

// SAI — adapter + client mới mỗi lần import
export function getDb() {
  const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL });
  return new PrismaClient({ adapter });
}
 
// ĐÚNG — singleton pattern
const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL });
const prisma = new PrismaClient({ adapter });
export { prisma };

// SAI — trả về Promise, không phải result
const url = prismaStore.findByShortCode(code);
console.log(url.shortCode); // Error: shortCode không tồn tại trên Promise
 
// ĐÚNG
const url = await prismaStore.findByShortCode(code);

# Development — interactive, generate migration file
npx prisma migrate dev --name add_expires_at
 
# Production — non-interactive, apply pending migration
npx prisma migrate deploy

// CHẬM — không có index trên originalUrl nghĩa là full table scan
model Url {
  originalUrl String
}
 
// NHANH — index cho phép O(log n) lookup
model Url {
  originalUrl String
  @@index([originalUrl])
}

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

import { Prisma } from '@prisma/client';
 
// SAI — crash khi database lỗi
const record = await prisma.url.findUnique({ where: { shortCode } });
 
// ĐÚNG — handle gracefully
try {
  const record = await prisma.url.findUnique({ where: { shortCode } });
} catch (error) {
  if (error instanceof Prisma.PrismaClientKnownRequestError) {
    // Handle specific Prisma error (ví dụ: unique constraint violation P2002)
  }
  throw error; // Re-throw unknown error
}

# Chỉ dùng khi develop — sync schema không tạo migration file
npx prisma db push
 
# Production — luôn dùng migration cho auditable, reversible change
npx prisma migrate deploy

url-shortener/
├── prisma/
│   ├── migrations/
│   │   └── 20260321100000_init/
│   │       └── migration.sql
│   └── schema.prisma
├── src/
│   ├── config/
│   │   └── env.ts
│   ├── lib/
│   │   └── prisma.ts              # MỚI — Prisma client singleton
│   ├── middleware/
│   │   ├── errorHandler.ts
│   │   └── validateRequest.ts
│   ├── routes/
│   │   ├── urlRoutes.ts
│   │   └── redirectRoutes.ts
│   ├── services/
│   │   └── urlService.ts          # CẬP NHẬT — dùng prismaStore
│   ├── store/
│   │   ├── memoryStore.ts         # Giữ lại để tham khảo (không dùng)
│   │   └── prismaStore.ts         # MỚI — database-backed store
│   ├── types/
│   │   └── url.ts
│   ├── utils/
│   │   └── shortCode.ts
│   ├── app.ts                     # CẬP NHẬT — health check với DB
│   └── index.ts                   # CẬP NHẬT — graceful shutdown
├── docker-compose.yml             # MỚI — PostgreSQL
├── prisma.config.ts               # MỚI — cấu hình Prisma 7
├── .env                           # CẬP NHẬT — DATABASE_URL
├── .env.example
├── .gitignore
├── package.json                   # CẬP NHẬT — db script + dependencies
└── tsconfig.json