A small Node.js API backed by SQLite for books, copies, members, and loans.
npm install
npm run seed
npm run benchmark
npm start
The API is available at GET /members/:memberId/loans. It returns the books currently out to that member, newest borrow first:
{
"memberId": 1,
"loans": [{ "title": "Book 1", "due_date": "2026-02-01" }]
}
The seed creates 200 books, 1,000 copies, 500 members, and 10,000 loans. The last 1,000 loans are active, so the endpoint has realistic current-loan rows to query.
npm run benchmark runs the endpoint query 1,000 times before and after the lookup index. On the seeded database, the important plan change is:
Before the index:
7|0|SCAN l USING INDEX one_active_loan_per_copy
12|0|SEARCH c USING INTEGER PRIMARY KEY (rowid=?)
15|0|SEARCH b USING INTEGER PRIMARY KEY (rowid=?)
24|0|USE TEMP B-TREE FOR ORDER BY
After idx_loans_member_active_borrowed:
7|0|SEARCH l USING INDEX idx_loans_member_active_borrowed (member_id=? AND returned_at=?)
15|0|SEARCH c USING INTEGER PRIMARY KEY (rowid=?)
18|0|SEARCH b USING INTEGER PRIMARY KEY (rowid=?)
The measured averages on Node 24.13.1 were 0.244 ms before the index and 0.018 ms after it. The benchmark prints the exact plan and timings for the installed SQLite version. The indexed query stays well below the 200 ms requirement on the 10,000-loan seed; the endpoint performs one prepared, indexed query and no application-side filtering.
SQLite enforces the lending invariant with the partial unique index one_active_loan_per_copy:
CREATE UNIQUE INDEX one_active_loan_per_copy
ON loans(copy_id)
WHERE returned_at IS NULL;
This belongs in the database because every writer, including future scripts or administrative tooling, must obey it. Application code alone could race between checking availability and inserting a loan. A second active loan for the same copy fails with a SQLite constraint error.
The separate idx_loans_member_active_borrowed index is for the list endpoint and is intentionally distinct from the constraint: it matches the member, active-loan filter, and newest-first ordering.