SQLite Views Explained with Examples

The same filter written in the application, in a report, and in a maintenance script drifts apart the first time the rule behind it changes. CREATE VIEW stores the query in the database and gives it a name, so all three read the one definition and the rule lives in a single place. The examples run in the sqlite3 shell against one table:

CREATE TABLE Employees(id INTEGER PRIMARY KEY, name TEXT, role TEXT);

INSERT INTO Employees(name, role) VALUES
    ('John Doe', 'Manager'),
    ('Jane Smith', 'Developer'),
    ('David Gray', 'Developer'),
    ('Mia Brown', 'Sales'),
    ('Max Green', 'Developer');

What a view is in SQLite

A view is a stored SELECT that behaves like a table when you read from it. It holds no rows of its own: SQLite runs the stored statement each time the view is queried, so the answer always reflects the current contents of the base tables. What the view stores is text, kept in the schema table alongside the tables and indexes, where its type column reads view. The general shape of views across engines is covered in SQL views explained; the rest of this article is what SQLite does with them.

Three parts of CREATE VIEW are worth knowing before you write one. IF NOT EXISTS makes a second run of the script a no-op instead of an error. TEMP creates a view visible only to the database connection that created it and automatically deleted when that connection closes, which suits a reporting session that should leave nothing behind. And a column-name list after the view name determines the names of the columns for the view, instead of letting SQLite derive them from the result-set columns of the query.

What views are used for

A view hides a query, not data. A three-table join with two conditions becomes one name that the application selects from, and the join stays in the database where it can be fixed once. A view over a subset of columns is how a connection gets the columns it needs and no others, which matters in a SQLite file that also holds a salary or a token column. And a view is a stable interface over a schema that is still moving: rename a column in the base table, adjust the view, and the queries that read the view keep working.

The cost is that the query still runs. A view over an expensive join is exactly as expensive as the join, once per read, since nothing is stored. Where the same aggregate is read hundreds of times a minute, a table maintained by a trigger, as described in SQLite CREATE TRIGGER, is the cheaper answer.

Create a view in sqlite3

Open the database file, create the table above, then store the query that picks out the developers:

sqlite3 TestDB.db

CREATE VIEW Developers AS
SELECT name FROM Employees WHERE role = 'Developer';

Read it exactly as you would read a table:

SELECT * FROM Developers;

The view returns one column, because the stored SELECT asks for one. Add a row to Employees with the role Developer and it appears in the next read of the view without anything being rebuilt. A view can also be read with a WHERE clause of its own, joined to a table, or used inside another view, which is how a chain of report definitions gets built without repeating the filter at the bottom of it.

To see which views a file holds, read the schema table, whose sql column returns each definition as it was written:

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

That query is worth running before a deployment. A view is usually created by a setup script rather than by a reviewed migration, so the definition in the production file and the one in your repository can disagree without anyone noticing. Comparing the stored text with the version in Git settles it in a second.

What a view can and cannot do in SQLite

The rule that catches everyone: you cannot DELETE, INSERT, or UPDATE a view, since views are read-only in SQLite. Send a row to Developers and the statement is rejected:

INSERT INTO Developers(name) VALUES ('Nina Patel');

cannot modify Developers because it is a view

The read-only rule is documented; the wording of the message is not, and this one comes from SQLite 3.50.4.

The documented way around the rule is an INSTEAD OF trigger on the view, which catches the write and turns it into statements against the base tables. That is the only kind of trigger a view accepts, and the only way a write to a view reaches the data.

Removing a view is DROP VIEW, and it takes IF EXISTS, which turns a missing view into a no-op instead of an error:

DROP VIEW IF EXISTS Developers;

Dropping the view removes the definition from the schema and modifies no data in the underlying base tables. Do not run it on a production file before the definition is in your repository, because the statement takes the only copy with it. Dropping a base table is the case to watch, because the view survives it:

DROP TABLE Employees;

The definition stays in the schema, so the failure comes at the next read of the view, again with the message text of SQLite 3.50.4:

SELECT * FROM Developers;

no such table: main.Employees

Creating a view in DbSchema

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

CREATE VIEW is SQL, so DbSchema runs it the way the shell does. Choose Connect to Database, pick SQLite, and give the Connection Dialog the path to the file. Open the SQL Editor from the Editors menu, paste the definition, and press Execute Query. The statement goes to the SQLite database; the editor itself is saved inside the .dbs model file, so the definition is still in the project the next time you open it.

Reverse engineering in DbSchema brings the views back with the tables. The Project Structure panel lists schemas, tables, views, procedures, indexes, constraints, and your saved diagrams, and a right-click on any of them opens its data, edits its structure, or adds it to a diagram.

A view on the diagram is where DbSchema adds something the file does not have. SQLite declares no foreign key between a view and the tables it reads, so nothing links them on the canvas. A virtual foreign key is a relationship that exists only in the project file, and the diagram documentation names linking views as one of the cases it is for. Drawn between the view and its base table, it behaves like a regular foreign key line for data browsing and query building, and it modifies nothing in the database.

Write the query once, store it as a view, and let the read side of your application forget the filter. Download DbSchema at https://dbschema.com/download.html, open the SQLite file, and put the view and its base table on one diagram with a virtual foreign key between them: connecting, reverse engineering, the interactive diagrams, and the SQL editor are in the free Community Edition, while browsing the data across those relations and saving the model to a file are in Pro.

Sources

1. SQLite documentation: CREATE VIEW
2. SQLite documentation: DROP VIEW
3. SQLite documentation: The schema table