Database Programming and Application Integration

Introduction

A database management system (DBMS) becomes valuable when it supports reliable applications rather than merely storing data. Database programming connects application logic—written in languages such as Java, Python, C#, or JavaScript—to relational data and database-resident logic. It includes stored procedures, functions, triggers, cursors, prepared statements, transactions, APIs, object–relational mapping (ORM), connection pooling, and security controls.

The central design question is not simply where code can execute, but where a rule should execute. A business rule may belong in the user interface, application service, database, or more than one layer. Good design balances consistency, performance, maintainability, security, testability, and operational needs.

This chapter uses SQL concepts that are broadly applicable to systems such as PostgreSQL, Oracle, SQL Server, and MySQL. Exact syntax differs among products; production code must therefore be checked against the selected DBMS documentation.

Learning Objectives

After studying this chapter, you should be able to:

• explain the purpose of database-programming constructs;
• write and evaluate stored procedures, functions, triggers, and cursor-based logic;
• use prepared statements and parameterized APIs securely;
• design transactions with appropriate isolation and error handling;
• explain ORM, connection pooling, and database APIs;
• decide where business rules should be implemented;
• analyze an integrated database application and identify reliability, security, and governance risks.

1. Database Applications as Layered Systems

A typical application has four cooperating layers:

1. Presentation layer: user interfaces, reports, or external clients.

2. Application or service layer: workflows, authorization, validation, and orchestration.

3. Data-access layer: APIs, repositories, query builders, and ORM mappings.

4. Database layer: tables, constraints, indexes, views, stored programs, triggers, and transaction management.

An API is a defined interface through which software requests services. A database driver implements a database connectivity API, such as JDBC for Java or DB-API-style interfaces for Python. A request normally follows this path: client request → authentication and service logic → parameterized database operation → transaction commit or rollback → response.

Layering improves separation of concerns, but it does not eliminate design choices. For example, a foreign-key constraint must be enforced by the DBMS because every client must obey it. A complex pricing policy may be easier to test in an application service. A short, highly reused calculation may be implemented as a database function or application function depending on reporting and consistency requirements.

2. Stored Procedures and Functions

A stored procedure is a named program stored and executed by the DBMS. It can accept input parameters, modify data, return result sets, and signal errors. A stored function returns a value and can often be used in expressions, queries, or computed columns, subject to DBMS-specific restrictions.

A simplified procedure illustrates the idea:

```sql

CREATE PROCEDURE transfer_funds(

IN p_from_account INTEGER,

IN p_to_account INTEGER,

IN p_amount DECIMAL(12,2)

)

BEGIN

IF p_amount <= 0 THEN

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Amount must be positive';

END IF;

UPDATE Account

SET balance = balance - p_amount

WHERE account_id = p_from_account AND balance >= p_amount;

IF ROW_COUNT() <> 1 THEN

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds or invalid account';

END IF;

UPDATE Account

SET balance = balance + p_amount

WHERE account_id = p_to_account;

END;

```

The example is illustrative: transaction syntax, error signaling, and `ROW_COUNT()` vary by DBMS. A production procedure should also verify that the destination account exists and should execute both updates within a transaction.

Functions are suitable for deterministic or reusable calculations. For example, a function might calculate a tax amount from a subtotal. However, functions that read or modify tables may have restrictions and performance consequences. Calling a function once per row in a large query can create significant overhead.

Advantages and limitations

Stored programs can reduce network round trips, centralize shared rules, improve controlled access, and exploit database transactions. They may also be difficult to version, test, debug, and migrate when code becomes vendor-specific. Excessive procedural logic in the database can produce a “database monolith,” in which application behavior is distributed across triggers, procedures, and services with unclear ownership.

Best practice is to keep stored programs cohesive, document side effects, use explicit naming, version them with source control, and test them with representative data.

3. Triggers and Automated Integrity

A trigger is database code automatically activated by an event such as `INSERT`, `UPDATE`, or `DELETE`. It may execute before or after the event, depending on DBMS support.

Common uses include:

