SQL extensions

Rust, TypeScript and the CLI register the same statically linked extensions on every BoxDB connection, including snapshot import. No system extension packages or runtime shared-library loading are needed. Registration is local to BoxDB’s connections and does not change other SQLite connections in the process.

Capability	Implementation	Reference
Full-text search	SQLite FTS5, including Unicode and trigram tokenizers	FTS5
Spatial indexing	SQLite R*Tree	R*Tree
Native UUID, temporal values and temporal SQL functions	BoxDB’s patched SQLite engine	Temporal types
Exact decimal arithmetic	BoxDB’s patched SQLite engine	Native decimals
Unicode text functions and collation	sqlean-text	Text functions
Regular expressions	sqlean-regexp with PCRE2	Regexp functions
Vector search	sqlite-vec 0.1.9	sqlite-vec

Native decimals

DECIMAL(p,s) is a native type in BoxDB’s patched SQLite. Operators, comparison, indexes, ORDER BY, grouping, and aggregates use exact decimal values. Rust uses Type::Decimal and Value::Decimal(String); TypeScript returns strings.

CREATE TABLE prices(amount DECIMAL(12,2));
INSERT INTO prices VALUES('10.00'),('2.00'),('0.10'),('0.20');
SELECT SUM(amount) FROM prices; -- decimal: 12.3
SELECT amount FROM prices ORDER BY amount;
SELECT CAST('0.1' AS DECIMAL) + CAST('0.2' AS DECIMAL); -- decimal: 0.3
SELECT decimal_div('1','3',2,'half_even'); -- decimal: 0.33
SELECT decimal_round('123.456',2,'half_even'); -- decimal: 123.46

Use decimal strings for exact input. REAL mixing requires explicit conversion. Native / and AVG require terminating results; use decimal_div with an explicit scale and rounding mode for other divisions. Column precision and scale are checked without implicit rounding. See native decimals for conversions, limits, windows, and storage semantics.

Text and regular expressions

SELECT text_lower('ПРИВЕТ'); -- привет
SELECT 'Ёж' = 'ёж' COLLATE text_nocase; -- 1
SELECT text_split('a/b/c','/',2); -- b
SELECT regexp_substr('order #123','\d+'); -- 123
SELECT regexp_replace('a1b2','\d','_'); -- a_b_

Use the explicit text_nocase collation for Unicode comparisons. SQLite’s lower, upper, NOCASE, concat, concat_ws, ltrim, rtrim and octet_length keep their original behavior. sqlean’s corresponding functions remain available as text_concat, text_join, text_ltrim, text_rtrim and text_size. These names do not redirect calls inside other sqlean functions.

Full-text and vector search

CREATE VIRTUAL TABLE documents USING fts5(title,body);
INSERT INTO documents VALUES('SQLite guide','A small database');
SELECT title, bm25(documents), highlight(documents,0,'[',']')
FROM documents WHERE documents MATCH 'sqlite' ORDER BY bm25(documents);

CREATE VIRTUAL TABLE fragments USING fts5(body,tokenize='trigram');
INSERT INTO fragments VALUES('abcdefghij');
SELECT body FROM fragments WHERE fragments MATCH 'cdefg';

CREATE VIRTUAL TABLE embeddings USING vec0(embedding float[3]);
INSERT INTO embeddings(rowid,embedding) VALUES(1,'[1,0,0]'),(2,'[0,1,0]');
SELECT rowid,distance FROM embeddings
WHERE embedding MATCH '[0.9,0,0]' AND k=1;

Trigram search matches substrings; it does not automatically correct typos. FTS5 indexes linked to ordinary tables need synchronization, usually with application triggers as described in the FTS5 documentation.

vec0 enforces vector element type and dimension. In an ordinary STRICT table, use a BLOB column and vec_f32(?) to pack a JSON vector. BoxDB’s DOUBLE[] is a JSON array, not sqlite-vec’s packed vector representation. Embeddings must be generated by the application; sqlite-vec stores and searches them. The library is pre-v1 and its version is pinned. Measure vector query latency for your data and VPS before setting capacity limits.

Storage and interoperability

Scalar extensions work with both binary SQLite and readable YAML. FTS5, R*Tree and vec0 virtual tables work in memory and binary databases, including binary snapshot export and restore. Readable YAML does not support virtual tables; attempting to create one rolls back the statement and leaves the file usable.

The file format remains ordinary SQLite. External SQLite connections need the same modules, functions or collations when accessing schema objects that use them. For example, an index on text_lower(name) requires that function, and a table declaring COLLATE text_nocase requires that collation. Native values and their indexes require BoxDB’s patched engine. JSON columns validate complete documents; JSON1/JSONB SQL functions, operators and virtual tables are disabled.