Build a URL shortener in Python and SQLite (with tests)

A URL shortener turns a long link into a short code such as /wZUSXi, and sends visitors to the original address. It is a classic project because it touches real web ideas: HTTP methods, redirects, status codes, a database and input validation. This version uses only Python's standard library and SQLite, so nothing needs installing. The code and the outputs below were run with Python 3 before publishing.

How it works

1. A client sends POST /shorten with JSON {"url": "https://..."}.
2. The server validates the URL, generates a random 6-character code and stores code -> url in SQLite.
3. Anyone who opens GET /<code> is sent to the original URL with an HTTP 302 redirect.
4. Each visit increases a hit counter.

The code

Save as shortener.py:

import json
import os
import secrets
import sqlite3
import string
from http.server import BaseHTTPRequestHandler, HTTPServer
from urllib.parse import urlparse

ALPHABET = string.ascii_letters + string.digits


def make_db(path=":memory:"):
    db = sqlite3.connect(path, check_same_thread=False)
    db.execute("CREATE TABLE IF NOT EXISTS links (code TEXT PRIMARY KEY, url TEXT NOT NULL, hits INTEGER NOT NULL DEFAULT 0)")
    return db


def valid_url(url):
    parts = urlparse(url)
    return parts.scheme in ("http", "https") and bool(parts.netloc) and len(url) <= 2000


def create_link(db, url):
    for _ in range(5):  # a collision is very unlikely, but retry just in case
        code = "".join(secrets.choice(ALPHABET) for _ in range(6))
        try:
            with db:
                db.execute("INSERT INTO links (code, url) VALUES (?, ?)", (code, url))
            return code
        except sqlite3.IntegrityError:
            continue
    raise RuntimeError("could not generate a unique code")


def resolve(db, code):
    with db:
        db.execute("UPDATE links SET hits = hits + 1 WHERE code = ?", (code,))
    row = db.execute("SELECT url FROM links WHERE code = ?", (code,)).fetchone()
    return row[0] if row else None


def make_handler(db):
    class Handler(BaseHTTPRequestHandler):
        def _send(self, status, body=b"", headers=None):
            self.send_response(status)
            for key, value in (headers or {}).items():
                self.send_header(key, value)
            self.send_header("Content-Length", str(len(body)))
            self.end_headers()
            self.wfile.write(body)

        def do_POST(self):
            if self.path != "/shorten":
                return self._send(404)
            length = int(self.headers.get("Content-Length", 0))
            if length > 10_000:
                return self._send(413)
            try:
                url = json.loads(self.rfile.read(length)).get("url", "")
            except (ValueError, AttributeError):
                return self._send(400, b'{"error":"invalid JSON"}', {"Content-Type": "application/json"})
            if not valid_url(url):
                return self._send(400, b'{"error":"url must start with http:// or https://"}', {"Content-Type": "application/json"})
            body = json.dumps({"code": create_link(db, url)}).encode()
            self._send(201, body, {"Content-Type": "application/json"})

        def do_GET(self):
            url = resolve(db, self.path.lstrip("/"))
            if url is None:
                return self._send(404, b"Not found")
            self._send(302, headers={"Location": url})

        def log_message(self, *args):
            pass  # keep the console quiet

    return Handler


if __name__ == "__main__":
    port = int(os.environ.get("PORT", 8000))
    print(f"Short links on http://127.0.0.1:{port}")
    HTTPServer(("127.0.0.1", port), make_handler(make_db("links.db"))).serve_forever()

Run it

$ PORT=8931 python3 shortener.py
Short links on http://127.0.0.1:8931

$ curl -X POST localhost:8931/shorten -d '{"url":"https://example.com/docs/getting-started"}'
{"code": "wZUSXi"}

$ curl -i localhost:8931/wZUSXi
HTTP/1.0 302 Found
Location: https://example.com/docs/getting-started

$ curl -X POST localhost:8931/shorten -d '{"url":"javascript:alert(1)"}'
{"error":"url must start with http:// or https://"}        (status 400)

$ curl localhost:8931/zzzzzz
Not found                                                  (status 404)

$ curl -X POST localhost:8931/shorten -d 'oops'
{"error":"invalid JSON"}                                   (status 400)

(The code is random, so yours will differ.)

Why it is built this way

Random codes with secrets. Six characters from 62 (letters and digits) give about 56 billion combinations. We use the secrets module rather than random so codes are not predictable: with sequential ids (1, 2, 3...) anyone could walk through every link. A primary key on code guarantees uniqueness, and on a rare collision the loop tries again.