• recording an audit trail;
• maintaining derived or summary data;
• enforcing rules that cannot be expressed by a simple constraint;
• preventing invalid state transitions.

An audit trigger might record the old and new values of a salary change. However, triggers should not silently perform surprising business actions. A trigger that inserts into several unrelated tables can make transaction behavior difficult to understand and can cause recursive or cascading effects.

Use declarative constraints first. `NOT NULL`, `UNIQUE`, `CHECK`, primary-key, and foreign-key constraints are visible, optimizable, and easier to reason about. Use a trigger when the requirement genuinely depends on an event or on database-wide enforcement. Every trigger should have documented timing, firing conditions, side effects, and failure behavior.

4. Cursors and Set-Based Processing

A cursor provides controlled, sequential access to rows returned by a query. It commonly involves declaring a query, opening the cursor, fetching rows, processing each row, and closing the cursor.

```sql

DECLARE done BOOLEAN DEFAULT FALSE;

DECLARE v_id INTEGER;

DECLARE v_amount DECIMAL(12,2);

DECLARE cur CURSOR FOR SELECT order_id, amount FROM PendingOrder;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;

read_loop: LOOP

FETCH cur INTO v_id, v_amount;

IF done THEN LEAVE read_loop; END IF;

-- process the current order

END LOOP;

CLOSE cur;

```

Syntax differs across systems. Cursors are useful when each row requires procedural decisions, external calls are prohibited inside a single set operation, or a streaming result is needed. Nevertheless, row-by-row processing is often slower than set-based SQL because it increases interpretation and context-switching overhead.

For example, instead of looping through orders to apply a ten-percent discount, use an appropriately constrained statement:

```sql

UPDATE OrderLine

SET unit_price = unit_price * 0.90

WHERE promotion_id = :promotion_id;

```

The arithmetic preserves the currency unit because the multiplier is dimensionless. Rounding and currency precision must be handled according to the organization’s accounting policy. A cursor should be a deliberate choice, not the default programming style.

5. Prepared Statements and Secure Data Access

A prepared statement separates SQL structure from data values. The SQL is parsed with placeholders, and values are bound separately:

```python

sql = "SELECT user_id, role FROM AppUser WHERE email = ?"

row = connection.execute(sql, (email,)).fetchone()

```

Placeholder syntax varies. The essential property is that `email` is bound as a value rather than concatenated into SQL text. Unsafe code looks like:

```python

sql = "SELECT * FROM AppUser WHERE email = '" + email + "'"

```

If input contains SQL syntax, concatenation can alter the intended query. This is SQL injection. Parameterization is the primary defense for values. Identifiers such as table names, column names, and sort directions generally cannot be bound as ordinary values; they must be selected from a strict allow-list and safely quoted according to the DBMS.

Prepared statements can also improve performance when the same statement is executed repeatedly, although caching behavior is DBMS- and driver-dependent. Other defenses include least-privilege accounts, input validation, secure secrets management, encrypted connections, controlled error messages, and logging that avoids passwords or sensitive personal data.

6. Transactions, Concurrency, and Recovery

A transaction is a logical unit of work governed by ACID properties:

• Atomicity: all intended changes occur, or none do.
• Consistency: declared integrity rules remain satisfied after commit.
• Isolation: concurrent transactions are controlled so that intermediate states are not improperly observed.
• Durability: committed changes survive specified failures.

A basic application pattern is:

```python

try:

connection.begin()

debit_account(connection, source, amount)

credit_account(connection, destination, amount)

connection.commit()

except Exception:

connection.rollback()

raise

```

The application should not assume that a successful first statement means the whole operation succeeded. Commit is the point at which the transaction becomes durable according to the DBMS configuration.

Isolation levels represent different concurrency guarantees. In general, stronger isolation reduces some anomalies but may increase locking, waiting, or aborts. Typical anomalies include dirty reads (observing uncommitted data), non-repeatable reads (the same row changes between reads), and phantoms (a repeated predicate returns a different set of rows). Exact behavior depends on the DBMS’s locking or multiversion implementation.

