The SQL behind each endpoint

One query per job

CRUD stands for Create, Read, Update, Delete: the four basic things an app does with data. In SQL they are INSERT, SELECT, UPDATE and DELETE. Each endpoint from the module 2 contract maps to one of them.

Placeholders: the most important habit

A placeholder is a marker in the SQL text, ? in SQLite and MySQL or $1, $2 in PostgreSQL, where a value will go. You send the SQL and the values separately. The database treats the values only as data, never as SQL commands.

// DANGEROUS: the search text becomes part of the SQL
const sql = "SELECT * FROM notes WHERE user_id = 1 AND title LIKE '%" + q + "%'";
// If q is  %' OR 1=1 --  the query returns EVERY user's notes.

// SAFE: placeholders (better-sqlite3 for SQLite)
const rows = db
  .prepare('SELECT * FROM notes WHERE user_id = ? AND title LIKE ?')
  .all(userId, '%' + q + '%');

// SAFE: placeholders (node-postgres for PostgreSQL)
const result = await pool.query(
  'SELECT * FROM notes WHERE user_id = $1 AND title LIKE $2',
  [userId, '%' + q + '%'],
);

Listing, reading and searching

-- List: my notes, pinned first, then the most recently changed
SELECT id, title, body, pinned, created_at, updated_at
FROM notes
WHERE user_id = ?
ORDER BY pinned DESC, updated_at DESC;

-- Read one: only if it is mine
SELECT id, title, body, pinned, created_at, updated_at
FROM notes
WHERE id = ? AND user_id = ?;

-- Search: note the brackets around the OR
SELECT id, title FROM notes
WHERE user_id = ? AND (title LIKE ? OR body LIKE ?)
ORDER BY pinned DESC, updated_at DESC;

AND is stronger than OR in SQL, just as multiply is stronger than add in maths. Without brackets, "user_id = ? AND title LIKE ? OR body LIKE ?" means "(my notes with the word in the title) OR (anyone's notes with the word in the body)". That leaks other users' notes. Always put brackets around the OR part.

If the read-one query returns no row, the note either does not exist or belongs to someone else. The server answers 404 in both cases, so a stranger cannot even learn that note 42 exists.

Creating, changing and deleting

INSERT INTO notes (user_id, title, body, pinned, created_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?);

UPDATE notes
SET title = ?, body = ?, pinned = ?, updated_at = ?
WHERE id = ? AND user_id = ?;

DELETE FROM notes
WHERE id = ? AND user_id = ?;

Every UPDATE also sets updated_at to the current time, so the "newest first" order stays correct. After an INSERT, the driver gives you the new id (lastInsertRowid in better-sqlite3, or add RETURNING id in PostgreSQL and SQLite).

Paging with LIMIT and OFFSET

A user with 2,000 notes should not get all of them in one response. LIMIT says how many rows to return; OFFSET says how many to skip first. For page p (starting at 1) with size n: LIMIT n OFFSET (p - 1) * n. Paging needs a fixed ORDER BY, or rows can jump between pages.