ProjectIntermediate 60 min· Ship it · 19 of 20

Project: library management API

Build a complete Library Management API with FastAPI and SQLAlchemy: books, members, loans, limits, fines in rupees and overdue reports. Add automated tests and containerise it. A portfolio-ready backend project.

What you will build: a Library Management API for a college library. Staff can add books and members, members can borrow and return books, the API enforces the library's rules, calculates fines in rupees and lists overdue loans. It has automated tests and runs in Docker.

Finished result: about 300 lines of Python across five files, seven passing tests, and an interactive documentation page at /docs where you can try every endpoint.

Prerequisites: Python 3.10 or newer, and the ideas from earlier lessons in this track: REST design, FastAPI, SQL and ORMs, validation and error handling. Docker is optional, for the last stage. This project ties those lessons together, so if a step feels unfamiliar, revisit the matching lesson.

Stage 1: plan before you code

Good backend work starts with the rules, not the code. Write them down in plain words first. The library says:

A book has a title, author, ISBN and a number of copies. A member has a name and a unique email. A loan lasts 14 days. A member may hold at most 3 books at a time. A book can be borrowed only if a copy is available. Returning a book late costs ₹2 per day.

The API we will build

Method and path	Purpose	Main error responses
`POST /books`	Add a book	409 duplicate ISBN, 422 invalid data
`GET /books`	List or search books (`q`, `limit`, `offset`)	none
`GET /books/{id}`	One book with copies available	404
`POST /members`	Register a member	409 duplicate email, 422 invalid email
`GET /members/{id}/loans`	A member's loans (`active_only`)	404
`POST /loans`	Borrow a book	404, 409 limit reached or no copies
`POST /loans/{id}/return`	Return a book, get the fine	404, 409 already returned
`GET /loans/overdue`	Loans past their due date and not returned	none

Design decisions worth noticing. copies_available is not stored; it is calculated as total copies minus active loans. Storing it twice would let the two numbers drift apart. Fines are calculated at return time from the dates, never typed in by a client. Status codes follow the HTTP lesson: 201 for created, 404 for missing, 409 for a conflict with current state, 422 for invalid input.

Prompt · Review my API design before I build it

I am building a library management REST API with FastAPI and SQLAlchemy. Rules: a book has several copies; a member can hold at most [3] books; loans last [14] days; late returns cost ₹[2] per day.

Here is my endpoint list:
[paste your table]

Review it as a senior backend developer. Point out missing endpoints, wrong status codes, race conditions and rules I forgot. Keep it to the five most important points.

What to expect: Compare the answer with the table above. Decide yourself which suggestions are worth adding now; some, like authentication, belong in stretch goals.

Stage 2: project setup

Create the project

1Make the folder and move in: mkdir library-api and cd library-api.
2Create a virtual environment: python -m venv .venv, then activate it (source .venv/bin/activate on macOS/Linux or .venv\Scripts\activate on Windows).
3Create the folders app and tests, and an empty file app/__init__.py.
4Create requirements.txt with the content below and run pip install -r requirements.txt.

requirements.txt

fastapi
uvicorn[standard]
sqlalchemy
pytest
httpx

pytest and httpx are used only by the tests. In a real project you would pin exact versions.

Stage 3: database and models

We use SQLite so there is nothing to install, with SQLAlchemy so the same code works on Postgres later. database.py creates the connection and a get_db function that gives each request its own session and always closes it. The database address comes from the DATABASE_URL environment variable, so tests and production can use different databases.

app/database.py

import os

from sqlalchemy import create_engine
from sqlalchemy.orm import DeclarativeBase, sessionmaker

DATABASE_URL = os.getenv("DATABASE_URL", "sqlite:///./library.db")

# check_same_thread is a SQLite-only setting that FastAPI needs
connect_args = {"check_same_thread": False} if DATABASE_URL.startswith("sqlite") else {}
engine = create_engine(DATABASE_URL, connect_args=connect_args)
SessionLocal = sessionmaker(bind=engine, autoflush=False)


class Base(DeclarativeBase):
    pass


def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

Run with no settings and it creates library.db in the current folder.

Now the three tables. A Loan links one book to one member. returned_on is empty (NULL) while the book is still out, which makes "active loan" easy to query.

app/models.py

from datetime import date

from sqlalchemy import Date, ForeignKey, Integer, String
from sqlalchemy.orm import Mapped, mapped_column

from app.database import Base


