Part VI — Database Programming and Application Development

Chapter 24. Database APIs, Web Applications, and Integration

Chapter 23 built the middle tier as a service; this chapter puts the world in front of it: HTTP, REST, web frameworks, and the integration practices — authentication, pagination, ORMs, migrations, analytics pipelines, deployment — that turn a database-backed program into a running system. The chapter's spine is the picture from Chapter 4, now fully dressed: browser → application server → database, with every request walking the whole stack.

The throughline continues from Chapters 22–23: the registrar's application. By the end of the chapter it exists as a small REST API over the university database — sections listed, enrollments created through Chapter 22's procedures, authenticated users, paginated results — the same system Chapter 30 will deploy as a capstone.

After studying this chapter you will be able to:

• Explain how web applications use relational databases across the request lifecycle.
• Design REST resources backed by queries and procedures.
• Structure server-side code: layers, repositories, dialect isolation.
• Build database-backed authentication with modern password hashing and sessions.
• Implement filtering, searching, and both pagination styles over the database.
• Use ORMs knowingly: their model, their N+1, and when to drop beneath them.
• Version and manage schema migrations as code.
• Integrate analytics workloads without hurting the operational database.
• Deploy with configuration, secrets, health checks, and rollback.

24.1 Relational databases in web applications

The request lifecycle, once, from URL to rows:

 browser ──HTTP──► web/app server ──SQL/CALL──► PostgreSQL / MySQL
   ▲                    │                            │
   └────── HTML/JSON ◄──┴────── rows → domain ──────┘

Every request is (at most) one logical unit of Chapter 18's design: authenticate, load what the request needs (one query, not N), perform the action (often one procedure call), render the response, and release the pooled connection. The disciplines of Chapter 23 all bind here: repositories own SQL, connections checked out late and returned early, errors mapped at the boundary. What the web adds is scale behavior: requests arrive concurrently and unpredictably (pool sizing, retry loops), state must live server-side or in signed tokens (Chapter 23's connection state is per-request, never per-user), and every input is untrusted — Chapter 20's law applies to every form field and query string parameter the tier touches.

24.2 REST APIs and database services

REST maps resources to URLs and methods to meanings — and the mapping to the database is almost mechanical:

