Database Objects and Application Access

Introduction

A database is not merely a collection of tables. In a production information system, tables are surrounded by database objects that control access, encapsulate logic, enforce business rules, improve performance, and expose data to applications. Views, materialized views, stored procedures, user-defined functions, triggers, and parameterized queries are therefore central to professional SQL development.

Application access also involves technologies outside the database engine. APIs define how software services communicate; connectors provide drivers and protocols for database connections; and object-relational mapping (ORM) frameworks translate between relational tables and programming-language objects. These mechanisms improve productivity, but they also introduce risks involving security, performance, concurrency, data quality, and maintainability.

This chapter uses broadly familiar SQL syntax. Exact syntax differs among PostgreSQL, MySQL, SQL Server, Oracle, and other systems. Production developers must therefore consult the documentation for the selected database management system (DBMS).

Learning Objectives

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

1. Explain the purposes and trade-offs of major database objects.
2. Create and use views and materialized views appropriately.
3. Design stored procedures and user-defined functions.
4. Explain trigger behavior and identify suitable and unsuitable uses.
5. Write secure parameterized queries.
6. Distinguish APIs, connectors, and ORM access patterns.
7. Evaluate database access designs in relation to performance, security, governance, and maintainability.

1. Database Objects as Layers of Responsibility

A relational database commonly contains the following objects:

A useful design principle is to place each rule at the lowest layer that can enforce it safely, but not to place all application behavior in the database. A primary key belongs in the database because every client must respect uniqueness. A screen-specific formatting rule usually belongs in the application or presentation layer.

2. Views: Logical Data Abstraction

A view is a named query whose result is logically treated as a table. In a conventional view, the DBMS stores the definition rather than a separate copy of the result. When a user queries the view, the optimizer generally expands or rewrites the view definition and accesses the underlying tables.

For example, suppose an institution has the following tables:

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    region VARCHAR(50) NOT NULL
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date DATE NOT NULL,
    order_total DECIMAL(12,2) NOT NULL CHECK (order_total >= 0),
    status VARCHAR(20) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

A reporting view can hide implementation details and expose only approved columns:

CREATE VIEW v_completed_orders AS
SELECT
    o.order_id,
    c.customer_name,
    c.region,
    o.order_date,
    o.order_total
FROM orders AS o
JOIN customers AS c
  ON c.customer_id = o.customer_id
WHERE o.status = 'Completed';

Users can query the view as follows:

SELECT region, SUM(order_total) AS revenue
FROM v_completed_orders
GROUP BY region;

Uses of views

Views are especially useful for:

• restricting access to sensitive columns or rows;
• providing stable names when underlying tables change;
• simplifying repeated joins and calculations;
• presenting business concepts such as “completed orders” or “active accounts”;
• supporting reporting and self-service analytics.

A view can implement row-level filtering, but it should not be treated as a complete security solution without understanding the DBMS security model. Permissions on the view, permissions on base tables, ownership chaining, and row-level security policies must be evaluated together.

Updatable views

Some simple views can accept INSERT, UPDATE, or DELETE operations. An uncomplicated view over one base table may be updatable, whereas a view containing joins, grouping, DISTINCT, set operations, or calculated aggregates may not be. Even when technically updatable, an operation may be confusing because a change to a view can affect multiple underlying tables or violate constraints.

A view is best considered an interface, not automatically a safe editing mechanism. If controlled changes are required, a stored procedure often makes the permitted operation more explicit.

View limitations

A conventional view does not automatically improve performance. Repeatedly querying a complex view may still require expensive joins and aggregations. Views can also conceal complexity from analysts, making it harder to identify why a query is slow. Clear naming, documentation, and execution-plan analysis are therefore important.

3. Materialized Views: Stored Query Results

A materialized view stores the result of a query physically. It can be queried like a table and can often be indexed. This is valuable when a complex analytical query is executed frequently but the underlying data changes less frequently than the query is read.

A generic definition might be:

CREATE MATERIALIZED VIEW mv_daily_revenue AS
SELECT
    order_date,
    SUM(order_total) AS revenue,
    COUNT(*) AS order_count
FROM orders
WHERE status = 'Completed'
GROUP BY order_date;

The exact command varies by DBMS. Refreshing may be manual, scheduled, incremental, or automatic:

REFRESH MATERIALIZED VIEW mv_daily_revenue;

Freshness and performance

Materialized views create a trade-off between read performance and data freshness. Let t_r represent the time at which the source data is read and t_v represent the time at which the materialized view was last refreshed. The data age at query time is approximately:

