# FastAPI CRUD API

A FastAPI 0.162 CRUD API starter in Python 3.12, served by Uvicorn, that stores one resource, items, in PostgreSQL through psycopg 3 and a connection pool. It is for developers who want a tested create, read, update and delete service with Pydantic validation, OpenAPI docs at /docs, JSON errors, limit and offset paging and a database /health check.

- Framework: FastAPI (Python)
- Category: Starters / CRUD APIs
- Creates: a server app and a PostgreSQL database on the Shared tier (1 GB)
- Plan: Lite or above
- Price: Free
- Author: Light Cloud
- Page: https://templates.light-cloud.com/starters/crud-apis/fastapi-postgresql-crud-api
- Updated: 2026-11-21

## Features

- GET, POST, PUT and DELETE on /items, with GET /items?limit=20&offset=0 paging oldest first and limit 1 to 100
- A Pydantic ItemIn model trims name and requires 1 to 200 characters; description is an optional string
- Every error is JSON { "error": "..." }: 400 for invalid bodies or paging, 404 for unknown or non-numeric item ids
- Swagger UI at /docs and the schema at /openapi.json, generated by FastAPI from the routes
- GET /health runs SELECT 1 and answers 200, or 503 when the database does not answer
- A lifespan handler opens a psycopg-pool pool of 1 to 5 connections and creates the items table, with no migration step
- TLS to the database by default with DB_SSLMODE=require; set it to disable for a local container
- pytest tests against a real PostgreSQL that delete the items they create, plus ruff for linting

## What you need

- A paid plan: deploying creates a PostgreSQL database, which the Free plan does not include
- To run it locally: Python 3.12 and pip in a virtual environment, and Docker for a local PostgreSQL
- No API keys: it starts with nothing to configure
- DB_HOST, DB_NAME, DB_USER and DB_PASSWORD: Light Cloud sets them on deploy; locally, copy .env.example to .env and load it

## About

The app is built in app/main.py and re-exported from a root main.py, so Uvicorn starts it as main:app. Each route borrows a connection from the shared pool and returns rows as dicts, so there are no ORM models or response models to keep in step with the table. If the database is unreachable the lifespan handler fails and the app does not start, which surfaces a bad connection at deploy time. Renaming items starts in app/db.py, app/schemas.py and app/routers/items.py. On Light Cloud the deploy installs requirements.txt and runs uvicorn main:app with the DB_* variables set, and every push to your copy redeploys.

## Questions

### How do I connect FastAPI to PostgreSQL without SQLAlchemy?

This starter uses psycopg 3 with psycopg-pool: app/config.py builds the connection from DB_HOST, DB_NAME, DB_USER, DB_PASSWORD and DB_SSLMODE, and the routes run plain SQL through the pool. You can add SQLAlchemy or SQLModel later without changing the routes' shape.

### How do I run the FastAPI tests?

Start PostgreSQL in Docker and load .env as in the README's quick start, then run pytest. The tests use that real database and delete the items they create; ruff check . lints the code.

### How do I add a column to the items table?

Add it to the CREATE TABLE in app/db.py, to ItemIn in app/schemas.py and to the queries in app/routers/items.py. CREATE TABLE IF NOT EXISTS does not alter an existing table, so run an ALTER TABLE once on a database that already has items.

### How do I deploy a FastAPI app with a PostgreSQL database?

Press Deploy on this template: Light Cloud copies the repo into your GitHub account, creates a PostgreSQL database and sets DB_HOST, DB_NAME, DB_USER and DB_PASSWORD on the app. Databases need a paid plan.

### Can I host this FastAPI app without Light Cloud?

Yes. Any host with Python 3.12 and a reachable PostgreSQL runs it: install requirements.txt, set the DB_* variables and start uvicorn main:app --host 0.0.0.0 --port $PORT.
