View collections

Read-only collections based on SQL queries for complex data aggregations and joins

View collections are special read-only collections backed by SQL queries. They’re perfect for complex data aggregations, joins, and computed fields without duplicating data.

When to use view collections

Use view collections when you need to:

• Aggregate data from multiple collections
• Create computed fields based on existing data
• Join related data for easier querying
• Generate reports and statistics
• Denormalize data for read performance
• Expose complex SQL queries through the REST API

View collections are read-only. You cannot create, update, or delete records through the API. To modify data, update the underlying source collections.

Creating a view collection

Create a view collection with a SQL query:

stats := core.NewViewCollection("user_stats")
stats.ViewQuery = `
    SELECT
        users.id,
        users.email,
        users.username,
        COUNT(DISTINCT posts.id) as total_posts,
        COUNT(DISTINCT comments.id) as total_comments,
        MAX(posts.created) as last_post_date
    FROM users
    LEFT JOIN posts ON posts.author = users.id
    LEFT JOIN comments ON comments.author = users.id
    GROUP BY users.id
`

The query must include an id column. If your query doesn’t naturally have one, use SQL to create it (e.g., ROW_NUMBER() or alias an existing unique column).

Query requirements

Your SQL query must follow these rules:

1. Include an id column

   Every view must have an id column for PocketBase to identify records.

   -- Using existing ID
   SELECT users.id, users.name FROM users
   
   -- Creating an ID from multiple columns
   SELECT 
       (users.id || '_' || posts.id) as id,
       users.name,
       posts.title
   FROM users
   JOIN posts ON posts.author = users.id
   
2. Use valid SQLite syntax

   PocketBase uses SQLite, so your queries must be valid SQLite SQL.

   -- Good: SQLite functions
   SELECT id, UPPER(name) as name FROM users
   
   -- Bad: PostgreSQL-specific syntax
   SELECT id, name::text FROM users
   
3. Reference collections by name

   Always use the collection name (not the table name) in your queries.

   -- Correct
   SELECT * FROM users WHERE status = 'active'
   
   -- Also correct with backticks
   SELECT * FROM `users` WHERE `status` = 'active'
   
4. Be mindful of performance

   Complex queries can impact performance. Add indexes to source collections.

   -- This might be slow without indexes
   SELECT * FROM users u
   JOIN posts p ON p.author = u.id
   WHERE p.created > '2024-01-01'
   

Field inference

PocketBase automatically infers field types from your query:

stats := core.NewViewCollection("stats")
stats.ViewQuery = `
    SELECT
        id,                           -- TextField (primary key)
        email,                        -- TextField
        total_posts,                  -- NumberField
        is_active,                    -- BoolField
        created,                      -- DateField
        metadata                      -- JSONField
    FROM users
`

You don’t manually define fields for view collections. They’re generated from the SQL query when you save the collection.

Complete examples

User statistics view

package main

import (
    "log"
    "github.com/pocketbase/pocketbase"
    "github.com/pocketbase/pocketbase/core"
)

func main() {
    app := pocketbase.New()

    app.OnBootstrap().BindFunc(func(e *core.BootstrapEvent) error {
        stats := core.NewViewCollection("user_stats")
        
        stats.ViewQuery = `
            SELECT
                users.id as id,
                users.email,
                users.username,
                users.created as joined_date,
                COUNT(DISTINCT posts.id) as post_count,
                COUNT(DISTINCT comments.id) as comment_count,
                COALESCE(AVG(posts.views), 0) as avg_post_views,
                MAX(posts.created) as last_post_date
            FROM users
            LEFT JOIN posts ON posts.author = users.id
            LEFT JOIN comments ON comments.author = users.id
            GROUP BY users.id
        `
        
        // Set API rules (read-only)
        stats.ListRule = core.String("")
        stats.ViewRule = core.String("")
        
        if err := e.App.Save(stats); err != nil {
            return err
        }
        
        return e.Next()
    })

    if err := app.Start(); err != nil {
        log.Fatal(err)
    }
}

Product inventory view

inventory := core.NewViewCollection("product_inventory")

inventory.ViewQuery = `
    SELECT
        products.id,
        products.name,
        products.sku,
        products.price,
        COALESCE(SUM(stock.quantity), 0) as total_stock,
        COUNT(DISTINCT warehouses.id) as warehouse_count,
        CASE 
            WHEN COALESCE(SUM(stock.quantity), 0) = 0 THEN 'out_of_stock'
            WHEN COALESCE(SUM(stock.quantity), 0) < 10 THEN 'low_stock'
            ELSE 'in_stock'
        END as stock_status
    FROM products
    LEFT JOIN stock ON stock.product = products.id
    LEFT JOIN warehouses ON warehouses.id = stock.warehouse
    GROUP BY products.id
`

