# Built-in 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.
| Full-text search | SQLite FTS5, including Unicode and trigram tokenizers | [FTS5](https://www.sqlite.org/fts5.html) |
| Spatial indexing | SQLite R*Tree | [R*Tree](https://www.sqlite.org/rtree.html) |
| Native UUID, temporal values and temporal SQL functions | BoxDB's patched SQLite engine | [Temporal types](temporal.md) |
| Exact decimal arithmetic | BoxDB's patched SQLite engine | [Native decimals](native-decimal.md) |
| Unicode text functions and collation | sqlean-text | [Text functions](https://github.com/nalgeon/sqlean/blob/0.28.4/docs/text.md) |
| Regular expressions | sqlean-regexp with PCRE2 | [Regexp functions](https://github.com/nalgeon/sqlean/blob/0.28.4/docs/regexp.md) |
| Vector search | sqlite-vec 0.1.9 | [sqlite-vec](https://github.com/asg017/sqlite-vec/tree/v0.1.9) |
## 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.
```sql
CREATE TABLE prices(amount DECIMAL(12,2));
INSERT INTO prices VALUES('10.00'),('2.00'),('0.10'),('0.20');
SELECT SUM(amount) FROM prices; SELECT amount FROM prices ORDER BY amount;
SELECT CAST('0.1' AS DECIMAL) + CAST('0.2' AS DECIMAL); SELECT decimal_div('1','3',2,'half_even'); SELECT decimal_round('123.456',2,'half_even'); ```
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](native-decimal.md)
for conversions, limits, windows, and storage semantics.
## Text and regular expressions
```sql
SELECT text_lower('ПРИВЕТ'); SELECT 'Ёж' = 'ёж' COLLATE text_nocase; SELECT text_split('a/b/c','/',2); SELECT regexp_substr('order #123','\d+'); SELECT regexp_replace('a1b2','\d','_'); ```
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
```sql
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.