How to Write and Run SQL Queries Against a SpacetimeDB Module

SQL is a quick way to answer a focused question about the data in your SpacetimeDB module. You can filter a table to find one record, select only a few columns, count rows, or compare related tables with a join. Spacetime Studio puts that workflow in its Query Editor, with schema-aware help so you can write queries against the database you have actually connected to.

This guide shows how to get started, what the editor can help with, and where SpacetimeDB’s SQL support differs from a full PostgreSQL database. SQL is useful for exploration and management; your module’s reducers remain the place for application behavior and transactional rules.

When SQL is useful

Table browsing is convenient when you want to scan records. A query is more precise when you know what you are looking for. For example, you can find a player by an identity, inspect just the fields involved in a bug, count rows, or join a record to related data. Queries are also handy while debugging because you can check whether a reducer produced the state you expected.

Use table and column names exactly as they are defined by your module. SpacetimeDB does not automatically pluralize or change their casing. If your table is named player_profile, use that name in SQL. The official table documentation explains how the schema names map to SQL.

Open the Query Editor

Connect Spacetime Studio to the server and database module you want to inspect, then open that connection’s Query Editor page. Check the selected connection before you run anything, especially when you have local, staging, and production databases. The editor runs queries against the selected database; it does not run them against your module source code.

The Query Editor is designed for an interactive loop: write a statement, run it, and inspect the returned rows or error. You can keep the editor open alongside your code and use it to verify what is stored as you develop.

Write a first query

Start with a small read query. This example returns a few columns from a table named player:

SELECT identity, username
FROM player
LIMIT 20;

To narrow the results, add a WHERE condition using a column from your schema:

SELECT identity, username
FROM player
WHERE username = 'Ada';

For an overview, count rows with COUNT(*):

SELECT COUNT(*) AS player_count
FROM player;

Replace these example names and values with those in your module. Add a LIMIT while exploring a large table so the result stays manageable. A limit controls how many rows are returned; it does not sort them, so use it to preview rather than to assume which rows come first.

Use editor assistance

The Query Editor includes IntelliSense based on the database you are connected to. As you write, it can help you discover that module’s tables and columns and complete identifiers, which reduces guesswork and catches naming mistakes early. Suggestions reflect the selected database schema, so switch connections if you mean to query another module.

Syntax highlighting makes SQL keywords and identifiers easier to scan. Tabs let you format a query across multiple lines, and your queries are saved between sessions so you can return to useful snippets later. These editor features make iteration easier, while the database’s SQL parser still determines whether a statement is supported.

Know the SQL subset

SpacetimeDB supports a defined subset of SQL rather than every command and feature available in a general-purpose PostgreSQL database. The current SQL reference lists SELECT, INSERT, UPDATE, DELETE, and the experimental SET and SHOW statements. For the exact grammar and version-specific details, see the official SpacetimeDB SQL reference.

For common reads, SELECT supports column projections, filters, joins, COUNT(*), and LIMIT. The query API can join multiple tables. Some familiar SQL features are outside the supported grammar, though: for example, the reference documents a restricted predicate syntax, projections are limited, and UPDATE and DELETE do not support joins. Do not assume that a query accepted by PostgreSQL will work unchanged here.

SQL queries and subscriptions are also different interfaces. A subscription must replicate complete rows from one table and has extra restrictions to keep real-time updates efficient. A one-off query in the Query Editor can select particular columns and join tables according to the broader query syntax. If you are designing a client subscription, consult the subscription section of the SQL reference rather than treating a successful editor query as proof that the same statement can be subscribed to.

Treat writes carefully

The Query Editor can be useful for direct data changes as well as reads. The supported SQL subset includes INSERT, UPDATE, and DELETE, so a statement can permanently alter rows in the selected database. A missing or overly broad WHERE condition can affect more data than intended; an UPDATE or DELETE without a filter applies to all matching table rows.

When you only need to inspect data, stick to SELECT. Before a write, verify the connection and predicate, and try it against a local or development database first. For normal application behavior, prefer reducers: they let your module validate inputs and enforce the rules that keep related data consistent. Direct SQL is an administrative tool, not a substitute for those rules.

Troubleshoot query errors

• Unknown table or column: confirm the selected connection and use the exact names from the module schema.
• Unexpected syntax error: check the supported SQL reference; a PostgreSQL feature may not be part of SpacetimeDB’s subset.
• No rows returned: test the table with a simple SELECT, then add filters one at a time and confirm the values and types.
• Access denied or missing private data: confirm that the connected identity has the required access. Table visibility and row-level security affect what can be read.
• Query works but subscription fails: review the stricter subscription rules, including full-row results and table/index requirements.

Other ways to run SQL

Spacetime Studio is one option for interactive queries. The SpacetimeDB CLI can run a query from a terminal, for example spacetime sql my-database "SELECT * FROM player". The HTTP database API also exposes SQL execution for management and debugging workflows. Those options are useful for scripts or terminal-first work; Studio adds a visual editor with schema-aware IntelliSense, highlighting, saved queries, and results in the same desktop workspace.

Studio is free and open source, and it connects to local, Maincloud, and reachable self-hosted databases. You can download Spacetime Studio or browse the source on GitHub.

Frequently asked questions

Can I run any PostgreSQL query in SpacetimeDB?

No. SpacetimeDB implements a defined SQL subset. Check the current SQL reference for supported statements and syntax.

Does the Query Editor change my database?

SELECT reads data. Supported INSERT, UPDATE, and DELETE statements write data, so verify the selected database and conditions before running them.

Are editor queries saved?

Yes. The Query Editor saves queries between sessions, and it also provides syntax highlighting, tab support, and schema-aware IntelliSense.

Can I use the same query in a subscription?

Not always. Subscriptions have additional restrictions because they maintain a live replicated view. Refer to the subscription rules in the official SQL reference.