Views in SQLite

A view assigns a name to a stored SELECT statement. Once created, you use it in the FROM clause anywhere you would use a table, and SQLite runs the underlying query each time you reference it. Views are good for hiding join complexity, exposing a stable column set over a changing schema, and giving a query a reusable name.

This guide covers creating, querying, and dropping views, the read-only rule and the trigger workaround, and where a view differs from a cached result. For the query patterns you put inside a view, see the SQLite joins and subqueries guides.

Creating a view

CREATE VIEW active_customers AS
SELECT id, name, email
FROM customers
WHERE status = 'active';

Now query it like a table:

SELECT name FROM active_customers WHERE email LIKE '%@example.com';

Use CREATE VIEW IF NOT EXISTS to avoid an error when the view already exists. A view stores only the query text, no data, so creating one is cheap and instant regardless of table size.

Naming the columns

You can name the view's columns explicitly. The column-name list syntax was added in SQLite 3.9.0 (2015-10-14).

CREATE VIEW order_summary (customer, order_count, lifetime_value) AS
SELECT c.name, count(o.id), sum(o.total)
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;

Naming columns is recommended. If you omit the list, SQLite derives names from the SELECT result columns, and for computed columns those auto-generated names are not a stable part of the interface and may change between releases. Either name the columns here or use AS aliases in the SELECT.

Temporary views

A TEMP (or TEMPORARY) view is visible only to the connection that created it and is dropped automatically when that connection closes.

CREATE TEMP VIEW recent_errors AS
SELECT * FROM logs WHERE level = 'error' AND ts > unixepoch('now', '-1 day');

Useful for an analysis session where you do not want to leave a permanent object in the schema. You cannot combine an explicit schema name with TEMP unless that schema is temp.

Views are read-only

You cannot INSERT, UPDATE, or DELETE through a view in SQLite. Views are read-only. Attempting a write raises an error like cannot modify active_customers because it is a view.

This is a deliberate design choice, not a missing feature. Many databases support "automatically updatable" views under restrictive conditions; SQLite instead gives you one explicit mechanism: triggers.

Making a view writable with INSTEAD OF triggers

An INSTEAD OF trigger fires in place of a write against a view and lets you translate that write into operations on the base tables. This is the supported way to make a view behave as if it were writable.

CREATE TRIGGER active_customers_update
INSTEAD OF UPDATE ON active_customers
FOR EACH ROW
BEGIN
  UPDATE customers
  SET name = NEW.name,
      email = NEW.email
  WHERE id = OLD.id;
END;

With that trigger in place, UPDATE active_customers SET email = 'new@x.com' WHERE id = 5 runs the body against the customers table. You write separate INSTEAD OF INSERT and INSTEAD OF DELETE triggers for those operations. The NEW and OLD references give you the proposed and existing row values.

The catch: you are writing the mapping logic by hand. For a view over a single table it is straightforward; for a view that joins several tables, you decide which base table each change touches, and some changes may not map cleanly at all.

A view is not a cached result

A view re-runs its SELECT every time you query it. It does not store results, so it always reflects current data, but a view over an expensive aggregate pays that cost on every read.

SQLite has no MATERIALIZED VIEW. When you need a cached snapshot, the common patterns are:

• A regular table you refill on a schedule: CREATE TABLE sales_rollup AS SELECT ..., then DELETE and re-INSERT (or DROP/recreate) when you want fresh numbers.
• A CTE with the MATERIALIZED hint (SQLite 3.35.0+) inside a single query, which forces the planner to compute the subquery once for that query only.

Neither is a true materialized view; both are explicit and under your control.

Dropping a view

DROP VIEW order_summary;
DROP VIEW IF EXISTS order_summary;

Dropping a view removes only the definition. Base tables and their data are untouched. To see existing views, query the schema table:

SELECT name, sql FROM sqlite_schema WHERE type = 'view';

Common mistakes

• Trying to write through a view directly. It will error. Add an INSTEAD OF trigger or write to the base table.
• Relying on auto-generated column names for computed columns. Name them with a column list or AS aliases so they stay stable across SQLite versions.
• Expecting a view to cache. It re-runs every query. For expensive aggregates, refill a real table instead.
• An INSTEAD OF trigger that ignores OLD/NEW correctly. Map OLD to the row being changed and NEW to the incoming values, or you will update the wrong rows.
• Combining TEMP with a non-temp schema name. Not allowed unless the schema is temp.

When you are building a view over several joined tables and want to confirm which columns survive the join, Mako's AI autocomplete can expand the underlying SELECT so you can check the result set before you wrap it in CREATE VIEW.

Mako connects to SQLite and eight other databases with AI-powered autocomplete. Try it free at mako.ai.