Transactions should be short, explicit, and limited to required work. Avoid waiting for user input while a transaction is open. Handle deadlocks and serialization failures with bounded retry logic when the operation is safe to repeat. Idempotency—designing repeated requests to have one intended effect—is especially important for payment and message-processing systems.

Worked example: inventory reservation

Suppose 3 units are available and two transactions each attempt to reserve 2 units. If both read availability before either update is protected, both may conclude that the reservation is possible, creating an oversell. A safer design uses a transaction and an atomic conditional update:

```sql

UPDATE Product

SET available_units = available_units - :requested

WHERE product_id = :id

AND available_units >= :requested;

```

The application checks that exactly one row was updated before inserting the reservation and committing. The database evaluates the update as a concurrency-controlled operation. If zero rows are affected, the product is unavailable or the identifier is invalid. This design is preferable to an unprotected read followed by a separate update.

7. Database APIs, ORM, and the Object–Relational Mismatch

A low-level database API exposes connections, statements, transactions, and result sets. It gives precise SQL control and usually makes performance characteristics visible. A repository or query-builder layer can standardize access while retaining SQL where needed.

An object–relational mapper (ORM) maps program objects to relational tables. It commonly provides identity tracking, relationship navigation, change detection, and migration support. ORM advantages include reduced repetitive CRUD code, consistent parameterization, and integration with application models. Risks include:

• the N+1 query problem, in which one query loads parent rows and one additional query runs for each parent;
• inefficient generated SQL;
• accidental loading of large relationships;
• ambiguity about transaction boundaries;
• difficulty expressing specialized analytical queries.

Developers should inspect generated SQL, use eager loading or explicit joins where appropriate, paginate large results, select only needed columns, and retain direct SQL for complex or performance-critical operations. ORM abstraction is useful, but it does not remove the need to understand relational algebra, indexes, constraints, and transactions.

8. Connection Pooling and Resource Management

Opening a database connection is relatively expensive and database servers can support only a finite number of concurrent sessions. A connection pool maintains reusable connections. An application borrows a connection, performs a short operation, commits or rolls back, and returns the connection to the pool.

Pool configuration involves maximum size, minimum idle connections, acquisition timeout, idle timeout, and lifetime. A large pool does not automatically improve throughput; it may overload the database and increase lock contention. A small pool may cause application threads to wait. Capacity should be measured under realistic workloads.

Connections returned to a pool must not retain an open transaction, session-specific settings, temporary objects, or unconsumed results. Pool monitoring should include active connections, wait time, timeout counts, transaction duration, and database saturation. In distributed deployments, the total possible connections across application instances—not the pool size of one instance—must be considered.

9. Where Should Business Rules Be Placed?

A business rule is a condition or policy governing valid operations. Placement can be evaluated using five questions:

1. Must every client obey it, including reporting tools and direct integrations?

2. Does it depend on user interaction or external services?

3. Is it naturally expressed as a declarative integrity condition?

4. Does it require a coordinated transaction over several tables?

5. How often will it change, and who owns its testing and release?

Use the database for universal integrity, such as keys, referential relationships, nonnegative quantities, and uniqueness. Use application services for workflow, authorization decisions, external calls, and policies that require rapid independent release. Use stored procedures when atomic data operations are shared across many clients or when reducing network round trips is important. Duplication may be justified for defense in depth, but duplicated rules must have a clear source of truth and automated consistency tests.

A useful architectural principle is: the closer a rule is to the data invariant, the stronger the case for database enforcement; the closer it is to user workflow, the stronger the case for service-layer enforcement. This is a guideline rather than an absolute law.

10. Integrated Case: Illustrative Online Pharmacy

Background

An online pharmacy stores customers, prescriptions, products, inventory, orders, and payments. It offers a web application and a partner API. The organization must prevent overselling, protect health information, and maintain an audit trail.

Problem

The initial system validates stock only in the web interface. Partner orders bypass that validation. SQL statements are built by string concatenation, and a connection is held while a customer confirms payment. Under concurrent demand, stock becomes negative; security testing finds injection vulnerabilities; and connections are exhausted.

Analysis

