Skip to content

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. For comprehensive information about Torii's data indexing and storage, see the Torii overview.

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

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');