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: Views are read-only. You cannot create, update, or delete records 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

1. Use specific columns: only select needed columns in view SQL
2. Add WHERE clauses: filter data as early as possible
3. Use indexes: ensure underlying tables have proper indexes
4. Limit results: use pagination with page and per_page in get_view()
5. Avoid complex joins: keep view queries simple when possible
6. Consider caching results: for frequently accessed data
7. 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

1. Use descriptive view names: make view purposes clear (e.g., posts_view_published_posts, posts_view_monthly_stats)
2. Document complex queries: add comments to complex view SQL
3. Test view performance: monitor view query execution time
4. Use appropriate indexes: create indexes on underlying tables
5. Keep views simple: avoid overly complex queries when possible
6. Consider read-only: remember views cannot be modified
7. Version control: the YAML is the version control
8. Monitor usage: track which views are used most frequently

Troubleshooting

View Not Found

If you get a “view not found” error:

1. Check that the view is defined in the YAML (or collection options)
2. Verify the slug matches exactly (case-sensitive)
3. Ensure the collection exists

Slow View Performance

If views are slow:

1. Check for missing indexes on underlying tables
2. Simplify the view SQL if possible
3. Add explicit filtering to reduce result set
4. Consider using pagination to limit results

Permission Errors

Views respect the same rules as the parent collection:

• If the collection has a list rule, non-admin users must satisfy it
• Admin users bypass all rules regardless of view configuration