Views

A VIEW is a saved SELECT query that you can query again by name, as if it were a table. It doesn't store any data of its own — every time you query a view, SQLite substitutes in its underlying SELECT and runs it fresh. Views are a great way to hide a complicated join or aggregation behind a simple, reusable name.

CREATE VIEW

The syntax mirrors a normal query, just wrapped with a name:

CREATE VIEW view_name AS
SELECT ...
FROM ...;

RecipeBox uses a view to combine each recipe with its average rating and review count, so application code never has to repeat that JOIN/ GROUP BY logic:

CREATE VIEW recipe_ratings AS
SELECT
  r.id,
  r.title,
  COUNT(rv.id) AS review_count,
  ROUND(AVG(rv.rating), 1) AS avg_rating
FROM recipes r
LEFT JOIN reviews rv ON rv.recipe_id = r.id
GROUP BY r.id;

Querying a View Like a Table

Once created, recipe_ratings can be selected from, filtered, and sorted exactly like a real table:

SELECT title, avg_rating, review_count
FROM recipe_ratings
WHERE avg_rating >= 4.0
ORDER BY avg_rating DESC;

Notice how much simpler this is to write and read than repeating the join and aggregation every time the app needs "recipes with their rating" — the view captures that logic once.

DROP VIEW

Removing a view only removes the saved query definition — it never touches the underlying tables or their data:

DROP VIEW IF EXISTS recipe_ratings;

Views Are Not Materialized

This is the detail that trips people up coming from other tools: a SQLite view is not a cache. It stores no rows and builds nothing in advance — every single query against recipe_ratings re-runs the full underlying JOIN and GROUP BY against the live recipes and reviews tables at that moment. That means:

In short: a view is a naming convenience and a readability tool, not a performance optimization. If recipe_ratings is queried very often against a large reviews table, the underlying JOIN still runs every time — proper indexes (covered soon in this track) are what actually speed that up.