Materialized Views¶

SurrealEngine provides support for SurrealDB materialized views, allowing you to create pre-computed aggregations and transformations of your data. In SurrealDB 2.x+, materialized views are live and automatically update when underlying data changes.

Creating Materialized Views¶

Basic Creation¶

Create a materialized view from a document model:

from surrealengine import Document, StringField, IntField, FloatField
from surrealengine import MaterializedView

class Product(Document):
    name = StringField(required=True)
    category = StringField(required=True)
    price = FloatField(required=True)
    quantity = IntField()

    class Meta:
        collection = "products"

# Create a materialized view
class ProductStats(MaterializedView):
    table_name = "product_stats"

    @classmethod
    def get_source_query(cls):
        return Product.objects.group_by("category")

# Create the view in the database
await ProductStats.create()

Create Options (v0.5.0+)¶

Control how the materialized view table is created:

# Create with overwrite - replaces existing table
await ProductStats.create(overwrite=True)

# Create only if it doesn't exist
await ProductStats.create(if_not_exists=True)

# Basic create (will fail if table exists)
await ProductStats.create()

These options map to SurrealDB’s DEFINE TABLE clauses:

overwrite=True → DEFINE TABLE OVERWRITE ...
if_not_exists=True → DEFINE TABLE IF NOT EXISTS ...

Advanced View Configuration¶

Group By with Aggregations (v0.5.0+)¶

Create views with aggregated data using improved GROUP BY parsing:

class CategoryStats(MaterializedView):
    table_name = "category_stats"

    @classmethod
    def get_source_query(cls):
        return (Product.objects
            .group_by("category")
            .select("category", "COUNT(*) as total", "AVG(price) as avg_price"))

await CategoryStats.create()

Group All (v0.5.0+)¶

Aggregate across all records without grouping:

class OverallStats(MaterializedView):
    table_name = "overall_stats"

    @classmethod
    def get_source_query(cls):
        return (Product.objects
            .group_by(all=True)
            .select("COUNT(*) as total_products", "SUM(quantity) as total_inventory"))

await OverallStats.create()

Querying Materialized Views¶

Query materialized views like regular documents:

# Get all stats
stats = await ProductStats.objects.all()

# Filter stats
electronics = await ProductStats.objects.filter(category="electronics")

# Order by aggregated values
top_categories = await ProductStats.objects.order_by("-total_sales").limit(10)

# Use with other query methods
stats = await (ProductStats.objects
    .filter(total_sales__gt=1000)
    .omit("internal_field")
    .timeout("5s")
    .all())

Synchronous Operations¶

All async operations have synchronous equivalents:

# Create view synchronously
ProductStats.create_sync()
ProductStats.create_sync(overwrite=True)
ProductStats.create_sync(if_not_exists=True)

# Query synchronously
stats = ProductStats.objects.all_sync()
stats = ProductStats.objects.filter(category="electronics").all_sync()

Deprecation Notice¶

Warning

refresh() and refresh_sync() are deprecated (v0.5.0+)

In SurrealDB 2.x+, materialized views derived from tables are live and automatically update when underlying data changes. Manual refresh is not needed and these methods are now no-ops:

# DEPRECATED - No longer needed
await ProductStats.refresh()
ProductStats.refresh_sync()

These methods will be removed in a future version. Simply query the view to get current data:

# Correct way - views are always up to date
stats = await ProductStats.objects.all()

Live Updates¶

SurrealDB materialized views are live, meaning they automatically update when:

Source table data is inserted
Source table data is updated
Source table data is deleted

No manual refresh or rebuild is required. Just query the view to get current aggregated data.

Complex Aggregations¶

Use aggregation pipelines for complex transformations:

from surrealengine import AggregationPipeline
from surrealengine.aggregation import Count, Sum, Avg, Max, Min

class SalesSummary(MaterializedView):
    table_name = "sales_summary"

    @classmethod
    def get_source_query(cls):
        pipeline = AggregationPipeline(Product.objects)
        return (pipeline
            .group(by_fields="category",
                   total_items=Count("*"),
                   total_revenue=Sum("price * quantity"),
                   avg_price=Avg("price"),
                   max_price=Max("price"),
                   min_price=Min("price"))
            .sort(total_revenue="DESC")
            .limit(50))

await SalesSummary.create(if_not_exists=True)

Best Practices¶

Use if_not_exists for idempotency:

await ProductStats.create(if_not_exists=True)

Choose meaningful view names:

class UserActivitySummary(MaterializedView):
    table_name = "user_activity_summary"

Don’t use refresh() - views are live and always current

Index view columns used in filters:

class ProductStats(MaterializedView):
    table_name = "product_stats"

    class Meta:
        indexes = [
            {"fields": ["category"], "unique": False},
            {"fields": ["total_sales"], "unique": False}
        ]

Use GROUP ALL for global aggregations:

# For global stats across all records
return Product.objects.group_by(all=True).select(...)

Performance Considerations¶

Materialized views improve query performance by pre-computing aggregations
Views are stored as regular tables in SurrealDB
Live updates may have a small performance impact on write operations
For very large datasets, consider partitioning data or using time-based views
Use omit() and field selection to minimize data transfer

Migration from Older Versions¶

If you’re upgrading from versions < 0.5.0:

# OLD (< v0.5.0)
await ProductStats.create()
await ProductStats.refresh()  # Manually refresh

# NEW (v0.5.0+)
await ProductStats.create(if_not_exists=True)
# No refresh needed - views are live!
stats = await ProductStats.objects.all()  # Always current