SpacetimeDB Query Language Basics: A Complete Guide to Subscriptions and SQL Syntax

SpacetimeDB exposes two SQL-like dialects—a restricted subscription language for real-time data streaming and a full query language for ad-hoc requests—both sharing the same parser but enforcing different constraints for deterministic replication.

SpacetimeDB is a relational database optimized for real-time multiplayer applications, offering a unique approach to data synchronization through its SQL-like query interface. Understanding the SpacetimeDB query language basics is essential for developers building reactive applications that require low-latency updates. The system distinguishes between persistent subscriptions and one-off queries, each with specific syntax rules enforced at the parser level in the sql-parser crate.

Understanding the Two SQL Dialects

SpacetimeDB implements two closely related dialects that share the same underlying parser but serve different execution models.

Subscription Language

The subscription language is designed for real-time data synchronization over WebSocket or SDK connections. This dialect imposes strict restrictions to guarantee deterministic, low-latency incremental updates. When you create a subscription, the server retains the query AST as a subscription handle and pushes delta updates whenever underlying data changes.

Query Language

The query language is a superset used for one-off requests via the CLI or HTTP API. It relaxes the constraints required for live subscriptions, allowing arbitrary column projections, aggregations, and unlimited joins. These queries execute once and return a static result set without establishing a persistent stream.

Grammar and Syntax Comparison

Both dialects support standard SQL clauses but with different capabilities:

• SELECT clause: Subscriptions require SELECT * or SELECT table.*, returning all columns of a single table. Queries allow arbitrary column lists (SELECT name, price) and aggregates (SELECT COUNT(*) AS alias).
• FROM clause: Subscriptions permit at most two tables with an INNER JOIN, requiring indexed columns on both sides. Queries support unlimited joins without index requirements.
• WHERE clause: Both dialects support boolean predicates built from literals and column references, but subscriptions disallow arithmetic expressions.
• LIMIT clause: Only available in ad-hoc queries. Subscriptions stream all matching rows continuously without caps.

The full BNF grammar and clause specifications are documented in docs/versioned_docs/version-1.12.0/00300-resources/00200-reference/00400-sql-reference.md, while the parser implementation resides in crates/sql-parser/src/parser/sql.rs where the parse_select function constructs the AST.

Subscription-Only Constraints

Because subscriptions must remain incrementally updatable, the engine enforces three critical constraints that are validated during parsing and execution.

Single-Table Results

Every subscription must return rows from exactly one table. The SELECT clause may only use * or table.* syntax; mixed-table projections are rejected. In crates/sql-parser/src/ast/sub.rs, the SqlSelect struct defines the subscription AST with project, from, and optional filter fields that enforce this restriction.

Qualified Column Names in Joins

When joining tables, every column reference in the ON predicate must be prefixed with the table name or alias. The parser calls SqlSelect::find_unqualified_vars() to reject ambiguous column references before execution.

Index Requirements for Joins

The VM validates that both columns used in a join predicate have database indexes. According to crates/vm/src/rel_ops.rs, this guarantee allows the server to compute delta-updates efficiently without full table scans. Without indexed join columns, the subscription parser rejects the query.

Query-Only Features

Ad-hoc queries relax subscription constraints to support analytical workloads and data exploration.

• Arbitrary column projections: Select specific fields with SELECT name, price FROM Inventory.
• Aggregations: Compute summaries using SELECT COUNT(*) AS n FROM Inventory.
• Multiple joins: Chain any number of tables without the two-table limit imposed on subscriptions.
• Result limiting: Use SELECT * FROM Inventory LIMIT 10 to bound the result set for performance.

One-off queries follow the same parsing path as subscriptions but skip index validation and the single-table restriction, evaluating directly via crates/vm/src/eval.rs.

Runtime Execution Flow

When a client submits a subscription, the server executes a specific validation pipeline:

1. Parses the SQL string into a SqlSelect AST using crates/sql-parser/src/parser/sql.rs.
2. Invokes find_unqualified_vars() to ensure all column references are properly qualified.
3. Verifies index existence for any joined columns in crates/vm/src/rel_ops.rs.
4. Stores the validated AST as a subscription handle. Each subsequent INSERT, UPDATE, or DELETE triggers the relational operator pipeline to recompute affected rows and push deltas to subscribed clients.

