Views and Readable Query Interfaces

Learning outcomes

A normalized schema is good at storing facts, but application screens and reports often need the same multi-table join again and again. Copying that join into every caller creates another kind of duplication: query logic. SQLite views let the schema publish a named query interface without creating another copy of the underlying rows.

Start with the repeated query, not CREATE VIEW syntax

Suppose FieldNotes repeatedly needs a device dashboard containing a device code, its human-readable site name, device status, and the number of maintenance notes. The SQL is not difficult, but repeating it in a CLI script, desktop application, reporting export, and test fixture creates multiple places that can drift.

SELECT d.device_code,       d.device_name,       s.site_name,       d.status,       COUNT(n.note_id) AS note_countFROM device AS dJOIN site AS s ON s.site_id=d.site_idLEFT JOIN maintenance_note AS n ON n.device_id=d.device_idGROUP BY d.device_id, d.device_code, d.device_name, s.site_name, d.status;

A view assigns a schema-level name to a SELECT statement. When a later query reads the view, SQLite evaluates the underlying query as part of that statement. Ordinary views do not materialize or maintain a second stored result set.

Create a stable query interface

DROP VIEW IF EXISTS device_summary;CREATE VIEW device_summary(  device_code,  device_name,  site_name,  status,  note_count) ASSELECT d.device_code,       d.device_name,       s.site_name,       d.status,       COUNT(n.note_id)FROM device AS dJOIN site AS s ON s.site_id=d.site_idLEFT JOIN maintenance_note AS n ON n.device_id=d.device_idGROUP BY d.device_id, d.device_code, d.device_name, s.site_name, d.status;SELECT * FROM device_summary ORDER BY device_code;

The explicit column-name list is part of the interface. SQLite can derive view column names from result expressions, but current documentation recommends explicit names or stable AS aliases because automatically generated names are not a durable interface contract.

A view is a schema object, not a hidden table

SELECT type, name, tbl_name, sqlFROM sqlite_schemaWHERE type='view' AND name='device_summary';PRAGMA table_info('device_summary');

The sqlite_schema.sql value records the CREATE VIEW definition. There is no separate collection of device-summary rows to refresh. If a base row changes, the next query against the view sees the database state visible to that transaction.

Dependencies are real even if SQLite does not build a dependency manager for you

A view can reference tables, other views, expressions, functions, and columns. If a later schema change removes or renames something the view depends on, the view can become unusable or change meaning. Treat view definitions as versioned schema code and exercise them in migration tests.

DROP VIEW device_summary;-- Querying device_summary now fails: the schema object is gone.CREATE VIEW device_summary(device_code,device_name,site_name,status,note_count) ASSELECT d.device_code,d.device_name,s.site_name,d.status,COUNT(n.note_id)FROM device AS dJOIN site AS s ON s.site_id=d.site_idLEFT JOIN maintenance_note AS n ON n.device_id=d.device_idGROUP BY d.device_id,d.device_code,d.device_name,s.site_name,d.status;

ORDER BY: the consumer owns final ordering

SQL result order is not guaranteed without an ORDER BY that applies to the final query result. A view definition may contain ORDER BY in SQLite, but treating that internal clause as a permanent promise that every outer query receives rows in that exact order is fragile. The outer query may add filtering, grouping, joins, or its own ordering, and query planning can transform execution.

CREATE VIEW inspection_queue ASSELECT device_code, device_name, updated_atFROM deviceWHERE status='inspection_due';SELECT *FROM inspection_queueORDER BY updated_at, device_code;

Design a view around stable columns and semantics. Let each consumer state the ordering it actually requires.

The planner still works through the view

CREATE INDEX IF NOT EXISTS idx_device_status_codeON device(status, device_code);EXPLAIN QUERY PLANSELECT device_code, device_nameFROM inspection_queueWHERE device_code >= 'F';

