Database Programming and Reusable Logic

Introduction

A relational database is more than a collection of tables. It can also provide reusable, enforceable, and centrally managed logic for presenting data, validating changes, automating derived values, and coordinating recurring work. Database programming refers to the use of views, routines, triggers, generated columns, scheduled execution, and error-handling mechanisms within or adjacent to a database system.

This capability is valuable in full-stack development because the database is often shared by several applications, services, reporting tools, and administrative processes. Logic implemented only in one application can be bypassed by another client. Conversely, placing every rule in the database can make systems difficult to test, migrate, and evolve. Good design therefore treats business logic placement as an architectural decision rather than a matter of personal preference.

This chapter compares practical techniques in MySQL and PostgreSQL and evaluates them according to correctness, portability, maintainability, performance, testing, and governance.

Learning Objectives

By the end of this chapter, you should be able to:

• distinguish views, materialized views, functions, procedures, triggers, generated columns, and scheduled jobs;
• design reusable database logic in MySQL and PostgreSQL;
• explain transaction and exception-handling behavior;
• select an appropriate location for a business rule;
• identify portability and maintainability risks;
• test database logic systematically; and
• evaluate security, auditing, and operational implications.

1. A Model for Locating Logic

A useful design question is: What must always be true, and who must enforce it? Structural rules such as uniqueness and referential integrity belong in constraints whenever possible. Rules that transform a row into a deterministic value may belong in generated columns. Reusable read logic may be expressed as a view. Cross-row workflows, external communication, and complex orchestration often belong in application services or job workers.

The following dimensions help evaluate a candidate rule:

A rule such as “an email address must be unique” is best represented with a unique constraint or index. “Display a customer’s full name consistently” is a good view or generated expression. “Send a payment-provider request” should not normally be performed by a database trigger because network calls create latency, retry, and transaction-boundary problems.

2. Views: Reusable Virtual Queries

A view is a named query whose result is computed when it is read. It normally stores the query definition, not a separate copy of the result.

CREATE VIEW active_customer_orders AS
SELECT
    c.customer_id,
    c.email,
    o.order_id,
    o.order_date,
    o.total_amount
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.status = 'PAID';

Applications can query the view as if it were a table:

SELECT *
FROM active_customer_orders
WHERE customer_id = 42;

Views provide abstraction, reuse, and controlled exposure. A view can hide implementation details, expose only approved columns, and give reporting users a stable interface while base tables evolve. It can also simplify row-level filtering, although security-sensitive filtering requires careful authorization design.

A view is not automatically a performance solution. The optimizer may merge or rewrite a view, but a complex view can still produce an expensive query. Indexes are created on base tables in ordinary views. In PostgreSQL, some views can be updated automatically when they meet defined conditions; otherwise an INSTEAD OF trigger may implement write behavior. MySQL supports updatable views subject to restrictions involving joins, aggregation, DISTINCT, grouping, and other query features.

Views should be designed with explicit column lists and stable semantics. Avoid SELECT *, because adding a base-table column can unexpectedly change the view interface. Treat important views as versioned database APIs.

3. Materialized Views and Cached Results

A materialized view stores the result of a query physically. It is useful when a query is expensive and slightly stale data is acceptable.

PostgreSQL provides native materialized views:

CREATE MATERIALIZED VIEW monthly_sales AS
SELECT
    date_trunc('month', order_date) AS sales_month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue
FROM orders
WHERE status = 'PAID'
GROUP BY date_trunc('month', order_date);

REFRESH MATERIALIZED VIEW monthly_sales;

A unique index may allow concurrent refresh in PostgreSQL, provided the required uniqueness conditions are met:

CREATE UNIQUE INDEX monthly_sales_month_idx
ON monthly_sales (sales_month);

REFRESH MATERIALIZED VIEW CONCURRENTLY reduces blocking of readers but has operational and indexing requirements. Refreshing is not automatic unless a scheduler, extension, or external job invokes it.

MySQL does not provide a general native CREATE MATERIALIZED VIEW feature. The usual substitute is a summary table populated by a scheduled event, application job, or data pipeline. That approach requires explicit decisions about refresh frequency, incremental updates, locking, and failure recovery.

Materialization introduces a freshness model. If a report is refreshed every 15 minutes, its maximum intended age is approximately 15 minutes, but actual age can be greater when a refresh fails or is delayed. Document the freshness guarantee rather than implying that cached data is current.

4. Functions and Stored Procedures

