Part VI — Database Programming and Application Development

Chapter 23. Application Development with Relational Databases

The database is built, secured, indexed, and backed up — this chapter connects it to the programs people actually use. Chapter 4 drew the three-tier picture; this chapter writes the middle tier: how Java, Python, and PHP applications connect, query, and transact; how connections are pooled; how errors — including Chapter 18's serialization failures — are handled in real code; and how Chapter 20's security law (parameterized everything) is enforced by every modern driver.

The throughline is a single small program, written three times: connect, query the university, call Chapter 22's enroll_student, handle the failure paths. One application, three languages, one set of disciplines — the disciplines are the chapter; the languages are exercises.

After studying this chapter you will be able to:

• Explain application-database architecture and where each concern lives.
• Connect to PostgreSQL and MySQL from Java, Python, and PHP.
• Write prepared statements with parameters in all three.
• Implement CRUD (create, read, update, delete) against the university schema.
• Pool connections and articulate why pooling is mandatory at scale.
• Manage transactions from application code, including retry on 40001/40P01.
• Handle database exceptions by class and surface them usefully.
• Apply application-side security: secrets, least privilege, TLS, validation.

23.1 Database application architecture

The database tier's boundary is precise: everything before this chapter ran in the database; everything in this chapter runs next to it. The application tier's job list: translate user actions into SQL or procedure calls, manage connections and transactions, convert rows into the domain's shapes (objects, JSON, HTML), enforce interaction-level rules, and handle failures. The database's job list is everything else — and Chapter 22's toolkit shows the dividing line at its sharpest: when the application CALLs enroll_student, the choreography lives in the database (atomic, reusable, guarded) and the interaction lives in the application (forms, messages, session state).

Two architectural patterns organize the tier. The repository pattern — a module that owns all SQL for one entity (StudentRepository.find(id), .enroll(...)) — gives the codebase exactly one place where queries live, which is where Chapter 13's dialect layer, Chapter 19's tuning, and every EXPLAIN begin. And connection discipline — connections are expensive, stateful, and not thread-safe: acquired late, released early, never shared mid-request. Both patterns are language-independent; the next sections show them three times.

23.2 Connecting applications to a database

Every driver speaks the same five facts as Chapter 14 — host, port, database, user, password — in a connection string (URL/DSN):

PostgreSQL:  postgresql://portal:secret@db.example.edu:5432/university
MySQL:       mysql://portal:secret@db.example.edu:3306/university
JDBC (PG):   jdbc:postgresql://db.example.edu:5432/university
JDBC (MySQL): jdbc:mysql://db.example.edu:3306/university

The rules that carry across every language: credentials come from configuration, not code — environment variables or a secret manager (os.environ["DATABASE_URL"]), never literals and never git (Chapter 20); the application account is the portal account — least-privileged (SELECT on the transcript view, EXECUTE on the procedures), never postgres/root; TLS is on (sslmode=verify-full in the URL — connection strings carry the Chapter 20 settings); and failures at connect time are connection-ladder failures — Chapter 14's diagnosis table, now read from a stack trace.

23.3 Java Database Connectivity (JDBC)

JDBC is the standard Java API; every database ships a driver implementing it:

import java.sql.*;

public class EnrollmentService {
    public void enroll(int studentId, int sectionId) throws Exception {
        String url = System.getenv("DATABASE_URL");   // jdbc:postgresql://...
        try (Connection conn = DriverManager.getConnection(url)) {
            conn.setAutoCommit(false);
            try (CallableStatement cs = conn.prepareCall(
                     "{CALL enroll_student(?, ?)}")) {
                cs.setInt(1, studentId);
                cs.setInt(2, sectionId);
                cs.execute();
            }
            conn.commit();
        }
    }
}

The JDBC disciplines, all visible: try-with-resources closes every statement and connection (the resource-leak class of bugs disappears with the syntax); setAutoCommit(false) opens explicit transactions; **CallableStatement** calls Chapter 22's procedure by name — parameters bound by position, no SQL assembly; and setInt/setString are the parameter bindings (Section 23.6). Result sets iterate with the cursor idiom (try (ResultSet rs = ps.executeQuery()) { while (rs.next()) { ... } }) — getString("full_name") by column name, never by fragile position.

