Build a URL Shortener from Scratch — Part 2: Database Schema and Base62 Encoding

What We’re Building

In this part, we set up the database schema and implement short code generation. By the end, you’ll have a working Base62 encoder and a migration-ready PostgreSQL schema.

Step 1: Set Up Alembic

pip install alembic sqlalchemy psycopg2-binary
alembic init alembic

Edit alembic/env.py to point to your models:

from app.models import Base
target_metadata = Base.metadata

Step 2: Define SQLAlchemy Models

# app/models.py
from sqlalchemy import Column, BigInteger, String, Text, DateTime, ForeignKey, Index
from sqlalchemy.orm import declarative_base, relationship
from datetime import datetime, timezone

Base = declarative_base()

class URL(Base):
    __tablename__ = "urls"

    id = Column(BigInteger, primary_key=True, autoincrement=True)
    short_code = Column(String(10), unique=True, nullable=False, index=True)
    original_url = Column(Text, nullable=False)
    created_at = Column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc))
    expires_at = Column(DateTime(timezone=True), nullable=True)
    click_count = Column(BigInteger, default=0)  # Denormalised counter

    clicks = relationship("Click", back_populates="url")

class Click(Base):
    __tablename__ = "clicks"

    id = Column(BigInteger, primary_key=True, autoincrement=True)
    url_id = Column(BigInteger, ForeignKey("urls.id"), nullable=False, index=True)
    clicked_at = Column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc))
    referrer = Column(Text, nullable=True)
    country = Column(String(2), nullable=True)
    user_agent = Column(Text, nullable=True)

    url = relationship("URL", back_populates="clicks")

    __table_args__ = (
        Index("idx_clicks_url_id_clicked", "url_id", "clicked_at"),
    )

Design decision: We denormalised click_count on the urls table for fast reads without JOINs. The clicks table stores the detailed analytics data.

Step 3: Generate and Run Migration

alembic revision --autogenerate -m "create urls and clicks tables"
alembic upgrade head

Verify the schema:

\d urls
\d clicks

Step 4: Implement Base62 Encoding

# app/utils/base62.py
import string

ALPHABET = string.digits + string.ascii_lowercase + string.ascii_uppercase
# "0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"
BASE = len(ALPHABET)  # 62

def encode(number: int) -> str:
    """Convert an integer to a Base62 string."""
    if number == 0:
        return ALPHABET[0]

    result = []
    while number > 0:
        number, remainder = divmod(number, BASE)
        result.append(ALPHABET[remainder])

    return "".join(reversed(result))

def decode(code: str) -> int:
    """Convert a Base62 string back to an integer."""
    number = 0
    for char in code:
        number = number * BASE + ALPHABET.index(char)
    return number

Test it:

# encode(1) → "1"
# encode(62) → "10"
# encode(1000) → "g8"
# decode("g8") → 1000
# decode(encode(99999)) → 99999 (roundtrip)

Step 5: ID Generation Strategy

We use a simple auto-incrementing BIGINT with a starting offset to avoid the shortest codes being single characters:

ID_OFFSET = 100_000  # Start codes at ~3 characters

def generate_short_code(url_id: int) -> str:
    return encode(url_id + ID_OFFSET)

This guarantees uniqueness (database handles it) and generates codes like:

ID 1 + offset → encode(100001) → "q0Z"

Step 6: Database Session Factory

# app/core/database.py
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from app.config import settings

engine = create_engine(settings.database_url, pool_size=20, max_overflow=40)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

Verification

# Create some URLs manually to verify
psql -d url_shortener

INSERT INTO urls (short_code, original_url) VALUES ('test123', 'https://example.com');
SELECT * FROM urls;
# Should return the inserted row with auto-generated ID, timestamps

# Test Base62 in Python
python -c "from app.utils.base62 import encode, decode; assert decode(encode(123456)) == 123456; print('OK')"

Summary

  • PostgreSQL schema with urls and clicks tables, denormalised click_count
  • Alembic migrations set up for schema versioning
  • Base62 encoding implemented: encode() and decode() functions
  • ID generation using auto-increment + offset for minimal short codes
  • Session factory with connection pooling ready for FastAPI

← Part 1 | Part 3 →

Advertisement