A stored function returns a value and can often be used inside SQL expressions. A stored procedure represents an invoked operation and may return result sets or output parameters depending on the database system.

PostgreSQL commonly uses PL/pgSQL functions:

CREATE OR REPLACE FUNCTION order_total(p_order_id integer)
RETURNS numeric(12,2)
LANGUAGE plpgsql
STABLE
AS $$
DECLARE
    v_total numeric(12,2);
BEGIN
    SELECT COALESCE(SUM(quantity * unit_price), 0.00)
    INTO v_total
    FROM order_items
    WHERE order_id = p_order_id;

    RETURN v_total;
END;
$$;

MySQL uses a different syntax and delimiter convention:

DELIMITER //

CREATE FUNCTION order_total(p_order_id INT)
RETURNS DECIMAL(12,2)
READS SQL DATA
BEGIN
    DECLARE v_total DECIMAL(12,2);

    SELECT COALESCE(SUM(quantity * unit_price), 0.00)
    INTO v_total
    FROM order_items
    WHERE order_id = p_order_id;

    RETURN v_total;
END//

DELIMITER ;

The examples are conceptually similar but not directly portable. PostgreSQL function volatility categories—IMMUTABLE, STABLE, and VOLATILE—communicate whether a function can change its result during a statement or transaction. MySQL uses characteristics such as DETERMINISTIC, READS SQL DATA, and MODIFIES SQL DATA; these declarations affect documentation and some server behavior but should never be used dishonestly.

Procedures are appropriate for database-local operations such as controlled batch updates, administrative workflows, or multi-step operations that must be executed consistently. PostgreSQL procedures are invoked with CALL and can have transaction-control capabilities in particular contexts. MySQL procedures are also invoked with CALL. Exact transaction behavior and restrictions differ, so routine documentation should state assumptions explicitly.

A routine should have a narrow contract: clear parameters, defined return values, documented errors, and predictable side effects. Avoid routines that silently modify unrelated tables or depend on session variables.

5. Triggers: Automatic Reactions to Data Changes

A trigger executes automatically when an event occurs. Typical events are INSERT, UPDATE, and DELETE. Triggers may run before or after an event, and PostgreSQL also supports statement-level triggers and transition tables in suitable cases.

A common use is maintaining an audit record:

CREATE TABLE order_audit (
    audit_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id bigint NOT NULL,
    old_status text,
    new_status text,
    changed_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
);

In PostgreSQL, an audit trigger might be written as follows:

CREATE OR REPLACE FUNCTION audit_order_status()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF OLD.status IS DISTINCT FROM NEW.status THEN
        INSERT INTO order_audit(order_id, old_status, new_status)
        VALUES (OLD.order_id, OLD.status, NEW.status);
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER order_status_audit
AFTER UPDATE OF status ON orders
FOR EACH ROW
EXECUTE FUNCTION audit_order_status();

MySQL requires a trigger for each relevant timing and event combination and uses OLD and NEW row references, but its syntax differs. MySQL does not support PostgreSQL’s statement-level trigger model in the same way.

Triggers are valuable for local invariants, audit trails, and automatic timestamps. They are risky when they hide substantial workflows. A trigger can cause writes to other tables that are invisible in application code, create recursive effects, increase latency, and complicate bulk loading. Triggers should be short, deterministic where possible, documented, and covered by integration tests.

Do not use a trigger to call an external HTTP service. If the transaction rolls back after a network request, the external side effect cannot automatically be rolled back. Instead, use a transactional outbox: write an event row in the same transaction, then let a worker publish it with retry and idempotency controls.

6. Generated Columns

A generated column derives its value from other columns. It is not normally assigned directly by application code.

A conceptual example is a line-item amount:

CREATE TABLE order_items (
    order_id bigint NOT NULL,
    product_id bigint NOT NULL,
    quantity integer NOT NULL CHECK (quantity > 0),
    unit_price numeric(12,2) NOT NULL CHECK (unit_price >= 0),
    line_total numeric(14,2)
        GENERATED ALWAYS AS (quantity * unit_price) STORED
);

The generated expression must obey database-specific restrictions. PostgreSQL supports stored generated columns in current releases, with expressions constrained to immutable behavior. MySQL supports generated columns as VIRTUAL or STORED; exact indexing and expression restrictions depend on the version.

Generated columns are useful for deterministic transformations, normalized search keys, and values that should be indexed consistently. They are preferable to duplicated application calculations because every writer obtains the same result. They are not suitable for values depending on unrelated rows, arbitrary queries, volatile functions, or external services.