where A is the staleness interval, measured in time units. This expression assumes that the refresh timestamp represents the effective data snapshot and does not account for source-system delays or failed refreshes. A business requirement such as “reports must be no more than 60 minutes old” imposes the operational condition A ≤ 60 minutes at the relevant reporting time.

Materialized views are appropriate when:

• queries are expensive and repeated;
• data can tolerate a documented delay;
• refresh cost is lower than repeatedly calculating the result;
• the result can be indexed or partitioned effectively.

They are less appropriate for real-time balances, rapidly changing inventory, or decisions requiring transaction-level freshness. Refresh jobs must be monitored: a materialized view that silently fails can produce plausible but obsolete results.

4. Stored Procedures

A stored procedure is a named program stored and executed inside the DBMS. It may accept input parameters, modify data, return result sets, and control transactions, depending on the DBMS.

For example, a procedure for completing an order might be expressed in a PostgreSQL-style form:

CREATE OR REPLACE PROCEDURE complete_order(p_order_id INTEGER)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE orders
    SET status = 'Completed'
    WHERE order_id = p_order_id
      AND status = 'Pending';

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Order does not exist or is not pending';
    END IF;
END;
$$;

The procedure can then be called with:

CALL complete_order(1042);

Why use procedures?

Stored procedures can:

• encapsulate multi-step transactions;
• reduce network round trips;
• provide a controlled interface to sensitive tables;
• centralize rules shared by multiple applications;
• record audit events consistently;
• reduce the amount of SQL generated by client code.

They are particularly useful when an operation must be atomic. An atomic operation either completes according to its contract or leaves the database in a valid prior state. Transaction design must still account for isolation levels, locking, deadlocks, and error handling.

Design considerations

A well-designed procedure has a narrow purpose, explicit parameters, documented preconditions and postconditions, predictable error behavior, and appropriate authorization. It should avoid embedding presentation logic or assumptions about a single user interface. Procedures should also be version-controlled like application code.

A major limitation is portability. Procedure languages, exception syntax, transaction commands, and result-set conventions vary considerably among vendors. Excessive use can also distribute business logic between the database and application in a way that is difficult to understand.

5. User-Defined Functions

A user-defined function (UDF) is a named routine that returns a scalar value, a row, or a table. A scalar function might standardize a calculation:

CREATE FUNCTION order_status_label(p_status VARCHAR(20))
RETURNS VARCHAR(30)
DETERMINISTIC
RETURN CASE p_status
    WHEN 'P' THEN 'Pending'
    WHEN 'C' THEN 'Completed'
    WHEN 'X' THEN 'Cancelled'
    ELSE 'Unknown'
END;

The syntax and determinism declaration are DBMS-specific. A function may be called in a query:

SELECT order_id, order_status_label(status)
FROM orders;

A table-valued function can provide parameterized relational output and may be useful where a reusable query interface is needed.

Functions should generally be predictable and side-effect free when used in SELECT statements. A deterministic function returns the same output for the same inputs under the same relevant database state. However, “same inputs” is not enough if the function reads changing tables, depends on time, uses random values, or depends on session settings. Such dependencies affect optimization and testing.

A scalar function called once per row can be expensive. For large datasets, a set-based expression or join may perform better than repeated procedural calls. Developers should inspect execution plans rather than assuming that abstraction is costless.

6. Triggers and Event-Driven Database Logic

A trigger is a database program automatically invoked by an event such as INSERT, UPDATE, or DELETE. It may execute BEFORE, AFTER, or INSTEAD OF the event, depending on the DBMS and object type.

An audit trigger illustrates a defensible use:

CREATE TABLE order_audit (
    audit_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id INTEGER NOT NULL,
    old_status VARCHAR(20),
    new_status VARCHAR(20),
    changed_at TIMESTAMP NOT NULL,
    changed_by VARCHAR(100) NOT NULL
);

A trigger could record status changes automatically. Exact syntax varies, but its logical behavior is:

1. detect an update to an order;
2. compare old and new status values;
3. insert an audit row when the values differ.

Triggers are valuable for audit trails, maintaining derived metadata, and enforcing rules that must apply regardless of the client. They should not normally be used for surprising side effects, complex workflows, external web-service calls, or rules that users cannot discover. Hidden actions can create debugging difficulties, trigger chains, recursion, and performance problems.

A trigger does not replace a constraint when a declarative constraint is available. A CHECK constraint, foreign key, UNIQUE constraint, or NOT NULL constraint communicates intent more clearly and is usually easier for the optimizer and administrators to manage.

