The FastAPI–MySQL Stack That Survives Past the Demo
Most FastAPI tutorials stop at a single-file demo with a raw connection string. This lays out the layered architecture — Engine, Session factory, repository, service, router — that prevents the database code from becoming unmaintainable spaghetti once the project grows beyond a few endpoints.
Connecting FastAPI to MySQL means coordinating four distinct layers: PyMySQL handles the wire protocol, SQLAlchemy manages the ORM and connection pool, a per-request Session factory enforces transaction boundaries, and FastAPI's Depends injects that session into route handlers. The article walks through each layer's exact responsibility, from the Engine and SessionLocal configuration to the commit/rollback lifecycle.
A full project structure is laid out with config, database, dependencies, and per-module directories for models, schemas, repository, service, and router files. The repository handles raw reads and writes, the service owns business rules and transaction commits, and the router stays thin — translating HTTP semantics and delegating everything else.
Common pitfalls like forgetting to commit, failing to rollback on exceptions, creating engines inside route handlers, and missing model imports are addressed directly. The guide also clarifies when to use synchronous SQLAlchemy versus the async stack, and why Alembic migrations replace create_all in team projects.
The guide's emphasis on explicit driver naming — mysql+pymysql:// rather than mysql:// — is a small habit that prevents environment-specific breakage in team settings.
Placing commit and rollback inside the Service layer, not the Repository, keeps transaction boundaries aligned with business actions rather than individual database calls.
The distinction between Session (a unit-of-work context) and the web notion of a session is a persistent source of confusion for newcomers; the article addresses it head-on.
Recommending synchronous SQLAlchemy as the default, with async treated as an optimization for specific needs, pushes back against the reflex to make everything async just because FastAPI supports it.
About FastAPI connecting to databases, I also started by writing the synchronous version following the docs. Later, when building a proxy gateway, I discovered a big pitfall 😂 The routing layer used async, but the database operations still used synchronous SQLAlchemy sessions, meaning every DB query was blocking the event loop. After switching everything to async session + aiomysql, with the same concurrency, the response time dropped by almost half. But the dependency injection pattern mentioned in the article is indeed the most comfortable — each request gets its own independent session, automatically closed when the request ends, much cleaner than manually managing sessions back in my Flask days.