Table of contents
Views
Views allow you to create virtual collections backed by custom SQL queries. This enables powerful data aggregation, joining across collections, and creating read-only derived data sources. Views are configured on collections.
Note
through views.
Defining Views
Views are declared in the IaC YAML (see IaC) and applied with
vski apply. SQL views live in the views list; filter/sort views live in
options.views.
Using Raw SQL
# vski/default/collections.yaml
version: 1
database: default
collections:
posts:
type: base
fields:
- {name: title, type: text, required: true}
- {name: content, type: text}
- {name: published, type: bool}
- {name: views, type: number}
views:
- name: posts_view_published_posts
sql: "SELECT id, title, content, created, updated
FROM posts
WHERE published = true
ORDER BY created DESC"
- name: posts_view_popular_posts
sql: "SELECT id, title, content, views
FROM posts
WHERE views > 100
ORDER BY views DESC"
name is the full materialized view name (<collection>_view_<slug>); the
slug is derived from it and is what you query (published_posts for
posts_view_published_posts).
Using Filter and Sort
Simpler variant, no SQL: filter/sort views are part of the collection options and applied as a collection update.
collections:
posts:
type: base
fields:
- {name: title, type: text, required: true}
- {name: content, type: text}
- {name: published, type: bool}
options:
views:
recent_posts:
page: 1
perPage: 10
filter: "published = true"
sort: "-created"
top_viewed:
page: 1
perPage: 10
filter: "published = true"
sort: "-views"
The filter and sort syntax is the same as used in collection queries.
Use SQL views for:
- Complex queries with joins
- Aggregations and grouping
- Custom transformations
- Union operations across collections
Use filter/sort views for:
- Simple filtered lists
- Sorted results
- Common query patterns
- Basic pagination
Querying Views
Views are queried with get_view() on a collection:
results = await client.collection("posts").get_view("published_posts", page=1, per_page=20)
# additional filtering on top of the view query
filtered = await client.collection("posts").get_view(
"published_posts", page=1, per_page=20, filter="views > 100"
)
# additional sorting on top of the view query
sorted_ = await client.collection("posts").get_view(
"published_posts", page=1, per_page=20, sort="-created"
)
# expand related records
expanded = await client.collection("posts").get_view(
"published_posts", page=1, per_page=20, expand="author"
)
View Management
From the command line: vski collections show posts lists views; remove a view by
deleting it from the YAML and applying. Imperatively, from the SDK:
views = await client.settings.collections.get_views("posts")
print(views["views"])
await client.settings.collections.drop_view("posts", "posts_view_published_posts")
Practical Examples
Joins Between Collections
collections:
posts:
type: base
fields:
- {name: title, type: text, required: true}
- {name: authorId, type: text, required: true}
views:
- name: posts_view_posts_with_authors
sql: "SELECT p.id, p.title, p.authorId, u.name as author_name, u.email as author_email
FROM posts p
LEFT JOIN users u ON p.authorId = u.id"
Aggregated Analytics
collections:
posts:
type: base
fields:
- {name: title, type: text, required: true}
- {name: category, type: text}
- {name: views, type: number}
views:
- name: posts_view_post_stats
sql: "SELECT category,
COUNT(*) as total_posts,
AVG(views) as avg_views,
MAX(views) as max_views,
SUM(views) as total_views
FROM posts
GROUP BY category
ORDER BY total_posts DESC"
Time-Based Reports
collections:
posts:
type: base
fields:
- {name: title, type: text, required: true}
views:
- name: posts_view_monthly_posts
sql: "SELECT strftime('%Y-%m', created) as month, COUNT(*) as post_count
FROM posts
GROUP BY month
ORDER BY month DESC"
Union Across Collections
collections:
posts:
type: base
fields:
- {name: title, type: text, required: true}
views:
- name: posts_view_all_content
sql: "SELECT 'post' as type, id, title, created FROM posts
UNION ALL
SELECT 'page' as type, id, title, created FROM pages
UNION ALL
SELECT 'article' as type, id, title, created FROM articles"
Dashboard Analytics
collections:
posts:
type: base
fields:
- {name: title, type: text, required: true}
views:
- name: posts_view_dashboard_analytics
sql: "SELECT 'posts' as metric, COUNT(*) as value FROM posts
UNION ALL
SELECT 'users' as metric, COUNT(*) as value FROM _users
UNION ALL
SELECT 'comments' as metric, COUNT(*) as value FROM comments"
View Performance
Indexes for Views
Views benefit from indexes on the underlying tables; declare both together:
collections:
posts:
type: base
fields:
- {name: title, type: text, required: true}
- {name: published, type: bool}
indexes:
- {name: posts_published_idx, sql: "CREATE INDEX posts_published_idx ON posts(published)"}
- {name: posts_created_idx, sql: "CREATE INDEX posts_created_idx ON posts(created)"}
views:
- name: posts_view_published_posts
sql: "SELECT id, title, created
FROM posts
WHERE published = true
ORDER BY created DESC"
View Optimization Tips
- Use specific columns: only select needed columns in view SQL
- Add WHERE clauses: filter data as early as possible
- Use indexes: ensure underlying tables have proper indexes
- Limit results: use pagination with
pageandper_pageinget_view() - Avoid complex joins: keep view queries simple when possible
- Consider caching results: for frequently accessed data
- Use filter/sort views when possible: more efficient than raw SQL for simple cases
View Limitations
Views are read-only virtual collections with the following limitations:
- Cannot create records through views
- Cannot update records through views
- Cannot delete records through views
- Views are recalculated on each query
- Views don't have their own indexes (they use underlying table indexes)
What Views CAN Do
- Query data from multiple tables with joins
- Aggregate data with GROUP BY and functions
- Filter and sort data
- Perform unions across collections
- Apply transformations and calculations
API Endpoints
Views are managed via the collection API:
| Method | Endpoint | Description |
|---|---|---|
GET |
/api/collections/:name/views |
List all views for collection |
DELETE |
/api/collections/:name/views/:viewName |
Drop a view |
Views are queried via get_view(), which internally calls:
| Method | Endpoint | Description |
|---|---|---|
GET |
/api/collections/:name/records/views/:slug |
Query records from a view |
Best Practices
- Use descriptive view names: make view purposes clear (e.g.,
posts_view_published_posts,posts_view_monthly_stats) - Document complex queries: add comments to complex view SQL
- Test view performance: monitor view query execution time
- Use appropriate indexes: create indexes on underlying tables
- Keep views simple: avoid overly complex queries when possible
- Consider read-only: remember views cannot be modified
- Version control: the YAML is the version control
- Monitor usage: track which views are used most frequently
Troubleshooting
View Not Found
If you get a "view not found" error:
- Check that the view is defined in the YAML (or collection options)
- Verify the slug matches exactly (case-sensitive)
- Ensure the collection exists
Slow View Performance
If views are slow:
- Check for missing indexes on underlying tables
- Simplify the view SQL if possible
- Add explicit filtering to reduce result set
- Consider using pagination to limit results
Permission Errors
Views respect the same rules as the parent collection:
- If the collection has a
listrule, non-admin users must satisfy it - Admin users bypass all rules regardless of view configuration
- Introduction
- Performance
- Getting Started
- Coding Agents
- Command Line
- Infra as Code
- CI/CD Guide
- Project Boilerplate
- SDKs
- Authentication
- Databases
- Collections
- Gated Queries
- Graphs
- Rules and Access Control
- Granular Access Control
- Triggers
- Plugins
- Containers Plugin
- vski mcp
- Realtime
- Full Text Search
- Vector Embeddings
- KWH
- Workflows
- Pipelines
- Dashboard Widgets
- Connectors
- Cron
- Webhooks
- Gates
- Migrations
- Replication
- File Sync
- Backup
- Distributed Realtime
- Tutorial: ReactJS Todo
- Tutorial: Graph-Memory Agent
- Tutorial: Image Gallery
- Tutorial: Embeddings
- Tutorial: Graphs
- Recipe: Calendar Timetree
- Tutorial: Plugins
- Tutorial: MCP Server