> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify.com/pocketbase/pocketbase/llms.txt
> Use this file to discover all available pages before exploring further.

# 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

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

## Creating a view collection

Create a view collection with a SQL query:

```go theme={null}
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
`
```

<Note>
  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).
</Note>

## Query requirements

Your SQL query must follow these rules:

<Steps>
  <Step title="Include an id column">
    Every view must have an `id` column for PocketBase to identify records.

    ```sql theme={null}
    -- 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
    ```
  </Step>

  <Step title="Use valid SQLite syntax">
    PocketBase uses SQLite, so your queries must be valid SQLite SQL.

    ```sql theme={null}
    -- Good: SQLite functions
    SELECT id, UPPER(name) as name FROM users

    -- Bad: PostgreSQL-specific syntax
    SELECT id, name::text FROM users
    ```
  </Step>

  <Step title="Reference collections by name">
    Always use the collection name (not the table name) in your queries.

    ```sql theme={null}
    -- Correct
    SELECT * FROM users WHERE status = 'active'

    -- Also correct with backticks
    SELECT * FROM `users` WHERE `status` = 'active'
    ```
  </Step>

  <Step title="Be mindful of performance">
    Complex queries can impact performance. Add indexes to source collections.

    ```sql theme={null}
    -- This might be slow without indexes
    SELECT * FROM users u
    JOIN posts p ON p.author = u.id
    WHERE p.created > '2024-01-01'
    ```
  </Step>
</Steps>

## Field inference

PocketBase automatically infers field types from your query:

```go theme={null}
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
`
```

<Info>
  You don't manually define fields for view collections. They're generated from the SQL query when you save the collection.
</Info>

## Complete examples

### User statistics view

```go theme={null}
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

```go theme={null}
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

```go theme={null}
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 theme={null}
// 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:

```javascript theme={null}
// 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:

```go theme={null}
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
}
```

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

## Limitations

<Warning>
  Be aware of these limitations when using view collections:
</Warning>

* **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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

<Tabs>
  <Tab title="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
  </Tab>

  <Tab title="Maintenance">
    * Document what each view represents
    * Use clear, descriptive column aliases
    * Keep related views in the same migration
    * Version your view queries
  </Tab>

  <Tab title="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
  </Tab>
</Tabs>

## Next steps

<CardGroup cols={2}>
  <Card title="Base collections" icon="database" href="/collections/base-collections">
    Learn about standard collections
  </Card>

  <Card title="Auth collections" icon="user-lock" href="/collections/auth-collections">
    Add user authentication
  </Card>

  <Card title="API rules" icon="shield" href="/concepts/api-rules">
    Secure your collections
  </Card>

  <Card title="Indexes" icon="bolt" href="/concepts/records">
    Optimize query performance
  </Card>
</CardGroup>