The stock invariant must apply to every client and therefore belongs in the database transaction. Payment authorization depends on an external provider and should not be held inside a long database transaction. Health information requires strict access controls, minimization, encryption in transit and at rest where appropriate, and auditable access.

Approach

The redesigned service uses parameterized statements or a carefully inspected ORM. A transaction performs an atomic conditional inventory update, inserts order lines, and records an order state such as `PENDING_PAYMENT`. It commits quickly. A separate payment workflow is idempotent and changes the state to `PAID` or `PAYMENT_FAILED`. A database constraint prevents negative inventory, while an audit trigger records changes to prescription status. A connection pool is sized through load testing, and every borrowed connection is rolled back or reset before return.

The partner API authenticates clients, authorizes access by role and tenant, validates request schemas, applies rate limits, and returns generic errors. Secrets are stored outside source code. Logs contain correlation identifiers rather than prescription contents or payment credentials.

Outcome

The design prevents the principal overselling race, removes the injection vulnerability, reduces connection exhaustion, and creates traceable state transitions. It also makes payment failure a visible workflow state rather than a partially committed database operation.

Learning points

The case demonstrates that database programming is an architectural activity. Constraints and transactions protect shared invariants; services coordinate human and external workflows; APIs enforce controlled access; and observability is necessary to verify behavior in production.

11. Challenges, Governance, and Contemporary Practice

Database applications face vendor lock-in, schema evolution, migration failures, hidden ORM queries, deadlocks, data leakage through logs, and inconsistent behavior across services. Automated tests should include unit tests for service logic, integration tests against the actual DBMS, migration tests, concurrency tests, and security tests for authorization and injection.

Schema changes should be backward-compatible when old and new application versions coexist. A common approach is expand-and-contract: add the new structure, deploy code that can use both versions, migrate data, switch reads and writes, and later remove obsolete structures.

Privacy and governance require data minimization, retention limits, access review, auditability, and lawful processing. Regulations vary by jurisdiction and industry; technical controls do not replace legal advice. AI applications add further concerns: training data may contain personal information, model-generated SQL must be parameterized and reviewed, and an AI agent should receive narrowly scoped database permissions. Natural-language-to-SQL systems should use read-only roles by default, query allow-lists where feasible, result-size limits, and human approval for destructive operations.

Best-Practice Checklist

• Define integrity constraints in the database.
• Parameterize every data value; never concatenate untrusted input into SQL.
• Keep transactions short and make failure handling explicit.
• Prefer set-based SQL over unnecessary cursors.
• Document and test trigger side effects.
• Inspect ORM-generated SQL and monitor query performance.
• Use least-privilege accounts and protect secrets.
• Configure and measure connection pools rather than guessing their size.
• Make externally retried operations idempotent.
• Version schema and stored-program changes with application code.
• Audit sensitive operations while minimizing sensitive log content.

Chapter Summary

Database programming integrates application code with DBMS capabilities. Stored procedures and functions centralize reusable operations, while triggers automate event-based enforcement and auditing. Cursors support controlled row processing but should not replace efficient set-based operations. Prepared statements separate SQL structure from values and are fundamental to preventing SQL injection.

Transactions provide atomic, consistent, isolated, and durable units of work. Correct concurrency design often requires atomic conditional updates, suitable isolation, short transaction duration, and retry or idempotency strategies. APIs and ORMs simplify access but must be evaluated through their generated SQL and transaction behavior. Connection pools improve reuse but require careful sizing and cleanup.

Business rules should be placed according to their relationship to data invariants, workflows, ownership, change frequency, and security. Database constraints protect universal truths; application services coordinate workflows and external systems. Secure, governed database applications combine these layers rather than relying on any single mechanism.

Key Terms

Stored procedure: Named database program that can execute operations and produce outputs.

Stored function: Database program that returns a value and may be used in expressions.

Trigger: Program automatically activated by a database event.

Cursor: Mechanism for sequentially fetching query rows.

Prepared statement: SQL statement whose values are supplied separately through parameters.

SQL injection: Attack that alters SQL through unsafe input concatenation.

