Add a SQLite Database to Your FastHTML App with Fastlite

Welcome back to the FastHTML series. In part 4 we built an AI Title Generator using FastHTML and PydanticAI. That app generates titles on demand but loses everything when the server restarts.

This tutorial fixes that. We’re adding a FastHTML SQLite database using fastlite, the official FastHTML database library. Fastlite replaces the old hand-rolled sqlite3 approach with about 90% less code, and it uses APSW under the hood instead of Python’s stdlib sqlite3, which means better threading behavior and no check_same_thread footgun.

If you’re new to the series, start with FastHTML for Beginners: Build a UI for Your Python App and work forward.

Why add a database to your FastHTML app?

SQLite is the right fit here. Zero configuration, no separate server process, embedded in the app, SQL-standard queries. FastHTML officially recommends it through fastlite, backed by APSW, a thin SQLite wrapper with better concurrency semantics than the stdlib sqlite3 module. If you’ve used best Python web frameworks like Django or Flask with their ORM layers, you’ll find fastlite refreshingly minimal.

Project structure: where the SQLite database lives

We’re simplifying the project from part 4. The old db/ directory with database.py and history_dao.py is replaced by a single db.py file:

ai-title-generator/
├── main.py                   # Updated with history routes
├── config.py                 # Updated with DB settings
├── ai_service.py             # Unchanged
├── db.py                     # Replaces db/database.py + db/history_dao.py
├── components/
│   ├── __init__.py
│   ├── header.py             # Updated with history link
│   ├── footer.py
│   └── page_layout.py
├── pages/
│   ├── __init__.py
│   ├── home.py
│   ├── title_generator.py    # Updated to save history
│   └── history.py            # New history page
├── tools/
│   ├── __init__.py
│   └── title_generator.py
└── tools.db                  # SQLite file (created automatically)

Setting up the SQLite database with fastlite

Why fastlite replaces raw sqlite3

The old approach in this tutorial used a custom Database class (~60 lines) with get_connection() context managers, plus a HistoryDAO class (~130 lines) with hand-written SQL. Fastlite replaces all of that with about 15 lines total.

Key differences:

• APSW backend: fastlite uses APSW instead of stdlib sqlite3. APSW doesn’t have the check_same_thread=True default that makes sqlite3 fragile in async/threaded apps.
• db.create(Class) is idempotent. It creates the table if it doesn’t exist, returns the table object either way. Replaces CREATE TABLE IF NOT EXISTS.
• transform=True handles schema migrations automatically when fields change.
• Dataclass objects: fastlite returns objects with attribute access (record.topic) instead of dict-style access (record['topic']).

from fastlite import database  # or: from fastlite import *

Create the TitleHistory table with db.create()

Here’s the complete db.py. It replaces both db/database.py and db/history_dao.py from the old version:

# db.py
import json
from datetime import datetime, timezone
from fastlite import database
from config import DB_PATH

db = database(DB_PATH)

class TitleHistory:
    id: int
    topic: str
    platform: str
    style: str
    number_of_titles: int
    titles: str          # JSON-encoded list
    created_at: str      # ISO-8601 UTC timestamp

histories = db.create(TitleHistory, pk='id', transform=True)

That’s the entire database layer. db.create() creates the table if it doesn’t exist and returns a table object. transform=True means if you add a field to TitleHistory later, fastlite will alter the table to match. No manual migration scripts needed.

class TitleHistory:
    id: int
    topic: str
    platform: str
    style: str
    number_of_titles: int
    titles: str
    created_at: str

histories = db.create(TitleHistory, pk='id', transform=True)

from fastlite import database

db = database('tools.db')
histories = db.create('title_history', id=int, topic=str,
                       platform=str, style=str,
                       number_of_titles=int, titles=str,
                       created_at=str, pk='id', transform=True)

Install fastlite and dependencies

pip install python-fasthtml fastlite