A generated column is different from a default. A default supplies a value when a row is inserted and can often be overridden; a generated column is derived by the database according to its definition. It is also different from a trigger, which can execute procedural side effects.

7. Events and Scheduled Jobs

Recurring work includes refreshing summaries, deleting expired sessions, and processing deferred tasks. MySQL has an Event Scheduler:

CREATE EVENT purge_expired_sessions
ON SCHEDULE EVERY 1 HOUR
DO
  DELETE FROM sessions
  WHERE expires_at < CURRENT_TIMESTAMP;

The scheduler must be enabled and operationally monitored. PostgreSQL has no built-in scheduler equivalent to MySQL events. PostgreSQL deployments commonly use operating-system timers, container schedulers, orchestration platforms, or extensions such as pg_cron where approved.

A scheduled operation should be idempotent: running it twice should not corrupt data or duplicate effects. It should process bounded batches, record progress where necessary, expose failures, and avoid holding locks for excessive periods. Time zones must be explicit, especially for global systems. Prefer UTC storage for timestamps representing instants, while preserving the user’s time zone separately when local calendar meaning matters.

8. Exception Handling and Transaction Semantics

Exception handling determines what happens when a statement or routine fails. PostgreSQL PL/pgSQL supports exception blocks:

BEGIN
    INSERT INTO payments(order_id, amount)
    VALUES (p_order_id, p_amount);
EXCEPTION
    WHEN unique_violation THEN
        RAISE EXCEPTION 'Payment already recorded for order %', p_order_id;
END;

In MySQL, handlers are declared inside stored programs:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;

The handler action—CONTINUE or EXIT—must be chosen carefully. A handler that merely suppresses an error can leave the caller believing that an operation succeeded.

Transactions provide atomicity: either all required database changes commit, or the transaction rolls back. They do not automatically undo external effects. A database routine should define whether it owns the transaction or operates within a transaction controlled by the application. Mixing implicit commits, nested transaction assumptions, and long-running routines often causes portability and correctness problems.

Error handling should distinguish expected business rejection from infrastructure failure. For example, “inventory insufficient” may be a normal domain outcome, whereas a lost connection is an operational failure requiring retry or escalation. Never expose raw database diagnostics, table names, or stack traces to untrusted users. Map internal errors to safe application responses and log sufficient diagnostic context privately.

9. Testing Database Logic

Database logic requires more than unit tests in application code. A practical test strategy includes:

1. Definition tests: verify that routines, views, triggers, and generated columns are present with expected definitions.
2. Example-based tests: test ordinary valid inputs and expected outputs.
3. Boundary tests: use zero, minimum, maximum, duplicate, null, and invalid values.
4. Transaction tests: verify commit, rollback, deadlock retry, and concurrent updates.
5. Migration tests: apply migrations to an empty database and upgrade a realistic existing database.
6. Performance tests: inspect execution plans and measure representative data volumes.
7. Compatibility tests: run shared SQL against every supported engine and version.

Tests should isolate data using transactions, disposable schemas, or containers. A test that passes only because it relies on another test’s rows is not reliable. For concurrency-sensitive behavior, use multiple sessions and deliberately coordinate their execution.

Property-based testing can be useful for invariants. For example, after any permitted order update, the stored total should equal the sum of valid line totals. Such tests evaluate a class of inputs rather than a few hand-selected examples.

10. Portability and Maintainability

SQL standards provide a foundation, but database programming is particularly vendor-specific. Differences include procedural languages, identity syntax, trigger semantics, exception names, materialized-view support, scheduling, locking behavior, and type systems.

A portability strategy should:

• use standard SQL for core queries where practical;
• isolate vendor-specific routines in clearly named migration files;
• avoid pretending that similar syntax has identical semantics;
• document supported versions and extensions;
• maintain separate compatibility tests; and
• define an exit strategy if a vendor feature becomes unacceptable.

Portability is not always the highest value. PostgreSQL-specific features may provide excellent correctness and performance, while forcing all systems into the lowest common denominator can reduce quality. The decision should consider the cost of migration against the benefit of the feature.

Maintainability improves when logic has one authoritative implementation, meaningful names, comments explaining why, explicit dependencies, and observability. Schema migrations should be reviewed like application code. Destructive changes require compatibility periods, backups, and rollback or roll-forward plans.

11. Applied Case: A Multi-Channel Order Platform

