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:

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