Fastlite is a dependency of python-fasthtml, so it’s already installed if you have FastHTML. But it’s worth calling out explicitly since you’ll import it directly. Python >= 3.10 is required.

Create your .env file:

OPENROUTER_API_KEY=your_api_key_here
DB_PATH=tools.db
DEFAULT_MODEL=openai/gpt-4o-mini

Updated config.py:

import os
from dotenv import load_dotenv

load_dotenv()

OPENROUTER_API_KEY = os.getenv("OPENROUTER_API_KEY")
OPENROUTER_BASE_URL = "https://openrouter.ai/api/v1"
DEFAULT_MODEL = os.getenv("DEFAULT_MODEL", "openai/gpt-4o-mini")
DB_PATH = os.getenv("DB_PATH", "tools.db")
DEBUG = os.getenv("DEBUG", "True").lower() == "true"
APP_NAME = "AI Title Generator"

SQLite CRUD operations: insert, query, and delete history

These four helper functions replace the old HistoryDAO class. Add them to db.py after the table setup:

Insert a new title generation

def save_generation(topic, platform, style, number_of_titles, titles):
    """Save a generation and return the new record."""
    return histories.insert(
        topic=topic,
        platform=platform,
        style=style,
        number_of_titles=number_of_titles,
        titles=json.dumps(titles),
        created_at=datetime.now(timezone.utc).isoformat()
    )

histories.insert() returns the inserted record object, so result.id gives you the new primary key. The titles list is serialized to JSON, and created_at is stored as an explicit UTC ISO-8601 string. No ambiguous CURRENT_TIMESTAMP behavior.

List history records (newest first)

def get_history(limit=10, offset=0):
    """Get history records, newest first."""
    return histories(order_by='created_at DESC', limit=limit, offset=offset)

Fastlite’s table callable supports order_by, limit, and offset directly. It returns a list of dataclass objects, so you access fields as record.topic, record.titles, etc.

Get a single record by ID

def get_by_id(record_id):
    """Get a single record. Raises NotFoundError if missing."""
    return histories[record_id]

Delete a record and handle missing IDs

def delete_record(record_id):
    """Delete a record. Raises NotFoundError if missing."""
    histories.delete(record_id)

The NotFoundError comes from fastlite:

from fastlite import NotFoundError

Building the history page

The history page has three views: a paginated list, a detail view, and a delete confirmation. The UX features from the original tutorial are kept: pagination, title preview, copy buttons, “Generate Similar” pre-fill, and delete with confirmation. The main change is switching from dict access (record['topic']) to attribute access (record.topic).

File: pages/history.py

import json
from fasthtml.common import *
from db import get_history, get_by_id, delete_record
from fastlite import NotFoundError

RECORDS_PER_PAGE = 10

