Eric Campbell 5 min read SQLite

How to Use Views in SQLite

Views in SQLite are named queries stored in your database that act as virtual tables. Unlike physical tables, views don't store data themselves—they dynamically execute their underlying SELECT...

Key Insights

  • Views are virtual tables that encapsulate SELECT queries, providing data abstraction and simplifying complex operations without storing duplicate data
  • SQLite views can be queried like regular tables but have strict updateability constraints—only simple single-table views without aggregates or joins support direct modifications
  • Views don’t improve query performance on their own since SQLite expands them inline, but they significantly enhance code maintainability and security by centralizing query logic

Introduction to SQLite Views

Views in SQLite are named queries stored in your database that act as virtual tables. Unlike physical tables, views don’t store data themselves—they dynamically execute their underlying SELECT statement whenever queried. This makes them powerful tools for simplifying application code, enforcing security boundaries, and maintaining consistent data access patterns.

The primary benefits of views include query simplification (encapsulating complex joins and calculations), security (exposing only specific columns to certain users), and abstraction (hiding schema changes from application code). Use views when you repeatedly execute the same complex query, need to present data differently than it’s stored, or want to restrict access to sensitive columns. Stick with regular tables when you need to store actual data, require frequent updates, or need the performance benefits of physical storage and indexing.

Creating Basic Views

The fundamental syntax for creating a view is straightforward: CREATE VIEW view_name AS SELECT .... Let’s start with practical examples using a sample database.

-- Create sample tables
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    department TEXT,
    salary INTEGER,
    hire_date TEXT,
    ssn TEXT
);

CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    budget INTEGER
);

-- Insert sample data
INSERT INTO employees VALUES 
    (1, 'Alice Johnson', 'Engineering', 95000, '2020-03-15', '123-45-6789'),
    (2, 'Bob Smith', 'Sales', 75000, '2019-07-22', '234-56-7890'),
    (3, 'Carol White', 'Engineering', 105000, '2018-01-10', '345-67-8901');

Now create a basic view that selects specific columns:

-- View excluding sensitive information
CREATE VIEW employee_directory AS
SELECT id, name, department, hire_date
FROM employees;

-- Query the view
SELECT * FROM employee_directory;

This view hides the salary and ssn columns, making it safe to expose to broader audiences. Views with filtering are equally simple:

-- View with WHERE clause
CREATE VIEW engineering_team AS
SELECT name, salary, hire_date
FROM employees
WHERE department = 'Engineering';

-- View with column aliases for clarity
CREATE VIEW employee_summary AS
SELECT 
    name AS employee_name,
    department AS dept,
    salary / 12 AS monthly_salary
FROM employees;

Column aliases in views are particularly useful because they become the actual column names when querying the view, allowing you to rename calculated fields or clarify ambiguous names.

Working with Complex Views

Views shine when dealing with complex queries that you’d otherwise repeat throughout your application. Here’s where they become indispensable.

-- View with JOIN combining multiple tables
CREATE VIEW department_roster AS
SELECT 
    e.name AS employee_name,
    e.salary,
    d.name AS department_name,
    d.budget AS department_budget
FROM employees e
JOIN departments d ON e.department = d.name;

-- View with aggregate functions
CREATE VIEW department_stats AS
SELECT 
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary,
    SUM(salary) AS total_payroll
FROM employees
GROUP BY department;

These views transform multi-table operations into simple SELECT statements. Query them like any table:

SELECT * FROM department_roster WHERE department_budget > 500000;
SELECT * FROM department_stats ORDER BY avg_salary DESC;

Subqueries within views add another layer of sophistication:

-- View with subquery to show employees earning above average
CREATE VIEW high_earners AS
SELECT name, salary, department
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

You can even create views based on other views (nested views):

-- View based on another view
CREATE VIEW top_engineering_earners AS
SELECT employee_name, monthly_salary
FROM employee_summary
WHERE dept = 'Engineering'
ORDER BY monthly_salary DESC
LIMIT 5;

While nested views work perfectly fine, be cautious about creating deep hierarchies—they can make debugging difficult and obscure the actual data sources.

Querying and Managing Views

Once created, views behave like read-only tables for most purposes. You query them with standard SELECT syntax:

-- Standard queries work on views
SELECT * FROM employee_directory WHERE name LIKE 'A%';

