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 as options on collections.

Note

through views.

Creating Views

Views are defined in the options field when creating or updating a collection. There are two ways to define views:

Using Raw SQL

Define a view with a custom SQL query:

await client.settings.collections.create({
  name: "posts",
  type: "base",
  schema: [
    { name: "title", type: "text", required: true },
    { name: "content", type: "text" },
    { name: "published", type: "bool" },
    { name: "views", type: "number" },
  ],
  options: JSON.stringify({
    views: [
      {
        slug: "published_posts",
        sql: `
          SELECT
            id,
            title,
            content,
            created,
            updated
          FROM posts
          WHERE published = true
          ORDER BY created DESC
        `,
      },
      {
        slug: "popular_posts",
        sql: `
          SELECT
            id,
            title,
            content,
            views
          FROM posts
          WHERE views > 100
          ORDER BY views DESC
        `,
      },
    ],
  }),
});

Using Filter and Sort

Define a view using filter and sort parameters (simpler option):

await client.settings.collections.create({
  name: "posts",
  type: "base",
  schema: [
    { name: "title", type: "text", required: true },
    { name: "content", type: "text" },
    { name: "published", type: "bool" },
    { name: "created", type: "text" },
  ],
  options: JSON.stringify({
    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.

Querying Views

Views are queried using the getView() method on collections:

// List view results
const results = await client.collection("posts").getView("published_posts", {
  page: 1,
  perPage: 20,
});

// Filter views (additional filtering on top of view query)
const filtered = await client.collection("posts").getView("published_posts", {
  page: 1,
  perPage: 20,
  filter: "views > 100",
});

// Sort views (additional sorting on top of view query)
const sorted = await client.collection("posts").getView("published_posts", {
  page: 1,
  perPage: 20,
  sort: "-created",
});

// Expand related records
const expanded = await client.collection("posts").getView("published_posts", {
  page: 1,
  perPage: 20,
  expand: "author",
});

View Management

List All Views

const views = await client.settings.collections.getViews("posts");

console.log(views.views);
// [
//   { name: "posts_view_published_posts", slug: "published_posts", sql: "SELECT ..." },
//   { name: "posts_view_recent_posts", slug: "recent_posts", filter: "...", sort: "..." },
// ]

Drop a View

await client.settings.collections.dropView(
  "posts",
  "posts_view_published_posts",
);

View Definition Methods

Views can be defined in two ways:

1. Raw SQL Views

Define a view with a custom SQL query. This gives you full control over the query logic:

await client.settings.collections.create({
  name: "posts",
  type: "base",
  schema: [
    { name: "title", type: "text", required: true },
    { name: "authorId", type: "text", required: true },
  ],
  options: JSON.stringify({
    views: [
      {
        slug: "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
        `,
      },
    ],
  }),
});

2. Filter and Sort Views

Define a view using filter and sort parameters. This is simpler and automatically handles pagination:

await client.settings.collections.create({
  name: "posts",
  type: "base",
  schema: [
    { name: "title", type: "text", required: true },
    {
      name: "status",
      type: "select",
      options: { values: ["active", "inactive"] },
    },
    { name: "priority", type: "number" },
  ],
  options: JSON.stringify({
    views: {
      active_items: {
        page: 1,
        perPage: 10,
        filter: "status = 'active'",
        sort: "-priority",
      },
      pending_items: {
        page: 1,
        perPage: 10,
        filter: "status = 'pending'",
      },
    },
  }),
});

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

Practical Examples

Joins Between Collections

await client.settings.collections.create({
  name: "posts",
  type: "base",
  schema: [
    { name: "title", type: "text", required: true },
    { name: "authorId", type: "text", required: true },
  ],
  options: JSON.stringify({
    views: [
      {
        slug: "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

await client.settings.collections.create({
  name: "posts",
  type: "base",
  schema: [
    { name: "title", type: "text", required: true },
    { name: "category", type: "text" },
    { name: "views", type: "number" },
  ],
  options: JSON.stringify({
    views: [
      {
        slug: "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

await client.settings.collections.create({
  name: "posts",
  type: "base",
  schema: [
    { name: "title", type: "text", required: true },
    { name: "created", type: "text" },
  ],
  options: JSON.stringify({
    views: [
      {
        slug: "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

await client.settings.collections.create({
  name: "posts",
  type: "base",
  schema: [
    { name: "title", type: "text", required: true },
    { name: "type", type: "text" },
  ],
  options: JSON.stringify({
    views: [
      {
        slug: "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

await client.settings.collections.create({
  name: "posts",
  type: "base",
  schema: [
    { name: "title", type: "text", required: true },
  ],
  options: JSON.stringify({
    views: [
      {
        slug: "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 underlying tables. Create indexes to improve view performance:

await client.settings.collections.create({
  name: "posts",
  type: "base",
  schema: [
    { name: "title", type: "text", required: true },
    { name: "published", type: "bool" },
    { name: "created", type: "text" },
  ],
  options: JSON.stringify({
    indexes: [
      {
        name: "posts_published_idx",
        type: "index",
        fields: ["published"],
      },
      {
        name: "posts_created_idx",
        type: "index",
        fields: ["created"],
      },
    ],
    views: [
      {
        slug: "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 page and perPage in getView()
Avoid complex joins - Keep view queries simple when possible
Consider materialized views - For frequently accessed data, consider caching results
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 the getView() method, which internally calls:

Method	Endpoint	Description
`GET`	`/api/collections/:name/records/views/:slug`	Query records from a view

Best Practices

Use descriptive slug names - Make view purposes clear (e.g., published_posts, 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 - Track view definitions in 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 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 collection has a listRule, non-admin users must satisfy it
Admin users bypass all rules regardless of view configuration