SQLite Views: CREATE VIEW, Updating Through Triggers and DROP VIEW (Tested)

Ninad Pathak

October 7, 2026

Views save a query under a name, so you can select from it like a table instead of repeating the same joins. SQLite’s views are read-only, and there’s no CREATE OR REPLACE VIEW. In this article I’ll walk you through creating and using views in SQLite, and how to work around what it doesn’t support.

TLDR

CREATE VIEW name AS SELECT …; saves a query. Then SELECT * FROM name runs it.
A view stores no data. It reads the underlying tables every time, so it’s always current.

Views are read-only in SQLite. To write through one, add an INSTEAD OF trigger.
There’s no CREATE OR REPLACE VIEW or ALTER VIEW. Drop the view and create it again, inside a transaction.
List views with SELECT name FROM sqlite_schema WHERE type = 'view';.

What is a view in SQLite?

A view is a saved SELECT statement with a name. The SQLite docs say it “assigns a name to a pre-packaged SELECT statement”, which you can then use “in the FROM clause of another SELECT in place of a table name”. SQLite keeps only the view’s SQL in the schema, not its rows, so there’s nothing to keep in sync.

SQLite CREATE VIEW syntax

CREATE [TEMP] VIEW [IF NOT EXISTS] view_name [(column1, column2, ...)]
AS select_statement;

DROP VIEW [IF EXISTS] view_name;

Part	What it does
`TEMP`	The view exists only for this connection and is deleted when it closes
`IF NOT EXISTS`	Does nothing if a view with that name already exists
Column list	Names the view’s columns. The docs recommend it (SQLite 3.9.0 and later)
`select_statement`	Any `SELECT`, including joins, `GROUP BY` and subqueries

How to create and use a view

To show you how this works, I will use a small shop database with customers and orders:

sqlite> CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, country TEXT NOT NULL);
sqlite> CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id), total REAL NOT NULL, status TEXT NOT NULL);
sqlite> INSERT INTO customers (name, country) VALUES ('Ada', 'UK'), ('Linus', 'FI'), ('Grace', 'US');
sqlite> INSERT INTO orders (customer_id, total, status) VALUES (1, 120.00, 'paid'), (1, 35.50, 'paid'), (2, 80.00, 'refunded'), (3, 210.00, 'paid');

Step 1: Create the view

This view adds up each customer’s paid orders:

sqlite> CREATE VIEW customer_revenue AS SELECT c.id, c.name, c.country, count(o.id) AS orders, sum(o.total) AS revenue FROM customers c JOIN orders o ON o.customer_id = c.id WHERE o.status = 'paid' GROUP BY c.id;

Step 2: Select from it like a table

sqlite> SELECT * FROM customer_revenue;
id  name   country  orders  revenue
--  -----  -------  ------  -------
1   Ada    UK       2       155.5
3   Grace  US       1       210.0
sqlite> SELECT name, revenue FROM customer_revenue WHERE revenue > 150 ORDER BY revenue DESC;
name   revenue
-----  -------
Grace  210.0
Ada    155.5

A view takes WHERE and ORDER BY the same way a table in a SELECT does. Linus has no row yet, because his only order was refunded.

Step 3: Add data and query again

The view isn’t a copy. A new paid order shows up straight away:

sqlite> INSERT INTO orders (customer_id, total, status) VALUES (2, 60.00, 'paid');
sqlite> SELECT * FROM customer_revenue;
id  name   country  orders  revenue
--  -----  -------  ------  -------
1   Ada    UK       2       155.5
2   Linus  FI       1       60.0
3   Grace  US       1       210.0

Examples

These examples keep using the same shop database.

Name the view’s columns

A column list after the view name sets the names callers see:

sqlite> CREATE VIEW paid_orders (order_id, customer, amount) AS SELECT o.id, c.name, o.total FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.status = 'paid';
sqlite> SELECT * FROM paid_orders;
order_id  customer  amount
--------  --------  ------
1         Ada       120.0
2         Ada       35.5
4         Grace     210.0
5         Linus     60.0

List the views in a database

Views live in sqlite_schema with type = 'view'. The shell’s .tables lists them along with the tables. See SQLite show tables:

sqlite> SELECT name, type FROM sqlite_schema WHERE type = 'view';
name              type
----------------  ----
customer_revenue  view
paid_orders       view
sqlite> .tables
customer_revenue  customers         orders            paid_orders

See the SQL behind a view

sqlite> .schema paid_orders
CREATE VIEW paid_orders (order_id, customer, amount) AS SELECT o.id, c.name, o.total FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.status = 'paid'
/* paid_orders(order_id,customer,amount) */;

Update data through a view with an INSTEAD OF trigger

A plain UPDATE or INSERT on a view fails:

sqlite> UPDATE paid_orders SET amount = 0 WHERE order_id = 1;
Error: in prepare, cannot modify paid_orders because it is a view
sqlite> INSERT INTO paid_orders (customer, amount) VALUES ('Ada', 10);
Error: in prepare, cannot modify paid_orders because it is a view

