Tutorial 1: REST, JSON, SQL, and Transactions

Unit 5 · Web data and integration

Objectives

SELECT id, title
FROM courses
WHERE level = ?
ORDER BY title
LIMIT ? OFFSET ?;

REST-style APIs expose resources through consistent representations. JSON is a useful interchange format, but the server remains responsible for validation and authorization. Never concatenate untrusted input into SQL; parameterized queries keep data separate from query structure.

A transaction groups related operations into an all-or-nothing unit. If creating an enrollment also updates a seat count, both changes should commit together or roll back together.

Exercises

  1. Design JSON for a course and its prerequisites.
  2. Rewrite an unsafe string-built query as a parameterized query.
  3. List the operations that belong in one enrollment transaction.

Self-check

  1. Why parameterize SQL?
  2. What does rollback provide?
  3. What is a resource in a REST API?

Self-Check Quiz

1. What does a transaction guarantee when it commits?

AnswerIts grouped changes are applied as one consistent unit according to the database's transaction guarantees.

2. Why is WHERE id = '" + id unsafe?

AnswerIt mixes untrusted data with SQL syntax and can enable injection; a parameterized query keeps them separate.

Homework

  1. Design a database-backed course endpoint with pagination.
  2. Implement a safe query and map rows to JSON.
  3. Describe rollback behavior when a second related write fails.
Sample answerThe endpoint should use a bounded page size, parameterized level and offset values, and a stable ordering. A failed enrollment update rolls back the enrollment insert and seat decrement so the database cannot report contradictory state.
👤