Chapter 31, Projects
Blog API: overview and PostgreSQL
The project that brings everything together: a blog API with users, login, posts, author authorization, pagination, search, PostgreSQL, and tests. In this chapter, I define the contract, prepare the database, and set up the foundation of the application.
What we will build
The original course ends with a blog API using PostgreSQL, JWT, and pagination. I maintain this outline and correct what makes it too insecure to be called a real project: in the original, the login route did not require a username or password (anyone received a token), there was no concept of a user, and any authenticated person could change any other person's post. This version has users with protected passwords, tokens that identify who is logged in, and only the author can change their own post.
The API contract, which is what a frontend needs to know:
| Method | Path | No token gives | Input | Success | Possible errors |
|---|---|---|---|---|---|
POST | /usuarios | No | JSON {nome, email, senha} | 201 repeated user | 409 email, 422 |
POST | /auth/token | No | Form username (email) and password | 200 token | 401 |
GET | /usuarios/eu | Yes | 200 repeated user | 401 | |
GET | /posts | No | Query pagina, limite (up to 50), busca, autor_id | 200 posts page | 422 |
GET | /posts/{id} | No | 200 post | 404 | |
POST | /posts | Yes | JSON {titulo, conteudo} | 201 post | 401, 422 |
PUT | /posts/{id} | Yes (only the author) | JSON {titulo, conteudo} | 200 post | 401, 403, 404, 422 |
DELETE | /posts/{id} | Yes (only the author) | 204 without body | 401, 403, 404 | |
GET | /saude | No | 200 {"status": "ok"} |
The layered structure
Each folder and each file has a responsibility. This separation is what allows testing each piece and swapping one without breaking the others:
blog_api/
pyproject.toml
.env.example
app/
config.py # configurações vindas do ambiente
database.py # engine, sessão e a classe Base
models.py # tabelas (SQLAlchemy)
schemas.py # contratos de entrada e saída (Pydantic)
security.py # hash de senha e JWT
deps.py # dependências: sessão, configuração, usuário logado
routers/
auth.py # cadastro, login, dados do usuário
posts.py # CRUD de posts
main.py # monta a aplicação
tests/
conftest.py # fixtures: banco limpo por teste, usuários
test_auth.py
test_posts.py
A dependency rule keeps this healthy: the routes (routers/) use deps, models, schemas and security, and none of these layers import the routes. The models do not know Pydantic, and the schemas do not know the database.
PostgreSQL
The course uses SQLite so far. For a real project, PostgreSQL is the common choice: it supports many simultaneous accesses, has richer types and constraints, and is what most platforms offer as a managed database. Install:
# macOS (Homebrew)
brew install postgresql@16
brew services start postgresql@16
# Ubuntu ou Debian
sudo apt install postgresql
sudo service postgresql start
# Qualquer sistema com Docker
docker run --name pg-blog -e POSTGRES_PASSWORD=admin -p 5432:5432 -d postgres:16
What I tested
I ran this project on PostgreSQL 16.15 installed via
apton Ubuntu. The Homebrew and Docker commands above follow the documentation for each tool, but I did not run them here.
Instead of using the admin user postgres, I create a user and a database just for this application, with the minimum privileges. In psql (connect with psql -U postgres):
CREATE ROLE blog LOGIN PASSWORD 'blog_senha';
CREATE DATABASE blog_db OWNER blog;
The application finds the database via a connection URL, which always has the same anatomy:
postgresql+psycopg://blog:blog_senha@localhost:5432/blog_db
│ │ │ │ │ │ └─ nome do banco
│ │ │ │ │ └─ porta
│ │ │ │ └─ servidor
│ │ │ └─ senha
│ │ └─ usuário
│ └─ o driver Python (psycopg 3)
└─ o tipo de banco
The course uses the psycopg2-binary. I use the psycopg in version 3, the current driver, and that’s why the URL starts with postgresql+psycopg://. The password does not goes in the code: it is stored in the environment variable BLOG_BANCO_URL, in the .env that does not go to Git (chapter 23).
The project and dependencies
uv init blog_api
cd blog_api
uv add "fastapi[standard]" sqlalchemy "psycopg[binary]" pydantic-settings pyjwt "pwdlib[argon2]"
uv add --dev pytest httpx2 mypy ruff
[project]
name = "blog-api"
version = "1.0.0"
description = "API de blog com FastAPI, PostgreSQL, JWT e testes"
requires-python = ">=3.10"
dependencies = [
"fastapi[standard]>=0.142",
"sqlalchemy>=2.0",
"psycopg[binary]>=3.2",
"pydantic-settings>=2.4",
"pyjwt>=2.8",
"pwdlib[argon2]>=0.2",
]
[dependency-groups]
dev = ["pytest>=8", "httpx2", "mypy>=1.10", "ruff>=0.6"]
[tool.uv]
package = false
[tool.pytest.ini_options]
testpaths = ["tests"]
pythonpath = ["."]
[tool.ruff]
line-length = 100
target-version = "py310"
[tool.ruff.lint]
select = ["E", "F", "I", "B", "UP"]
[tool.mypy]
python_version = "3.10"
strict = true
files = ["app"]
The file .env.example is the model, which goes to Git, with the names of the variables. Whoever clones the project copies it to .env and fills in the values:
# Copie para .env e preencha. O arquivo .env NUNCA vai para o Git.
BLOG_CHAVE_SECRETA=gere-com-openssl-rand-hex-32
BLOG_BANCO_URL=postgresql+psycopg://blog:blog_senha@localhost:5432/blog_db
BLOG_EXPIRA_EM_MINUTOS=30
BLOG_ORIGENS_PERMITIDAS=["http://localhost:5173"]
Configuration and database
The configurations use the pydantic-settings from chapter 23: typed, validated at startup, and with the secret key protected by SecretStr (and a requirement of at least 32 characters):
from functools import lru_cache
from pydantic import Field, SecretStr
from pydantic_settings import BaseSettings, SettingsConfigDict
class Configuracoes(BaseSettings):
"""Tudo vem do ambiente (variáveis BLOG_*) ou do arquivo .env, e é validado na partida."""
model_config = SettingsConfigDict(env_file=".env", env_prefix="BLOG_", extra="ignore")
chave_secreta: SecretStr = Field(min_length=32)
banco_url: str = "sqlite:///./blog.db"
algoritmo: str = "HS256"
expira_em_minutos: int = Field(default=30, gt=0)
origens_permitidas: list[str] = Field(default_factory=list)
@lru_cache
def obter_configuracoes() -> Configuracoes:
# A chave obrigatória vem do ambiente, e o mypy não enxerga isso.
return Configuracoes() # type: ignore[call-arg]
The database.py creates the engine and the session factory. The function criar_engine decides the options by the URL: the in-memory SQLite (which I use in tests) needs the unique connection we saw in chapter 16, and PostgreSQL uses the default pool with pool_pre_ping, which tests the connection before using it and recovers from connections dropped by the server:
from typing import Any
from sqlalchemy import Engine, create_engine
from sqlalchemy.orm import DeclarativeBase, Session, sessionmaker
from sqlalchemy.pool import StaticPool
class Base(DeclarativeBase):
pass
def criar_engine(url: str) -> Engine:
"""SQLite em memória precisa de uma conexão única; PostgreSQL usa o pool padrão."""
if url.startswith("sqlite"):
opcoes: dict[str, Any] = {"connect_args": {"check_same_thread": False}}
if url in ("sqlite://", "sqlite:///:memory:"):
opcoes["poolclass"] = StaticPool
return create_engine(url, **opcoes)
return create_engine(url, pool_pre_ping=True)
def criar_fabrica(engine: Engine) -> "sessionmaker[Session]":
return sessionmaker(engine, expire_on_commit=False)