def history_page(page: int = 1):
    offset = (page - 1) * RECORDS_PER_PAGE
    history_records = get_history(limit=RECORDS_PER_PAGE, offset=offset)

    history_cards = []
    if not history_records:
        history_cards.append(
            Div(
                P("No generation history found. Try generating some titles first!",
                  cls="text-gray-600 italic"),
                cls="bg-white p-6 rounded-lg shadow-md"
            )
        )
    else:
        for record in history_records:
            titles = json.loads(record.titles)
            display_titles = titles[:3]
            has_more = len(titles) > 3

            title_items = [Li(t, cls="mb-1") for t in display_titles]
            if has_more:
                title_items.append(
                    Li(
                        A(f"...and {len(titles) - 3} more",
                          href=f"/history/{record.id}",
                          cls="text-blue-600 hover:underline italic"),
                        cls="mt-2"
                    )
                )

            # Format timestamp for display
            created_display = record.created_at[:19].replace('T', ' ')

            history_cards.append(
                Div(
                    Div(
                        Div(
                            H3(record.topic[:50] + ("..." if len(record.topic) > 50 else ""),
                               cls="text-lg font-semibold"),
                            P(f"{record.platform} • {record.style} • {record.number_of_titles} titles",
                              cls="text-sm text-gray-600"),
                            cls="flex-grow"
                        ),
                        P(created_display, cls="text-xs text-gray-500"),
                        cls="flex justify-between items-start mb-3"
                    ),
                    Div(
                        H4("Generated Titles:", cls="font-medium mb-2"),
                        Ul(*title_items, cls="list-disc pl-5 text-gray-700"),
                        cls="mb-3"
                    ),
                    Div(
                        A("View Details", href=f"/history/{record.id}",
                          cls="text-blue-600 hover:underline text-sm mr-4"),
                        A("Delete", href=f"/history/{record.id}/delete",
                          cls="text-red-600 hover:underline text-sm"),
                        cls="flex justify-end"
                    ),
                    cls="bg-white p-6 rounded-lg shadow-md mb-4"
                )
            )

    # Pagination
    has_next = len(history_records) == RECORDS_PER_PAGE
    pagination = Div(
        Div(
            A("← Previous",
              href=f"/history?page={page - 1}" if page > 1 else "#",
              cls=f"px-4 py-2 rounded {'bg-blue-600 text-white' if page > 1 else 'bg-gray-200 text-gray-500 cursor-default'}"),
            Span(f"Page {page}", cls="px-4 py-2"),
            A("Next →",
              href=f"/history?page={page + 1}" if has_next else "#",
              cls=f"px-4 py-2 rounded {'bg-blue-600 text-white' if has_next else 'bg-gray-200 text-gray-500 cursor-default'}"),
            cls="flex items-center justify-center space-x-2"
        ),
        cls="mt-6"
    )

    return Div(
        H1("Generation History", cls="text-3xl font-bold text-gray-800 mb-6"),
        P("View your previously generated titles.", cls="text-gray-600 mb-6"),
        Div(*history_cards),
        pagination,
        cls="max-w-4xl mx-auto"
    )

The detail page shows all titles with copy buttons, metadata, and action buttons:

def history_detail_page(record_id: int):
    try:
        record = get_by_id(record_id)
    except NotFoundError:
        return Div(
            H1("Record Not Found", cls="text-3xl font-bold text-red-600 mb-4"),
            P("The requested history record could not be found.", cls="mb-4"),
            A("Back to History", href="/history",
              cls="bg-blue-600 hover:bg-blue-700 text-white font-bold py-2 px-4 rounded"),
            cls="max-w-2xl mx-auto bg-white p-6 rounded-lg shadow-md"
        )

    titles = json.loads(record.titles)
    created_display = record.created_at[:19].replace('T', ' ')

    title_items = []
    for title in titles:
        title_items.append(
            Li(
                Div(
                    P(title, cls="font-medium"),
                    Button(
                        "Copy", type="button",
                        onclick=f"navigator.clipboard.writeText('{title.replace(chr(39), chr(92)+chr(39))}'); this.textContent = 'Copied!'; setTimeout(() => this.textContent = 'Copy', 2000);",
                        cls="ml-auto text-sm bg-gray-200 hover:bg-gray-300 px-2 py-1 rounded"
                    ),
                    cls="flex justify-between items-center"
                ),
                cls="p-3 border-b last:border-b-0"
            )
        )

    return Div(
        H1("Title Generation Details", cls="text-3xl font-bold text-gray-800 mb-6"),
        Div(
            Div(
                H2("Generation Information", cls="text-xl font-semibold mb-4"),
                Div(
                    Div(Strong("Date & Time:"), P(created_display, cls="text-gray-700 mb-2"), cls="mb-3"),
                    Div(Strong("Topic:"), P(record.topic, cls="text-gray-700 mb-2"), cls="mb-3"),
                    Div(Strong("Platform:"), P(record.platform, cls="text-gray-700 mb-2"), cls="mb-3"),
                    Div(Strong("Style:"), P(record.style, cls="text-gray-700 mb-2"), cls="mb-3"),
                    Div(Strong("Number of Titles:"), P(str(record.number_of_titles), cls="text-gray-700 mb-2"), cls="mb-3"),
                    cls="bg-gray-50 p-4 rounded-lg mb-6"
                ),
                H2("Generated Titles", cls="text-xl font-semibold mb-4"),
                P("Click 'Copy' to copy any title to your clipboard.", cls="text-gray-600 mb-3"),
                Ul(*title_items, cls="border rounded divide-y mb-6"),
                Div(
                    A("Generate Similar",
                      href=f"/title-generator?topic={record.topic}&platform={record.platform}&style={record.style}&number_of_titles={record.number_of_titles}",
                      cls="bg-blue-600 hover:bg-blue-700 text-white font-bold py-2 px-4 rounded mr-3"),
                    A("Back to History", href="/history",
                      cls="bg-gray-200 hover:bg-gray-300 text-gray-800 font-bold py-2 px-4 rounded mr-3"),
                    A("Delete Record", href=f"/history/{record_id}/delete",
                      cls="bg-red-600 hover:bg-red-700 text-white font-bold py-2 px-4 rounded"),
                    cls="flex flex-wrap gap-y-2"
                ),
                cls="bg-white p-6 rounded-lg shadow-md"
            ),
            cls="max-w-2xl mx-auto"
        )
    )