7. Parameterized Queries and SQL Injection Prevention

A parameterized query separates SQL code from data values. Instead of concatenating a user-provided string into SQL, the application sends a statement template and a value separately.

Unsafe construction:

sql = "SELECT * FROM customers WHERE customer_name = '" + user_input + "'"

If the input contains SQL control characters, the resulting statement may be altered. The problem is not solved reliably by manually escaping strings in application code.

Safe construction using placeholders:

SELECT customer_id, customer_name, region
FROM customers
WHERE customer_name = :customer_name;

Illustrative application code using a DB-API-style connector:

cursor.execute(
    "SELECT customer_id, customer_name, region "
    "FROM customers WHERE customer_name = %s",
    (user_input,)
)

The placeholder style differs by connector. Parameters represent values, not arbitrary SQL identifiers. If a user must select a sort column, the application should map an approved symbolic choice to a fixed identifier rather than binding it as a value:

allowed_sort = {
    "name": "customer_name",
    "region": "region"
}
sort_column = allowed_sort.get(requested_sort, "customer_name")
sql = f"SELECT customer_id, customer_name, region " \
      f"FROM customers ORDER BY {sort_column}"
cursor.execute(sql)

The identifier is selected from a controlled allow-list; it is not copied directly from the request.

Parameterized queries improve security, but they do not by themselves solve authorization, excessive data exposure, weak passwords, insecure transport, or poor input validation. Least-privilege database accounts, encrypted connections, secret management, and logging remain necessary.

8. APIs, Connectors, and ORM Access Patterns

These three terms describe related but distinct layers.

APIs

An application programming interface defines how software components interact. A REST API may expose resources through HTTP methods such as GET, POST, PUT, and DELETE, often returning JSON. A GraphQL API allows clients to request selected fields through a query language. The API should expose business capabilities rather than blindly exposing every database table.

A service might receive a request to create an order, validate it, call a stored procedure or transaction, and return a response. This creates a boundary at which authentication, authorization, rate limiting, validation, observability, and error translation can be applied.

Connectors and drivers

A connector or driver is the library that allows application code to communicate with a particular DBMS. It manages protocol details, authentication, parameter binding, result retrieval, transactions, and often connection pooling.

A typical access sequence is:

1. obtain a connection from a pool;
2. create a cursor or command object;
3. execute a parameterized statement;
4. fetch results or inspect affected rows;
5. commit on success or roll back on failure;
6. release the connection.

Connection pooling improves performance by reusing established connections. Pool limits must be chosen carefully. If too many application workers compete for too few database connections, requests queue; if the pool is too large, the database may exhaust memory or worker capacity. A pool does not eliminate the need to close cursors and release connections.

ORM access

An ORM maps relational structures to programming-language objects. For example, an Order object may correspond to an orders row, while a relationship may represent the customer foreign key. ORMs provide identity management, change tracking, migrations, and query abstractions.

A simple ORM-style query might be:

completed = (
    session.query(Order)
    .filter(Order.status == "Completed")
    .all()
)

ORMs improve developer productivity and reduce repetitive SQL, but they do not remove relational concepts. Developers must still understand joins, indexes, transactions, cardinality, and execution plans.

A common ORM problem is the N+1 query pattern: one query retrieves a set of parent rows, followed by one additional query for each parent. If there are n parent rows, the application may issue approximately n + 1 queries instead of one appropriately joined or eagerly loaded query. The expression counts the initial query plus one query per parent; it is a query-count model, not a prediction of elapsed time.

Other ORM risks include accidental full-table retrieval, inefficient generated SQL, lazy loading after a session closes, and mass assignment of fields that should not be client-editable. Profiling generated SQL is essential.

9. Integrated Application Architecture

A robust architecture often separates responsibilities as follows:

Figure: A layered application path from client software to database objects.

The database should enforce fundamental integrity and access rules, while the API should coordinate user-facing workflows and translate technical errors into appropriate responses. This division is not absolute. Critical invariants should not depend solely on an API because other clients, batch jobs, or administrators may access the database.

10. Illustrative Case: Regional Retail Analytics Platform

Background

A fictional retailer operates an online store, a mobile application, and a business-intelligence dashboard. Its orders table contains millions of rows. Analysts repeatedly calculate daily revenue by region, while customer-service applications update order status.

Problem