An INSTEAD OF trigger runs your own statement in place of the write. This one passes changes to amount through to orders:

sqlite> CREATE TRIGGER paid_orders_update INSTEAD OF UPDATE OF amount ON paid_orders BEGIN UPDATE orders SET total = NEW.amount WHERE id = OLD.order_id; END;
sqlite> UPDATE paid_orders SET amount = 125.00 WHERE order_id = 1;
sqlite> SELECT id, total FROM orders WHERE id = 1;
id  total
--  -----
1   125.0

Change a view’s definition

SQLite has neither CREATE OR REPLACE VIEW nor ALTER VIEW:

sqlite> CREATE OR REPLACE VIEW paid_orders AS SELECT id FROM orders;
Error: in prepare, near "OR": syntax error
  CREATE OR REPLACE VIEW paid_orders AS SELECT id FROM orders;
         ^--- error here
sqlite> ALTER VIEW paid_orders AS SELECT id FROM orders;
Error: in prepare, near "VIEW": syntax error
  ALTER VIEW paid_orders AS SELECT id FROM orders;
        ^--- error here

Drop the view and create it again. Doing both inside a transaction means no other connection sees the view missing:

sqlite> BEGIN; DROP VIEW paid_orders; CREATE VIEW paid_orders (order_id, customer, amount, placed) AS SELECT o.id, c.name, o.total, o.status FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.status = 'paid'; COMMIT;
sqlite> SELECT * FROM paid_orders LIMIT 2;
order_id  customer  amount  placed
--------  --------  ------  ------
1         Ada       125.0   paid
2         Ada       35.5    paid

Create a view only if it doesn’t exist

sqlite> CREATE VIEW IF NOT EXISTS customer_revenue AS SELECT 1;
sqlite> CREATE VIEW customer_revenue AS SELECT 1;
Error: in prepare, view customer_revenue already exists
  CREATE VIEW customer_revenue AS SELECT 1;
              ^--- error here

IF NOT EXISTS doesn’t compare definitions. The old customer_revenue stays as it was.

Create a temporary view

A TEMP view disappears when the connection closes. Each sqlite3 command here is a new connection, so the second one can’t see it:

$ sqlite3 -header -column t.db "CREATE TEMP VIEW uk_customers AS SELECT * FROM customers WHERE country = 'UK'; SELECT * FROM uk_customers;"
id  name  country
--  ----  -------
1   Ada   UK
$ sqlite3 t.db "SELECT * FROM uk_customers;"
Error: in prepare, no such table: uk_customers

Drop a view

DROP VIEW removes only the view. The tables and their rows stay:

sqlite> DROP VIEW customer_revenue;
sqlite> DROP VIEW customer_revenue;
Error: in prepare, no such view: customer_revenue
sqlite> DROP VIEW IF EXISTS customer_revenue;

Edge cases

Both of these come from the test run above.

Renamed columns and dropped tables

ALTER TABLE … RENAME COLUMN renames the column “within all indexes, triggers, and views that reference the column” (ALTER TABLE). Dropping a table a view depends on is allowed, but the view then fails when you query it:

sqlite> CREATE VIEW country_list AS SELECT DISTINCT country FROM customers;
sqlite> ALTER TABLE customers RENAME COLUMN country TO country_code;
sqlite> .schema country_list
CREATE VIEW country_list AS SELECT DISTINCT country_code FROM customers
/* country_list(country_code) */;
sqlite> CREATE TABLE tmp_t (a INTEGER); CREATE VIEW tmp_v AS SELECT a FROM tmp_t; DROP TABLE tmp_t;
sqlite> SELECT * FROM tmp_v;
Error: in prepare, no such table: main.tmp_t

Performance and materialized views

The query planner can fold a view into the outer query (query flattening), so indexes on the base tables still apply. Here both lookups use the primary key:

sqlite> EXPLAIN QUERY PLAN SELECT * FROM paid_orders WHERE order_id = 4;
QUERY PLAN
|--SEARCH o USING INTEGER PRIMARY KEY (rowid=?)
`--SEARCH c USING INTEGER PRIMARY KEY (rowid=?)

SQLite has no materialized views. When a view is too slow to run on every read, store its result in a table with CREATE TABLE … AS SELECT, and rebuild that table when the data changes:

sqlite> CREATE TABLE customer_revenue_snapshot AS SELECT c.id, c.name, sum(o.total) AS revenue FROM customers c JOIN orders o ON o.customer_id = c.id WHERE o.status = 'paid' GROUP BY c.id;
sqlite> SELECT * FROM customer_revenue_snapshot;
id  name   revenue
--  -----  -------
1   Ada    160.5
2   Linus  60.0
3   Grace  210.0

Conclusion

Use a view to give a complex query a name, so every report runs the same SQL. Name its columns, and change it with DROP VIEW plus CREATE VIEW in one transaction. Add an INSTEAD OF trigger only when callers have to write through the view. For a slow view that’s read often, a snapshot table is the closest thing to a materialized view.