SQL Endpoint
Torii exposes a SQL endpoint for direct database access, enabling custom queries, analytics, and reporting beyond what GraphQL and gRPC provide.
Overview
Key Features:
- Direct Access: Query the underlying SQLite database directly
- Custom Analytics: Build complex aggregations and reports
- Schema Introspection: Explore database structure and relationships
- Read-Only: Database cannot be modified through this interface
Use Cases:
- Custom dashboards and analytics
- Data export and migration
- Advanced filtering not available in GraphQL
- Database schema exploration
- Performance debugging and optimization
Endpoint Access
The SQL endpoint is available at /sql
on your Torii server:
- Base URL:
http://localhost:8080/sql
- Methods: GET (query parameter) or POST (request body)
- Format: Raw SQL queries
- Response: JSON format with query results
Interactive SQL Playground
Visiting http://localhost:8080/sql
in your browser opens an interactive SQL playground featuring:
- Monaco Editor: Full-featured SQL editor with syntax highlighting and auto-completion
- Schema Explorer: Browse database tables and columns with expandable tree view
- Query History: Automatic query history with favorites and timestamps
- Real-time Results: Execute queries and view results in formatted tables
- Export Options: Download results as JSON files
- Performance Metrics: Query execution time and row count display
Database Schema
Understanding Torii's database structure is essential for effective querying:
Core System Tables
entities
All entities with at least one component (ID is Poseidon hash of keys)
models
Registry of all models and events with metadata
entity_model
Junction table mapping entities to their models
model_members
Schema definition for model fields and types
Optional Data Tables
events
Raw blockchain events from world contract
transactions
World-related transactions with calldata
event_messages
Custom events emitted via world.emit_event
API
event_messages_historical
Preserved historical event messages
ERC Token Tables
balances
ERC20/ERC721/ERC1155 token balances by account
tokens
Token metadata (name, symbol, decimals)
erc_transfers
Token transfer events with amounts and parties
Additional Tables
controllers
Cartridge controller integration
transaction_calls
Detailed transaction call information with entrypoints
entities_historical
Entity state snapshots over time
Dynamic Model Tables
Torii automatically creates tables for each registered model:
Table Naming Convention:
- Format:
<NAMESPACE>-<MODEL_NAME>
- Example:
game-Position
,combat-Health
Field Mapping:
- Model fields are prefixed with
external_
in the database - Primitive types (felt252, u32, bool, ByteArray) are stored directly
- Complex types (arrays, enums, structs) create separate tables:
- Format:
<NAMESPACE>-<MODEL_NAME>lt;FIELD_NAME>
- Example:
game-Inventory$items
- Format:
Key Fields:
- Fields marked with
#[key]
in your model are used for entity identification - Composite keys are supported for multi-key entities
- Key fields are automatically indexed for query performance
Endpoint queries
To submit a query to the SQL endpoint, append /sql
to the Torii URL.
You can submit the query using a GET
or POST
request.
Using GET
The query is sent as a URL parameter. Both q
and query
parameters are supported:
query=$(printf '%s' "SELECT * FROM [ns-Position];" | jq -s -R -r @uri)
curl "0.0.0.0:8080/sql?query=${query}" | jq
curl "0.0.0.0:8080/sql?query=SELECT%20*%20FROM%20models;" | jq
Using POST
The query is sent as the body of the request.
curl -X POST "0.0.0.0:8080/sql" -d "SELECT * FROM [ns-Position];" | jq
Common Query Examples
Schema Exploration
List all tables in the database:
SELECT name FROM sqlite_master
WHERE type='table'
ORDER BY name;
Get table schema information:
SELECT
m.name as table_name,
p.name as column_name,
p.type as data_type,
p.pk as is_primary_key,
p."notnull" as not_null
FROM sqlite_master m
JOIN pragma_table_info(m.name) p
WHERE m.type = 'table'
AND m.name NOT LIKE 'sqlite_%'
ORDER BY m.name, p.cid;
Entity Queries
Find entities with specific models:
SELECT e.id, e.keys, e.updated_at
FROM entities e
JOIN entity_model em ON e.id = em.entity_id
JOIN models m ON em.model_id = m.id
WHERE m.name = 'Position'
LIMIT 100;
Query model-specific data (remember to quote table names):
SELECT external_player, external_x, external_y
FROM "dojo_starter-Position"
WHERE external_x > 0 AND external_y > 0;
Performance Analytics
Entity count by model:
SELECT m.name, COUNT(*) as entity_count
FROM models m
JOIN entity_model em ON m.id = em.model_id
GROUP BY m.id, m.name
ORDER BY entity_count DESC;
Recent activity:
SELECT COUNT(*) as recent_entities
FROM entities
WHERE updated_at > datetime('now', '-1 hour');