A REST API for managing a personal song library with mood-based tagging and search. Full CRUD, tag filtering (AND/OR), a random-song-by-mood endpoint, request logging, observability metrics, and 16 automated integration tests — all running on FastAPI + SQLAlchemy + SQLite.
This was my first time building an API from scratch (as opposed to consuming one), and a few things clicked that I want to call out:
SQLAlchemy as an ORM layer. I wrote zero raw SQL in this project. Every database interaction — creating tables, inserting rows, querying with filters — happens through Python objects that inherit from a shared Base class. That inheritance is what lets Base.metadata.create_all() discover and build every table automatically. The ORM pattern translates cleanly to how production services interact with Postgres or MySQL — same idea, different database underneath.
How FastAPI wires everything together. The framework uses dependency injection (Depends(get_db)) so routes declare what they need and FastAPI provides it. This means swapping the real database for a test database is a one-line override, not a rewrite. Pydantic schemas validate inputs and shape outputs separately from the SQLAlchemy models, which keeps the API contract decoupled from the database schema.
API-specific testing patterns. Each of the 16 tests gets a fresh in-memory SQLite database via a pytest fixture, so tests are fully isolated and deterministic. I ran into a real debugging exercise here: SQLite's in-memory mode creates a separate database per connection, so create_all and the test sessions were talking to different databases. The fix was StaticPool to force a single shared connection — a subtle gotcha that only shows up in testing, not production.
(More learning in "LEARNING_JOURNAL_API Project 2_with Fast API and SQLAlchemy.md")
| Method | Path | Description |
|---|---|---|
| GET | / |
Health check |
| GET | /songs |
List songs (optional ?tag= filter) |
| GET | /songs/search?tags=x&tags=y&match=all |
Search by multiple tags |
| GET | /songs/random?tag=focus |
Random song matching a tag |
| GET | /songs/{id} |
Get one song |
| POST | /songs |
Create a song |
| PUT | /songs/{id} |
Update a song |
| DELETE | /songs/{id} |
Delete a song |
| GET | /tags |
List all unique tags |
| GET | /metrics |
Observability metrics |
git clone https://github.com/CB98x/song-library-api.git
cd song-library-api
python3 -m venv venv
source venv/bin/activate # Windows: source venv/Scripts/activate
pip install -r requirements.txt
python seed.py # populate the DB with 25 songsuvicorn app.main:app --reloadAPI at http://127.0.0.1:8000 · Interactive docs at http://127.0.0.1:8000/docs
pytest -v16 integration tests covering happy paths, error paths, and validation rules. Every test hits the full stack (HTTP → routing → dependency injection → database → response) against an isolated in-memory database.
app/
├── main.py FastAPI app, routes, middleware (logging + metrics)
├── database.py SQLAlchemy engine, session factory, DB dependency
├── models.py ORM model — Song table definition inheriting from Base
├── schemas.py Pydantic schemas — separate shapes for create, update, read
└── metrics.py In-memory request/error counters with thread-safe locking
Two model systems, on purpose. SQLAlchemy models define what's in the database. Pydantic schemas define what crosses the wire. A POST request sends a SongCreate (no id, no timestamp). The database stores a full Song row. The response returns a SongRead (includes id and timestamp). Three different shapes for the same data at different stages — this is how production APIs avoid leaking internal fields.
Tags as JSON. Tags are stored as a JSON array on each song. At this scale that's simpler than a many-to-many join table. At production scale you'd want a tags table and a song_tags join table for indexed queries.
MIT