This is an illustrative case. A retailer receives orders through a web application, a mobile application, and partner APIs. Each channel previously calculated order totals independently. Differences in rounding and discount application caused inconsistent invoices. Reporting queries also joined large tables repeatedly, and managers needed near-current sales summaries.

Problem

The organization needed a single authoritative calculation, an audit trail for status changes, a fast reporting structure, and reliable publication of order events. It also wanted the option to support both PostgreSQL and MySQL in different deployments.

Analysis

The line total is deterministic from quantity and unit price, so it belongs in a generated column or a consistently constrained database expression. The complete order total includes discounts, tax policy, and potentially many rows; it is better implemented as a versioned database routine or application service with a clear contract. Status changes must be auditable for every client, so a trigger is appropriate for recording an audit row. Publishing to a message broker must not occur directly inside the trigger.

Solution

The team implemented the following design:

• CHECK constraints validated positive quantities and nonnegative prices.
• A generated line_total column removed duplicated arithmetic.
• A transaction inserted or updated the order and wrote an outbox event.
• A small trigger recorded old and new statuses in an audit table.
• PostgreSQL used a materialized view for hourly management reports; the MySQL deployment used a summary table refreshed by a scheduled worker.
• A worker read outbox rows, published events with an idempotency key, and marked successful delivery.
• Integration tests ran against both database engines, while vendor-specific migrations were kept separate.

Outcome

All order-entry channels obtained the same line calculations, and status history became available for investigation. Reports were faster because they read precomputed summaries, with freshness explicitly documented. The outbox avoided losing events when a transaction succeeded but a message publication failed. The team accepted some vendor-specific code because the portability boundary was documented and tested.

Learning Points

The case demonstrates that no single mechanism solves every problem. Constraints and generated columns enforce local correctness; routines coordinate reusable database operations; triggers capture local side effects; materialization supports read performance; and external workers handle integration and retries. Most importantly, the design separates transactional truth from asynchronous work.

12. Security, Governance, and Professional Practice

Database logic executes with database privileges, so least privilege is essential. Application roles should receive only the permissions required for their tasks. A reporting user should generally receive access to approved views rather than unrestricted base tables. Routine execution privileges, ownership, and SECURITY DEFINER-style features require careful review because unsafe search paths or dynamic SQL can create privilege-escalation vulnerabilities.

Dynamic SQL should use parameterization and safe identifier handling. Never concatenate untrusted values into SQL. Audit records should protect sensitive information and comply with applicable privacy requirements. Retention periods, deletion requests, legal holds, and access reviews should be designed before enabling extensive auditing.

Artificial intelligence tools can assist with routine scaffolding, migration drafts, and test generation, but generated SQL must be reviewed for locking behavior, privilege escalation, transaction semantics, and vendor compatibility. Do not paste production credentials or personal data into an external model. Human review remains responsible for correctness and governance.

Best-Practice Checklist

• Prefer constraints for rules that are structural and universally enforceable.
• Use views to expose stable read interfaces, not merely to hide poor queries.
• Document materialized-view freshness and refresh failure behavior.
• Keep routines narrow, deterministic where possible, and explicit about side effects.
• Use triggers for local, unavoidable reactions; avoid hidden workflows.
• Use generated columns for deterministic row-level derivations.
• Make scheduled work idempotent, observable, and bounded.
• Separate database transactions from external side effects with an outbox or job queue.
• Test definitions, boundaries, concurrency, migrations, and performance.
• Treat vendor-specific behavior as a documented architectural choice.

Chapter Summary

Database programming extends relational databases with reusable read models, stored computation, automatic reactions, derived values, and scheduled operations. Ordinary views provide named virtual queries, while materialized views and summary tables trade freshness for performance. Functions and procedures encapsulate reusable operations, but their syntax and transaction behavior differ between MySQL and PostgreSQL. Triggers are effective for local auditing and invariants but can obscure control flow and should not perform external network actions. Generated columns provide consistent deterministic derivations. Events and external schedulers support recurring work when designed for idempotency and monitoring. Exception handling must preserve meaningful failure behavior and respect transaction boundaries. Sound architecture places each rule where its consistency, portability, testability, and operational requirements can be satisfied.

Key Terms

View: A named query whose result is normally computed when read.

Materialized view: A physically stored query result that must be refreshed.

Stored function: A database routine that returns a value.

Stored procedure: An invoked database routine that performs an operation.

Trigger: An automatic action associated with a table or database event.

Generated column: A column whose value is derived from an expression.