One-off queries bypass steps 2 and 3, executing immediately through the evaluation engine without persistent state.

Client Implementation Examples

The Rust SDK demonstrates how to invoke both dialects using the same SQL syntax but different API methods.

Basic Table Subscription

Subscribe to all rows in the Inventory table for real-time updates:

let sub = client.subscribe_background(&["SELECT * FROM Inventory"], 1)?;

This returns a SubscriptionHandle that streams every row change. The implementation in crates/smoketests/src/lib.rs shows how subscribe_background creates the background listener.

Qualified Join Subscription

Join two tables while returning only the Orders data:

let sub = client.subscribe_background(&[
    "SELECT o.* FROM Orders o JOIN Inventory i ON o.product_id = i.id"
], 1)?;

The o.* qualifier is mandatory because the FROM clause contains two tables.

One-Off Query with Column List

Fetch specific fields with a row limit:

let result = client.query(
    "SELECT name, price FROM Inventory WHERE quantity > 0 LIMIT 5"
)?;
println!("{:?}", result);

Aggregation Query

Count total rows without establishing a subscription:

let count = client.query("SELECT COUNT(*) AS n FROM Inventory")?;
println!("Rows in Inventory: {}", count[0]["n"]);

COUNT(*) always returns exactly one row, even for empty tables.

Key Source Files and Architecture

Understanding the implementation requires familiarity with these critical paths in the SpacetimeDB repository:

• docs/versioned_docs/version-1.12.0/00300-resources/00200-reference/00400-sql-reference.md – Human-readable reference defining both dialects and their constraints.
• crates/sql-parser/src/ast/sub.rs – Defines the SqlSelect struct used exclusively for subscription parsing.
• crates/sql-parser/src/parser/sql.rs – Implements parse_select, enforcing subscription-only restrictions and building the AST.
• crates/vm/src/rel_ops.rs – Contains join validation logic and index checks required for efficient subscription updates.
• crates/vm/src/eval.rs – Executes one-off queries without subscription constraints.
• crates/smoketests/src/lib.rs – Provides the subscribe_background helper function demonstrating client-side subscription management.

Summary

• SpacetimeDB implements two SQL dialects: a restricted subscription language for real-time streaming and a full query language for ad-hoc requests.
• Subscriptions require single-table results, qualified column references in joins, and indexed join columns to enable efficient delta updates.
• Queries support arbitrary projections, aggregations, unlimited joins, and LIMIT clauses unavailable in subscriptions.
• Both dialects share the sql-parser crate for parsing, but subscriptions undergo additional validation in crates/vm/src/rel_ops.rs.
• Client SDKs use subscribe_background for persistent streams and query for one-off execution.

Frequently Asked Questions

What is the difference between SpacetimeDB's subscription language and query language?

The subscription language is a restricted SQL dialect used for real-time data synchronization that requires single-table results and indexed joins to support incremental updates. The query language is a superset that removes these restrictions, allowing arbitrary column selections, aggregations, and unlimited joins for one-off analytical requests.

Why do subscriptions require indexed columns for joins?

Subscriptions require indexed join columns to guarantee efficient delta computation. As implemented in crates/vm/src/rel_ops.rs, the database uses these indexes to quickly identify which subscribed rows change when data is modified, avoiding expensive full-table scans during real-time replication.

Can I use LIMIT in a SpacetimeDB subscription?

No, the LIMIT clause is not supported in subscriptions because the server streams all matching rows continuously to maintain a consistent real-time view. Use LIMIT only in one-off queries via the HTTP API or CLI where result sets need explicit bounding.

How does SpacetimeDB handle ambiguous column references in subscriptions?

The parser automatically rejects ambiguous references by calling SqlSelect::find_unqualified_vars() during AST construction. In queries with multiple tables, you must prefix every column in join predicates with the table alias or name (e.g., o.product_id rather than just product_id).

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}