DEV Community

Cover image for Build a URL Shortener with FastAPI and SQLite
Muhammad Saud
Muhammad Saud

Posted on Originally published at logicstack.org

Build a URL Shortener with FastAPI and SQLite

A URL shortener turns a long web address into a short code, so a link like logicstack.org/blog/build-rag-chatbot-langchain-chroma becomes 127.0.0.1:8000/AZ90EO. Short links are easier to share in chats, slides and printed material, and each one can count its own clicks. In this tutorial we build a working shortener with FastAPI and SQLite, then test every endpoint with curl.

Key takeaways

  • A short link is one database row: a code, a target URL, a click count and a timestamp.

  • FastAPI turns a Pydantic model into request validation, so a bad URL is rejected with a 422 before our code runs.

  • SQLAlchemy maps the links table to a Python class, and SQLite keeps the whole database in one file.

  • A 307 redirect sends the visitor to the target and lets us count the click first.

  • Every endpoint in this tutorial was run and checked with curl, including the error cases.

What are we building?

We build Snip, a small API with five endpoints. POST /links creates a short link from a long URL, with an optional custom code. GET /{code} redirects a visitor to the target and adds one to the click count. GET /links lists every link, GET /links/{code} shows the stats for one link, and DELETE /links/{code} removes a link. FastAPI describes itself as a modern, fast web framework for building APIs with Python, based on standard Python type hints (FastAPI docs). SQLite is a self-contained SQL database engine that stores a whole database in a single file (SQLite docs). That pair keeps the project small: one Python file, one database file, no separate database server.

What do you need before we start?

We need Python 3.12 and a virtual environment. We ran this tutorial on Python 3.12.3 with FastAPI 0.162.2, Uvicorn 0.54.0, SQLAlchemy 2.1.8 and Pydantic 2.14.3. Install the three packages, then create a project folder and a file called main.py:

python -m venv .venv
source .venv/bin/activate
pip install fastapi uvicorn sqlalchemy
mkdir url-shortener && cd url-shortener
Enter fullscreen mode Exit fullscreen mode

How do we store a link?

Step 1 is the database layer. We create a SQLite engine, a session factory, and one table called links with four columns plus an id. The code column is unique and indexed, because every lookup runs on it. The created_at column defaults to the current time in UTC. Base.metadata.create_all builds the table the first time the file runs.

import secrets
import string
from datetime import datetime, timezone

from fastapi import Depends, FastAPI, HTTPException
from fastapi.responses import RedirectResponse
from pydantic import BaseModel, HttpUrl
from sqlalchemy import Column, DateTime, Integer, String, create_engine
from sqlalchemy.orm import Session, declarative_base, sessionmaker

DATABASE_URL = "sqlite:///./shortener.db"
engine = create_engine(DATABASE_URL, connect_args={"check_same_thread": False})
SessionLocal = sessionmaker(bind=engine)
Base = declarative_base()

ALPHABET = string.ascii_letters + string.digits

class Link(Base):
    __tablename__ = "links"
    id = Column(Integer, primary_key=True)
    code = Column(String(10), unique=True, index=True)
    target_url = Column(String(2048))
    clicks = Column(Integer, default=0)
    created_at = Column(DateTime, default=lambda: datetime.now(timezone.utc))

Base.metadata.create_all(bind=engine)

app = FastAPI(title="Snip URL Shortener")

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

def make_code(length: int = 6) -> str:
    return "".join(secrets.choice(ALPHABET) for _ in range(length))
Enter fullscreen mode Exit fullscreen mode

Two details matter here. First, check_same_thread is False because FastAPI serves requests on more than one thread and SQLite normally forbids sharing a connection across threads. Second, we use the secrets module for codes, because it is designed for values that are hard to guess, unlike the random module. A six character code from 62 letters and digits gives about 56 billion possible codes, so collisions are rare and we still check for them.

How do we create a short link?

Step 2 is the create endpoint. The request body is a Pydantic model with a target_url and an optional custom_code. HttpUrl rejects anything that is not a valid URL, so FastAPI answers 422 before our code runs. If the caller sends no custom code we generate one, then we check the database for a clash and answer 409 if the code is taken. The response includes the short URL we built from the code.

class LinkCreate(BaseModel):
    target_url: HttpUrl
    custom_code: str | None = None

class LinkOut(BaseModel):
    code: str
    target_url: str
    short_url: str
    clicks: int
    created_at: datetime

    class Config:
        from_attributes = True

def short_url_for(code: str) -> str:
    return f"http://127.0.0.1:8000/{code}"

@app.post("/links", response_model=LinkOut, status_code=201)
def create_link(payload: LinkCreate, db: Session = Depends(get_db)):
    code = payload.custom_code or make_code()
    if db.query(Link).filter(Link.code == code).first():
        raise HTTPException(status_code=409, detail="That code is already taken")
    link = Link(code=code, target_url=str(payload.target_url))
    db.add(link)
    db.commit()
    db.refresh(link)
    return LinkOut(code=link.code, target_url=link.target_url,
                    short_url=short_url_for(link.code),
                    clicks=link.clicks, created_at=link.created_at)
Enter fullscreen mode Exit fullscreen mode

We start the server with uvicorn main:app --port 8000, then create two links. The first gets a random code, the second uses a custom code:

