Skip to content

usage.md

Latest commit

 

History

History
102 lines (75 loc) · 5.91 KB

usage.md

File metadata and controls

102 lines (75 loc) · 5.91 KB

Using SQLRite

Launching

cargo run                           # in-memory REPL, no persistence
cargo run -- mydb.sqlrite           # open (or create) mydb.sqlrite, auto-save enabled
cargo run --release -- mydb.sqlrite # same, optimized build

The positional FILE argument is equivalent to typing .open FILE right after the REPL starts — existing files are loaded, missing files are created. Without it, you land in a transient in-memory database.

--help prints the meta-command list and the supported SQL surface inline; worth a read if you're new to the tool.

Meta commands

Meta commands start with a dot and don't need a trailing semicolon.

Command Behavior
.help Print the meta-command list
.exit Write history, quit cleanly
.open FILENAME Open (or create) a .sqlrite file. From this point on, every committing SQL statement auto-saves.
.save FILENAME Force-flush the current DB to FILENAME. Rarely needed — auto-save makes this redundant when it's the active file. Useful for "save as" to a different path.
.tables List tables in the current database, sorted alphabetically
.ask QUESTION Natural-language → SQL via the configured LLM. Requires SQLRITE_LLM_API_KEY.
.spawn Mint a sibling connection sharing the same backing database. Switches to it. (Phase 11.11a)
.use NAME Switch the active handle (case-insensitive). (Phase 11.11a)
.conns List every active handle; marks the current one with * and flags handles in an open BEGIN CONCURRENT. (Phase 11.11a)
.read / .ast Not yet implemented

.open semantics

  • If FILENAME exists and is a valid SQLRite database: load it and enable auto-save.
  • If FILENAME doesn't exist: create an empty database at that path (auto-save enabled immediately).
  • If FILENAME exists but is not a valid SQLRite database: reject with a bad magic bytes error — the REPL stays in its previous state.

Only one database is active at a time. A subsequent .open replaces the in-memory state and collapses every sibling handle minted via .spawn back to a single one (named A) — siblings pointing at the previous database would be stranded otherwise.

Multi-handle mode (Phase 11.11a)

The REPL holds a vector of Connections; the prompt always shows which one is active: sqlrite[A]> , sqlrite[B]> , etc.

  • .spawn mints a new sibling off the active handle and switches to it. Each new handle gets the next letter in sequence (A, B, C, …, Z, AA, AB).
  • .use NAME switches the active handle. The next SQL line runs on that connection.
  • .conns shows the current roster, with * next to the active handle and (BEGIN CONCURRENT) next to any handle holding an open concurrent transaction.

A worked demo (assumes PRAGMA journal_mode = mvcc;):

sqlrite[A]> CREATE TABLE t (id INTEGER PRIMARY KEY, v INTEGER);
sqlrite[A]> INSERT INTO t (id, v) VALUES (1, 0);
sqlrite[A]> .spawn
Spawned sibling handle 'B' and switched to it. 2 handles open.
sqlrite[B]> .use A
sqlrite[A]> BEGIN CONCURRENT;
sqlrite[A]> UPDATE t SET v = 100 WHERE id = 1;
sqlrite[A]> .use B
sqlrite[B]> BEGIN CONCURRENT;
sqlrite[B]> UPDATE t SET v = 200 WHERE id = 1;
sqlrite[B]> COMMIT;
sqlrite[B]> .use A
sqlrite[A]> COMMIT;            -- Busy: write-write conflict on t/1

Supported SQL

The full SQL surface — every statement, every operator, every edge case, every "not yet" — lives in the canonical reference: Supported SQL.

Quick hits worth knowing when you're working at the REPL:

  • One statement per call. The REPL / Connection::execute expects a single statement; multi-statement strings return Expected a single query statement, but there are N. For batch execution use the SDKs' executescript / execute_batch.
  • Transactions are real. BEGIN / COMMIT / ROLLBACK land as expected; auto-save is suppressed inside a transaction and everything flushes in one WAL commit frame on COMMIT. No nested transactions yet.
  • Arithmetic stays honest. Integer-only operations stay integer; any REAL operand promotes to f64; divide-by-zero is a typed runtime error, never a panic.
  • NULL follows three-valued logic. NULL = NULL is unknown (not true) — treated as false in WHERE. Use IS NULL / IS NOT NULL for explicit null tests, e.g. SELECT id FROM t WHERE qty IS NULL;.
  • Identifiers are case-sensitive (table / column names; no normalization), but keywords aren't. String literals preserve case.
  • Not yet supported: joins, subqueries, GROUP BY / aggregates, DISTINCT, LIKE / IN, projection expressions, column aliases, OFFSET, multi-column ORDER BY, savepoints, ALTER TABLE, DROP TABLE, DROP INDEX. See the full list in the reference.

History

The REPL persists an interaction history file named history in the working directory. Delete it to reset.

Programmatic use

The engine is both a binary (the REPL you've been using) and a library (the sqlrite crate). Phase 5a landed a stable public API:

use sqlrite::Connection;

let mut conn = Connection::open("foo.sqlrite")?;
conn.execute("INSERT INTO users (name) VALUES ('alice')")?;

let mut stmt = conn.prepare("SELECT id, name FROM users")?;
let mut rows = stmt.query()?;
while let Some(row) = rows.next()? {
    let id: i64 = row.get(0)?;
    let name: String = row.get_by_name("name")?;
    println!("{id}: {name}");
}

See Embedding the SQLRite engine for the full API reference, and examples/ for runnable samples (cargo run --example quickstart walks through the basics end-to-end). Language SDKs shipped across Phases 5b – 5g — Python (PyPI: sqlrite), Node.js (npm: @joaoh82/sqlrite), Go (github.com/joaoh82/rust_sqlite/sdk/go), WASM (npm: @joaoh82/sqlrite-wasm). For LLM-agent-driven access, the sqlrite-mcp MCP server (Phase 7h) wraps the same engine over stdio.