Querying

Online: https://thekevinscott.github.io/dirsql/guide/querying

Once a DirSQL instance is created, the initial directory scan is complete and you can run SQL queries against the indexed data.

From the CLI

The dirsql HTTP server exposes the same query interface over POST /query. Send {"sql": "..."} and get back the same JSON array of row objects. See the CLI section for the full server setup.

Basic queries

Python

# All rows from a table
results = db.query("SELECT * FROM comments")

# Filter with WHERE
results = db.query("SELECT * FROM comments WHERE author = 'alice'")

# Aggregations
results = db.query("SELECT author, COUNT(*) as n FROM comments GROUP BY author")

# JOINs across tables
results = db.query("""
    SELECT posts.title, authors.name
    FROM posts
    JOIN authors ON posts.author_id = authors.id
""")

Rust

// All rows from a table
let results = db.query("SELECT * FROM comments")?;

// Filter with WHERE
let results = db.query("SELECT * FROM comments WHERE author = 'alice'")?;

// Aggregations
let results = db.query("SELECT author, COUNT(*) as n FROM comments GROUP BY author")?;

// JOINs across tables
let results = db.query(
    "SELECT posts.title, authors.name \
     FROM posts JOIN authors ON posts.author_id = authors.id"
)?;

TypeScript

// All rows from a table
const results = await db.query('SELECT * FROM comments');

// Filter with WHERE
const filtered = await db.query("SELECT * FROM comments WHERE author = 'alice'");

// Aggregations
const counts = await db.query('SELECT author, COUNT(*) as n FROM comments GROUP BY author');

// JOINs across tables
const joined = await db.query(`
  SELECT posts.title, authors.name
  FROM posts
  JOIN authors ON posts.author_id = authors.id
`);

Any valid SQLite SELECT works. The in-memory database supports the full SQLite dialect including subqueries, CTEs, window functions, and aggregate functions. See Read-only queries below for why write statements (INSERT, UPDATE, DELETE, DROP, etc.) are rejected.

Return format

query() returns a list of dicts (Python), a Vec<HashMap> (Rust), or an array of objects (TypeScript). Each entry maps column names to values.

Python

results = db.query("SELECT title, author FROM posts")
# [
#     {"title": "Hello World", "author": "alice"},
#     {"title": "Second Post", "author": "bob"},
# ]

Rust

let results = db.query("SELECT title, author FROM posts")?;
// Vec<HashMap<String, Value>>
// [{"title": "Hello World", "author": "alice"}, ...]

TypeScript

const results = await db.query('SELECT title, author FROM posts');
// [
//   { title: 'Hello World', author: 'alice' },
//   { title: 'Second Post', author: 'bob' },
// ]

SQLite types map back to Python types:

SQLite type	Python type
TEXT	str
INTEGER	int
REAL	float
BLOB	bytes
NULL	None

Internal columns

dirsql adds internal tracking columns (_dirsql_file_path, _dirsql_row_index) to each table for file-change diffing. These columns are automatically excluded from SELECT * results, so day-to-day queries don't need to account for them.

If you want to know which file a row came from, you can name the tracking columns explicitly in the projection:

Python

rows = db.query("SELECT title, _dirsql_file_path FROM posts")
# [{"title": "Hello World", "_dirsql_file_path": "posts/hello.json"}, ...]

Rust

let rows = db.query("SELECT title, _dirsql_file_path FROM posts")?;
// [{"title": "Hello World", "_dirsql_file_path": "posts/hello.json"}, ...]

TypeScript

const rows = await db.query('SELECT title, _dirsql_file_path FROM posts');
// [{ title: 'Hello World', _dirsql_file_path: 'posts/hello.json' }, ...]

Tracking columns are only returned when named explicitly — SELECT * continues to exclude them.

Read-only queries

query() accepts only read-only statements. Each statement is prepared on SQLite and then classified via sqlite3_stmt_readonly; anything SQLite itself flags as a write — INSERT, UPDATE, DELETE, DROP, CREATE, ALTER, REPLACE, VACUUM, ANALYZE, etc. — is rejected before any rows are produced.

This keeps the in-memory index consistent with the on-disk files that back it. Mutations only happen through the watcher/indexer pipeline: to change data, edit the underlying file and let the watcher re-extract rows.

Python

# Raises a RuntimeError; the index is unchanged.
db.query("DELETE FROM posts")

Rust

// Returns DirSqlError::WriteForbidden; the index is unchanged.
let err = db.query("DELETE FROM posts").unwrap_err();
assert!(matches!(err, dirsql::DirSqlError::WriteForbidden));

TypeScript

// Throws an Error whose message explains writes are not accepted.
expect(() => db.query('DELETE FROM posts')).toThrow(/read-only/i);

Error handling

Invalid SQL raises an exception:

Python

try:
    db.query("NOT VALID SQL")
except Exception as e:
    print(f"Query error: {e}")

Rust

match db.query("NOT VALID SQL") {
    Ok(results) => println!("{:?}", results),
    Err(e) => eprintln!("Query error: {}", e),
}

TypeScript

try {
  await db.query('NOT VALID SQL');
} catch (e) {
  console.error(`Query error: ${e}`);
}

Empty results

Queries that match no rows return an empty collection:

Python

results = db.query("SELECT * FROM posts WHERE author = 'nobody'")
assert results == []

Rust

let results = db.query("SELECT * FROM posts WHERE author = 'nobody'")?;
assert!(results.is_empty());

TypeScript

const results = await db.query("SELECT * FROM posts WHERE author = 'nobody'");
console.assert(results.length === 0);