SQLite Views: CREATE VIEW, Temporary Views, and INSTEAD OF

A View Is a Saved Query

A view is a SELECT statement with a name. Once you've created one, you can query it like a table — but nothing is stored. Every time you read from a view, SQLite runs the underlying query fresh.

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    amount REAL,
    status TEXT
);

INSERT INTO orders (customer, amount, status) VALUES
    ('Ada', 120.00, 'paid'),
    ('Boris', 45.50, 'pending'),
    ('Rosa', 300.00, 'paid'),
    ('Boris', 80.00, 'paid');

CREATE VIEW paid_orders AS
SELECT customer, amount FROM orders WHERE status = 'paid';

SELECT * FROM paid_orders;

paid_orders looks and behaves like a table. It has columns, you can SELECT from it, you can join it. But under the hood, every query expands to the original WHERE status = 'paid' filter.

That's the whole mental model: a view is an alias for a query.

Why Views Are Useful

The main win is naming. A complicated query gets a short, descriptive name, and the rest of your code stays readable:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    amount REAL,
    status TEXT
);

INSERT INTO orders (customer, amount, status) VALUES
    ('Ada', 120.00, 'paid'),
    ('Rosa', 300.00, 'paid'),
    ('Boris', 80.00, 'paid');

CREATE VIEW customer_totals AS
SELECT customer, SUM(amount) AS total, COUNT(*) AS order_count
FROM orders
WHERE status = 'paid'
GROUP BY customer;

SELECT * FROM customer_totals WHERE total > 100;

Without the view, every caller would write the GROUP BY themselves — and any one of them could get the filter wrong. With it, the aggregation is defined once. Callers just ask for customer_totals and add whatever extra filters they need on top.

Views also work as a permission-style boundary. If a query shouldn't expose a password_hash column, build a view that selects everything except that column and have application code use the view.

CREATE VIEW Syntax

The full form:

CREATE [TEMPORARY] VIEW [IF NOT EXISTS] view_name [(column_aliases)] AS
SELECT ...;

A few things worth knowing:

• IF NOT EXISTS skips creation silently if the view already exists.
• TEMPORARY (or TEMP) creates a view that vanishes when the connection closes.
• Column aliases in parentheses let you rename the view's columns without touching the underlying SELECT.

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price_cents INTEGER
);

INSERT INTO products (name, price_cents) VALUES
    ('Notebook', 450),
    ('Pen', 200);

CREATE VIEW IF NOT EXISTS price_list (item, dollars) AS
SELECT name, price_cents / 100.0 FROM products;

SELECT * FROM price_list;

The view exposes friendlier names (item, dollars) without renaming columns in the source table.

Replacing and Dropping Views

SQLite doesn't have CREATE OR REPLACE VIEW or ALTER VIEW. To change a view's definition, drop it and recreate it:

CREATE TABLE orders (id INTEGER, status TEXT);
INSERT INTO orders VALUES (1, 'paid'), (2, 'pending');

CREATE VIEW active_orders AS
SELECT * FROM orders WHERE status = 'paid';

DROP VIEW active_orders;

CREATE VIEW active_orders AS
SELECT * FROM orders WHERE status IN ('paid', 'shipped');

SELECT * FROM active_orders;

DROP VIEW IF EXISTS active_orders; is the safe form — it won't error if the view isn't there. Dropping a view never affects the underlying tables; you're only deleting the saved query.

Temporary Views

A TEMP VIEW exists only for the current database connection. When the connection closes, the view is gone. Useful for ad-hoc analysis sessions where you don't want to leave definitions behind:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    type TEXT,
    occurred_at TEXT
);

INSERT INTO events (type, occurred_at) VALUES
    ('login', '2026-04-01'),
    ('purchase', '2026-04-01'),
    ('login', '2026-04-02');

CREATE TEMP VIEW today_logins AS
SELECT * FROM events WHERE type = 'login' AND occurred_at = '2026-04-02';

SELECT * FROM today_logins;

Temp views also let you shadow a query name without committing to it in your schema — handy during exploration.

Views Are Read-Only by Default

This is the most important gotcha. You can't INSERT, UPDATE, or DELETE through a view directly:

sqlite> INSERT INTO paid_orders (customer, amount) VALUES ('Eve', 50);
Runtime error: cannot modify paid_orders because it is a view

The fix is INSTEAD OF triggers. You write a trigger that fires instead of the write attempt and translates it into a real operation on the underlying table:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    amount REAL,
    status TEXT
);

CREATE VIEW paid_orders AS
SELECT id, customer, amount FROM orders WHERE status = 'paid';

CREATE TRIGGER paid_orders_insert
INSTEAD OF INSERT ON paid_orders
BEGIN
    INSERT INTO orders (customer, amount, status)
    VALUES (NEW.customer, NEW.amount, 'paid');
END;

INSERT INTO paid_orders (customer, amount) VALUES ('Eve', 50.00);

SELECT * FROM orders;

The view stays a view — but writes against it now have somewhere to go. We'll cover triggers properly in the next page.

No Materialized Views — Roll Your Own

Some databases let you cache a view's results on disk and refresh them on demand. SQLite doesn't. Every read of a view re-executes the underlying query. For most workloads that's fine — SQLite is fast and the query planner is good. For expensive aggregations queried many times, build a real table and keep it in sync yourself:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    amount REAL
);

INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120.00), ('Rosa', 300.00), ('Boris', 80.00);

CREATE TABLE customer_totals_cache AS
SELECT customer, SUM(amount) AS total
FROM orders
GROUP BY customer;

SELECT * FROM customer_totals_cache;

You'd then refresh the cache on a schedule, or wire up triggers on orders to keep it current. It's manual work — but it's also the only option in SQLite.

Listing Views

The metadata for views lives in sqlite_master alongside tables and indexes:

CREATE TABLE t (x INTEGER);
CREATE VIEW v1 AS SELECT * FROM t;
CREATE VIEW v2 AS SELECT x * 2 AS doubled FROM t;

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

The sql column gives you back the original CREATE VIEW statement — useful when you've forgotten what a view does. In the CLI, .schema view_name prints the same thing more cleanly.

When to Reach for a View

Views earn their keep when:

• A non-trivial query gets reused in three or more places. Naming it once beats copy-pasting.
• You want to expose a curated subset of columns or rows to part of an application.
• An aggregation is conceptually a single thing (monthly_sales, active_users) that callers should treat as a noun.

Skip the view when:

• The query is used in exactly one place. Inline it.
• Performance matters and the underlying query is expensive — you're paying that cost on every read. Cache into a real table instead.
• The view depends on another view that depends on another view. SQLite handles nesting fine, but a chain of three or four views makes the actual SQL hard to follow during debugging.

Next: Triggers

Views and triggers come up together a lot — the INSTEAD OF pattern that makes views writable is one of the main reasons triggers exist. Triggers are also useful on their own for audit logging, cascading updates, and enforcing invariants. That's the next page.