A Database, then Ship It

The API works, but every restart wipes it clean. Real data has to outlive the process, so this phase swaps the dictionary for SQLite. SQLite is a full SQL database that stores everything in one file, and it ships with Python as the sqlite3 module - nothing to install, no server to run. Perfect for this.

The thing worth noticing as we go: your routes barely change. That's the payoff of having kept the storage logic small. We'll put the database code in its own file, and the routes will call it the same way they called the dict.

A database module

Create a new file, db.py, next to main.py:

import sqlite3

DB_PATH = "notes.db"


def connect():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row  # rows behave like dicts
    return conn


def init_db():
    with connect() as conn:
        conn.execute(
            """
            CREATE TABLE IF NOT EXISTS notes (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                content TEXT NOT NULL,
                pinned INTEGER NOT NULL DEFAULT 0
            )
            """
        )

Two details to call out:

• conn.row_factory = sqlite3.Row makes each result row act like a dictionary, so dict(row) gives you {"id": 1, "title": ...} - the same shape your API already returns.
• id INTEGER PRIMARY KEY AUTOINCREMENT means SQLite hands out the ids itself. That global next_id counter from earlier? Gone. The database owns ids now.

Rewrite main.py to use the database

Replace main.py with this. The models and the error handling are unchanged - only the storage swaps from a dict to SQL calls:

from fastapi import FastAPI, HTTPException, status
from pydantic import BaseModel, Field

from db import connect, init_db

app = FastAPI()


@app.on_event("startup")
def startup():
    init_db()


class NoteIn(BaseModel):
    title: str = Field(min_length=1, max_length=120)
    content: str = Field(min_length=1)
    pinned: bool = False


def get_or_404(note_id: int) -> dict:
    with connect() as conn:
        row = conn.execute(
            "SELECT * FROM notes WHERE id = ?", (note_id,)
        ).fetchone()
    if row is None:
        raise HTTPException(status_code=404, detail=f"Note {note_id} not found")
    return dict(row)


@app.get("/notes")
def list_notes():
    with connect() as conn:
        rows = conn.execute("SELECT * FROM notes ORDER BY id").fetchall()
    return [dict(r) for r in rows]


@app.get("/notes/{note_id}")
def get_note(note_id: int):
    return get_or_404(note_id)


@app.post("/notes", status_code=status.HTTP_201_CREATED)
def create_note(note: NoteIn):
    with connect() as conn:
        cur = conn.execute(
            "INSERT INTO notes (title, content, pinned) VALUES (?, ?, ?)",
            (note.title, note.content, int(note.pinned)),
        )
        new_id = cur.lastrowid
    return get_or_404(new_id)


@app.put("/notes/{note_id}")
def update_note(note_id: int, note: NoteIn):
    get_or_404(note_id)
    with connect() as conn:
        conn.execute(
            "UPDATE notes SET title = ?, content = ?, pinned = ? WHERE id = ?",
            (note.title, note.content, int(note.pinned), note_id),
        )
    return get_or_404(note_id)


@app.delete("/notes/{note_id}")
def delete_note(note_id: int):
    get_or_404(note_id)
    with connect() as conn:
        conn.execute("DELETE FROM notes WHERE id = ?", (note_id,))
    return {"deleted": note_id}

A few things to take away from this:

• The routes look the same. Same paths, same methods, same status codes, same 404s. Callers can't tell the storage changed - which is the whole point.
• Those ? placeholders matter. Never build SQL by pasting values into the string. The ? lets SQLite insert the value safely, which is what stops SQL injection. Always pass values as the tuple, never with f-strings.
• @app.on_event("startup") runs init_db() once when the server boots, so the table exists before the first request. CREATE TABLE IF NOT EXISTS makes that safe to run every time.
• int(note.pinned) because SQLite has no boolean type - we store True/ False as 1/0.

Run and test it

Start the server the same way as before:

uvicorn main:app --reload

On the first request a file called notes.db appears in your folder - that's your database. Run the same curl commands from phase 3 to create and read notes:

curl -X POST http://127.0.0.1:8000/notes \
  -H "Content-Type: application/json" \
  -d '{"title": "Buy milk", "content": "2% if they have it"}'

curl http://127.0.0.1:8000/notes

Now the real test. Stop the server with Ctrl+C, start it again, and list the notes:

curl http://127.0.0.1:8000/notes

Your note is still there. The data survived the restart. That's the line between a demo and something you could actually use.

A note on SQLAlchemy

We used the built-in sqlite3 module because it's already there and the SQL is short. On a bigger project you'll likely reach for SQLAlchemy, an ORM that lets you work with Python objects instead of writing SQL by hand, and lets you switch from SQLite to PostgreSQL by changing a connection string. It's the right tool once your queries grow - but the concepts you learned here (a connection, a table, the CRUD statements, parameterized values) are exactly what it wraps. You haven't learned a throwaway version; you've learned the layer underneath.

Getting it ready to ship

A few things stand between this and a deployed service. Quick tour so you know what's next:

A minimal Dockerfile for this project looks like:

FROM python:3.12-slim
WORKDIR /app
COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt
COPY . .
CMD ["uvicorn", "main:app", "--host", "0.0.0.0", "--port", "8000"]

Build and run it with:

docker build -t notes-api .
docker run -p 8000:8000 notes-api

Hosts like Railway, Render, Fly.io, or any cloud that runs containers will take this image and put it on the internet. Each has its own steps, but they all want the same thing you now have: an app that starts with one command and listens on a port.

Where we are - and what you built

Step back and look at the folder. Two files, main.py and db.py, and you have:

• five REST endpoints covering full CRUD
• input validated from type hints and Pydantic Field rules
• proper status codes - 201 on create, 404 on missing, 422 on bad input
• a SQLite database that keeps your data across restarts
• auto-generated interactive docs at /docs
• a Dockerfile and a clear path to deployment

That's a real REST API, built the way you'd build one at work - start small, add validation, separate the storage, and only then worry about shipping. The "notes" subject was an excuse; swap it for tasks, users, products, or anything else and the same five-phase shape holds. You've got the pattern now.