# graphitesql
[](https://github.com/KarpelesLab/graphitesql/actions/workflows/ci.yml)
[](https://crates.io/crates/graphitesql)
[](https://docs.rs/graphitesql)
[-blue.svg)](LICENSE)
A pure, safe, `no_std`-capable Rust re-implementation of **SQLite**, as a single
crate, aiming for **byte-for-byte compatibility with the SQLite 3 database file
format**.
> **Status: read + write working, with a broad SQL engine.** graphitesql opens
> real SQLite files and **creates databases** that the real `sqlite3` CLI opens
> with `PRAGMA integrity_check = ok`. Storage covers rowid **and `WITHOUT ROWID`**
> tables, secondary/`UNIQUE`/partial/expression indexes, overflow pages, `VACUUM`,
> the full **`auto_vacuum`** track, and the **WAL read *and* write** path. The SQL
> surface covers `INNER`/`LEFT`/`RIGHT`/`FULL`/`NATURAL`/`USING` **joins**,
> aggregates, `GROUP BY`/`HAVING`, compound queries, (recursive) **CTEs**,
> correlated subqueries & `EXISTS`, **window functions**, UPSERT, `RETURNING`,
> `STRICT` tables, generated columns, **triggers**, **foreign keys**, **ATTACH /
> TEMP** multi-schema, **virtual tables** (built-in `series`, `rtree`, and
> `fts5` modules — full-text `MATCH` with phrases, prefixes, column filters,
> `AND`/`OR`/`NOT`, `NEAR`, `^` anchors, `bm25()`/`rank` relevance ordering, and
> `highlight()`, and `fts5vocab` vocabulary tables — the read-only `dbstat`
> per-page storage introspection table,
> plus `register_module` / `register_function` for your own), and
> a wide function
> library — date/time,
> `printf`, math, **JSON + JSONB**, and more — with an index-driven planner and
> **`EXPLAIN QUERY PLAN`** matching sqlite. `SELECT` now executes through a
> **register-machine VDBE engine by default** (it falls back to the tree-walker
> for shapes it does not yet compile), with plain `EXPLAIN` listing the compiled
> bytecode. Everything is verified differentially against `sqlite3` (a 1,600+
> query corpus plus 140+ focused suites). R-Tree **and FTS5** files are now
> byte-compatible with sqlite: an FTS5 table written by graphite (stored in
> sqlite's `_content`/`_data`/`_idx`/`_docsize`/`_config` shadow tables) is
> opened, full-text-`MATCH`ed, and integrity-checked by stock `sqlite3`, and an
> R-Tree round-trips through its `_node` on-disk format. *(FTS5 sqlite-readability
> covers ASCII, Latin-1, Latin Extended-A/B, and Latin Extended Additional text —
> graphite folds diacritics exactly like `unicode61` (`café`→`cafe`,
> `Dvořák`→`dvorak`, `mạ`→`ma`), and honors the full `tokenize=` option set —
> `remove_diacritics 0|1|2`, `porter`, `ascii`, and `tokenchars`/`separators` — on
> both the index and query sides; the fold tables are derived byte-for-byte from
> `sqlite3`; CJK and other scripts pass through unfolded, as in sqlite.)* R-Tree
> queries **prune the node tree by the query's coordinate bounds** rather than
> scanning every entry. What remains is depth: finishing the VDBE (per-cursor
> joins, more single-block shapes) and concurrency — see the full plan in
> **[ROADMAP.md](ROADMAP.md)**.
## Why
SQLite is the most-deployed database in the world, but it's C. graphitesql brings
the same file format and SQL dialect to places where a **safe, dependency-free,
`no_std` Rust** library shines:
- **WebAssembly** — run a real SQLite-compatible database in the browser or in
a wasm sandbox with no JS shim and no Emscripten.
- **Embedded / bare-metal** — `no_std` + `alloc`, bring-your-own storage.
- **Sandboxed / capability-based hosts** — no `unsafe`, no FFI, no syscalls
except through a `Vfs` trait you control.
## Goals
- ✅ **File-format compatible.** Open a database written by `sqlite3`; write one
`sqlite3` can open. Verified with differential tests against the C library.
- ✅ **Safe.** `#![forbid(unsafe_code)]` across the whole crate.
- ✅ **Portable.** `#![no_std]` + `alloc`. Optional `std` feature for real files.
- ✅ **Single crate.** Storage, B-tree, SQL parser, and VM all live in
`graphitesql`.
- ✅ **No dependencies.** Only `core` and `alloc`.
## Non-goals (at least initially)
- Being a faster SQLite. Correctness and compatibility first.
- 100% of every SQLite extension (FTS5, R-Tree, sessions, …). These are layered
in later, behind features. See the roadmap.
- Drop-in C ABI (`libsqlite3.so`). A C-API shim is a possible future crate, not
the core.
## Usage
Create a database, write to it, and read it back — and `sqlite3` can open it too:
```rust,ignore
use graphitesql::{Connection, Value};
let mut db = Connection::open_memory()?; // or Connection::create("app.db")?
db.execute("CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT)")?;
db.execute("INSERT INTO users(name) VALUES ('ada'), ('grace')")?;
db.execute("UPDATE users SET name = 'Ada Lovelace' WHERE id = 1")?;
let result = db.query("SELECT id, name FROM users ORDER BY name")?;
for row in &result.rows {
if let (Value::Integer(id), Value::Text(name)) = (&row[0], &row[1]) {
println!("{id}: {name}");
}
}
// Aggregates, GROUP BY, joins, expressions, scalar functions, transactions:
db.query("SELECT u.name, sum(o.amount) FROM users u JOIN orders o \
ON u.id = o.user_id GROUP BY u.name")?;
db.execute("BEGIN")?;
db.execute("DELETE FROM users WHERE id = 2")?;
db.execute("COMMIT")?;
```
Open an existing `sqlite3`-written file with `Connection::open("file.db")` (or
`open_readonly`). Low-level format primitives are public too
(`graphitesql::format::DatabaseHeader`, `graphitesql::btree`, …).
## Command-line shell
The crate ships a `graphitesql` binary modeled on the `sqlite3` CLI:
```sh
cargo run --bin graphitesql # in-memory, interactive
cargo run --bin graphitesql -- app.db # open/create app.db, interactive
cargo run --bin graphitesql -- app.db "SELECT * FROM users;" # one-shot
```
It accepts `;`-terminated SQL (multi-line) and dot-commands: `.tables`,
`.schema [table]`, `.headers on|off`, `.help`, `.quit`. Results print in
SQLite's default `|`-separated list mode.
## Feature flags
| `std` | on | std-file `Vfs`, `std::error::Error` impl |
| `fts5` | on | built-in FTS5 full-text search (`MATCH`, `bm25()`/`rank`, `highlight()`) |
Disable default features for `no_std`. An in-memory VFS (`:memory:`) is always
available, including on wasm. Drop `fts5` (e.g. `--no-default-features --features
std`) to build without full-text search.
## Building & testing
```sh
cargo test # full suite (std)
cargo build --no-default-features # no_std build
cargo clippy --all-targets # lints (unsafe is forbidden)
```
## Reference material & attribution
graphitesql is an independent re-implementation. It uses SQLite's public-domain
source and documentation purely as a **specification reference** — no SQLite code
is compiled into this crate. Fetch the (git-ignored, hash-verified) reference
tree with:
```sh
./reference/fetch.sh
```
Deep gratitude to D. Richard Hipp and the SQLite developers. See
[`NOTICE`](NOTICE) and [`ATTRIBUTION.md`](ATTRIBUTION.md).
## License
**Public domain**, mirroring SQLite. In place of a legal notice, graphitesql
carries a blessing — see [`LICENSE`](LICENSE). The SPDX identifier is
`blessing` (the SQLite Blessing).