The delete confirmation page:

def delete_confirm_page(record_id: int):
    try:
        record = get_by_id(record_id)
    except NotFoundError:
        return Div(
            H1("Record Not Found", cls="text-3xl font-bold text-red-600 mb-4"),
            P("The requested history record could not be found.", cls="mb-4"),
            A("Back to History", href="/history",
              cls="bg-blue-600 hover:bg-blue-700 text-white font-bold py-2 px-4 rounded"),
            cls="max-w-2xl mx-auto bg-white p-6 rounded-lg shadow-md"
        )

    created_display = record.created_at[:19].replace('T', ' ')

    return Div(
        H1("Confirm Deletion", cls="text-3xl font-bold text-gray-800 mb-6"),
        Div(
            P("Are you sure you want to delete this history record?", cls="text-lg mb-4"),
            Div(
                P(f"Topic: {record.topic[:100]}{'...' if len(record.topic) > 100 else ''}", cls="mb-2"),
                P(f"Platform: {record.platform}", cls="mb-2"),
                P(f"Created: {created_display}", cls="mb-2"),
                cls="bg-gray-100 p-4 rounded-lg mb-6"
            ),
            P("This action cannot be undone.", cls="text-red-600 mb-6"),
            Form(
                Div(
                    Button("Yes, Delete Record", type="submit",
                           cls="bg-red-600 hover:bg-red-700 text-white font-bold py-2 px-4 rounded mr-3"),
                    A("Cancel", href=f"/history/{record_id}",
                      cls="bg-gray-200 hover:bg-gray-300 text-gray-800 font-bold py-2 px-4 rounded"),
                    cls="flex"
                ),
                method="post",
                action=f"/history/{record_id}/delete"
            ),
            cls="bg-white p-6 rounded-lg shadow-md"
        ),
        cls="max-w-2xl mx-auto"
    )

For more on building multi-page tools like this, see building multi-page AI tools in FastHTML.

Wiring routes and running the app

Here’s the updated main.py with all routes. Key changes from the old version:

• Import from db instead of HistoryDAO
• Route handlers are sync (not async). FastHTML runs sync handlers in a threadpool, and APSW handles threading correctly
• serve() replaces uvicorn.run(...)

from fasthtml.common import *

from pages.home import home as home_page
from pages.title_generator import title_generator_form, title_generator_results
from pages.history import history_page, history_detail_page, delete_confirm_page
from components.page_layout import page_layout
from tools.title_generator import TitleGenerator
from db import save_generation, get_by_id, delete_record
from fastlite import NotFoundError
import config

app = FastHTML()
title_generator = TitleGenerator()