inventory.ListRule = core.String("@request.auth.role = 'admin' || @request.auth.role = 'staff'")
inventory.ViewRule = core.String("@request.auth.role = 'admin' || @request.auth.role = 'staff'")

Monthly sales report

salesReport := core.NewViewCollection("monthly_sales")

salesReport.ViewQuery = `
    SELECT
        (STRFTIME('%Y-%m', orders.created) || '-' || products.id) as id,
        STRFTIME('%Y-%m', orders.created) as month,
        products.id as product_id,
        products.name as product_name,
        COUNT(order_items.id) as total_orders,
        SUM(order_items.quantity) as total_quantity,
        SUM(order_items.quantity * order_items.price) as total_revenue
    FROM orders
    JOIN order_items ON order_items.order = orders.id
    JOIN products ON products.id = order_items.product
    WHERE orders.status = 'completed'
    GROUP BY STRFTIME('%Y-%m', orders.created), products.id
`

salesReport.ListRule = core.String("@request.auth.role = 'admin'")
salesReport.ViewRule = core.String("@request.auth.role = 'admin'")

Working with view collections

Querying views

View collections support all standard list/view operations:

// JavaScript SDK example
const records = await pb.collection('user_stats').getList(1, 50, {
    filter: 'post_count > 10',
    sort: '-post_count',
});

const topUser = await pb.collection('user_stats').getOne('user_id', {
    fields: 'username,post_count,comment_count',
});

Filtering and sorting

You can filter and sort on any field in the view:

// Get users with high engagement
const active = await pb.collection('user_stats').getList(1, 20, {
    filter: 'post_count >= 5 && comment_count >= 10',
    sort: '-last_post_date',
});

// Get low stock products
const lowStock = await pb.collection('product_inventory').getList(1, 50, {
    filter: 'stock_status = "low_stock"',
    sort: 'total_stock',
});

Updating view queries

To modify a view collection’s query:

view, err := app.FindCollectionByNameOrId("user_stats")
if err != nil {
    return err
}

// Update the query
view.ViewQuery = `
    SELECT
        users.id,
        users.username,
        COUNT(posts.id) as post_count,
        -- Add new computed field
        ROUND(AVG(posts.views), 2) as avg_views
    FROM users
    LEFT JOIN posts ON posts.author = users.id
    GROUP BY users.id
`

if err := app.Save(view); err != nil {
    return err
}

When you update a view query, PocketBase automatically regenerates the field definitions based on the new query results.

Limitations

Be aware of these limitations when using view collections:

• Read-only: No create, update, or delete operations
• No real-time subscriptions: Views don’t support real-time updates
• No file fields: Views cannot have file upload fields
• No relations from other collections: Other collections cannot have relation fields pointing to views (but views can reference any collection)
• Performance: Complex queries can be slow; optimize with indexes on source collections

Common patterns

Denormalized data for performance

-- Flatten nested relations for faster reads
SELECT
    posts.id,
    posts.title,
    posts.content,
    users.username as author_name,
    users.email as author_email,
    categories.name as category_name
FROM posts
JOIN users ON users.id = posts.author
JOIN categories ON categories.id = posts.category

Aggregated metrics

-- Calculate engagement scores
SELECT
    posts.id,
    posts.title,
    COUNT(DISTINCT likes.id) as like_count,
    COUNT(DISTINCT comments.id) as comment_count,
    COUNT(DISTINCT shares.id) as share_count,
    (
        COUNT(DISTINCT likes.id) * 1 +
        COUNT(DISTINCT comments.id) * 2 +
        COUNT(DISTINCT shares.id) * 3
    ) as engagement_score
FROM posts
LEFT JOIN likes ON likes.post = posts.id
LEFT JOIN comments ON comments.post = posts.id
LEFT JOIN shares ON shares.post = posts.id
GROUP BY posts.id

Time-based rollups

-- Daily activity summary
SELECT
    DATE(created) as id,
    DATE(created) as date,
    COUNT(DISTINCT user_id) as active_users,
    COUNT(*) as total_events,
    SUM(CASE WHEN event_type = 'login' THEN 1 ELSE 0 END) as logins,
    SUM(CASE WHEN event_type = 'signup' THEN 1 ELSE 0 END) as signups
FROM activity_logs
GROUP BY DATE(created)

Best practices

Performance

• Keep queries simple when possible
• Add indexes to frequently joined/filtered columns
• Use LEFT JOIN instead of subqueries when possible
• Avoid SELECT * - specify only needed columns
• Test query performance before deploying

Maintenance

• Document what each view represents
• Use clear, descriptive column aliases
• Keep related views in the same migration
• Version your view queries

Security

• Set appropriate API rules (views are read-only but can still be restricted)
• Don’t expose sensitive data in public views
• Use filters instead of creating multiple similar views

Next steps

Base collections

Learn about standard collections

Auth collections

Add user authentication

API rules

Secure your collections

Indexes

Optimize query performance