How to Build a URL Shortener with Node.js and PostgreSQL

CREATE DATABASE urlshortener;

\c urlshortener

CREATE TABLE links (
  id         SERIAL PRIMARY KEY,
  slug       VARCHAR(20) NOT NULL UNIQUE,
  target_url TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  expires_at TIMESTAMPTZ
);

CREATE TABLE clicks (
  id         SERIAL PRIMARY KEY,
  link_id    INTEGER REFERENCES links(id) ON DELETE CASCADE,
  clicked_at TIMESTAMPTZ DEFAULT NOW(),
  user_agent TEXT,
  ip_address INET
);

CREATE INDEX idx_clicks_link_id ON clicks(link_id);
CREATE INDEX idx_links_slug ON links(slug);

mkdir url-shortener && cd url-shortener
npm init -y
npm install express pg nanoid express-rate-limit dotenv
npm install -D typescript @types/express @types/pg ts-node-dev

import express from 'express';
import { Pool } from 'pg';
import { nanoid } from 'nanoid';
import rateLimit from 'express-rate-limit';

const app = express();
app.use(express.json());

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

const createLimiter = rateLimit({ windowMs: 60_000, max: 20 });

app.post('/links', createLimiter, async (req, res) => {
  const { url, slug, expiresAt } = req.body;
  if (!url) return res.status(400).json({ error: 'url is required' });
  const shortSlug = slug ?? nanoid(7);
  const result = await pool.query(
    'INSERT INTO links (slug, target_url, expires_at) VALUES ($1, $2, $3) RETURNING *',
    [shortSlug, url, expiresAt ?? null]
  );
  res.status(201).json(result.rows[0]);
});

app.get('/:slug', async (req, res) => {
  const { slug } = req.params;
  const result = await pool.query(
    'SELECT * FROM links WHERE slug = $1 AND (expires_at IS NULL OR expires_at > NOW())',
    [slug]
  );
  if (result.rows.length === 0) return res.status(404).json({ error: 'Link not found' });
  const link = result.rows[0];
  await pool.query(
    'INSERT INTO clicks (link_id, user_agent, ip_address) VALUES ($1, $2, $3)',
    [link.id, req.headers['user-agent'] ?? null, req.ip]
  );
  res.redirect(301, link.target_url);
});

app.get('/links/:slug/stats', async (req, res) => {
  const result = await pool.query(
    'SELECT COUNT(*) AS total_clicks FROM clicks c JOIN links l ON c.link_id = l.id WHERE l.slug = $1',
    [req.params.slug]
  );
  res.json(result.rows[0]);
});

app.listen(3000, () => console.log('Listening on port 3000'));

npx tsc
pm2 start dist/index.js --name url-shortener
pm2 save
pm2 startup