Skip to main content
ToraDB supports a retrieval-oriented SQL dialect for search, analytics, and DDL. Run statements with db.sql(...) or toradb sql PATH "...". 
db.sql(
    "SELECT id FROM articles "
    "SPARSE SEARCH body BM25('Nikola Tesla alternating current') LIMIT 3"
)
Returns ranked SearchResults (use .to_pandas()). 
db.sql(
    "SELECT id FROM papers VECTOR SEARCH embedding "
    "ANN([0.9, 0.1, 0.0, 0.0]) LIMIT 2"
)

​
Hybrid retrieval in one query

db.sql(
    "SELECT tag, COUNT(*) FROM papers "
    "SPARSE SEARCH title BM25('Tesla coil') "
    "VECTOR SEARCH embedding ANN([0.9, 0.1, 0.0, 0.0]) "
    "GROUP BY tag"
)

​
Analytics

db.sql("SELECT tag, COUNT(*) FROM articles GROUP BY tag").to_pandas()
db.sql(
    "SELECT tag, COUNT(*) FROM articles WHERE tag = 'patent' GROUP BY tag"
).to_pandas()
Aggregates: COUNT(*), SUM(col), AVG, MIN, MAX. WHERE supports comparisons and IN (...).

​
Ordering and paging

SELECT id FROM docs
SPARSE SEARCH body BM25('Nikola Tesla motor')
ORDER BY score DESC
LIMIT 3 OFFSET 2

​
EXPLAIN

EXPLAIN SELECT id FROM docs SPARSE SEARCH body BM25('Tesla') LIMIT 5

​
DISTRIBUTED

Adds parallel segment scanning to a retrieval SELECT: 
SELECT id FROM docs
DISTRIBUTED SPARSE SEARCH body BM25('motor')
LIMIT 10

​
Streaming results

from toradb.sql import sql_stream

for page in sql_stream(db, "SELECT id FROM docs SPARSE SEARCH body BM25('Tesla') LIMIT 100", batch_size=32):
    print(page.to_pandas())

​
Catalog and DDL

db.sql("SHOW TABLES")
db.sql("DESCRIBE articles")
db.sql("CREATE TABLE logs USING text")
db.sql("CREATE INDEX text_idx ON articles (text) USING BM25")
db.sql("CREATE INDEX ann_idx ON papers (embedding) USING DISKANN")
db.sql("DROP TABLE logs")
db.sql("COMPACT TABLE articles FULL")

​
Materialized views

CREATE MATERIALIZED VIEW top_tags AS
SELECT tag, COUNT(*) FROM articles GROUP BY tag;

REFRESH MATERIALIZED VIEW top_tags;
SHOW MATERIALIZED VIEWS;
DROP MATERIALIZED VIEW top_tags;
See the SQL API reference for grammar details.