Transaction: Logical unit of database work.

Atomicity: Transaction property requiring all-or-nothing effect.

Consistency: Preservation of defined integrity rules.

Isolation: Control of interactions among concurrent transactions.

Durability: Persistence of committed changes after failure.

Deadlock: Cyclic waiting among transactions for locked resources.

API: Defined interface through which software requests services.

ORM: Software mapping objects to relational data.

N+1 query problem: Inefficient pattern involving one query plus one query per returned parent.

Connection pool: Managed collection of reusable database connections.

Idempotency: Property that repeated execution has one intended effect.

Least privilege: Granting only the permissions required for a task.

Business rule: Policy or condition governing valid business operations.

Review Questions

Short Answer Questions

1. What is the difference between a stored procedure and a stored function?

2. Why are prepared statements important for database security?

3. Name and briefly explain the four ACID properties.

4. What is the N+1 query problem?

5. Why must pooled connections be reset before reuse?

Long Answer Questions

1. Compare the advantages and limitations of stored programs, application services, and database constraints.

2. Explain how transaction isolation affects concurrent database operations and describe common anomalies.

3. Discuss ORM benefits and risks, including strategies for detecting inefficient generated SQL.

4. Explain how connection pooling should be configured and monitored in a multi-instance application.

5. Design a secure database access architecture for an application handling sensitive personal data.

Analytical / Application Questions

1. Two users attempt to reserve the final unit of a product simultaneously. Propose SQL and transaction behavior that prevents overselling, and explain why it works.

2. An organization has the same discount rule in a mobile app, web service, and reporting procedure. Analyze the risks of duplication and recommend a source-of-truth strategy.

3. A payment service may retry requests after a timeout. Design an idempotent order workflow and identify which operations should occur inside and outside the database transaction.

Multiple Choice Questions with Answers

Question 1

Which mechanism separates SQL structure from supplied data values?

A. A cursor

B. A prepared statement

C. A trigger

D. A connection pool

Correct Answer: B. A prepared statement

Question 2

Which property ensures that a transaction’s changes are all applied or none are applied?

A. Isolation

B. Durability

C. Atomicity

D. Consistency

Correct Answer: C. Atomicity

Question 3

What is the preferred first choice for enforcing a simple universal rule such as a nonnegative quantity?

A. A database constraint

B. A user-interface warning

C. A cursor

D. A report query

Correct Answer: A. A database constraint

Question 4

Why can row-by-row cursor processing be less efficient than set-based SQL?

A. Cursors always disable indexes

B. It can create repeated processing and context-switching overhead

C. Cursors cannot read relational tables

D. Set-based SQL cannot modify data

Correct Answer: B. It can create repeated processing and context-switching overhead

Question 5

Which practice directly prevents ordinary user input from changing SQL syntax?

A. String concatenation

B. Parameter binding

C. Increasing pool size

D. Adding a database trigger

Correct Answer: B. Parameter binding

Question 6

What is a common risk of an ORM?

A. It makes transactions impossible

B. It can generate an N+1 query pattern

C. It cannot represent any relationships

D. It automatically grants administrator privileges

Correct Answer: B. It can generate an N+1 query pattern

Question 7

What should normally happen when an application operation fails after beginning a transaction?

A. The connection should be returned without action

B. The transaction should be rolled back

C. All connections should be closed permanently

D. The error should be hidden

Correct Answer: B. The transaction should be rolled back

Question 8

Which operation is most suitable for an audit trigger?

A. Recording a changed salary with old and new values

B. Displaying a web page

C. Calling a customer by telephone

D. Choosing a browser color

Correct Answer: A. Recording a changed salary with old and new values

Question 9

What is the main purpose of a connection pool?

A. To store database backups

B. To reuse managed database connections

C. To replace authorization

D. To encrypt every column automatically

Correct Answer: B. To reuse managed database connections

Question 10

Which rule is generally strongest when placed in the database?

A. A universal foreign-key relationship

B. A temporary screen-layout preference

C. A customer email template

D. A third-party payment-provider policy

Correct Answer: A. A universal foreign-key relationship