sqlrite 1.0.2

RAG-oriented SQLite wrapper for AI agent workloads
Documentation 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136

# SQL Retrieval

SQLRite adds retrieval-aware operators, functions, and `SEARCH(...)` to SQLite.

## What SQLRite Adds

| Feature | Purpose |
|---|---|
| vector distance operators | compare stored vectors to a query vector |
| retrieval helper functions | compute lexical or hybrid scores in SQL |
| retrieval index DDL | declare vector and text indexes |
| `SEARCH(...)` | concise SQL-native hybrid retrieval |
| `EXPLAIN RETRIEVAL` | inspect retrieval planning |

Create a demo database first:

```bash
sqlrite init --db sqlrite_demo.db --seed-demo
```

## Interactive shell

```bash
sqlrite sql --db sqlrite_demo.db
```

Useful helpers:

- `.help`
- `.tables`
- `.schema [table]`
- `.example`
- `.exit`

## One-shot SQL

```bash
sqlrite sql --db sqlrite_demo.db --execute "SELECT id, doc_id FROM chunks LIMIT 3;"
```

## Distance operators

| Operator | Meaning |
|---|---|
| `<->` | L2 distance |
| `<=>` | cosine distance |
| `<#>` | negative inner product |

```bash
sqlrite sql --db sqlrite_demo.db --execute "
SELECT id,
       embedding <-> vector('0.95,0.05,0.0') AS l2,
       embedding <=> vector('0.95,0.05,0.0') AS cosine_distance,
       embedding <#> vector('0.95,0.05,0.0') AS neg_inner
FROM chunks
ORDER BY l2 ASC, id ASC
LIMIT 3;"
```

## Helper functions

| Function | Purpose |
|---|---|
| `vector(...)` | create a vector literal |
| `embed(text)` | embed a text string |
| `bm25_score(query, document)` | lexical score |
| `hybrid_score(vector_score, text_score, alpha)` | weighted fusion |
| `vec_dims(vector_expr)` | inspect vector dimensions |
| `vec_to_json(vector_expr)` | serialize vector data |

## Retrieval index DDL

### Vector index

```bash
sqlrite sql --db sqlrite_demo.db --execute "
CREATE VECTOR INDEX IF NOT EXISTS idx_chunks_embedding_hnsw
ON chunks(embedding)
USING HNSW
WITH (m=16, ef_construction=64);"
```

### Text index

```bash
sqlrite sql --db sqlrite_demo.db --execute "
CREATE TEXT INDEX IF NOT EXISTS idx_chunks_content_fts
ON chunks(content)
USING FTS5;"
```

## `SEARCH(...)`

```bash
sqlrite sql --db sqlrite_demo.db --execute "
SELECT chunk_id, doc_id, hybrid_score
FROM SEARCH(
       'local memory',
       vector('0.95,0.05,0.0'),
       5,
       0.65,
       500,
       'balanced',
       NULL,
       NULL
     )
ORDER BY hybrid_score DESC, chunk_id ASC;"
```

Sample output:

```json
[
  {
    "chunk_id": "demo-1",
    "doc_id": "doc-a",
    "hybrid_score": 1.3808426976203918
  }
]
```

## `EXPLAIN RETRIEVAL`

```bash
sqlrite sql --db sqlrite_demo.db --execute "
EXPLAIN RETRIEVAL
SELECT id,
       hybrid_score(
         1.0 - (embedding <=> vector('0.95,0.05,0.0')),
         bm25_score('local memory', content),
         0.65
       ) AS score
FROM chunks
ORDER BY score DESC, id ASC
LIMIT 3;"
```