SQLite - Views

SQLite - Views

A View is a virtual table based on the result-set of an SQL statement. Think of it as a stored, named query. It contains rows and columns, just like a real table, but it doesn’t store data itself. The data is generated dynamically when the view is queried.

Views are a powerful tool for modern database design, allowing you to:

• Simplify Complexity: Abstract complex multi-table joins and calculations into a single, simple-to-query interface.
• Enhance Security: Restrict data access by exposing only specific columns or rows to users, hiding sensitive information.
• Provide a Stable API: Maintain a consistent data structure for applications, even if the underlying base tables are refactored.
• Summarize Data: Create aggregated views for reporting and analytics purposes.

Creating Views

Views are created with the CREATE VIEW statement. The TEMP or TEMPORary keyword can be used to create a view that only exists for the duration of the current database connection.

Basic Syntax:

CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE [condition];

Example: Creating a View for Active Employees

Imagine an EMPLOYEES table with many columns. We want to create a simple view to show only the name and department of currently active employees.

-- First, let's define our base table and some data
CREATE TABLE EMPLOYEES (
  ID INTEGER PRIMARY KEY,
  NAME TEXT NOT NULL,
  DEPARTMENT TEXT NOT NULL,
  SALARY REAL,
  STATUS TEXT DEFAULT 'Active' -- 'Active' or 'Terminated'
);

INSERT INTO EMPLOYEES (NAME, DEPARTMENT, SALARY, STATUS)
VALUES
('Alice', 'Engineering', 90000, 'Active'),
('Bob', 'Marketing', 65000, 'Terminated'),
('Charlie', 'Engineering', 95000, 'Active');

-- Now, create the view
CREATE VIEW v_active_engineers AS
SELECT NAME, DEPARTMENT
FROM EMPLOYEES
WHERE STATUS = 'Active' AND DEPARTMENT = 'Engineering';

You can now query this view just like a regular table:

sqlite> SELECT * FROM v_active_engineers;

This produces the following result:

NAME      DEPARTMENT
--------  ----------
Alice     Engineering
Charlie   Engineering

Updatable Views and INSTEAD OF Triggers

By default, a view is only updatable (allowing INSERT, UPDATE, DELETE) if it’s simple: it must select from a single table and not use aggregate functions (GROUP BY, DISTINCT, etc.).

For complex, read-only views, you can define custom logic for INSERT, UPDATE, or DELETE operations using an INSTEAD OF trigger. This trigger executes your custom logic instead of the attempted operation on the view.

Example: Allowing deletion from our view.

CREATE TRIGGER trg_delete_active_engineer
INSTEAD OF DELETE ON v_active_engineers
FOR EACH ROW
BEGIN
  -- Instead of deleting from the view, we update the base table
  UPDATE EMPLOYEES
  SET STATUS = 'Terminated'
  WHERE NAME = OLD.NAME;
END;

-- Now, this operation will succeed
sqlite> DELETE FROM v_active_engineers WHERE NAME = 'Alice';

-- Verify the change in the base table
sqlite> SELECT NAME, STATUS FROM EMPLOYEES WHERE NAME = 'Alice';
NAME      STATUS
--------  ----------
Alice     Terminated

Dropping Views

To remove a view, use the DROP VIEW statement. You can add IF EXISTS to prevent an error if the view doesn’t exist.

sqlite> DROP VIEW IF EXISTS v_active_engineers;