PostgreSQL With FastAPI

Introduction

FastAPI pairs naturally with PostgreSQL for JSON APIs backed by relational data. This chapter wires psycopg through lifespan, dependency injection, Pydantic schemas, and a small posts CRUD router—following FastAPI project structure and psycopg basics. ORM details stay in the FastAPI track; here the focus is SQL, RETURNING, and ON CONFLICT.

Prerequisites

Project Layout

requirements.txt:

text
fastapi[standard]>=0.115.0
psycopg[binary]>=3.1
pydantic-settings>=2.0
python-dotenv>=1.0

Settings

app/core/config.py:

python
from functools import lru_cache
from pydantic_settings import BaseSettings, SettingsConfigDict
 
class Settings(BaseSettings):
    model_config = SettingsConfigDict(env_file=".env", extra="ignore")
 
    app_name: str = "Postgres API"
    database_url: str = "postgresql://postgres:learnpass@127.0.0.1:5432/hello_demo"
 
@lru_cache
def get_settings() -> Settings:
    return Settings()

.env:

env
DATABASE_URL=postgresql://postgres:learnpass@127.0.0.1:5432/hello_demo

Warning

Do not commit .env files with production credentials—Git gitignore.

Connection Pool and Lifespan

app/db/postgres.py:

Add psycopg-pool to requirements.txt for production-style pooling.

app/main.py:

Code explanation:

  • init_pool runs SELECT 1 on startup—fail fast if Postgres is down
  • get_conn yields a connection per request—commit in endpoint after writes

Ensure Table Exists (Dev Bootstrap)

Run once in psql or migration tool:

sql
CREATE TABLE IF NOT EXISTS posts (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title TEXT NOT NULL,
  slug TEXT NOT NULL UNIQUE,
  body TEXT NOT NULL DEFAULT '',
  published BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Pydantic Schemas

app/schemas/post.py:

CRUD Router

app/api/v1/endpoints/posts.py:

Code explanation:

  • RETURNING returns the row in one round trip—Postgres advantage over LAST_INSERT_ID()
  • Dynamic UPDATE builds only changed columns from Pydantic exclude_unset
  • conn.commit() after successful writes; rollback() on errors

Compare with PyMongo With FastAPI—same router shape, SQL instead of BSON.

Run Locally

bash
cd pg-api
python -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt
uvicorn app.main:app --reload

Test:

bash
curl -X POST http://127.0.0.1:8000/api/v1/posts/ \
  -H "Content-Type: application/json" \
  -d '{"title":"Hello","slug":"hello","published":true}'

Async Option (Awareness)

For async def routes, use psycopg.AsyncConnection or asyncpg with SQLAlchemy 2.0 async session—patterns in FastAPI async database. Sync psycopg is simpler for learning SQL first.

FAQ

SQLAlchemy instead of raw SQL?

ORM maps models to tables—fine for large apps. Raw psycopg keeps SQL visible while learning Postgres features.

Why psycopg-pool?

Opening a TCP connection per request is slow—pool reuse matches production. PgBouncer adds server-side pooling—Performance chapter.

Migrations?

Use Alembic, Flyway, or SQL files in CI—not CREATE TABLE IF NOT EXISTS in production.

Upsert in API?

ON CONFLICT (slug) DO UPDATE—Update and Upsert.

Full stack deploy?

Next: PostgreSQL + Redis + API Deploy.

Java stack?

See Spring JDBC PostgreSQL.