Depending on data and statistics, EQP should reference the underlying device table/index rather than a stored inspection_queue result file. SQLite may flatten or otherwise optimize the view's SELECT together with the outer query. As Chapter 10 emphasized, read the evidence from your actual build/data rather than hard-coding one exact textual EQP output.

Views improve interfaces, not authorization

In a client/server DBMS, a view is often paired with GRANT/REVOKE privileges so a user can read a projection while being denied base-table access. SQLite is an embedded library and does not provide that server-style account/role permission model. If application code that opens the file can freely issue SQL on the same connection, a view by itself does not stop that code from querying the base tables.

Compatibility view: evolve callers gradually

Views are useful during schema evolution when a new normalized design needs to preserve an older read interface temporarily.

CREATE VIEW legacy_device_export(code, location, state) ASSELECT d.device_code,       s.site_name,       d.statusFROM device AS dJOIN site AS s ON s.site_id=d.site_id;SELECT code, location, stateFROM legacy_device_exportORDER BY code;

This does not make every migration backward compatible automatically. It gives you an explicit compatibility surface that can be tested and later retired.

Lab: build, inspect, query, and prove non-materialization

PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS maintenance_note;DROP TABLE IF EXISTS device;DROP TABLE IF EXISTS site;CREATE TABLE site(  site_id INTEGER PRIMARY KEY,  site_name TEXT NOT NULL UNIQUE);CREATE TABLE device(  device_id INTEGER PRIMARY KEY,  site_id INTEGER NOT NULL REFERENCES site(site_id),  device_code TEXT NOT NULL UNIQUE,  device_name TEXT NOT NULL,  status TEXT NOT NULL CHECK(status IN ('active','inspection_due','retired')),  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP);CREATE TABLE maintenance_note(  note_id INTEGER PRIMARY KEY,  device_id INTEGER NOT NULL REFERENCES device(device_id) ON DELETE CASCADE,  noted_at TEXT NOT NULL,  note_text TEXT NOT NULL);INSERT INTO site(site_name) VALUES ('North Plant'),('Harbor Lab');INSERT INTO device(site_id,device_code,device_name,status) VALUES(1,'PUMP-007','Cooling Water Pump 7','active'),(1,'FAN-014','Exhaust Fan 14','inspection_due'),(2,'SENS-003','Vibration Sensor 3','active');INSERT INTO maintenance_note(device_id,noted_at,note_text) VALUES(1,'2026-08-10T08:30:00Z','Seal inspected; no leak found.'),(2,'2026-08-11T14:15:00Z','Belt tension below preferred range.'),(1,'2026-08-12T06:00:00Z','Vibration rechecked after shift start.');CREATE VIEW device_summary(device_code,site_name,status,note_count) ASSELECT d.device_code,s.site_name,d.status,COUNT(n.note_id)FROM device dJOIN site s ON s.site_id=d.site_idLEFT JOIN maintenance_note n ON n.device_id=d.device_idGROUP BY d.device_id,d.device_code,s.site_name,d.status;SELECT * FROM device_summary ORDER BY device_code;INSERT INTO maintenance_note(device_id,noted_at,note_text)VALUES(3,'2026-08-12T08:00:00Z','Sensor baseline captured.');SELECT * FROM device_summary WHERE device_code='SENS-003';EXPLAIN QUERY PLAN SELECT * FROM device_summary WHERE status='active';

The second summary query should report note_count=1 for SENS-003 without any explicit “refresh view” command. That is the observable proof that the view is a stored query definition, not stored summary rows.

Verification checkpoint

Production judgment and bridge

Views are intentionally passive: they define how to read. Lesson 2 introduces active schema behavior—triggers—and therefore a larger reasoning burden. We will use automation sparingly, make every side effect visible, and compare a maintainable trigger with an over-engineered one.

Authoritative references

• SQLite — CREATE VIEW.
• SQLite — DROP VIEW.
• SQLite — Schema Table.
• SQLite — EXPLAIN QUERY PLAN.