class Book(Base):
    __tablename__ = "books"

    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(200))
    author: Mapped[str] = mapped_column(String(120))
    isbn: Mapped[str] = mapped_column(String(20), unique=True)
    copies_total: Mapped[int] = mapped_column(Integer, default=1)


class Member(Base):
    __tablename__ = "members"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(120))
    email: Mapped[str] = mapped_column(String(200), unique=True)


class Loan(Base):
    __tablename__ = "loans"

    id: Mapped[int] = mapped_column(primary_key=True)
    book_id: Mapped[int] = mapped_column(ForeignKey("books.id"))
    member_id: Mapped[int] = mapped_column(ForeignKey("members.id"))
    borrowed_on: Mapped[date] = mapped_column(Date)
    due_on: Mapped[date] = mapped_column(Date)
    returned_on: Mapped[date | None] = mapped_column(Date, nullable=True)

Stage 4: request and response schemas

Pydantic models define what clients may send and what they get back. They are separate from the database models on purpose: the client cannot set an id, and responses never expose more than we decide. Notice the validation built into the fields: titles cannot be empty, copies_total must be between 1 and 100, and the email must match a simple pattern.

app/schemas.py

from datetime import date

from pydantic import BaseModel, ConfigDict, Field


class BookIn(BaseModel):
    title: str = Field(min_length=1, max_length=200)
    author: str = Field(min_length=1, max_length=120)
    isbn: str = Field(min_length=10, max_length=20)
    copies_total: int = Field(default=1, ge=1, le=100)


class BookOut(BookIn):
    id: int
    copies_available: int


class MemberIn(BaseModel):
    name: str = Field(min_length=1, max_length=120)
    email: str = Field(pattern=r"^[^@\s]+@[^@\s]+\.[^@\s]+$")


class MemberOut(MemberIn):
    model_config = ConfigDict(from_attributes=True)

    id: int


class LoanIn(BaseModel):
    book_id: int
    member_id: int


class LoanOut(BaseModel):
    model_config = ConfigDict(from_attributes=True)

    id: int
    book_id: int
    member_id: int
    borrowed_on: date
    due_on: date
    returned_on: date | None = None


class ReturnOut(LoanOut):
    fine_rupees: int

ReturnOut extends LoanOut with the fine, so only the return endpoint shows it.

Stage 5: the API

This file holds the business rules. Read it slowly, in this order: the two helper functions at the top, then the book routes, then borrow_book, which is the heart of the project.

In borrow_book the rules are checked one by one and each failure returns a clear 409 message. In return_book, the fine is days_late * FINE_PER_DAY, and max(..., 0) ensures an on-time return costs nothing.

app/main.py

from datetime import date, timedelta

from fastapi import Depends, FastAPI, HTTPException
from sqlalchemy import func, select
from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm import Session

from app import models, schemas
from app.database import Base, engine, get_db

LOAN_DAYS = 14
MAX_ACTIVE_LOANS = 3
FINE_PER_DAY = 2  # rupees

Base.metadata.create_all(engine)

app = FastAPI(title="Library Management API")


def copies_available(db: Session, book: models.Book) -> int:
    active = db.scalar(
        select(func.count(models.Loan.id)).where(
            models.Loan.book_id == book.id, models.Loan.returned_on.is_(None)
        )
    )
    return book.copies_total - active


def book_out(db: Session, book: models.Book) -> schemas.BookOut:
    return schemas.BookOut(
        id=book.id,
        title=book.title,
        author=book.author,
        isbn=book.isbn,
        copies_total=book.copies_total,
        copies_available=copies_available(db, book),
    )


@app.get("/healthz")
def healthz():
    return {"status": "ok"}


# ---------- books ----------
@app.post("/books", response_model=schemas.BookOut, status_code=201)
def create_book(body: schemas.BookIn, db: Session = Depends(get_db)):
    book = models.Book(**body.model_dump())
    db.add(book)
    try:
        db.commit()
    except IntegrityError:
        db.rollback()
        raise HTTPException(status_code=409, detail="A book with this ISBN already exists")
    db.refresh(book)
    return book_out(db, book)


@app.get("/books", response_model=list[schemas.BookOut])
def list_books(q: str | None = None, limit: int = 20, offset: int = 0, db: Session = Depends(get_db)):
    limit = max(1, min(limit, 100))
    stmt = select(models.Book).order_by(models.Book.id).limit(limit).offset(max(offset, 0))
    if q:
        like = f"%{q}%"
        stmt = stmt.where(models.Book.title.ilike(like) | models.Book.author.ilike(like))
    return [book_out(db, b) for b in db.scalars(stmt)]