@app.get("/")
def home():
    return page_layout(
        title=f"Home - {config.APP_NAME}",
        content=home_page(),
        current_page="/"
    )

@app.get("/title-generator")
def title_generator_page(topic: str = "", platform: str = "Blog",
                          style: str = "Professional", number_of_titles: str = "5"):
    return page_layout(
        title=f"Title Generator - {config.APP_NAME}",
        content=title_generator_form(),
        current_page="/title-generator"
    )

@app.post("/title-generator/generate")
async def generate_titles(topic: str, platform: str, style: str, number_of_titles: str):
    try:
        if not topic:
            return page_layout(
                title=f"Error - {config.APP_NAME}",
                content=Div(
                    H1("Error", cls="text-3xl font-bold text-red-600 mb-4"),
                    P("Please provide a topic for your titles.", cls="mb-4"),
                    A("Try Again", href="/title-generator",
                      cls="bg-blue-600 hover:bg-blue-700 text-white font-bold py-2 px-4 rounded"),
                    cls="max-w-2xl mx-auto bg-white p-6 rounded-lg shadow-md"
                ),
                current_page="/title-generator"
            )

        num_titles = int(number_of_titles)
        titles = await title_generator.generate_titles(
            topic=topic, platform=platform, style=style,
            number_of_titles=num_titles
        )

        # Save to database
        record = save_generation(
            topic=topic, platform=platform, style=style,
            number_of_titles=num_titles, titles=titles
        )

        return page_layout(
            title=f"Generated Titles - {config.APP_NAME}",
            content=title_generator_results(
                topic=topic, platform=platform, style=style,
                titles=titles, history_id=record.id
            ),
            current_page="/title-generator"
        )
    except Exception as e:
        return page_layout(
            title=f"Error - {config.APP_NAME}",
            content=Div(
                H1("Error", cls="text-3xl font-bold text-red-600 mb-4"),
                P(f"An error occurred: {str(e)}", cls="mb-4"),
                A("Try Again", href="/title-generator",
                  cls="bg-blue-600 hover:bg-blue-700 text-white font-bold py-2 px-4 rounded"),
                cls="max-w-2xl mx-auto bg-white p-6 rounded-lg shadow-md"
            ),
            current_page="/title-generator"
        )

# History routes
@app.get("/history")
def history(page: int = 1):
    if page < 1:
        page = 1
    return page_layout(
        title=f"Generation History - {config.APP_NAME}",
        content=history_page(page=page),
        current_page="/history"
    )

@app.get("/history/{record_id:int}")
def history_detail(record_id: int):
    return page_layout(
        title=f"History Details - {config.APP_NAME}",
        content=history_detail_page(record_id=record_id),
        current_page="/history"
    )

@app.get("/history/{record_id:int}/delete")
def confirm_delete(record_id: int):
    return page_layout(
        title=f"Confirm Deletion - {config.APP_NAME}",
        content=delete_confirm_page(record_id=record_id),
        current_page="/history"
    )

@app.post("/history/{record_id:int}/delete")
def handle_delete(record_id: int):
    try:
        delete_record(record_id)
        return page_layout(
            title=f"Record Deleted - {config.APP_NAME}",
            content=Div(
                H1("Record Deleted", cls="text-3xl font-bold text-green-600 mb-4"),
                P("The history record has been successfully deleted.", cls="mb-4"),
                A("Back to History", href="/history",
                  cls="bg-blue-600 hover:bg-blue-700 text-white font-bold py-2 px-4 rounded"),
                cls="max-w-2xl mx-auto bg-white p-6 rounded-lg shadow-md"
            ),
            current_page="/history"
        )
    except NotFoundError:
        return page_layout(
            title=f"Error - {config.APP_NAME}",
            content=Div(
                H1("Error", cls="text-3xl font-bold text-red-600 mb-4"),
                P("The record could not be deleted or doesn't exist.", cls="mb-4"),
                A("Back to History", href="/history",
                  cls="bg-blue-600 hover:bg-blue-700 text-white font-bold py-2 px-4 rounded"),
                cls="max-w-2xl mx-auto bg-white p-6 rounded-lg shadow-md"
            ),
            current_page="/history"
        )

