06 · SQL, PostgreSQL & Prisma — data gets a real home

Phase 4 gave the Library one source of truth on a server. Phase 5 upgrades WHERE that truth lives: from a JSON file to a database — a program built for exactly one job, keeping data safe, fast, and queryable. Your bookId foreign-key idea becomes literal here.

1. Why a JSON file isn't enough

The Phase 4 endpoint saves books.json to disk. It works — until it doesn't. Each of these breaks it, and each is a database's reason to exist:

2. Tables — your interfaces, made of rows

You already designed the Library's tables without knowing it. An interface describes one thing's shape; a table stores MANY things of that shape — one row per thing, one column per field:

-- your interface Book, spoken in SQL
CREATE TABLE books (
    id       SERIAL PRIMARY KEY,   -- SERIAL = auto-numbered (your Math.max trick, built in!)
    title    TEXT NOT NULL,
    author   TEXT NOT NULL,
    category TEXT NOT NULL,
    copies   INTEGER NOT NULL
);

3. SELECT — filter, find and sort, spoken in SQL

Every query below is an array method you already own. Same intent, new grammar:

-- books                              (the whole array)
SELECT * FROM books;

-- books.filter(b => b.copies <= 2)   (your low-stock filter)
SELECT * FROM books WHERE copies <= 2;

-- books.find(b => b.id === 3)        (find by id)
SELECT * FROM books WHERE id = 3;

-- title search                        (your search box)
SELECT * FROM books WHERE title ILIKE '%harry%';

-- books.sort((a,b) => a.copies - b.copies)   (your sortByCopies)
SELECT * FROM books ORDER BY copies;

-- count without fetching
SELECT COUNT(*) FROM books;

4. INSERT, UPDATE, DELETE — your CRUD, spoken in SQL

-- books.push({...})                  (addBook)
INSERT INTO books (title, author, category, copies)
VALUES ('Harry Potter', 'J.K. Rowling', 'Fantasy', 5);

-- book.copies = book.copies - 1      (borrowBook)
UPDATE books SET copies = copies - 1 WHERE id = 3;

-- books.splice(index, 1)             (deleteBook)
DELETE FROM books WHERE id = 3;

5. Foreign keys — your bookId was SQL all along

Capstone design decision, notes §13: a Loan stores bookId, never a copy of the book. SQL not only agrees — it enforces it:

CREATE TABLE loans (
    id            SERIAL PRIMARY KEY,
    book_id       INTEGER NOT NULL REFERENCES books(id),   -- ← the foreign key
    member_name   TEXT NOT NULL,
    borrowed_date TIMESTAMP NOT NULL DEFAULT now(),
    due_date      TIMESTAMP NOT NULL,
    returned      BOOLEAN NOT NULL DEFAULT false
);

6. JOIN — your renderLoans lookup, done by the database

renderLoans does this per loan: books.find(b => b.id === loan.bookId) to show a title. That id→row lookup across two collections has a SQL name — JOIN:

-- the loans table, but with each loan's book title attached
SELECT loans.id, books.title, loans.member_name, loans.due_date, loans.returned
FROM loans
JOIN books ON books.id = loans.book_id;

Read ON as your find-condition: "match rows where books.id equals loans.book_id." The database stitches the rows together and sends the finished combination — no loop in your code at all.

7. Prisma — the TypeScript bridge

You describe tables once, in schema.prisma — compare it to your interfaces:

model Book {
    id       Int    @id @default(autoincrement())
    title    String
    author   String
    category String
    copies   Int
    loans    Loan[]                                // ← the "many" side, navigable
}

model Loan {
    id           Int      @id @default(autoincrement())
    book         Book     @relation(fields: [bookId], references: [id])
    bookId       Int
    memberName   String
    borrowedDate DateTime @default(now())
    dueDate      DateTime
    returned     Boolean  @default(false)
}

Then npx prisma migrate dev turns the schema into real tables (and tracks every change as a migration — versioned history of your database's shape). Queries become typed method calls:

// SELECT * FROM books WHERE copies <= 2
const lowStock = await prisma.book.findMany({ where: { copies: { lte: 2 } } });

// find by id — returns Book | null (your narrowing guard lives on!)
const book = await prisma.book.findUnique({ where: { id: 3 } });

// the JOIN, as a nested include
const loansWithBooks = await prisma.loan.findMany({ include: { book: true } });
// each loan now carries loan.book.title — renderLoans' find, pre-done

8. await — because the database is a phone call, not a variable

9. The Phase 5 arc

10. The mental model to keep

← Prev: Node.js  ·  Back to contents  ·  Next: React →