@app.get("/books/{book_id}", response_model=schemas.BookOut)
def get_book(book_id: int, db: Session = Depends(get_db)):
    book = db.get(models.Book, book_id)
    if book is None:
        raise HTTPException(status_code=404, detail="Book not found")
    return book_out(db, book)


# ---------- members ----------
@app.post("/members", response_model=schemas.MemberOut, status_code=201)
def create_member(body: schemas.MemberIn, db: Session = Depends(get_db)):
    member = models.Member(**body.model_dump())
    db.add(member)
    try:
        db.commit()
    except IntegrityError:
        db.rollback()
        raise HTTPException(status_code=409, detail="A member with this email already exists")
    db.refresh(member)
    return member


@app.get("/members/{member_id}/loans", response_model=list[schemas.LoanOut])
def member_loans(member_id: int, active_only: bool = False, db: Session = Depends(get_db)):
    if db.get(models.Member, member_id) is None:
        raise HTTPException(status_code=404, detail="Member not found")
    stmt = select(models.Loan).where(models.Loan.member_id == member_id)
    if active_only:
        stmt = stmt.where(models.Loan.returned_on.is_(None))
    return list(db.scalars(stmt.order_by(models.Loan.id)))


# ---------- loans ----------
@app.post("/loans", response_model=schemas.LoanOut, status_code=201)
def borrow_book(body: schemas.LoanIn, db: Session = Depends(get_db)):
    book = db.get(models.Book, body.book_id)
    member = db.get(models.Member, body.member_id)
    if book is None or member is None:
        raise HTTPException(status_code=404, detail="Book or member not found")

    active_for_member = db.scalar(
        select(func.count(models.Loan.id)).where(
            models.Loan.member_id == member.id, models.Loan.returned_on.is_(None)
        )
    )
    if active_for_member >= MAX_ACTIVE_LOANS:
        raise HTTPException(status_code=409, detail=f"Loan limit reached ({MAX_ACTIVE_LOANS} books)")
    if copies_available(db, book) < 1:
        raise HTTPException(status_code=409, detail="No copies available")

    today = date.today()
    loan = models.Loan(
        book_id=book.id,
        member_id=member.id,
        borrowed_on=today,
        due_on=today + timedelta(days=LOAN_DAYS),
    )
    db.add(loan)
    db.commit()
    db.refresh(loan)
    return loan


@app.get("/loans/overdue", response_model=list[schemas.LoanOut])
def overdue_loans(db: Session = Depends(get_db)):
    stmt = select(models.Loan).where(
        models.Loan.returned_on.is_(None), models.Loan.due_on < date.today()
    )
    return list(db.scalars(stmt.order_by(models.Loan.due_on)))


@app.post("/loans/{loan_id}/return", response_model=schemas.ReturnOut)
def return_book(loan_id: int, db: Session = Depends(get_db)):
    loan = db.get(models.Loan, loan_id)
    if loan is None:
        raise HTTPException(status_code=404, detail="Loan not found")
    if loan.returned_on is not None:
        raise HTTPException(status_code=409, detail="Book already returned")
    loan.returned_on = date.today()
    db.commit()
    db.refresh(loan)
    days_late = max((loan.returned_on - loan.due_on).days, 0)
    return schemas.ReturnOut(
        id=loan.id,
        book_id=loan.book_id,
        member_id=loan.member_id,
        borrowed_on=loan.borrowed_on,
        due_on=loan.due_on,
        returned_on=loan.returned_on,
        fine_rupees=days_late * FINE_PER_DAY,
    )

The constants at the top (LOAN_DAYS, MAX_ACTIVE_LOANS, FINE_PER_DAY) keep the rules in one place. Base.metadata.create_all creates the tables on start; a real project would use Alembic migrations.

A known limitation: race conditions

If two requests borrow the last copy at the same instant, both might pass the availability check. For a college library this is rare, but in a real system you would lock the book row (for example with_for_update() on Postgres) or enforce the rule inside a database transaction. Naming this limitation in an interview shows you understand concurrency.

Stage 6: run it and try it

Start the server and walk through a borrowing

