SQLite Views: A Complete Guide to Using and Managing Views

Views are virtual tables that provide custom query results based on one or more underlying base tables in a database. Views don’t store data themselves but instead run a query and show the result set as a table. Views are useful for simplifying complex queries, restricting access to data, and encapsulating reusable queries.

This guide will cover all aspects of using and managing views in SQLite.

Creating Views in SQLite

Let’s look at how to create a basic view in SQLite.

First, we’ll create a table called employees and insert some data:

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT, 
  salary INTEGER
);

INSERT INTO employees VALUES
  (1, 'John', 50000),
  (2, 'Jane', 60000), 
  (3, 'Bob', 45000);

To create a simple view that shows employee names and salaries, we’ll use the SELECT statement:

CREATE VIEW employee_salaries AS
SELECT name, salary FROM employees;

We can query the view just like a regular table:

SELECT * FROM employee_salaries;

name | salary
John | 50000
Jane | 60000
Bob  | 45000

This view acts as a filtered version of the employees table, showing only certain columns.

We can create more complex views by using JOIN statements across multiple tables. For example:

CREATE VIEW employee_locations AS
SELECT name, state 
FROM employees
JOIN locations ON employees.location_id = locations.id;

This joins the employees and locations tables to show employee names and their state location.

Views are powerful for simplifying complex multi-table queries into a reusable virtual table.

Also read: SQLite Show Tables

Updating Views in SQLite

By default, views in SQLite are read-only – you can query them but not update them. To make a view updatable, it must meet two criteria:

1. The view is based on a single base table.
2. All columns in the view directly reference columns in the base table.

For example, this single-table view on employees meets the criteria:

CREATE VIEW employee_names AS
SELECT id, name FROM employees;

We can update it like a normal table:

UPDATE employee_names SET name = 'Susan' WHERE id = 2;

The underlying base employees table will be updated.

However, the multiple table view we created earlier cannot be updated:

CREATE VIEW employee_locations AS
SELECT name, state 
FROM employees
JOIN locations ON employees.location_id = locations.id; 

Updating this view would affect multiple tables, so SQLite disallows it.

Dropping Views in SQLite

When you no longer need a view, use DROP VIEW to delete it:

DROP VIEW employee_locations; 

The underlying base tables are not affected when a view is dropped.

Also read: SQLite DROP TABLE

SQLite View Management Tips

Here are some useful tips for managing views in SQLite:

• Give views meaningful names describing the data they represent.
• Avoid heavy use of views that reduce database performance.
• Periodically review and clean up unused views.
• Use views to restrict access to tables or columns.
• Build views on top of other views to transform data further.

Summary

SQLite views provide a powerful way to customize table data into reusable virtual tables. Views can simplify complex queries, implement security rules, and encapsulate queries for reuse. With the right approach, views are indispensable to working with SQLite databases.