Chapter 15 · Views, Routines, and Database Automation
Views and Logical Data Abstraction
A view gives a query a durable relation name. Used carefully, it becomes a database interface that hides joins and derived columns; used carelessly, it becomes an undocumented dependency that breaks when base tables evolve.
Learning outcomes
Build views as deliberate database interfaces
Explain how an ordinary view differs from a stored result.
Design an explicit and stable view-column contract.
Use views to centralize joins, filters, and derived columns without hiding essential semantics.
Inspect view definitions and query plans in SQLite.
Recognize dependency, privilege, and schema-evolution risks.
A named query, not a copied table
An ordinary view stores a query definition. When a statement references the view, the database resolves that definition against the underlying relations. PostgreSQL describes ordinary views as non-materialized; SQLite similarly treats a view as a named SELECT usable in a FROM clause.
The logical interface is stable only when its exposed names and meanings are intentionally managed.
Practice schema
Run this setup in an empty SQLite database. The same small commerce model is reused throughout Chapter 15.
PRAGMA foreign_keys = ON;DROP VIEW IF EXISTS customer_order_metrics;DROP VIEW IF EXISTS open_order_queue;DROP TABLE IF EXISTS order_audit;DROP TABLE IF EXISTS order_item;DROP TABLE IF EXISTS sales_order;DROP TABLE IF EXISTS customer;CREATE TABLE customer ( customer_id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE, region TEXT NOT NULL CHECK (region IN ('north','south','east','west')), active INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0,1))) STRICT;CREATE TABLE sales_order ( order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customer(customer_id), status TEXT NOT NULL CHECK (status IN ('draft','submitted','paid','cancelled')), ordered_at TEXT NOT NULL, updated_at TEXT NOT NULL, total_cents INTEGER NOT NULL CHECK (total_cents >= 0), version INTEGER NOT NULL DEFAULT 1 CHECK (version >= 1)) STRICT;CREATE TABLE order_item ( order_id INTEGER NOT NULL REFERENCES sales_order(order_id) ON DELETE CASCADE, line_no INTEGER NOT NULL, sku TEXT NOT NULL, quantity INTEGER NOT NULL CHECK (quantity > 0), unit_price_cents INTEGER NOT NULL CHECK (unit_price_cents >= 0), PRIMARY KEY (order_id, line_no)) STRICT;INSERT INTO customer (customer_id, email, region, active) VALUES(1, 'ada@example.com', 'north', 1),(2, 'linus@example.com', 'west', 1),(3, 'grace@example.com', 'east', 1),(4, 'alan@example.com', 'south', 0);INSERT INTO sales_order (order_id, customer_id, status, ordered_at, updated_at, total_cents, version)VALUES(101, 1, 'paid', '2026-07-01 09:00:00', '2026-07-01 09:10:00', 12500, 2),(102, 1, 'submitted', '2026-07-03 12:00:00', '2026-07-03 12:00:00', 7600, 1),(103, 2, 'paid', '2026-07-04 14:00:00', '2026-07-04 14:20:00', 22100, 3),(104, 3, 'draft', '2026-07-05 08:30:00', '2026-07-05 08:30:00', 4800, 1),(105, 3, 'cancelled', '2026-07-06 16:00:00', '2026-07-06 16:30:00', 9100, 2);INSERT INTO order_item VALUES(101, 1, 'BOOK-SQL', 1, 8500),(101, 2, 'LAB-CREDIT',1, 4000),(102, 1, 'BOOK-DATA', 1, 7600),(103, 1, 'COURSE-DB', 2, 9000),(103, 2, 'BOOK-SQL', 1, 4100),(104, 1, 'BOOK-INTRO',2, 2400),(105, 1, 'COURSE-SQL',1, 9100);Create a useful analytical view
The view below exposes one row per customer. Explicit aliases form its public contract; consumers do not need to repeat the join, conditional aggregation, or NULL handling.
DROP VIEW IF EXISTS customer_order_metrics;CREATE VIEW customer_order_metrics ( customer_id, customer_email, region, order_count, paid_order_count, paid_revenue_cents, latest_order_at) ASSELECT c.customer_id, c.email, c.region, COUNT(o.order_id), SUM(CASE WHEN o.status = 'paid' THEN 1 ELSE 0 END), COALESCE(SUM(CASE WHEN o.status = 'paid' THEN o.total_cents END), 0), MAX(o.ordered_at)FROM customer AS cLEFT JOIN sales_order AS o ON o.customer_id = c.customer_idGROUP BY c.customer_id, c.email, c.region;SELECT *FROM customer_order_metricsORDER BY paid_revenue_cents DESC, customer_id;Why explicit columns matter
Stable names
A column list documents the interface and avoids accidental vendor-generated names for expressions.
Controlled shape
Avoid SELECT *; adding or reordering base-table columns should not silently change consumers.
Central semantics
One tested definition can standardize status filters, units, and derived metrics.
Visible cost
A convenient view can still contain expensive joins or aggregation; its name does not make the query free.
Views remain live
Because the result is not persisted, a later base-table change is visible through the next query.
INSERT INTO sales_order (order_id, customer_id, status, ordered_at, updated_at, total_cents, version)VALUES (106, 2, 'paid', '2026-07-07 10:00:00', '2026-07-07 10:00:00', 5000, 1);SELECT customer_id, order_count, paid_revenue_centsFROM customer_order_metricsWHERE customer_id = 2;Inspect definition and expansion
SELECT name, type, sqlFROM sqlite_schemaWHERE type = 'view' AND name = 'customer_order_metrics';EXPLAIN QUERY PLANSELECT customer_email, paid_revenue_centsFROM customer_order_metricsWHERE region = 'north';The plan normally references base tables and temporary grouping work rather than a separately stored view result. Always inspect the expanded workload when a view becomes performance-critical.
Read-only and writable-view behavior
| Concern | SQLite | PostgreSQL | Design implication |
|---|---|---|---|
| Ordinary evaluation | Definition is evaluated when referenced | Definition is evaluated when referenced | Treat the view as a logical interface, not a cache. |
| Direct writes | Views are read-only | Some simple views are automatically updatable | Do not assume portability of INSERT, UPDATE, or DELETE through a view. |
| Write adaptation | Use INSTEAD OF triggers | Automatic rules, check options, or triggers may apply | Document write semantics separately from read semantics. |
| Security | SQLite is embedded and connection-controlled | View and function privilege modes matter | A view is not automatically a secure boundary. |
Schema evolution and dependencies
A view couples consumers to both its output contract and the base objects used by its definition. Safe changes follow an expand-and-contract sequence:
Version view contracts when a semantic or incompatible shape change cannot be introduced safely in place.
CREATE VIEW customer_order_metrics_v2 ASSELECT customer_id, customer_email, region, order_count, paid_order_count, paid_revenue_cents, paid_revenue_cents / 100.0 AS paid_revenueFROM customer_order_metrics;-- Migrate consumers before retiring the previous interface.-- DROP VIEW customer_order_metrics;Common failure modes
| Failure | Why it happens | Prevention |
|---|---|---|
| Hidden row filtering | The view name does not reveal excluded states | Use precise names and document predicates. |
| Nested-view maze | Views depend on many other views | Limit layers and inspect the fully expanded plan. |
| Broken consumers | Columns are renamed or semantics change | Version the contract and test dependent queries. |
| False security | Sensitive base columns remain reachable elsewhere | Use privileges and security controls, not naming alone. |
| Unexpected slowness | Every reference repeats expensive work | Measure; consider indexes, query redesign, or materialization. |
Guided exercise: operational queue
DROP VIEW IF EXISTS open_order_queue;CREATE VIEW open_order_queue ASSELECT o.order_id, c.email AS customer_email, o.status, o.ordered_at, o.total_cents, CAST(julianday('now') - julianday(o.ordered_at) AS INTEGER) AS age_daysFROM sales_order AS oJOIN customer AS c ON c.customer_id = o.customer_idWHERE o.status IN ('draft', 'submitted');SELECT *FROM open_order_queueORDER BY age_days DESC, order_id;Check your understanding
- Why is a view not a performance cache?
- Why should a production view avoid
SELECT *? - What makes a view contract more than merely a convenient query?
- When should an incompatible view change receive a new name?
Review the answers
An ordinary view stores its definition, not its rows. Explicit columns protect shape and meaning. A contract includes names, types, grain, filters, NULL behavior, ownership, and compatibility promises. Version incompatible changes when consumers cannot migrate atomically.
Summary and references
- Views provide reusable logical relations and stable read interfaces.
- Explicit names, grain, filters, and units form the public contract.
- Inspect expanded plans; abstraction does not remove query cost.
- Write behavior and security vary by engine and require explicit design.
- Version interfaces and migrate consumers before destructive changes.