1Start the server from the library-api folder: uvicorn app.main:app --reload.
2Open http://localhost:8000/docs. This page is generated automatically from your code.
3Add a book: curl -X POST http://localhost:8000/books -H "Content-Type: application/json" -d '{"title":"Wings of Fire","author":"A. P. J. Abdul Kalam","isbn":"9788173711466","copies_total":1}'.
4Add a member: curl -X POST http://localhost:8000/members -H "Content-Type: application/json" -d '{"name":"Rahul","email":"rahul@example.com"}'.
5Borrow the book: curl -X POST http://localhost:8000/loans -H "Content-Type: application/json" -d '{"book_id":1,"member_id":1}'.
6Check the book again with curl http://localhost:8000/books/1. copies_available is now 0.
7Try to borrow it a second time and read the 409 message, then return it with curl -X POST http://localhost:8000/loans/1/return.

Response to the first borrow (dates will differ)

{"id":1,"book_id":1,"member_id":1,"borrowed_on":"2026-10-31","due_on":"2026-11-14","returned_on":null}

On Windows PowerShell

PowerShell's curl is an alias for another command and quoting JSON is awkward. Use curl.exe instead of curl, or simply use the buttons on the /docs page, which need no quoting at all.

Stage 7: automated tests

Manual clicking does not scale. Tests check every rule in seconds and protect you when you change code later. conftest.py sets a separate test database before the app is imported (otherwise the tests would write into your real library.db) and gives every test a fresh, empty database.

The interesting test is test_return_with_fine. We cannot wait five days, so the test edits the loan's due date directly in the database and then checks that the fine is ₹10.

tests/conftest.py

import os

# Use a throw-away database for tests. Must be set before the app is imported.
os.environ["DATABASE_URL"] = "sqlite:///./test_library.db"

import pytest
from fastapi.testclient import TestClient

from app.database import Base, engine
from app.main import app


@pytest.fixture()
def client():
    Base.metadata.drop_all(engine)
    Base.metadata.create_all(engine)
    return TestClient(app)

tests/test_api.py

from datetime import date, timedelta

from app.database import SessionLocal
from app.models import Loan

BOOK = {"title": "Wings of Fire", "author": "A. P. J. Abdul Kalam", "isbn": "9788173711466", "copies_total": 1}
MEMBER = {"name": "Rahul", "email": "rahul@example.com"}


def make_book(client, **changes):
    return client.post("/books", json={**BOOK, **changes}).json()


def make_member(client, **changes):
    return client.post("/members", json={**MEMBER, **changes}).json()


def test_create_and_get_book(client):
    r = client.post("/books", json=BOOK)
    assert r.status_code == 201
    assert r.json()["copies_available"] == 1
    assert client.get(f"/books/{r.json()['id']}").json()["title"] == "Wings of Fire"


def test_duplicate_isbn_is_rejected(client):
    client.post("/books", json=BOOK)
    assert client.post("/books", json=BOOK).status_code == 409


def test_invalid_book_is_422(client):
    assert client.post("/books", json={**BOOK, "copies_total": 0}).status_code == 422


def test_borrow_reduces_copies_and_blocks_second_borrow(client):
    book, rahul = make_book(client), make_member(client)
    priya = make_member(client, name="Priya", email="priya@example.com")
    r = client.post("/loans", json={"book_id": book["id"], "member_id": rahul["id"]})
    assert r.status_code == 201
    assert client.get(f"/books/{book['id']}").json()["copies_available"] == 0
    r2 = client.post("/loans", json={"book_id": book["id"], "member_id": priya["id"]})
    assert r2.status_code == 409


def test_member_loan_limit(client):
    member = make_member(client)
    for i in range(3):
        book = make_book(client, isbn=f"97800000000{i:02d}")
        assert client.post("/loans", json={"book_id": book["id"], "member_id": member["id"]}).status_code == 201
    extra = make_book(client, isbn="9780000000099")
    assert client.post("/loans", json={"book_id": extra["id"], "member_id": member["id"]}).status_code == 409


def test_return_with_fine(client):
    book, member = make_book(client), make_member(client)
    loan = client.post("/loans", json={"book_id": book["id"], "member_id": member["id"]}).json()
    # Pretend the book was due 5 days ago
    with SessionLocal() as db:
        row = db.get(Loan, loan["id"])
        row.due_on = date.today() - timedelta(days=5)
        db.commit()
    assert len(client.get("/loans/overdue").json()) == 1
    r = client.post(f"/loans/{loan['id']}/return")
    assert r.status_code == 200
    assert r.json()["fine_rupees"] == 10
    assert client.post(f"/loans/{loan['id']}/return").status_code == 409
    assert client.get(f"/books/{book['id']}").json()["copies_available"] == 1


