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

> Master SpacetimeDB query language basics including subscription and SQL syntax. Learn real-time data streaming and ad-hoc requests for deterministic replication.

- Repository: [Clockwork Labs/SpacetimeDB](https://github.com/clockworklabs/SpacetimeDB)
- Tags: tutorial
- Published: 2026-03-09

---

**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`](https://github.com/clockworklabs/SpacetimeDB/blob/main/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`](https://github.com/clockworklabs/SpacetimeDB/blob/main/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`](https://github.com/clockworklabs/SpacetimeDB/blob/main/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`](https://github.com/clockworklabs/SpacetimeDB/blob/main/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`](https://github.com/clockworklabs/SpacetimeDB/blob/main/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`](https://github.com/clockworklabs/SpacetimeDB/blob/main/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`](https://github.com/clockworklabs/SpacetimeDB/blob/main/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:

```rust
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`](https://github.com/clockworklabs/SpacetimeDB/blob/main/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:

```rust
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:

```rust
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:

```rust
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`](https://github.com/clockworklabs/SpacetimeDB/blob/main/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`](https://github.com/clockworklabs/SpacetimeDB/blob/main/crates/sql-parser/src/ast/sub.rs)** – Defines the `SqlSelect` struct used exclusively for subscription parsing.
*   **[`crates/sql-parser/src/parser/sql.rs`](https://github.com/clockworklabs/SpacetimeDB/blob/main/crates/sql-parser/src/parser/sql.rs)** – Implements `parse_select`, enforcing subscription-only restrictions and building the AST.
*   **[`crates/vm/src/rel_ops.rs`](https://github.com/clockworklabs/SpacetimeDB/blob/main/crates/vm/src/rel_ops.rs)** – Contains join validation logic and index checks required for efficient subscription updates.
*   **[`crates/vm/src/eval.rs`](https://github.com/clockworklabs/SpacetimeDB/blob/main/crates/vm/src/eval.rs)** – Executes one-off queries without subscription constraints.
*   **[`crates/smoketests/src/lib.rs`](https://github.com/clockworklabs/SpacetimeDB/blob/main/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`](https://github.com/clockworklabs/SpacetimeDB/blob/main/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`](https://github.com/clockworklabs/SpacetimeDB/blob/main/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`).