Library Management API

A REST API for managing a personal library. In addition to standard CRUD operations, it supports wishlists, borrowed books, spending statistics, and the import and export of book data in JSON format.

Features

• Create, view, update, and delete books
• Search by title, author, publisher, or ISBN
• Filter by category, wishlist status, and borrowed status
• Analyze spending by month and year
• View statistics for authors, publishers, and book statuses
• Import and export JSON data
• Validate ISBN-10 and ISBN-13 values, including their checksums
• Browse automatically generated OpenAPI documentation

Technology

• Python 3.11+
• FastAPI and Uvicorn
• Pydantic 2
• SQLAlchemy 2
• MySQL 8 with PyMySQL
• Docker and Docker Compose

Project structure

.
├── .github/workflows/
│   └── ci.yml           # GitHub Actions workflow
├── app/
│   ├── routers/         # Books, statistics, and transfer endpoints
│   ├── config.py        # Environment-backed application settings
│   ├── crud.py          # Database queries and business logic
│   ├── database.py      # Engine, sessions, and FastAPI dependency
│   ├── exceptions.py    # Domain exceptions mapped by the HTTP layer
│   ├── main.py          # Application factory and error handlers
│   ├── models.py        # SQLAlchemy data model
│   └── schemas.py       # Pydantic schemas and ISBN validation
├── tests/               # API, schema, and code-policy tests
├── .env.example         # Local configuration template
├── AGENTS.md            # Working conventions for coding agents
├── DATABASE_SETUP.md    # Step-by-step MySQL setup guide
├── Dockerfile           # API container image
├── docker-compose.yaml  # API service
├── pyproject.toml        # Pytest and Ruff configuration
├── requirements-dev.txt # Development and test dependencies
└── requirements.txt     # Python dependencies

Prerequisites

Running the application locally requires Python 3.11 or newer and an accessible MySQL 8 database. Alternatively, the API can be built and run with Docker.

The application requires DATABASE_URL when a request first accesses the database. It does not create the database table automatically.

Database setup

Follow DATABASE_SETUP.md for the complete setup, including separate Docker, SQL, configuration, and verification steps.

To start a Docker-based development database:

docker network create library-network
docker run \
  --name mysql \
  --network library-network \
  -e MYSQL_ROOT_PASSWORD=password \
  -p 3306:3306 \
  -d mysql:8.4.0
docker exec -it mysql mysql -u root -p

Run the database and table statements from DATABASE_SETUP.md in the MySQL client. They create the library database, the libraryAPI user, and the books table.

The passwords in that file are placeholders for local development. Replace them with secure credentials in other environments.

Run locally

python3 -m venv venv
source venv/bin/activate
pip install -r requirements.txt
cp .env.example .env
uvicorn app.main:app --reload --host 0.0.0.0 --port 5000

The following interfaces are then available:

• API: http://localhost:5000
• Swagger UI: http://localhost:5000/docs
• ReDoc: http://localhost:5000/redoc

Call the health endpoint to verify that the API is running:

curl http://localhost:5000/

Run with Docker

The docker-compose.yaml file starts only the API. The MySQL container described above must already be running in the external library-network network.

export DATABASE_URL='mysql+pymysql://libraryAPI:PASSWORD@mysql:3306/library'
docker compose up --build

The API is then available on port 5000. To stop it:

docker compose down

When deploying with Portainer, create the stack from this Git repository so the Dockerfile and build context are available. Use the normal stack update action; do not enable Re-pull image or use Pull and redeploy, because library-api:latest is built locally and is not published to a registry.

Create a book

curl -X POST http://localhost:5000/books/ \
  -H 'Content-Type: application/json' \
  -d '{
    "category": "Technical",
    "title": "API Design",
    "author": "Example Author",
    "publisher": "Example Publisher",
    "volume": 1,
    "price": 39.90,
    "isbn": "9780132350884",
    "wishlist": false,
    "payDate": "2026-09-14"
  }'

Allowed categories are Manga, Novel, and Technical. ISBNs are transferred as strings. Hyphens and spaces are not accepted, and ISBN-10 and ISBN-13 values must have valid checksums.

API overview

Method	Path	Description
GET	/	Check API health
GET	/books/	List books with pagination
POST	/books/	Create a book
GET	/books/{book_id}	Retrieve a book
PUT	/books/{book_id}	Partially update a book
DELETE	/books/{book_id}	Delete a book
GET	/books/isbn/{isbn}	Find a book by ISBN
GET	/books/search/	Search titles, authors, and publishers
GET	/books/borrowed/{category}	List borrowed books
GET	/books/library-or-wishlist/{true_or_false}/{category}	Filter the library or wishlist
GET	/stats/all-pay/	Calculate total spending
GET	/stats/year-pay/{year}	Calculate spending for a year
GET	/stats/month-pay/{year}/{month}	Calculate spending for a month
GET	/stats/years_pay_in_table/	List totals for previous years
GET	/stats/true-false-counter/	Count book statuses
GET	/stats/author-counter/	Count books by author
GET	/stats/publisher-counter/	Count books by publisher
GET	/export/books/	Download all books as JSON
POST	/import/books/	Import books from a JSON file

Complete parameter, schema, and response documentation is available in the Swagger UI at /docs.

Pagination and search

GET /books/ accepts skip and limit. The skip value starts at 0; limit must be between 1 and 500 and defaults to 100.

curl 'http://localhost:5000/books/?skip=0&limit=25'
curl 'http://localhost:5000/books/search/?search_term=Author'

Spending statistics

Spending calculations include only books that have a payDate and are not marked as gifts. The annual overview includes only completed previous years.

JSON import and export

The export endpoint returns all books in a file named books.json:

curl -OJ http://localhost:5000/export/books/

The import endpoint expects a JSON file containing a list of books:

[
  {
    "category": "Novel",
    "title": "Example Book",
    "author": "Example Author",
    "publisher": "Example Publisher",
    "volume": 1,
    "price": 12.50,
    "isbn": "9783161484100"
  }
]

curl -X POST http://localhost:5000/import/books/ \
  -F 'file=@books.json;type=application/json'

The following import limits apply:

• Content type must be application/json or text/plain
• Maximum file size is 10 MiB
• Maximum number of entries is 10,000
• Existing ISBNs and duplicate ISBNs within the file are skipped
• Invalid entries are returned with their index and validation error

Error responses

Status	Meaning
400	Invalid import or unsupported content type
404	Book not found
409	ISBN is already assigned to another book
413	Import file exceeds 10 MiB
422	Request does not match the Pydantic schema

Development

Install the development dependencies in the active virtual environment:

pip install -r requirements-dev.txt

Run the linter and test suite before submitting a change:

ruff check .
pytest
git diff --check

The API tests use an isolated in-memory SQLite database, so they do not modify a local MySQL library. The suite covers endpoint behavior, validation, statistics, search, filtering, import/export, and the function policies from AGENTS.md.

The Quality Check GitHub Actions workflow runs Ruff and the complete test suite as separate Lint and Run Tests jobs for every push and pull request. Each job is reported as an individual repository check.

Changes to the data model must be kept synchronized across app/models.py, app/schemas.py, and DATABASE_SETUP.md. Existing databases also require an appropriate ALTER TABLE statement because migrations are not run automatically.

See AGENTS.md for additional project-specific conventions.