23.4 Python database connectivity

Python's standard is DB-API 2.0 — one interface, many drivers: psycopg (PostgreSQL, the modern driver), mysql-connector-python or PyMySQL (MySQL). The same service:

import os
import psycopg
from psycopg import sql

def enroll(student_id: int, section_id: int) -> None:
    with psycopg.connect(os.environ["DATABASE_URL"]) as conn:
        with conn.cursor() as cur:
            cur.execute("CALL enroll_student(%s, %s)",
                        (student_id, section_id))
        conn.commit()          # the with-block rolls back on exception

Python's gifts to database code: the with context manager makes commit-on-success, rollback-on-exception the default shape (a conn.transaction() block composes it further); parameters pass as tuples (%s placeholders, never % string formatting — Chapter 20's injection law, and Python's % interpolation would break it anyway); and driver errors arrive as a class hierarchy (psycopg.errors.UniqueViolation, .DeadlockDetected) — Section 23.10's dispatch. MySQL drivers spell placeholders ? or %s by driver — one dialect note worth checking before writing a repository.

23.5 PHP database connectivity

PHP's modern standard is PDO (PHP Data Objects) — one interface, drivers per database, prepared statements built in:

<?php
function enroll(int $studentId, int $sectionId): void
{
    $pdo = new PDO(getenv('DATABASE_URL'));
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $stmt = $pdo->prepare('CALL enroll_student(:student, :section)');
    $stmt->execute(['student' => $studentId, 'section' => $sectionId]);
}

PDO's disciplines: named parameters (:student) or positional ? — both parameterized, both injection-proof (Chapter 20's law, enforced by the API: query() is for parameterless SQL, prepare()/execute() for everything with input); ERRMODE_EXCEPTION turns silent false returns into catchable exceptions (the default silent mode is a bug farm); and the connection carries options in the DSN (charset=utf8mb4 for MySQL; persistent connections, next section). The older mysqli API remains common in MySQL-only codebases — recognize it, prefer PDO for any code that might outlive the platform choice (the Chapter 13 principle, as PHP).

23.6 Prepared statements and parameterized queries

Chapter 20 stated the law; every driver enforces it identically — the query's structure and its data travel separately. The full CRUD pattern, once, in Python (all three languages translate directly):

def find_section_students(cur, section_id: int) -> list[dict]:
    cur.execute("""
        SELECT s.student_id, s.full_name, e.grade
        FROM   enrollment e
        JOIN   student s ON s.student_id = e.student_id
        WHERE  e.section_id = %s
        ORDER  BY s.full_name
    """, (section_id,))
    return [dict(row) for row in cur.fetchall()]

def insert_enrollment(cur, student_id: int, section_id: int) -> None:
    cur.execute(
        "INSERT INTO enrollment (student_id, section_id) VALUES (%s, %s)",
        (student_id, section_id))

