Library Management System

REST API for managing books, borrowers, and borrowing transactions.

Table of Contents

• Running with Docker
• Running Locally (without Docker)
• Testing
• Database Schema
• API Documentation
  • Authentication
  • Books
  • Borrowers
  • Transactions
  • Export formats (CSV / XLSX)
• Assumptions

---

Running with Docker

Prerequisite: Install Docker Desktop for Windows (or Docker Engine + Docker Compose). The docker and docker compose commands must be available in your terminal.

From the project root:

cp .env.example .env
# Set MYSQL_ROOT_PASSWORD and DB_PASSWORD in .env (use the same value so the API can connect as root)
docker compose up --build

• The API is available at http://localhost:3000.
• On first run, MySQL is initialized from docker/mysql-init/ (schema and seed data).
• Run in the background: docker compose up --build -d.
• View logs: docker compose logs -f.
• To reset the database: docker compose down -v (removes the MySQL volume), then docker compose up --build again.

---

Running Locally (without Docker)

Prerequisites: Node.js (v18+), MySQL 8.

1. Install dependencies:

npm install

2. Set up the database:

mysql -u root -p < schema/schema.sql
mysql -u root -p library < schema/seed.sql   # optional: sample data

3. Configure environment variables:

cp .env.example .env
# Edit .env: set DB_HOST=localhost, DB_PASSWORD to your MySQL root password
# Also set JWT_SECRET to a secure random string for production

4. Start the server:

npm start       # production
npm run dev     # development with auto-reload

The API is available at http://localhost:3000.

---

Testing

This project uses Jest for unit testing.

Running Tests

npm test

Test Coverage

The test suite covers the Borrowers module (src/controllers/borrowerController.js):

Function	Test Cases
isValidEmail	Valid email, missing @, empty string, null/undefined, whitespace, non-string types
generateBorrowerId	Returns 10-char hex, uniqueness check
create	Success, missing firstName/lastName/email, invalid email, duplicate email (409)
getAll	Returns list of borrowers, returns empty array
getById	Found, not found (404)
update	Success, not found (404), invalid fields (400), duplicate email (409)
remove	Success, not found (404), has active checkouts (409)

Test Structure

• Helper function tests: Pure functions (isValidEmail, generateBorrowerId) tested without mocking
• Controller tests: Use Jest mocks for database (pool.execute) and Express req/res/next objects

---

Database Schema

Schema files are located in:

• schema/schema.sql — database and table definitions
• schema/seed.sql — sample data for testing
• docker/mysql-init/ — Docker initialization scripts (same schema + seed)

Entity Relationship Diagram

Indexes

• Books: FULLTEXT index on (Title, Author) for search optimization
• Borrowers: Index on Email
• BorrowingProcess: Indexes on BookISBN, BorrowerID, DueDate, ReturnDate
• Librarians: Index on Email

---

API Documentation

Base URL: http://localhost:3000/api

All responses follow the format:

{ "success": true, "data": ... }

Error responses:

{ "success": false, "error": { "message": "Error message" } }

---

Authentication

The API uses JWT (JSON Web Token) authentication. All endpoints except /api/auth/login require a valid Bearer token in the Authorization header.

Default credentials (from seed data):

• Email: admin@library.com
• Password: admin123

Login

POST /api/auth/login

Request body:

{
  "email": "admin@library.com",
  "password": "admin123"
}

Response:

{
  "success": true,
  "data": {
    "token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...",
    "librarian": {
      "id": 1,
      "email": "admin@library.com",
      "name": "Admin Librarian",
      "createdAt": "2026-01-15T10:30:00.000Z"
    }
  }
}

Get current librarian

GET /api/auth/me

Headers: Authorization: Bearer <token>

Response:

{
  "success": true,
  "data": {
    "id": 1,
    "email": "admin@library.com",
    "name": "Admin Librarian",
    "createdAt": "2026-01-15T10:30:00.000Z"
  }
}

Using authentication

Include the JWT token in the Authorization header for all protected endpoints:

Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...

Authentication errors:

Status	Message	Description
401	Authentication required	No token provided
401	Token has expired	Token is expired, login again
401	Invalid token	Token is malformed or invalid

---

Books

List all books

GET /api/books

Response:

{
  "success": true,
  "data": [
    {
      "ISBN": "9780134685991",
      "Title": "Effective Java",
      "Author": "Joshua Bloch",
      "TotalQuantity": 3,
      "AvailableQuantity": 2,
      "ShelfLocation": "A-12",
      "RegisteredAt": "2026-01-15T10:30:00.000Z"
    }
  ]
}

Get book by ISBN

GET /api/books/:isbn

Response: Same as single book object above, or 404 if not found.

Search books

GET /api/books/search

Query parameters:

Parameter	Description
q	Search title and author (FULLTEXT search)
title	Search by title (FULLTEXT, used if q not provided)
author	Search by author (FULLTEXT, used if q not provided)
isbn	Search by ISBN (exact or prefix match)

Example: GET /api/books/search?q=java

Response: Array of matching books.

Add a book

POST /api/books

Rate limited: 10 requests per minute.

Request body:

{
  "isbn": "978-0-13-468599-1",
  "title": "Effective Java",
  "author": "Joshua Bloch",
  "totalQuantity": 3,
  "availableQuantity": 3,
  "shelfLocation": "A-12"
}

