Admin stuff
CSCC09 Programming on the Web
Storing and Serving Data
Cho Yin Yong
So far
Problems
This Lecture
Now: One file approach
Model View Controller (MVC)
Example folder structure
static/ # view
routers/ # controllers
chirps-router.js
models/
chirp.js
app.js # entrypoint
Persistent storage on disk - like SQL (in C43)
CRUD operations in SQL
C - INSERT INTO Chirps VALUES (...)�R - SELECT * FROM Chirps�U - UPDATE Chirps SET …�D - DELETE Chirps WHERE …
SQL Abstraction - Object Relational Mapper (ORM)
Active Record Pattern
C - await Chirp.create({ content: "Hello" });
R - await Chirp.findByPk(123);
After creating Chirp object:
U - await chirp.update({ content: "Bye" })
D - await chirp.destroy()
async / await
app.get(“/messages”, async () => {
const msgs = await Message.findAll();
return res.json({ msgs });
})
Synchronous = blocking
for (let i = 0; i < 10; i++) {
user = await User.findByPk(i); // BLOCKING
console.log(user);
}
We can use Promise syntax instead
Resolving Promises in parallel
const users = await Promise.all([
User.findByPk(1),
User.findByPk(2),
User.findByPk(3),
])
https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Promise/all
These must be independent calls
Chirper Demo
Express + sequelize + sqlite
We will:
Chirp
Has many
Database Structure
https://tableplus.com/
Interacting with databases with code
Example code for GET /chirps
const chirps = await Chirp.findAll();
let user;
for (const chirp of chirps) {
user = await User.findByPk(chirp.userId)
}
What’s the problem?
Let’s assume this makes a SQL call
SELECT * FROM User WHERE UserId=1;
N+1 problem
const chirps = await Chirp.findAll();
let user;
for (const chirp of chirps) {
user = await User.findByPk(chirp.userId);
}
SELECT * FROM Chirps;
SELECT * FROM Users WHERE id=UserId
How many SQL calls in total?
Problems
Solution #1 - SQL JOINs
SELECT c1.*, c2.* FROM Chirps c1
LEFT JOIN Chirps c2 ON c1.ChirpId = c2.id;
id | ChirpId |
1 | 2 |
2 | NULL |
3 | 2 |
| |
| |
c1.id | c1.ChirpId | c2.id | c2.ChirpId |
1 | 2 | 2 | NULL |
3 | 2 | 2 | NULL |
2 | NULL | | |
| | | |
| | | |
ORM
Or… ORMs make it even easier.
await Chirp.findAll({
limit: 5,
include: { association: "Chirp", attributes: ["content"] },
});
Solution #2 - Batching Queries
messages = await Message.findAll();
users = await Users.findAll({
where: {id: messages.map((m) => m.UserId) }
}); // SELECT * FROM Users WHERE id IN (1, 2, 3)
// combine results here with code
...
N+1 problems are not limited to SQL calls
It applies to API calls as well!
Quick demo in Chirper
Pagination in APIs
What is pagination?
Dividing a collection into multiple pages, serving it one page at a time.
GET /patients?page=1&limit=10
Show me the first page with 10 patients
Return a list
[{...}, {...}]
Return an object with more information:
{total: 123, items: [{...}, {...}], next_page_uri: ‘/patients?page=2’}
No pagination
Store everything in the frontend, show user a subset.
Good: Given that everything is already in memory, it is fast.
Bad: It consumes large amounts of memory. Unsure if end-user’s device can handle it
When should we not use pagination?
In-memory backend pagination
Retrieve collection in backend memory, but serve paginated version to frontend.
Good: For collection storage which does not have a pagination API
Bad: Huge memory usage in the backend
Offset-limit pagination
page=0, limit=10
First page = first 10 records
Second page = skip first 10 records, read record 11-20
…
xth page = skip (10*x) records,
read record (10*x)+1 to (10*(x+1))
SQL offset-limit pagination
Retrieve and serve paginated collection from database query
Good: Filtering/Ordering usually comes for free with a database call
SELECT * FROM Messages WHERE UserId = 5 ORDER BY created_at DESC OFFSET 100000 LIMIT 10;
Bad: Poor for frequent data updates / OFFSET is not efficient
Problematic Scenario
[3, 2, 1, 0, -1, -2]
Cursor based pagination
GET /patients?limit=3&prev=2024-05-30T18:20:00Z
8 | 7 | 6 |
10
5
5 | 4 | 3 |
12 | 11 | 10 |
next
prev
Demo: Infinite scrolling with cursor-based pagination
Back to Chirper!
What pagination to use in Assignment 2?
Hint: both works, but which might be easier?
Uploading and serving files
Can’t you only put strings in the request body?
Sending the file to the backend
<form action="/url" method="POST" enctype="multipart/form-data">
HTML File upload element
What is received in the backend
MIME Type (Multipurpose Internet Mail Extensions)
“Browsers use the MIME type, not the file extension, to determine how to process a URL”
This information is exposed through HTTP Response header Content-Type
Demo
Uploading images with Fetch API in Chirper
Important notes
Security-first mindset