Three notes complete the pattern. Parameters bind values only — identifiers (table names, ORDER BY columns) come from allowlists (Chapter 20). Prepared statements earn a bonus in hot loops: many drivers cache the parse server-side (PostgreSQL named prepared statements; MySQL's binary protocol) — one prepare, many executes. And the IN-list shape needs care: a variable-length IN (%s, %s, %s) builds its placeholders programmatically (",".join(["%s"] * len(ids))) with values still bound — never interpolated into the SQL text.

23.7 CRUD operations

The four data verbs as a complete repository — the pattern every ORM wraps and every application needs beneath one (Chapter 24's honesty: ORM for the 90%, repository SQL for the rest):

• Create — INSERT with the column list (Chapter 10); retrieve generated keys: JDBC Statement.RETURN_GENERATED_KEYS + getGeneratedKeys(), Python's RETURNING clause (PostgreSQL: cur.execute("INSERT ... RETURNING student_id", ...) — the row comes back), PHP PDO lastInsertId() (MySQL's LAST_INSERT_ID()).
• Read — parameterized SELECT by key or predicate; the find_section_students shape above; one query, not N (the join does the work — Section 23.11's N+1).
• Update — UPDATE ... WHERE the key, with a rowcount check: JDBC executeUpdate() returns the count, Python cur.rowcount, PDO rowCount() — a zero count means the row vanished or changed underneath you, which is either a user message or an optimistic-concurrency signal (Chapter 18 again, at application scale).
• Delete — by key, verified the same way, and soft deletes (an active flag) preferred wherever audit matters — Chapter 6's referential-action judgments, replayed at the API layer.

23.8 Connection pooling

Chapter 4 priced connections (a process or thread, memory, authentication); Chapter 18 added the transaction cost (locks and snapshots live as long as the connection's transactions). A pool answers: N long-lived connections, checked out per request and returned — the application pays authentication once per connection, not once per request.

The pooling layers, by ecosystem: application pools — Java's HikariCP (the Spring default; a bounded pool, a timeout, and a leak detector), Python's SQLAlchemy pool (pool_size, pool_pre_ping) — and server-side poolers for connection churn across many application instances: PostgreSQL's PgBouncer (transaction-level pooling multiplexing thousands of clients onto tens of servers), MySQL's ProxySQL. The configuration judgment: pool size is small (each connection is a server-side process/thread — Chapter 17's table — and 10–20 per instance outperforms 200), and check out late, return early: the request borrows only for its queries, never across user think-time — the short-transaction rule of Chapter 18, enforced at the tier.

23.9 Transaction management in applications

The application owns the transaction boundary — Chapter 18's rules, written in code:

def transfer_section(conn, student_id: int, from_sec: int, to_sec: int) -> None:
    for attempt in range(3):                          # retry loop
        try:
            with conn.transaction():                  # BEGIN ... COMMIT/ROLLBACK
                with conn.cursor() as cur:
                    cur.execute("CALL withdraw_student(%s, %s)",
                                (student_id, from_sec))
                    cur.execute("CALL enroll_student(%s, %s)",
                                (student_id, to_sec))
            return                                    # success
        except psycopg.errors.SerializationFailure:
            continue                                  # 40001: retry whole txn

The three disciplines in one function: the unit is logical (withdraw + enroll — both or neither, the Chapter 18 boundary rule); the retry loop catches 40001/40P01 and re-executes the whole transaction (Chapters 18 and 22 promised this pattern — here it is), with backoff and a cap in production; and the context manager owns commit/rollback — application code never calls rollback on the happy path and never forgets it on the sad one. The Java spelling is setAutoCommit(false) / commit() / catch-and-retry; PHP's PDO wraps beginTransaction() / commit() / rollBack() — and SERIALIZABLE isolation is set per transaction (conn.isolation_level / SET TRANSACTION ISOLATION LEVEL) when the invariant justifies its cost (Chapter 18's choosing rule).

23.10 Error handling and database exceptions

Driver exceptions are a class hierarchy over SQLSTATE — catch by class, not by message: JDBC's SQLException carries getSQLState() (compare "23505", "40001"); Python's drivers expose typed errors (psycopg.errors.UniqueViolation, .DeadlockDetected, .SerializationFailure — catch these, let others bubble); PHP PDO's PDOException carries errorInfo with the SQLSTATE. The handling policy, in dispatch order:

• Retryable (40001 serialization, 40P01 deadlock): backoff and re-execute the whole transaction — never a partial replay.
• Expected business failures (23505 duplicate — "already enrolled"; 23503 foreign key — "that section/student no longer exists"; Chapter 22's 45000 signals like "section is full"): translate to user-facing messages, mapped at the repository boundary, never shown raw (SQL fragments and constraint names are not user interface).
• Everything else: log the exception with its SQLSTATE and correlation id, fail the request cleanly, and page a human — swallowing a database error converts an incident into a data-integrity problem.

One implementation note per language: JDBC exceptions are checked — decide (handle or declare), never catch (Exception e) {} (the empty catch block, database edition); Python's with-blocks have already rolled back by the time you catch; PDO's exception carries the message and the driver code — log both.

23.11 Application security and input validation

Chapter 20 built the database side; the application closes the loop. Parameterization is already done — Sections 23.3–23.6 enforce it structurally — and the remaining habits: validate input at the boundary (types, ranges, formats — for user experience and cheap rejection; validation is not injection defense, which parameters provide — both layers, different jobs); allowlist identifiers (the sortable-column case: if sort_col not in {"full_name", "gpa"}: reject); secrets from the environment/secret manager (never code, never config files in git — the DATABASE_URL pattern of Section 23.2); least-privilege account (the portal account can only do what the application does — an injection that slips through loses only what the grants allow — Chapter 20's blast-radius rule, now applied); TLS in the connection string (sslmode=verify-full); error messages shaped by the mapping of Section 23.10 (no SQL, no stack traces, no constraint names to the browser); and the ORM caveat of Chapter 24: ORMs parameterize by default but their escape hatches (raw query methods, string-built order_by) reintroduce every risk — treat them as SQL and apply the same law.

The closing observation: every security item above is a placement of something already taught — parameters (Chapter 20), secrets and TLS (Chapter 20), grants (Chapter 20), messages (Section 23.10). Application security is not a new topic; it is the same topic, enforced at a new tier — which is exactly why it works.

Chapter Summary

• The application tier translates actions to SQL/procedure calls, manages connections and transactions, and shapes rows for users; repositories own SQL; connections are acquired late and released early.
• Connection URLs carry the five facts plus policy (TLS, credentials from the environment — never code or git).
• JDBC: try-with-resources, CallableStatement to Chapter 22's procedures, setAutoCommit for transactions; Python DB-API: context managers with commit/rollback built in, typed errors, tuple parameters; PHP PDO: named parameters, exception mode, prepare/execute for all input.
• Parameters bind values; identifiers use allowlists; IN-lists build placeholders programmatically.
• CRUD: column-listed inserts with generated-key retrieval; joined reads (not N+1); rowcount-checked updates and deletes; soft deletes where audit matters.
• Pools are small, bounded, and mandatory at scale (HikariCP, SQLAlchemy; PgBouncer/ProxySQL server-side); check out late, return early — the short-transaction rule at tier level.
• Transactions: the application owns the boundary; retry loops catch 40001/40P01 and re-execute the whole unit; context managers make rollback-on-exception the default.
• Errors: a class hierarchy over SQLSTATE — retryable, business (translated at the repository), and everything else (logged, correlated, paged); never empty-catch, never raw SQL to users.
• Application security is Chapter 20's layers at the new tier: parameters, allowlists, secrets, least privilege, TLS, shaped messages — plus validation as a separate UX layer.

Key Terms

Laboratory Exercises

1. The same program, three times: connect from Java, Python, and PHP to your university database with credentials from the environment; print the six-count verification; call enroll_student(21600001, 12) in university_dev via each language and verify the enrollment after each. Expected results: counts 5, 6, 12, 10, 13, 28 printed by all three; enrollment 28 → 29 after each language's call (re-seed between runs).
2. Parameterization audit: write the vulnerable and the safe find-student in one script per language (concatenated vs prepared); run both with the Chapter 20 payload ' OR '1'='1; paste outputs. Expected results: the vulnerable form leaks 12 rows, the prepared form returns zero — in every language.
3. Repository build: implement StudentRepository in your chosen language with find-by-id, find-by-section (joined), insert (with generated key returned), update-gpa (rowcount-checked), and soft-delete; demonstrate each with expected outputs. Expected results: five operations, five verifications — including a zero-rowcount update against a deleted id.
4. The retry loop, exercised: run two concurrent transfer transactions at SERIALIZABLE against the same student in dev; catch 40001, retry, and record the outcome and attempt counts. Expected result: at least one observable serialization failure and a successful retry within the cap — Chapter 18's promise, executed by your code.
5. Error mapping: in your repository, insert a duplicate enrollment (23505), enroll a nonexistent student (23503 or the procedure's refusal), and trigger the full-section 45000; map each to a user message and log the SQLSTATE. Expected results: three caught errors, three clean messages, three log lines with codes — no SQL or constraint names surfaced.
6. Pool observation: run 100 sequential requests through a small pool (size 5) in your language's pool (or a loop with reused connections); measure against 100 open/close cycles, and record server-side session counts (pg_stat_activity / Threads_connected) during the run. Expected results: pooled run measurably faster; server sees ≤ 5 sessions during the pooled run versus 100 churn events unpooled.

Review Questions and Exercises

1. Name the application tier's five jobs and the one job it must not do. Translate actions to SQL/calls; manage connections/transactions; shape rows; enforce interaction rules; handle failures — not enforce data rules (constraints/triggers/procedures own those).
2. Write the PostgreSQL JDBC URL for host db.example.edu, database university, with TLS verification required. *jdbc:postgresql://db.example.edu:5432/university?sslmode=verify-full (credentials supplied separately — from the environment).*
3. Why is Python's % string formatting of SQL values wrong twice over? It defeats parameterization (injection risk) and mis-handles quoting/types — the driver's parameter binding exists precisely to do both correctly.
4. How does the with conn.transaction() block implement Chapter 18's boundary rules? BEGIN on entry, COMMIT on success, ROLLBACK on any exception — the logical unit is enforced structurally, not by hand.
5. An update returns rowcount 0. Give both meanings and the two appropriate responses. The row vanished, or a concurrent change moved it; respond with a user-facing "no longer exists/changed" or treat as an optimistic-concurrency signal — never silently ignore.
6. Why are pool sizes small (10–20), not large (100+)? Each connection is a server process/thread (Chapter 17) — the database, not the pool, is the scarce resource; queues at a small pool preserve the server where a large pool chokes it.
7. Which SQLSTATEs does a retry loop catch, and what does it re-execute? 40001 (serialization) and 40P01 (deadlock); the whole transaction, from its BEGIN — never a partial replay.
8. Map three business errors to user messages: 23505, 23503, and 45000 "section is full." Duplicate key → "You are already enrolled in this section"; foreign-key violation → "That selection is no longer available"; the custom signal → "This section is full — please choose another."
9. Why must identifiers come from allowlists while values come from parameters? Parameters are values, not grammar — an identifier must be part of the statement structure, so it can only come from your own fixed set.
10. What does PDO's ERRMODE_EXCEPTION prevent, and what replaces it? Silent false-returns from failed queries — exceptions replace them, making failures catchable (and logging real) instead of invisible.
11. Your report page sorts by a column name from the query string. Write the two-line defense. *Check sort_col in {"full_name", "gpa", "admission_year"} (allowlist) — reject otherwise; the value then goes into SQL only via the allowed branches, parameters for everything else.*
12. Where does the N+1 problem come from, and what is the one-query fix? *A loop (or lazy-loading ORM) issuing one query per row; one join (or one IN-list query) fetches the set — the repository's find_section_students is the shape.*

Mini-Project

Build the enrollment service — the middle tier as a runnable program in your strongest language (and a second one if you can): (1) configuration from the environment (URL with TLS, pool size) validated at startup with the Chapter 14 ladder mapped to startup errors; (2) a pooled connection layer with a small bounded pool; (3) StudentRepository and SectionRepository — all SQL parameterized, keys and joins documented, generated-key retrieval, rowcount checks; (4) EnrollmentService calling Chapter 22's enroll_student/withdraw_student inside a transaction manager with the retry loop (40001/40P01, backoff, capped); (5) the error mapper: retryables, business errors to user messages, the rest logged with correlation ids; (6) a CLI or test harness exercising five scenarios — list section 12 (Arif and Shahriar, in progress), enroll Zara, duplicate enrollment, full section (capacity pinned low in dev), and a concurrent transfer pair — each with expected output; (7) a short SECURITY.md addendum: what the service does under injection (parameters), what the portal account can do (grants), and where its secrets live. This service is Chapter 24's backend — the next chapter puts the web and API in front of it.