The design rules that matter: POST is the procedure call — the request body carries parameters, the handler runs the transaction with the Chapter 23 retry loop, business errors (23505, 45000) map to 409/422 responses with clean messages; GET is the query — safe, repeatable, cacheable, and never state-changing (a GET that enrolls is a REST bug and a CSRF hole); and responses are rows as JSON — shaped at the repository, not the raw table (hide salary; render in-progress grades as "IP" — Chapter 20's column grants, now an output policy). Status codes carry the Chapter 23 error mapping: 200/201 success, 400 validation, 401/403 authentication/authorization, 404 missing, 409/422 business conflicts, 5xx with a logged correlation id and a quiet message.

24.3 Server-side application architecture

The tier organizes into layers, whatever the framework (Spring, Django/FastAPI, Laravel — the names differ; the layers do not):

 routes/controllers   parse input, authorize, choose status
 services/domain      the transaction boundary, business flow
 repositories/ORM     all SQL lives here (the dialect layer inside)
 drivers/pool         connections, retry, mapping

Two rules keep the structure real. The transaction boundary lives in the service layer — the controller never opens transactions (it does not know the unit), the repository never does (it does not know the flow) — the Chapter 18 unit ("record this enrollment") is a service-level concept. The dialect layer is the repository package: platform-specific SQL (Chapter 13's ledger) sits in one module with two implementations, so the Chapter 17 migration story stays a module swap, not a codebase hunt. Views and templates read from the domain shapes; they never see SQL, tables, or drivers — the separation that makes the tier testable (a service testable against a dev database, a controller testable against a fake service).

24.4 Database-backed authentication

Authentication is a database feature like any other — and the one with the highest stakes. The modern recipe, the same in every framework:

• Password storage: hash with bcrypt, scrypt, or Argon2 — salted, deliberately slow — never plaintext, never SHA-256 alone (too fast to resist brute force). The app_user table holds username, password_hash, created_at; the hash column is never selected into domain objects (Chapter 20's column-grant discipline, applied to your own table).
• Login: fetch by username (parameterized — Section 23.6's law, on the highest-traffic unauthenticated query you own), verify with the hash library's check function, fail uniformly ("invalid username or password" — never reveal which half was wrong).
• Sessions: server-side session state keyed by a random token in an httpOnly cookie, or signed tokens (JWT) carrying claims; either way the request middleware re-establishes identity per request, and the database stays per-request stateless (Chapter 23's discipline intact).
• Authorization per request: identity → role (Chapter 20's RBAC in the application mirror: the route declares its role; middleware enforces), and for row scoping, the request's identity flows into queries as parameters (or into PostgreSQL's RLS current_setting — Section 20.5's contract, kept).

Rate-limiting login attempts and auditing failures (Section 20.9's questions) close the loop — the authentication flow is where Chapter 20's checklist and this chapter's code meet.

24.5 Pagination, filtering, and searching

List endpoints — sections, students, orders — are database questions wearing URLs, and Chapter 10's lessons price them:

• Page-based: ?page=3&size=20 → LIMIT 20 OFFSET 40 over a fixed ORDER BY — simple, and the cost grows with depth (every skipped row is produced and discarded). Fine for shallow UIs (page 2 of a 5-page report).
• Keyset: ?after=3.75,Nusrat%20Jahan → WHERE (gpa, full_name) < (previous) ORDER BY ... LIMIT 20 — the "load more" shape; every API with unbounded lists eventually migrates here (Chapter 10's economics, now a URL contract — opaque cursors encode the last-seen key).
• Filtering is the WHERE clause as API: ?year=2026&semester=Fall maps to parameters; column names in query strings (?sort=gpa) go through the Chapter 20/23 allowlist — the injection boundary every list endpoint owns.
• Searching is LIKE's territory for small data (LOWER(full_name) LIKE '%'||LOWER(?)||'%') and the full-text machinery (tsvector/GIN, MATCH ... AGAINST — Chapter 17) once it matters — the leading-wildcard scan (Chapter 19) that forces the upgrade is a load symptom, watched in the metrics.

24.6 ORM concepts and object-relational mapping

An ORM (Object-Relational Mapper) maps tables to classes and rows to objects — Hibernate/JPA in Java, SQLAlchemy (or Django's ORM) in Python, Eloquent in PHP — generating the SQL of Chapters 10–12 so the domain code reads objects:

class Student(Base):
    __tablename__ = "student"
    student_id = Column(Integer, primary_key=True)
    full_name  = Column(String(60))
    enrollments = relationship("Enrollment", back_populates="student")

section.students  # a list, lazily loaded from the enrollment table

The honest ledger. What ORMs buy: productivity (CRUD in objects, no hand SQL for the 90%), portability (the dialect layer exists already — the ORM writes both platforms' LIMITs and concatenations), migrations (Section 24.7), and injection safety by default (query builders parameterize — Chapter 20's law, enforced by the tool). What they cost: the N+1 problem (a loop over section.students triggers one query per row — lazy loading, the Chapter 23 anti-pattern, now automatic; the fix is eager loading (selectinload, JOIN FETCH) or falling beneath the ORM); opaque SQL (the Chapter 19 discipline demands the ORM's query log be enabled in development — every EXPLAIN begins by knowing what was generated); and leaky abstraction (the object model and the relational model genuinely differ — Chapter 2's impedance mismatch — and the seams show at exactly this book's hard questions: windows, recursive CTEs, dialect features). The professional pattern, stated once: ORM for the 90%, repository SQL for the 10%, query log always on in development — the tool serves the schema, never the reverse.

24.7 Database migrations and schema versioning

Chapter 9's rule — evolve with migrations, not edits — becomes practice here: the schema is versioned code. A migration is a small, ordered, reviewed script — up (apply) and down (revert):

migrations/
  001_create_core_tables.sql      -- the Appendix H schema
  002_add_meetings.sql           -- up: CREATE TABLE meeting ...; down: DROP TABLE
  003_add_grade_audit.sql         -- triggers from Chapter 22
  004_add_partial_index.sql       -- Chapter 19's hot-path index

The tools manage them: Flyway/Liquibase (Java ecosystem), Alembic (SQLAlchemy), framework built-ins (Django, Rails, Laravel) — each tracks an applied-version table in the database itself (the catalog hosting its own history — Chapter 4's metadata promise, again) and applies pending migrations in order on deploy. The disciplines are the chapter's: every schema change is a migration (never a hand-run ALTER — the "who changed production's schema" question must always answer from files); migrations are reviewed like code (Chapter 9's risky ALTER class — type changes, long rewrites — gets the downtime plan in review); down migrations exist and are tested (the rollback path is a real path — Chapter 21's recovery instincts, applied to schema); and large tables migrate online (PostgreSQL's NOT VALID+VALIDATE, MySQL's online DDL — Sections 15.4/17.5 — the constraint-without-downtime idiom, here in production form).

24.8 Integrating databases with analytics applications

Reports and dashboards ask the operational database questions it was not sized to answer — the Chapter 19 bulk workloads. The integration ladder, cheapest first:

1. Query the operational database carefully — the Chapter 11/13 dashboards with their indexes; fine at university scale, watched at product scale (the slow-query log is the tripwire).
2. Read replicas — Chapter 21's replication pointed at reporting: the dashboard reads the replica, the primary stays transactional; the cost is replication lag and "how fresh is fresh enough" becoming a stated business decision.
3. A warehouse — the Chapter 25 system: periodic ETL (extract-transform-load) copies operational data into a reporting-shaped store (denormalized stars, pre-aggregated summaries — Chapter 7's conscious denormalization, institutionalized); the operational database's exposure becomes the load window, not the queries.
4. Warehouse-native tools — the BI layer (Metabase, Superset, the commercial suites) speaks SQL to the warehouse, never to the primary.

The design constant across the ladder: operational schemas are for transactions, analytical schemas for questions — and the boundary between them (what is extracted, how often, into what shape) is a designed artifact, not an accident that grew.

24.9 Deployment and configuration management

Deployment is where every chapter's settings become one running thing, and the twelve-factor pattern is the default: configuration from the environment (the DATABASE_URL of Chapter 23), never in the artifact; secrets from a secret manager (Chapter 20's rule — the artifact is stored, copied, and sometimes leaked, and it must carry nothing that matters); logs to stdout shipped by the platform (Section 20.9's shipping, re-homed).

The database-specific deploy checklist: migrations run as a deploy step (Section 24.7's tool, in order, idempotently — and before the code that needs them, in the backward-compatible pattern: add column, deploy code, backfill, then remove the old — the zero-downtime schema dance); health checks are real (the service pings its pool and its dependencies — /healthz runs a trivial query, because "up but cannot reach the database" is the failure users see); connection settings deployed together (pool sizes against max_connections — Section 23.8's arithmetic, now a config review); observability (metrics: pool saturation, query latency percentiles, error rates by SQLSTATE class — Section 21.9's monitoring, at application granularity); and rollback practiced — the deploy is reversible because migrations have downs and the previous artifact is retained — Chapter 21's recovery discipline, applied to releases.

Chapter Summary

• The request lifecycle is one Chapter 18 unit: authenticate, one-query loads, procedure-call actions, pooled connections released early.
• REST maps mechanically: GET to queries, POST to procedure calls with retry, status codes to the Chapter 23 error mapping, rows shaped as JSON under output policy.
• The tier is layers: controllers parse and authorize, services own transaction boundaries, repositories own SQL and dialect; templates never see tables.
• Authentication: slow hashes (bcrypt/Argon2), uniform failures, sessions or signed tokens, RBAC mirrored per request, row scoping via parameters or RLS settings.
• List endpoints: page-based at shallow depth, keyset for unbounded; filters are parameters; sort columns are allowlisted; search graduates from LIKE to full-text on load evidence.
• ORMs buy productivity, portability, migrations, and safe defaults; they cost N+1, opacity (query log always on in dev), and leaky abstraction — ORM for the 90%, repositories for the rest.
• Migrations are versioned up/down code, tracked in the database, applied in order on deploy, reviewed for the risky ALTER class, and run online for large tables.
• Analytics integrates by ladder: tuned queries → read replicas → warehouse with ETL — the operational/analytical boundary is designed, not accidental.
• Deployment: twelve-factor configuration, secret-manager credentials, migrations as steps with backward-compatible ordering, real health checks, database-aware metrics, practiced rollback.

Key Terms

Laboratory Exercises

1. API over the university database: implement GET /api/sections?year=2026, GET /api/sections/12, GET /api/students/21300003/transcript, and POST /api/enrollments (calling enroll_student, with the retry loop) in your Chapter 23 language's framework; verify each response against the canonical data. Expected results: three Fall 2026 sections; section 12's two in-progress enrollments (Arif, Shahriar); Nusrat's three-row transcript; a POST creating enrollment 29 in dev, with 409 on the duplicate.
2. Authentication build: an app_user table (Argon2 hashes), login and session endpoints, middleware-guarded routes, and a rate-limited login; prove the uniform failure message for a wrong username and a wrong password. Expected results: login works, routes 401 without a session, both failure cases return the same message, hashes never appear in any response or log.
3. Pagination, both styles: a student list at page-based ?page=2&size=3 and keyset ?after=...; verify page 2 equals the Chapter 10 example rows and explain the plan difference at depth. Expected results: identical rows for page 2 either way; the OFFSET plan's discarded-rows behavior documented.
4. ORM field trip: map the six tables in your ecosystem's ORM; reproduce the honor roll (Chapter 12) three ways — ORM relationship walking, ORM query builder, raw repository SQL — with the query log on; count queries. Expected results: five honor students from all three; the relationship-walking form measurably more queries than the others — N+1 met, then fixed with eager loading.
5. Migrations: add the meeting table and the Chapter 22 triggers as two Alembic/Flyway/framework migrations with working downs; apply, inspect the version table, revert one, re-apply. Expected results: applied-version table updated; down-migration cleanly reverts the triggers; re-apply idempotent.
6. Deployment drill: containerize (or service-ize) the Chapter 23 service with twelve-factor configuration; wire /healthz to a real query; deploy the migration set; then break the database credential and show the health check failing while the process runs. Expected results: green deploy; red healthz on the broken credential — "up but unreachable" caught, not hidden.

Review Questions and Exercises

1. Why is a GET that enrolls students both a REST error and a security hole? GET must be safe and repeatable; a state-changing GET is also cacheable/prefetchable by intermediaries and CSRF-prone — the action belongs in POST.
2. Map POST /api/enrollments returning 409 and 422 to the database errors of Chapter 23. 409 for the duplicate enrollment (23505) or a concurrent conflict; 422 for business refusals like the full-section 45000 — both from the mapped business class.
3. Which layer owns transactions, and why not the two other candidates? The service layer — it knows the logical unit; controllers know only requests, repositories only statements.
4. Why is SHA-256(password) the wrong hash and Argon2 right? SHA-256 is fast — built for verification speed, which is exactly wrong for resisting brute force; Argon2 is salted and deliberately expensive per attempt.
5. State the two ways row scoping reaches the database and one risk of each. Query parameters (risk: a forgotten WHERE in one query) or PostgreSQL RLS with a per-request setting (risk: the setting must be set reliably per transaction — the contract must be kept).
6. Why does an opaque cursor beat ?after=gpa:3.75,name:Nusrat in a public API? The cursor hides the ordering contract — you can change the sort key or add columns without breaking clients, and it prevents clients from crafting arbitrary keys.
7. Name the ORM's four gifts and its three costs, with the standing policy. Productivity, portability, migrations, safe defaults; N+1, opaque SQL, leaky abstraction — ORM for the 90%, repository SQL for the rest, query log always on in development.
8. Why must migrations never be hand-run in production? The version table must reflect reality; a hand-run ALTER desyncs files from schema — "what is production's schema" must always answer from reviewed, ordered, tracked files.
9. Give the zero-downtime sequence for renaming a column and why each step is safe. Add the new column (nullable) → deploy code writing both → backfill → deploy code reading the new → drop the old — every step runs against code that works with what exists.
10. Your dashboard slows the primary every morning. Walk the integration ladder with one sentence per rung. Tune the dashboard queries and indexes; point it at a read replica (accepting stated lag); ETL into a warehouse star and answer from there — each rung removes load until the primary is transactional only.
11. Why is a health check that only pings the process insufficient? The process can be up while the pool, network, or database is down — users see the database failure; the check must prove the full path with a trivial query.
12. Where do pool size, max_connections, and the number of application instances meet, and what is the review? instances × pool_size ≤ server max_connections with headroom — the deploy-time arithmetic review of Section 23.8, checked whenever either side changes.

Mini-Project

Ship the registrar API — the capstone's rehearsal. Extend your Chapter 23 enrollment service into a full web application in your framework of choice: (1) the resource set of Section 24.2 (sections, section detail, transcript, enroll/withdraw POSTs) with status-code mapping and JSON shaping under the output policy; (2) authentication and RBAC (student/faculty/admin roles; students see their own transcripts only — RLS or parameterized scoping, stated in the README); (3) filtering, allowlisted sorting, and keyset pagination on the student and section lists; (4) the ORM layer with the query log enabled and the three-way honor-roll comparison from Laboratory 4 documented in a comment; (5) migrations: the full schema as migration 001, then the meeting table and Chapter 22 triggers as 002–003, with tested downs; (6) deployment: container or service definition, twelve-factor configuration, /healthz with a real query, and a README stating the deployment and rollback order; (7) a load test at 100 concurrent list requests proving the pool holds (watch pg_stat_activity/Threads_connected — bounded) and that no query regresses (pick your slowest endpoint and EXPLAIN it before and after). This application is the database's whole book in one program — and Chapter 30's foundation.