Field	Required	Description
isbn	Yes	13-character ISBN (hyphens allowed, will be stripped)
title	Yes	Book title
author	Yes	Book author
totalQuantity	Yes	Total copies owned
availableQuantity	No	Copies available (defaults to totalQuantity)
shelfLocation	No	Shelf location (max 10 characters)

Response: 201 Created with the created book object.

Update a book

PUT /api/books/:isbn

Request body: Any fields from the create request (all optional).

Response: Updated book object.

Delete a book

DELETE /api/books/:isbn

Response: 204 No Content on success.

Note: Fails with 409 Conflict if the book is currently checked out.

---

Borrowers

List all borrowers

GET /api/borrowers

Response:

{
  "success": true,
  "data": [
    {
      "ID": "a1b2c3d4e5",
      "FirstName": "John",
      "LastName": "Doe",
      "Email": "john.doe@example.com",
      "RegisteredAt": "2026-01-15"
    }
  ]
}

Get borrower by ID

GET /api/borrowers/:id

Response: Single borrower object, or 404 if not found.

Register a borrower

POST /api/borrowers

Request body:

{
  "firstName": "John",
  "lastName": "Doe",
  "email": "john.doe@example.com"
}

Field	Required	Description
firstName	Yes	First name (max 100 characters)
lastName	Yes	Last name (max 100 characters)
email	Yes	Email address (must be unique)

Response: 201 Created with the created borrower (ID is auto-generated).

Update a borrower

PUT /api/borrowers/:id

Request body: Any fields from the create request (all optional).

Response: Updated borrower object.

Delete a borrower

DELETE /api/borrowers/:id

Response: 204 No Content on success.

Note: Fails with 409 Conflict if the borrower has unreturned books.

---

Transactions

Check out a book

POST /api/transactions/checkout

Rate limited: 10 requests per minute.

Request body:

{
  "bookISBN": "9780134685991",
  "borrowerId": "a1b2c3d4e5"
}

Response:

{
  "success": true,
  "data": {
    "TransactionID": 1,
    "BookISBN": "9780134685991",
    "BorrowerID": "a1b2c3d4e5",
    "BorrowDate": "2026-01-15T14:30:00.000Z",
    "DueDate": "2026-01-29",
    "ReturnDate": null
  }
}

Notes:

• Due date is automatically set to 14 days from checkout.
• Fails if book has no available copies or borrower already has this book checked out.

Return a book

POST /api/transactions/return

Request body:

{
  "transactionId": 1
}

Response: Updated transaction with ReturnDate populated.

Get borrower's current checkouts

GET /api/transactions/borrower/:id

Response: Array of currently checked-out books (with book and borrower details).

List overdue books

GET /api/transactions/overdue

Response: Array of transactions where DueDate < today and ReturnDate IS NULL.

Export formats (CSV / XLSX)

Endpoints that accept format=json|csv|xlsx return:

• JSON — Structured response in the request body (default when format is omitted).
• CSV — File download (UTF-8, header row). Response has Content-Disposition: attachment and a filename.
• XLSX — File download (Office Open XML). Response has Content-Disposition: attachment and a filename.

Transaction columns (used in CSV and XLSX):

Transaction ID, Book ISBN, Book Title, Book Author, Borrower ID, Borrower First Name, Borrower Last Name, Borrower Email, Borrow Date, Due Date, Return Date.

Reports endpoint only (/api/transactions/reports):

• CSV: Single file with an extra Type column: Borrow, Return, or Still Out. All three datasets (borrows in range, returns in range, still out as of to) are combined into one table.
• XLSX: Four sheets — Analytics (period and counts: total borrows, returns, still out), Borrows, Returns, Still Out.

Overdue last month / Borrows last month (/api/transactions/export/overdue-last-month, /api/transactions/export/borrows-last-month):

• CSV / XLSX: Single file or single sheet with the transaction columns above (no Type column).

Filename patterns:

• Reports: transactions-report-{from}-to-{to}.csv or .xlsx
• Overdue last month: overdue-last-month-{from}-to-{to}.csv or .xlsx
• Borrows last month: borrows-last-month-{from}-to-{to}.csv or .xlsx

Borrowing reports (date range)

GET /api/transactions/reports

Query parameters:

Parameter	Required	Description
from	Yes	Start date (YYYY-MM-DD)
to	Yes	End date (YYYY-MM-DD)
format	No	json (default), csv, or xlsx

Response (JSON):

{
  "success": true,
  "data": {
    "analytics": {
      "period": { "from": "2026-01-01", "to": "2026-01-31" },
      "totalBorrows": 15,
      "totalReturns": 10,
      "stillOut": 5
    },
    "borrows": [...],
    "returns": [...],
    "stillOut": [...]
  }
}

For format=csv or format=xlsx, the response is a file download. Structure is described in Export formats (CSV / XLSX) above.

Export overdue borrows (last month)

GET /api/transactions/export/overdue-last-month

Query parameters:

Parameter	Required	Description
format	No	json (default), csv, or xlsx

Response: Transactions where due date was in the previous calendar month and the book was/is overdue. For format=csv or format=xlsx, the response is a file download (see Export formats (CSV / XLSX)).

Export all borrows (last month)

GET /api/transactions/export/borrows-last-month

Query parameters:

Parameter	Required	Description
format	No	json (default), csv, or xlsx

Response: All transactions where borrow date was in the previous calendar month. For format=csv or format=xlsx, the response is a file download (see Export formats (CSV / XLSX)).

---

Assumptions

See assumptions.md for design decisions and assumptions made during development.