The initial system exposed base tables directly to several applications. Analysts used inconsistent definitions of “completed order,” customer-service code concatenated SQL strings, and dashboards performed expensive aggregations during business hours. A status change made by a batch process was not always recorded in the audit system.

Analysis

The organization identified four separate concerns:

• a shared analytical definition was needed;
• dashboard reads needed lower latency;
• all status changes required an audit trail;
• applications needed secure and consistent write operations.

Approach and solution

The team created a view named v_completed_orders containing the approved definition of a completed order. It created a daily-revenue materialized view refreshed every 15 minutes, with monitoring for refresh failure and data age. The customer-service operation was moved into a stored procedure that validated the current status and performed the update transactionally. An audit trigger recorded old and new statuses, timestamp, and actor identity.

The API used connector parameters for all values and an allow-list for sortable fields. The dashboard queried the materialized view, while operational screens queried current tables through the API. ORM code was retained for simple transactional operations, but complex reports used reviewed SQL and execution-plan tests.

Outcome

The design established a common metric definition, reduced repeated aggregation work, and made status changes traceable. It also clarified that the dashboard was near-real-time rather than fully real-time. The team documented refresh latency and granted applications only the permissions required for their tasks.

Learning points

The case demonstrates that database objects should be selected according to responsibility. A view supports semantic consistency, a materialized view supports read performance with known staleness, a procedure supports a controlled write operation, a trigger supports automatic auditing, and parameterized access reduces injection risk. No single object solves every requirement.

11. Governance, Ethics, and Limitations

Database objects influence what people can see and what decisions organizations can make. Restricting sensitive columns is a privacy control, but it must be tested against indirect disclosure. For example, a count or aggregate may reveal confidential information when a group contains only one person.

Audit logs should be protected from unauthorized alteration and should record sufficient context without collecting unnecessary personal data. Retention periods should follow legal, contractual, and organizational requirements. Access should follow least privilege, separation of duties, and periodic review.

Data quality rules should be explicit. A trigger that silently changes an input may preserve technical consistency while obscuring the original value. Similarly, a materialized view used for credit, staffing, or compliance decisions should display its refresh time and known limitations.

Operationally, every important object requires lifecycle management: naming standards, source control, migration scripts, dependency documentation, automated tests, performance monitoring, backup planning, and rollback procedures. Security testing should include injection attempts, privilege escalation, unauthorized view access, and abuse of API parameters.

12. Best Practices

1. Use declarative constraints before procedural logic where possible.
2. Expose views or procedures rather than granting broad access to sensitive base tables.
3. Document materialized-view refresh schedules and acceptable staleness.
4. Keep procedures and functions focused, testable, and version-controlled.
5. Use triggers selectively, especially for auditing and cross-client invariants.
6. Parameterize all data values and use allow-lists for dynamic identifiers.
7. Review generated ORM SQL and test for N+1 queries.
8. Use transactions deliberately and define error and retry behavior.
9. Monitor connection pools, query latency, locks, failed refreshes, and database errors.
10. Apply least privilege to databases, procedures, APIs, and service accounts.
11. Treat database changes as migrations with review and rollback planning.
12. Make data freshness, provenance, and auditability visible to decision-makers.

Chapter Summary

Database objects extend relational tables into a controlled application platform. A view provides a reusable logical interface, while a materialized view stores query results for faster reads at the cost of refresh complexity and possible staleness. Stored procedures encapsulate operations and transactions; user-defined functions provide reusable computations; and triggers respond automatically to database events but can introduce hidden control flow.

Parameterized queries separate SQL code from values and are a fundamental defense against SQL injection. APIs define application-facing contracts, connectors manage database communication, and ORMs map relational data to programming objects. Each layer offers benefits but also creates performance, security, and maintainability obligations. Effective design combines constraints, carefully selected database objects, secure application access, monitoring, documentation, and governance.

Key Terms

View: A named query whose result is logically presented as a table.

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

Stored procedure: A named database routine that can perform operations and, depending on the DBMS, manage transactions.

User-defined function: A database routine that returns a scalar, row, or table result.

Trigger: A routine invoked automatically by a database event.

Parameterized query: A statement in which values are supplied separately from SQL text.

SQL injection: An attack in which untrusted input changes the meaning of dynamically constructed SQL.

API: A defined interface through which software components communicate.

Connector: A driver or library that communicates with a DBMS.

ORM: Object-relational mapping technology that represents relational data through programming-language objects.

Connection pool: A managed collection of reusable database connections.

Transaction: A logical unit of work governed by atomicity, consistency, isolation, and durability principles.

