Build a URL Shortener With Flask and SQLite

A URL shortener is a classic Flask project that covers routing, database design, redirects, and analytics in a compact scope. Here's the complete implementation.

Database Schema

CREATE TABLE IF NOT EXISTS urls (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    original_url TEXT NOT NULL,
    short_code TEXT UNIQUE NOT NULL,
    alias TEXT UNIQUE,
    click_count INTEGER DEFAULT 0,
    created_at TEXT DEFAULT (datetime('now')),
    expires_at TEXT
);

Short Code Generation

import string, secrets

def generate_short_code(length=6):
    chars = string.ascii_letters + string.digits
    while True:
        code = ''.join(secrets.choice(chars) for _ in range(length))
        if not db.execute("SELECT 1 FROM urls WHERE short_code=?", (code,)).fetchone():
            return code

Create Short URL Route

@app.route('/shorten', methods=['POST'])
def shorten():
    url = request.form.get('url', '').strip()
    alias = request.form.get('alias', '').strip() or None
    
    if not url.startswith(('http://', 'https://')):
        return jsonify({'error': 'Invalid URL'}), 400
    
    code = alias or generate_short_code()
    try:
        db.execute(
            "INSERT INTO urls (original_url, short_code, alias) VALUES (?,?,?)",
            (url, code, alias)
        )
        db.commit()
    except sqlite3.IntegrityError:
        return jsonify({'error': 'Alias already taken'}), 409
    
    return jsonify({'short_url': f'https://yoursite.com/{code}'})

Redirect Route

@app.route('/')
def redirect_url(code):
    row = db.execute(
        "SELECT original_url, expires_at FROM urls WHERE short_code=?", (code,)
    ).fetchone()
    
    if not row:
        abort(404)
    if row['expires_at'] and row['expires_at'] < datetime.utcnow().isoformat():
        abort(410)  # Gone
    
    db.execute("UPDATE urls SET click_count=click_count+1 WHERE short_code=?", (code,))
    db.commit()
    return redirect(row['original_url'], code=301)