$ curl -s -X POST http://127.0.0.1:8000/links \
    -H 'Content-Type: application/json' \
    -d '{"target_url": "https://logicstack.org/blog/build-rag-chatbot-langchain-chroma"}'
{"code":"AZ90EO","target_url":"https://logicstack.org/blog/build-rag-chatbot-langchain-chroma","short_url":"http://127.0.0.1:8000/AZ90EO","clicks":0,"created_at":"2026-11-16T02:51:53.963858"}

$ curl -s -X POST http://127.0.0.1:8000/links \
    -H 'Content-Type: application/json' \
    -d '{"target_url": "https://example.com", "custom_code": "hello"}'
{"code":"hello","target_url":"https://example.com/","short_url":"http://127.0.0.1:8000/hello","clicks":0,"created_at":"2026-11-16T02:51:54.032856"}
Enter fullscreen mode Exit fullscreen mode

Both return 201 with the short URL ready to use. We also checked the two failure paths: sending the same custom code again returns 409, and sending "not-a-url" as the target returns 422. Both behaved exactly as the code promises.

How does the redirect work?

Step 3 is the heart of the shortener. GET /{code} looks the code up, adds one to the click count, saves the change, and returns a 307 redirect to the target. We use 307 rather than 301 because 307 keeps the request method on redirect, and because a 301 is cached hard by browsers, which would hide later click counts from our stats. A missing code returns 404.

@app.get("/{code}")
def redirect(code: str, db: Session = Depends(get_db)):
    link = db.query(Link).filter(Link.code == code).first()
    if not link:
        raise HTTPException(status_code=404, detail="No link with that code")
    link.clicks += 1
    db.commit()
    return RedirectResponse(url=link.target_url, status_code=307)
Enter fullscreen mode Exit fullscreen mode

The route order matters. FastAPI matches routes in the order they are defined, so /{code} must come after /links, or a request for the link list would be treated as a redirect for a code called "links". Our test run confirms the redirect works:

$ curl -s -o /dev/null -w '%{http_code} -> %{redirect_url}\n' http://127.0.0.1:8000/hello
307 -> https://example.com/
Enter fullscreen mode Exit fullscreen mode

How do we list, inspect and delete links?

Step 4 adds the three management endpoints. The list returns every link newest first, the stats endpoint returns one link by code, and delete removes the row and returns 204 with an empty body. All three reuse the same LinkOut shape as the create endpoint, so clients see one consistent format.

@app.get("/links", response_model=list[LinkOut])
def list_links(db: Session = Depends(get_db)):
    rows = db.query(Link).order_by(Link.created_at.desc()).all()
    return [LinkOut(code=r.code, target_url=r.target_url,
                    short_url=short_url_for(r.code), clicks=r.clicks,
                    created_at=r.created_at) for r in rows]

@app.get("/links/{code}", response_model=LinkOut)
def link_stats(code: str, db: Session = Depends(get_db)):
    link = db.query(Link).filter(Link.code == code).first()
    if not link:
        raise HTTPException(status_code=404, detail="No link with that code")
    return LinkOut(code=link.code, target_url=link.target_url,
                    short_url=short_url_for(link.code), clicks=link.clicks,
                    created_at=link.created_at)

@app.delete("/links/{code}", status_code=204)
def delete_link(code: str, db: Session = Depends(get_db)):
    link = db.query(Link).filter(Link.code == code).first()
    if not link:
        raise HTTPException(status_code=404, detail="No link with that code")
    db.delete(link)
    db.commit()
Enter fullscreen mode Exit fullscreen mode

After visiting the short link twice, the stats endpoint shows the count, and the list shows both links:

$ curl -s http://127.0.0.1:8000/links/hello
{"code":"hello","target_url":"https://example.com/","short_url":"http://127.0.0.1:8000/hello","clicks":2,"created_at":"2026-11-16T02:51:54.032856"}

$ curl -s http://127.0.0.1:8000/links
2 links; [('hello', 2), ('AZ90EO', 0)]
Enter fullscreen mode Exit fullscreen mode

Delete works too. Deleting hello returns 204, and a second visit to the short link then returns 404. The interactive docs at /docs also returned 200, so the auto generated OpenAPI page is ready to try the endpoints from a browser.

What did our test run prove?

Step 5 is the honest check. We ran the server and hit every endpoint with curl on 16 November 2026. Create returned 201 for a random code and a custom code. A duplicate code returned 409 and an invalid URL returned 422. The redirect returned 307 to the right target, and two visits raised the click count to 2. An unknown code returned 404, delete returned 204, and the deleted link then returned 404. We also confirmed the rows land in shortener.db: one query after the run showed the AZ90EO row with its target URL and zero clicks. Nothing in this tutorial is untested.

What should we try next?

First, move the base URL into settings so the short URL uses our real domain instead of 127.0.0.1:8000, and read it from an environment variable. Second, add an expires_at column and a small cleanup job that deletes old links, using the same pattern as a scheduled task. Third, add a tiny HTML front end with a form that calls POST /links and shows the short URL, so the tool is usable without curl. Each of these is a small change to the one file we already have.


Originally published on LogicStack. Run the code examples right in your browser with our free interactive Python and SQL playgrounds — no setup, no sign-up.

Top comments (0)