def test_search_books(client):
    make_book(client)
    make_book(client, title="Python Basics", author="Asha", isbn="9780000000001")
    r = client.get("/books", params={"q": "python"})
    assert [b["title"] for b in r.json()] == ["Python Basics"]

Covers creating books, duplicate ISBN, validation, the copy limit, the member limit, fines with overdue listing, and search.

Run the tests

1Run python -m pytest -q from the library-api folder (not inside tests).
2Break a rule on purpose: change MAX_ACTIVE_LOANS = 3 to 5 in app/main.py and rerun. A test should fail.
3Change it back and confirm everything passes again.

Expected result

.......                                                                  [100%]
7 passed

You may also see a warning about httpx and the Starlette test client depending on your installed versions. It does not affect the result.

Prompt · Suggest missing tests

Here is my FastAPI route and its current tests:

[paste borrow_book route]
[paste test file]

List edge cases I have not tested (for example unknown IDs, boundary values, repeated actions). For each, write the pytest function using the same `client` fixture. Do not change the existing tests.

What to expect: Run every test the AI writes. If one fails, decide whether the test or your code is wrong; do not just delete it.

Stage 8: containerise it

Reuse what you learned in *Containerise your backend with Docker*. Create a Dockerfile that copies requirements.txt, installs it and runs uvicorn app.main:app --host 0.0.0.0 --port 8000. Since SQLite writes a file, the data disappears with the container unless you mount a volume. Better still, point DATABASE_URL at Postgres in Docker Compose, for example postgresql+psycopg://user:pass@db:5432/library, and add psycopg[binary] to requirements.txt. The code needs no other change, which is exactly why we read the URL from the environment. When it works, deploy it with *Deploy a backend to the cloud*.

Project review checklist

All seven tests pass from a clean checkout.
No password, key or database file is committed to Git (check .gitignore).
Every error returns a JSON message and a correct status code.
Rules (14 days, 3 books, ₹2) live in constants, not scattered numbers.
You can explain why copies_available is calculated, not stored.
README lists how to install, run and test.

How this project helps in interviews. Interviewers often ask you to walk through a project. You can describe the data model (three tables and their relations), the business rules, the status codes you chose, how you test, the limitation about concurrent borrowing and how you would improve it. A small project you understand completely beats a large one you copied.

Try it: Stretch goal 1: protect staff actions with an API key

Only library staff should add books or process returns. Add a dependency that reads an X-API-Key header, compares it with an API_KEY environment variable using secrets.compare_digest, and returns 401 if it is missing or wrong. Apply it to POST /books and POST /loans/{id}/return. Update the tests to send the header.

Try it: Stretch goal 2: renewals and reservations

Add POST /loans/{id}/renew that extends the due date by 7 days, but only once per loan and only if the loan is not overdue. This needs a new column renewals on Loan. Delete library.db after changing the model (or learn Alembic migrations), then write two tests.

Try it: Stretch goal 3: logging and a readiness check

Apply *Logging, metrics and health checks*: add JSON request logs with a request ID, and a /readyz endpoint that runs SELECT 1. Then add a GET /stats endpoint that returns the total books, active loans and overdue count in one response.

Key takeaways

Start from written rules and an endpoint table; the code follows from them.
Keep database models and request/response schemas separate.
Calculate derived values (copies available, fines) on the server instead of storing or trusting them.
Use status codes precisely: 201, 404, 409, 422.
Read configuration from environment variables so the same code runs in tests, Docker and the cloud.
Automated tests and a short list of known limitations make a project interview-ready.

Check yourself

Finishing the quiz marks the lesson as done.

1. Why is copies_available calculated from the loans table instead of stored as a column on books?

2. A member who already holds 3 books tries to borrow a fourth. Which response fits best?

3. Why does conftest.py set DATABASE_URL before importing the app?

4. How does test_return_with_fine test a fine without waiting days?

5. What is the main reason clients cannot send id or fine_rupees when creating records?

Saved on this device. Create a free account to keep it on every device.

Deploy a backend to the cloud GraphQL and gRPC: other API styles

Practise this with Sam

A real mock interview, out loud, with an honest scorecard. Free to start.

Start free