Virtual generated column: A generated value computed when read, where supported.

Stored generated column: A generated value physically stored, where supported.

Volatility: The degree to which a function’s result can change during execution.

Exception handler: Logic that responds to an error condition.

Transaction: A unit of work committed entirely or rolled back.

Idempotency: The property that repeating an operation does not create an incorrect additional effect.

Transactional outbox: A table used to record events in the same transaction as business data.

Materialization: The process of storing a query result for later reading.

Portability: The ability to operate across database products or versions.

Migration: A versioned change to database schema or programmed objects.

Audit trail: A record of significant changes and their context.

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

Review Questions

Short Answer Questions

1. What is the principal difference between an ordinary view and a materialized view?
2. Give two appropriate uses for a database trigger.
3. Why are generated columns useful for deterministic calculations?
4. What is an idempotent scheduled job?
5. Why should a database trigger generally not call an external HTTP service?

Long Answer Questions

1. Compare functions and procedures in MySQL and PostgreSQL, including portability concerns.
2. Explain the advantages and risks of using triggers for audit logging.
3. Discuss how materialized views affect performance, freshness, and operational complexity.
4. Develop a testing strategy for a database routine that updates inventory.
5. Explain how transaction boundaries and exception handling influence database application design.

Analytical / Application Questions

1. A reporting query takes 40 seconds but may display data up to ten minutes old. Should the team use an ordinary view, a materialized view, or an application cache? Justify the choice and identify operational risks.
2. Design a solution for publishing an OrderPaid event after a successful transaction without losing the event or sending it twice incorrectly.
3. A company must support both MySQL and PostgreSQL. Decide which parts of an order-total system should use standard constraints, generated columns, routines, triggers, or application services, and explain the portability trade-offs.

Multiple Choice Questions with Answers

Question 1

What does an ordinary database view normally store?

A. A copy of every row returned by the query

B. The query definition used to derive its result

C. Only the indexes from its base tables

D. A scheduled execution plan

Correct Answer: B. The query definition used to derive its result

Question 2

Which feature is natively provided by PostgreSQL but not as a general native feature by MySQL?

A. Ordinary views

B. Row-level triggers

C. Materialized views

D. Stored functions

Correct Answer: C. Materialized views

Question 3

Which is the most appropriate use of a generated column?

A. Calling a payment provider

B. Computing a deterministic line total from quantity and price

C. Refreshing a report every hour

D. Sending an email after insertion

Correct Answer: B. Computing a deterministic line total from quantity and price

Question 4

Why is a direct external HTTP call from a database trigger discouraged?

A. Triggers cannot read any table

B. HTTP requests are always read-only

C. External effects cannot automatically be rolled back with the database transaction

D. Triggers execute only after the server shuts down

Correct Answer: C. External effects cannot automatically be rolled back with the database transaction

Question 5

What is the principal purpose of a transactional outbox?

A. To replace all database indexes

B. To record an event in the same transaction as business data before asynchronous publication

C. To encrypt every database column

D. To prevent all scheduled jobs

Correct Answer: B. To record an event in the same transaction as business data before asynchronous publication

Question 6

Which characteristic best describes an idempotent scheduled job?

A. It must run exactly once or fail permanently

B. Repeating it does not create an incorrect additional effect

C. It always requires a materialized view

D. It cannot modify data

Correct Answer: B. Repeating it does not create an incorrect additional effect

Question 7

In PostgreSQL, which construct commonly handles errors inside a PL/pgSQL routine?

A. An EXCEPTION block

B. A VIEW block

C. A MATERIALIZE block

D. An INDEX block

Correct Answer: A. An EXCEPTION block

Question 8

Which practice improves portability between MySQL and PostgreSQL?

A. Assuming similarly named features have identical semantics

B. Using vendor-specific routines without documentation

C. Isolating vendor-specific migrations and testing each supported engine

D. Avoiding all constraints

Correct Answer: C. Isolating vendor-specific migrations and testing each supported engine

Question 9

Which database mechanism is generally best for enforcing uniqueness?

A. A user-interface warning only

B. A unique constraint or unique index

C. A report query

D. A scheduled event

Correct Answer: B. A unique constraint or unique index

Question 10

What is a major risk of a complex trigger-based workflow?

A. It makes all queries syntactically invalid

B. It can hide side effects and complicate testing and debugging

C. It prevents tables from having primary keys

D. It always disables transactions

Correct Answer: B. It can hide side effects and complicate testing and debugging