Build and deploy a REST API with PostgreSQL

Write the endpoints before any code. For a reading-list API:

Decide the error format now and use it everywhere: {"error": {"code": "not_found", "message": "..."}}.

Done when: the table above exists for your own idea.

Run PostgreSQL locally with Docker so dev matches production.

docker run -d --name pg -e POSTGRES_PASSWORD=dev -p 5432:5432 postgres:16
pip install fastapi uvicorn sqlalchemy psycopg[binary] pydantic pytest httpx

# db.py
from sqlalchemy import create_engine, String, Integer, DateTime, func
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, sessionmaker
import os

engine = create_engine(os.getenv("DATABASE_URL", "postgresql+psycopg://postgres:dev@localhost/postgres"))
Session = sessionmaker(engine, expire_on_commit=False)

class Base(DeclarativeBase): ...

class Book(Base):
    __tablename__ = "books"
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    title: Mapped[str] = mapped_column(String(200))
    author: Mapped[str] = mapped_column(String(120))
    created_at = mapped_column(DateTime(timezone=True), server_default=func.now(), index=True)

Done when: Base.metadata.create_all(engine) creates the table.

from fastapi import FastAPI, HTTPException, Header, Depends
from pydantic import BaseModel, Field

app = FastAPI(title="Reading list API")

class BookIn(BaseModel):
    title: str = Field(min_length=1, max_length=200)
    author: str = Field(min_length=1, max_length=120)

def require_key(x_api_key: str = Header(...)):
    if x_api_key != os.environ["API_KEY"]:
        raise HTTPException(401, detail={"code": "unauthorized", "message": "Bad API key"})

@app.get("/books")
def list_books(limit: int = 20, cursor: int | None = None):
    limit = max(1, min(limit, 100))
    with Session() as s:
        q = s.query(Book).order_by(Book.id.desc())
        if cursor:
            q = q.filter(Book.id < cursor)
        rows = q.limit(limit).all()
    return {"items": [{"id": b.id, "title": b.title, "author": b.author} for b in rows],
            "next_cursor": rows[-1].id if len(rows) == limit else None}

@app.post("/books", status_code=201, dependencies=[Depends(require_key)])
def create_book(body: BookIn):
    with Session() as s:
        book = Book(**body.model_dump()); s.add(book); s.commit()
        return {"id": book.id, **body.model_dump()}

Cursor pagination stays fast on big tables; OFFSET gets slower as pages grow.

Done when: /docs shows every endpoint and you can create and list books from it.

from fastapi.testclient import TestClient
from main import app

client = TestClient(app)

def test_create_needs_key():
    assert client.post("/books", json={"title": "x", "author": "y"}).status_code == 422

def test_create_and_list(monkeypatch):
    monkeypatch.setenv("API_KEY", "k")
    r = client.post("/books", json={"title": "Dune", "author": "Herbert"}, headers={"x-api-key": "k"})
    assert r.status_code == 201
    assert any(b["title"] == "Dune" for b in client.get("/books").json()["items"])

Done when: pytest -q is green.

1. Add a Dockerfile (python:3.12-slim, pip install -r requirements.txt, CMD uvicorn main:app --host 0.0.0.0 --port $PORT).
2. Create a managed PostgreSQL database (Render, Railway or Neon) and set DATABASE_URL and API_KEY.
3. Deploy, open /docs on the live URL, and create one record.
4. Add a rate limit in front (the platform's, or slowapi).

Done when: someone else can call your API with a key you gave them.