SELECT department, COUNT(*) 
FROM employee_directory 
GROUP BY department;

To see all views in your database, query the sqlite_master system table:

-- List all views
SELECT name, sql 
FROM sqlite_master 
WHERE type = 'view';

Understanding how SQLite processes views is crucial for performance tuning. Use EXPLAIN QUERY PLAN to see how SQLite expands the view:

EXPLAIN QUERY PLAN 
SELECT * FROM department_stats WHERE employee_count > 2;

This shows that SQLite essentially inlines the view definition, executing the underlying query directly rather than materializing an intermediate result.

To modify or remove views:

-- Drop a view
DROP VIEW IF EXISTS employee_directory;

-- SQLite doesn't support CREATE OR REPLACE, so drop and recreate
DROP VIEW IF EXISTS employee_directory;
CREATE VIEW employee_directory AS
SELECT id, name, department, hire_date, salary
FROM employees;

Updatable Views and Limitations

SQLite supports updating views, but with strict limitations. A view is updatable only if it meets all these criteria:

  • Contains columns from a single table
  • Doesn’t use DISTINCT, GROUP BY, HAVING, LIMIT, or aggregate functions
  • Doesn’t use UNION, INTERSECT, or EXCEPT
  • The FROM clause contains only one table (no joins or subqueries)

Here’s a simple updatable view:

-- This view is updatable
CREATE VIEW active_employees AS
SELECT id, name, department, salary
FROM employees
WHERE hire_date >= '2019-01-01';

-- These operations work
INSERT INTO active_employees (id, name, department, salary)
VALUES (4, 'David Brown', 'Sales', 80000);

UPDATE active_employees 
SET salary = 98000 
WHERE name = 'Alice Johnson';

DELETE FROM active_employees WHERE id = 4;

However, most practical views aren’t updatable:

-- NOT updatable: contains aggregate
CREATE VIEW dept_averages AS
SELECT department, AVG(salary) as avg_sal
FROM employees
GROUP BY department;

-- This will fail
UPDATE dept_averages SET avg_sal = 90000 WHERE department = 'Sales';

For complex views that need update capabilities, use INSTEAD OF triggers:

CREATE VIEW employee_dept_view AS
SELECT e.id, e.name, e.salary, d.name AS dept_name
FROM employees e
JOIN departments d ON e.department = d.name;

-- Make it updatable with triggers
CREATE TRIGGER update_employee_dept
INSTEAD OF UPDATE ON employee_dept_view
BEGIN
    UPDATE employees 
    SET name = NEW.name, salary = NEW.salary
    WHERE id = NEW.id;
END;

Now updates to employee_dept_view trigger the custom logic, allowing you to control exactly what happens.

Performance Considerations and Best Practices

Views don’t inherently improve or degrade performance—SQLite expands them inline during query execution. This means querying a view has essentially the same performance as executing its underlying SELECT directly:

-- These have identical performance
SELECT * FROM employee_directory WHERE department = 'Sales';

SELECT id, name, department, hire_date 
FROM employees 
WHERE department = 'Sales';

However, views can indirectly affect performance in several ways:

Positive impacts: Views centralize query logic, making it easier to optimize one definition rather than dozens of scattered queries. They also encourage consistent access patterns, which helps with query plan caching.

Negative impacts: Overly complex views with multiple nested subqueries can obscure inefficient queries. If you’re not careful, you might SELECT from a view that joins five tables when you only need data from one.

Best practices for view usage:

  1. Name views descriptively: Use prefixes like v_ or suffixes like _view to distinguish them from tables
  2. Document complex views: Add comments explaining the business logic
  3. Avoid deep nesting: More than two levels of view-on-view makes debugging painful
  4. Index the underlying tables: Views benefit from indexes on the base tables they query
  5. Use views for security: Create restricted views for different user roles instead of exposing raw tables
  6. Test view performance: Always EXPLAIN QUERY PLAN on views used in hot paths

Remember that SQLite doesn’t support materialized views natively. If you need cached results, create a regular table and populate it with a trigger or scheduled job.

Views are one of SQLite’s most underutilized features. They won’t solve every problem, but when used judiciously, they dramatically improve code maintainability and security without sacrificing the simplicity that makes SQLite attractive in the first place.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.