URL validation is a security feature. The check allows only http and https with a hostname. Without it, someone could store javascript:alert(1) or a file: address, and your redirect would send users to something dangerous. The tests prove those are rejected with 400. Also note the length limit and the 10,000-byte body limit, so nobody can store huge data.

302 vs 301. A 302 (temporary) redirect is used so that browsers do not cache it forever, which means you can still count hits and later change or disable a link. A 301 (permanent) is cached by browsers and search engines.

Parameterised SQL. Every query uses ? placeholders, never string formatting, which prevents SQL injection.

Hit counting is one UPDATE ... SET hits = hits + 1, which is atomic in the database. Reading the value and adding one in Python could lose counts if two requests arrive together.

Quiet logging. log_message is overridden only to keep demos clean. In a real service you would keep access logs.

Tests

The tests start the real server on a free port (0 lets the operating system choose), in a background thread, and use an in-memory database so they never touch real data. Save as test_shortener.py:

import http.client
import json
import threading
import unittest
from http.server import HTTPServer

import shortener


class ShortenerTest(unittest.TestCase):
    @classmethod
    def setUpClass(cls):
        cls.db = shortener.make_db()
        cls.server = HTTPServer(("127.0.0.1", 0), shortener.make_handler(cls.db))
        cls.port = cls.server.server_address[1]
        threading.Thread(target=cls.server.serve_forever, daemon=True).start()

    @classmethod
    def tearDownClass(cls):
        cls.server.shutdown()

    def request(self, method, path, body=None):
        conn = http.client.HTTPConnection("127.0.0.1", self.port)
        conn.request(method, path, body=json.dumps(body) if body is not None else None)
        return conn.getresponse()

    def test_shorten_then_redirect(self):
        res = self.request("POST", "/shorten", {"url": "https://example.com/a/very/long/path"})
        self.assertEqual(res.status, 201)
        code = json.loads(res.read())["code"]
        self.assertEqual(len(code), 6)
        redirect = self.request("GET", "/" + code)
        self.assertEqual(redirect.status, 302)
        self.assertEqual(redirect.getheader("Location"), "https://example.com/a/very/long/path")

    def test_rejects_dangerous_or_bad_urls(self):
        for bad in ["javascript:alert(1)", "ftp://x.com/file", "not a url", ""]:
            self.assertEqual(self.request("POST", "/shorten", {"url": bad}).status, 400, bad)

    def test_unknown_code_is_404(self):
        self.assertEqual(self.request("GET", "/nope12").status, 404)

    def test_counts_hits(self):
        code = shortener.create_link(self.db, "https://example.org")
        self.request("GET", "/" + code).read()
        self.request("GET", "/" + code).read()
        self.assertEqual(self.db.execute("SELECT hits FROM links WHERE code=?", (code,)).fetchone()[0], 2)


if __name__ == "__main__":
    unittest.main()

$ python3 -m unittest -v
test_counts_hits ... ok
test_rejects_dangerous_or_bad_urls ... ok
test_shorten_then_redirect ... ok
test_unknown_code_is_404 ... ok

Ran 4 tests in 0.028s

OK

Known limits (read before using it for real)

• http.server is for learning, not production. It handles requests simply and is not hardened. For real use, put the same logic in a framework (Flask, FastAPI) behind a proper server.
• No authentication. Anyone can create links. Open shorteners are often abused for spam and phishing. Real services require login, rate limits and a way to report and remove bad links.
• No rate limiting. One client could fill your database.
• No custom codes, expiry or analytics beyond a counter.
• An open redirect risk if you ever accept a redirect target from the query string. Here targets come only from stored, validated links.
• Only the stored URL is checked, not what is behind it. A valid https link can still point to a harmful site.

Challenges

1. Add GET /stats/<code> that returns the hit count as JSON.
2. Let users pick a custom code (check that it is not taken, and limit allowed characters).
3. Add an expiry time and return 410 Gone after it.
4. Add a per-IP rate limit.
5. Rewrite the handler in Flask or FastAPI and compare how much code the framework saves.
6. Add a simple HTML form so people can use it in a browser.

Takeaway: a URL shortener is a small project with real lessons: unpredictable ids, strict URL validation, correct status codes, atomic counters and tests. Treat any public "create a link" feature as something attackers will try to abuse.

---

Build-along projects series, Part 2 (tools you can reuse): 1 of 5.