@app.get("/{path:path}")
def not_found(path: str):
    return page_layout(
        title=f"404 Not Found - {config.APP_NAME}",
        content=Div(
            H1("404 - Page Not Found", cls="text-3xl font-bold text-gray-800 mb-4"),
            P(f"Sorry, the page '/{path}' does not exist.", cls="mb-4"),
            A("Return Home", href="/",
              cls="bg-blue-600 hover:bg-blue-700 text-white font-bold py-2 px-4 rounded"),
            cls="max-w-2xl mx-auto bg-white p-6 rounded-lg shadow-md text-center"
        ),
        current_page="/"
    )

serve()

Run the app:

python main.py

serve() starts the server on port 5001 with auto-reload enabled. No if __name__ == "__main__" guard needed.

For deploying to production, see deploy a Python uv project with Dokploy.

Verifying your SQLite database works

The original tutorial ended with “open your browser and click around.” Here’s how to actually confirm the database is working.

Test 1: generate and check the database file

Generate some titles through the UI, then check the database from the command line:

sqlite3 tools.db 'SELECT COUNT(*) FROM title_history;'

Expected output after generating one set of titles:

1

If you see 0, the save didn’t happen. Check your terminal for errors.

Test 2: restart and confirm persistence

Stop the server with Ctrl+C, then restart:

python main.py

Visit http://localhost:5001/history. Your records should still be there. This proves the data is on disk, not just in memory.

Test 3: confirm WAL mode

sqlite3 tools.db 'PRAGMA journal_mode;'

Expected output:

wal

If you see delete, WAL mode isn’t active. See the next section for how to enable it.

SQLite performance and hardening for production

Enable WAL mode

WAL (Write-Ahead Logging) allows concurrent readers while a write is in progress. Without it, a write blocks all readers. For any app that serves HTTP requests while writing to the database, WAL is essential.

Enable it once via the SQLite CLI:

sqlite3 tools.db 'PRAGMA journal_mode=WAL;'

Or add it to your app startup in db.py:

db = database(DB_PATH)
db.execute('PRAGMA journal_mode=WAL')

Add indexes for faster queries

Once your history grows past a few hundred records, add an index on the timestamp column:

db.execute('CREATE INDEX IF NOT EXISTS idx_created_at ON title_history(created_at)')

This speeds up the ORDER BY created_at DESC query used in the history list.

Backup your SQLite database

Two approaches:

1. CLI backup (cron one-liner):

sqlite3 tools.db ".backup tools-$(date +%F).db"

Run this daily via cron. It creates a dated copy of the database.

2. VACUUM INTO (compact copy):

VACUUM INTO 'tools-backup.db';

This creates a defragmented copy, smaller than .backup but takes longer.

volumes:
  - ./data:/app/data

SQLite’s single-writer limitation

SQLite allows one writer at a time. If two requests try to write simultaneously, one waits (with WAL, it waits briefly; without WAL, it can error). For a personal AI tool like this, that’s fine — you’re not going to have hundreds of concurrent writers. If you need concurrent writes from multiple processes, PostgreSQL is the better choice.

Advanced database enhancements

Here are improvements you can build on top of this foundation:

Key takeaways

Next steps in the FastHTML series

Here’s every article in the series:

• FastHTML for Beginners: Build a UI for Your Python App — start here if you’re new
• structuring a FastHTML multi-page website — routing and project layout
• building multi-page AI tools in FastHTML — complex tool patterns
• AI Title Generator using FastHTML and PydanticAI — part 4, the app this tutorial extends
• Adding a SQLite database with fastlite — this article
• Adding user authentication to your FastHTML AI Title Generator — next: link history to user accounts