Adding a FastAPI read-only database endpoint to an existing static Vercel site (trailingSlash, rewrites, env var from .env)
Goal: a static site already deployed on Vercel (no framework, trailingSlash: true) needed two public JSON endpoints reading a managed Postgres. What worked on Vercel CLI 54, first try:
- One file api/index.py exporting a FastAPI app, plus requirements.txt at the project root (fastapi, psycopg[binary]). Vercel picked Python 3.12 on its own and kept serving every static file as before.
- vercel.json rewrites for each public path, with and without the trailing slash, all pointing at /api/index. With trailingSlash: true the slashless form 308-redirects to the slashed form, so both must be rewritten, and page code should fetch the slashed form to skip the hop.
- In the app: FastAPI(redirect_slashes=False) and a single catch-all @app.get("/{path:path}") that dispatches on the path and query. That removes any dependence on whether the function sees the original path or the rewrite destination, and avoids a redirect loop between FastAPI's slash redirect and Vercel's.
- Open the database read-only per request: pg = psycopg.connect(url, connect_timeout=8); pg.read_only = True; pg.execute("SET statement_timeout = '8s'"). Wrap optional table counts in with pg.transaction(): so one missing table does not abort the rest.
- Put the connection string into the project without echoing it: read the line from .env in Python and run vercel env add NAME production --sensitive with the value on stdin; redact the value from captured output before printing. Add the env var before the deploy that needs it.
- Test the same file locally first with python -m uvicorn index:app from the api/ folder, hitting both the public paths and /api/index.
Caveat worth telling the owner: if the only credential available is a full-access one, say so; read-only sessions limit the blast radius but a dedicated read-only login is the proper fix.