Apply your knowledge to build something amazing!
Forget boring SQL scripts! In this project, you'll build a real, working book lending app that you and your friends can actually use to share books. You'll start simple with just 3 tables, then grow your app as you learn new concepts. By the end, you'll have a deployed web application with user authentication, real-time availability, and a beautiful interface.
Week 1-2: Basic API with book browsing
Week 3: User authentication and profiles
Week 4: Borrowing system with real-time updates
Week 5: Search, filters, and recommendations
Final: Deployed app your friends can use!
By building this app, you will:
Start with just 3 tables to get your app running:
-- Just enough to build something useful!
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
email TEXT UNIQUE NOT NULL,
name TEXT NOT NULL,
password_hash TEXT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE books (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
author TEXT NOT NULL,
isbn TEXT UNIQUE,
cover_url TEXT,
available BOOLEAN DEFAULT true,
added_by INTEGER REFERENCES users(id),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE loans (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER REFERENCES users(id),
book_id INTEGER REFERENCES books(id),
borrowed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
due_date TIMESTAMP NOT NULL,
returned_at TIMESTAMP,
UNIQUE(book_id, returned_at)
);
Your First Features:
As your app grows, you'll naturally discover why we need:
You'll refactor your database:
// Using Prisma schema (so much cleaner!)
model Author \{
id Int @id @default(autoincrement())
name String
bio String?
books Book[]
createdAt DateTime @default(now())
\}
model Category \{
id Int @id @default(autoincrement())
name String @unique
books Book[]
\}
model Book \{
id Int @id @default(autoincrement())
title String
isbn String? @unique
author Author @relation(fields: [authorId], references: [id])
authorId Int
category Category? @relation(fields: [categoryId], references: [id])
categoryId Int?
coverUrl String?
copies Int @default(1)
loans Loan[]
addedBy User @relation(fields: [userId], references: [id])
userId Int
createdAt DateTime @default(now())
\}
Add features users actually want:
# Create your project
mkdir book-lending-app
cd book-lending-app
# Initialize backend
mkdir backend && cd backend
npm init -y
npm install express prisma @prisma/client bcrypt jsonwebtoken cors
npm install -D nodemon dotenv
# Initialize Prisma
npx prisma init --datasource-provider sqlite
// backend/server.js
const express = require('express');
const { PrismaClient } = require('@prisma/client');
const cors = require('cors');
const app = express();
const prisma = new PrismaClient();
app.use(cors());
app.use(express.json());
// Your first endpoint - it works!
app.get('/api/books', async (req, res) => {
const books = await prisma.book.findMany({
where: { available: true },
include: { addedBy: { select: { name: true } } }
});
res.json(books);
});
// Add a book
app.post('/api/books', async (req, res) => {
const { title, author, isbn, coverUrl } = req.body;
const book = await prisma.book.create({
data: { title, author, isbn, coverUrl, userId: req.userId }
});
res.json(book);
});
app.listen(3000, () => {
console.log('[rocket] Server running on http://localhost:3000');
});
{/* frontend/index.html */}
<!DOCTYPE html>
<html>
<head>
<title>Book Lending App</title>
<script src="https://cdn.tailwindcss.com"></script>
</head>
<body class="bg-gray-100">
<div class="container mx-auto p-8">
<h1 class="text-3xl font-bold mb-8">[books] Community Library</h1>
<div id="books" class="grid grid-cols-1 md:grid-cols-3 gap-6">
{/* Books will appear here */}
</div>
</div>
<script>
// Fetch and display books
async function loadBooks() \{
const response = await fetch('http://localhost:3000/api/books');
const books = await response.json();
const booksDiv = document.getElementById('books');
booksDiv.innerHTML = books.map(book => `
<div class="bg-white p-6 rounded-lg shadow">
<h3 class="font-bold text-lg">${book.title}</h3>
<p class="text-gray-600">by ${book.author}</p>
<button class="mt-4 bg-blue-500 text-white px-4 py-2 rounded"
onclick="borrowBook(${book.id})">
Borrow This Book
</button>
</div>
`).join('');
}
loadBooks();
</script>
</body>
</html>
// backend/middleware/auth.js
const jwt = require('jsonwebtoken');
module.exports = (req, res, next) => {
const token = req.headers.authorization?.split(' ')[1];
if (!token) return res.status(401).json({ error: 'No token provided' });
try {
const decoded = jwt.verify(token, process.env.JWT_SECRET);
req.userId = decoded.userId;
next();
} catch (error) {
res.status(401).json({ error: 'Invalid token' });
}
};
// backend/routes/loans.js
app.post('/api/loans/borrow', auth, async (req, res) => {
const { bookId } = req.body;
// Check if book is available
const book = await prisma.book.findUnique({ where: { id: bookId } });
if (!book || !book.available) {
return res.status(400).json({ error: 'Book not available' });
}
// Create loan and update book
const loan = await prisma.$transaction(async (tx) => {
const newLoan = await tx.loan.create({
data: {
userId: req.userId,
bookId,
dueDate: new Date(Date.now() + 14 * 24 * 60 * 60 * 1000) // 14 days
}
});
await tx.book.update({
where: { id: bookId },
data: { available: false }
});
return newLoan;
});
res.json({ message: 'Book borrowed successfully!', loan });
});
// Update your Prisma schema for PostgreSQL
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
// Push to Supabase
npx prisma db push
# Install Railway CLI
npm install -g @railway/cli
# Deploy
railway login
railway init
railway add
railway up
# Install Vercel CLI
npm install -g vercel
# Deploy
vercel
// backend/server.js
const { Server } = require('socket.io');
const io = new Server(server, { cors: { origin: '*' } });
io.on('connection', (socket) => {
console.log('User connected');
socket.on('book-borrowed', (bookId) => {
// Notify all users that a book was borrowed
io.emit('book-status-changed', { bookId, available: false });
});
});
// frontend/app.js
const socket = io('http://localhost:3000');
socket.on('book-status-changed', ({ bookId, available }) => {
// Update UI in real-time
updateBookStatus(bookId, available);
});
// backend/routes/books.js
app.get('/api/books/search', async (req, res) => \{
const \{ query, category, available \} = req.query;
const books = await prisma.book.findMany(\{
where: \{
AND: [
query ? \{
OR: [
\{ title: \{ contains: query, mode: 'insensitive' \} \},
\{ author: \{ contains: query, mode: 'insensitive' \} \}
]
\} : \{\},
category ? \{ category: \{ name: category \} \} : \{\},
available !== undefined ? \{ available: available === 'true' \} : \{\}
]
\},
include: \{
loans: \{
where: { returnedAt: null },
include: { user: { select: { name: true } } }
\}
\}
\});
res.json(books);
\});
// Find books liked by similar users
app.get('/api/books/:id/recommendations', async (req, res) => \{
const bookId = parseInt(req.params.id);
// Find users who borrowed this book
const similarUsers = await prisma.loan.findMany({
where: { bookId },
select: { userId: true }
});
// Find other books they borrowed
const recommendations = await prisma.book.findMany({
where: {
loans: {
some: {
userId: { in: similarUsers.map(u => u.userId) }
}
},
NOT: { id: bookId },
available: true
},
take: 5,
orderBy: {
loans: { _count: 'desc' }
}
\});
res.json(recommendations);
\});
Instead of writing SQL in isolation, you'll use Prisma to generate optimized queries:
// 1. Find overdue books (you'll build a notification system!)
const overdueLoans = await prisma.loan.findMany(\{
where: {
dueDate: { lt: new Date() },
returnedAt: null
},
include: \{
user: true,
book: true
\}
\});
// 2. Get borrowing statistics (for your dashboard!)
const stats = await prisma.loan.groupBy(\{
by: ['bookId'],
_count: \{ bookId: true \},
orderBy: \{ _count: \{ bookId: 'desc' \} \},
take: 10
\});
// 3. Calculate fines (with a real payment system!)
const calculateFine = (dueDate, returnDate = new Date()) => \{
const daysLate = Math.floor((returnDate - dueDate) / (1000 * 60 * 60 * 24));
return Math.max(0, daysLate * 0.50);
\};
// 4. Database migrations (learn by doing!)
// When you need to add a reviews table:
npx prisma migrate dev --name add_reviews
// Prisma handles the SQL for you:
model Review \{
id Int @id @default(autoincrement())
rating Int
comment String?
book Book @relation(fields: [bookId], references: [id])
bookId Int
user User @relation(fields: [userId], references: [id])
userId Int
createdAt DateTime @default(now())
@@unique([bookId, userId]) // One review per user per book
\}
| Component | Weight | What We're Looking For |
|---|---|---|
| Working Features | 40% | Does your app actually work? Can people use it? |
| Code Quality | 20% | Clean, readable, well-organized code |
| Database Design | 20% | Smart decisions, proper relationships, good performance |
| User Experience | 10% | Is it pleasant to use? Good UI/UX? |
| Deployment | 10% | Properly deployed, handles real traffic |
# Clone our starter template
git clone https://github.com/telebort/book-lending-starter
cd book-lending-starter
npm install
npm run dev
Once your basic app is working, consider adding:
Remember: The goal isn't to build a perfect library system. It's to learn databases by building something real that people actually use. Every bug you fix, every feature you add, and every user complaint you address teaches you more than any textbook could.
By the end of this project, you won't just understand databases - you'll have built one that serves a real community. That's the difference between learning and doing.
Start building. Start learning. Start sharing. 🚀