Row-level security: A mechanism that restricts rows according to user or session context.

Staleness: The age of data relative to its source or last refresh.

Execution plan: The DBMS strategy for executing a query.

N+1 query problem: Inefficient execution involving one initial query followed by one query per returned parent row.

Least privilege: Granting only the access required to perform a task.

Audit trail: A record of actions, changes, actors, and times.

Declarative constraint: A database-enforced rule such as a primary key, foreign key, or CHECK constraint.

Review Questions

Short Answer Questions

1. What is the principal difference between a conventional view and a materialized view?
2. Give two appropriate uses of stored procedures.
3. Why are parameterized queries safer than string concatenation?
4. What is the N+1 query problem?
5. Why should materialized-view freshness be documented?

Long Answer Questions

1. Compare views, materialized views, stored procedures, functions, and triggers in terms of purpose, benefits, and limitations.
2. Explain how APIs, connectors, and ORMs cooperate in a database-backed application.
3. Discuss the security and governance controls required when exposing database data through an API.
4. Explain why triggers should be used selectively and compare them with declarative constraints.
5. Design a database access strategy for an analytical dashboard that requires fast reads but can tolerate fifteen minutes of data delay.

Analytical / Application Questions

1. A dashboard query takes two minutes and is executed every thirty seconds against a table that changes continuously. Recommend whether to use a view, materialized view, indexing, query redesign, or a combination. Justify the expected freshness and operational trade-offs.
2. An ORM application retrieves 500 customers and then issues one query for each customer’s orders. Identify the performance problem and propose at least two technically different solutions.
3. A developer accepts a user-selected sort field and inserts it directly into an ORDER BY clause, while all filter values are parameterized. Assess the security risk and design a safe replacement.

Multiple Choice Questions with Answers

Question 1

Which statement best describes a conventional database view?

A. It always stores a separate physical copy of query results

B. It stores a named query definition whose results are generally computed when queried

C. It automatically executes after every INSERT

D. It can only contain columns from one table

Correct Answer: B. It stores a named query definition whose results are generally computed when queried

Question 2

What is the principal trade-off introduced by a materialized view?

A. Faster reads in exchange for refresh management and possible stale data

B. Higher security in exchange for loss of all indexes

C. Automatic transactions in exchange for no query reuse

D. Lower storage use in exchange for slower reads

Correct Answer: A. Faster reads in exchange for refresh management and possible stale data

Question 3

Which practice most directly prevents SQL injection when supplying a customer name?

A. Concatenating the name after converting it to uppercase

B. Using a parameter placeholder and binding the name as a value

C. Allowing only alphabetic characters while concatenating the value

D. Displaying the generated SQL to the user

Correct Answer: B. Using a parameter placeholder and binding the name as a value

Question 4

What is a typical purpose of a stored procedure?

A. To define a programming-language class

B. To encapsulate a database operation or multi-step transaction

C. To replace every database constraint

D. To guarantee that a query never needs an index

Correct Answer: B. To encapsulate a database operation or multi-step transaction

Question 5

Which database object is automatically invoked by a specified INSERT, UPDATE, or DELETE event?

A. Trigger

B. Materialized view

C. Sequence

D. Connector

Correct Answer: A. Trigger

Question 6

If an ORM performs one initial query and then one additional query for each of 500 parent rows, approximately how many queries are issued?

A. 1

B. 500

C. 501

D. 1,000

Correct Answer: C. 501

Question 7

What is the safest general approach when a user must select a sort column dynamically?

A. Bind the column name as an ordinary value parameter

B. Insert the raw user input into SQL

C. Choose the identifier from a server-side allow-list

D. Disable all WHERE clauses

Correct Answer: C. Choose the identifier from a server-side allow-list

Question 8

What is the main function of a database connector?

A. To define business terms for analysts

B. To communicate between application code and a DBMS

C. To replace the database transaction log

D. To convert every table into a JSON document

Correct Answer: B. To communicate between application code and a DBMS

Question 9

Which option is generally preferable when a declarative foreign-key constraint can enforce a relationship?

A. A hidden trigger with several side effects

B. A client-side warning only

C. The declarative foreign-key constraint

D. A materialized view refresh

Correct Answer: C. The declarative foreign-key constraint

Question 10

A materialized view was refreshed at 10:00 and a report runs at 10:45. Ignoring source-system delays, what is the approximate data age?

A. 10 minutes

B. 30 minutes

C. 45 minutes

D. 60 minutes

Correct Answer: C. 45 minutes