This page covers the lower of litesearch’s two routes. database() opens a SQLite database ready for search, get_store creates a store in it, and Database.search runs a hybrid query against that store. The rest are the parts those three are made of, which you can also call on their own: keyword search, exact and approximate vector search, rank fusion, spelling correction and a few tools for writing from several threads.
A store is a table with one row per chunk of text. It has a content column for the text, an embedding column for the text’s vector, a metadata column for JSON, and an FTS5 full-text index over content and metadata that SQLite keeps up to date as rows change. If you do not need your own schema or SQL, [Index](https://Karthik777.github.io/litesearch/api.html#index) sets all of this up for you.
Tokenizers
FTS5 does not index text as written. A tokenizer splits it into tokens, the words FTS5 looks up, and can change them on the way in, for example by lowercasing or stemming (reducing running to run). The query goes through the same tokenizer, so a query matches a row when they share tokens. A store’s tokenizer is fixed when its table is created.
The common FTS5 setting, porter, stems the output of FTS5’s default tokenizer unicode61, which splits on anything that is not a letter or a digit. That breaks identifiers apart: fts_search is indexed as ft and search. It also breaks Devanagari words into pieces, because it treats the vowel signs as separators.
litesearch’s default chain is porter simplify casefold 1 sanskrit unicodewords. Each tokenizer passes its output to the one on its left. unicodewords splits text into words by the Unicode word-boundary rules (UAX #29), which keep fts_search whole. sanskrit adds, beside each word, copies reduced to plain ASCII, so a Devanagari or romanised word can be found by its ASCII spelling. simplify casefold 1 lowercases, and porter stems English. On this chain fts_search stays one token, and Krishna duties gives the tokens krishna, with the ASCII copy krsna beside it, and duti.
The order matters. sanskrit sits inside porter, so the ASCII copy is made from the original word and then stemmed the same way an ASCII query is. With sanskrit outside porter, धर्मक्षेत्रे would be indexed as dharmaksetre while the query dharmaksetre stems to dharmaksetr, and the word could not be found by its own spelling. That happened to 3 of 14 ordinary Devanagari words tested.
_tokenizers_ok checks that the whole chain can be built on a connection, which is the only reliable test: simplify refuses to be built on its own, so checking each name separately would report a working chain as broken.
Writing rows
process_content is the general way to add chunks to a store. It embeds them with your encoder function if asked, gives each row an id (a hash of its content, so the same text gets the same id), and upserts them, which inserts new rows and updates rows whose id already exists. It writes chunk rows per transaction. embed_chunk is the embedding step on its own.
write_txn opens a write transaction with BEGIN IMMEDIATE, which takes SQLite’s write lock at the start instead of at the first write, so two writers cannot both start and then deadlock. A write_txn inside another one joins the outer transaction. db_lock is the lock litesearch holds while it reads and writes on one connection, so threads that share a connection do not interleave. A thread may hold db_lock while it waits on workers that read the same connection, as Kosha.context does, so the read paths never take it: on an ordinary connection get_index and usearch_sql load under a lock of their own.
busy_window is for opening connections while another process holds the write lock. SQLite waits up to the busy timeout for a lock before raising BusyError. apsw’s recommended settings, which apswutils turns on, set a 100 ms timeout and run PRAGMA optimize while a connection is being opened, before your code can change the timeout. busy_window raises that default for every connection opened inside the block and restores it afterwards. It counts nested windows, so the outermost one restores the original value. database(busy_timeout=) uses it for you.
def process_content( store, # target Table (hash-id store) content, # iterable of chunk dicts embed:bool=True, # embed content before upsert emb_fn:NoneType=None, # embedder, required when embed=True hash_id_columns:tuple=('content',), # columns the row id is hashed over; add 'node_id' to keep identical text in different sections distinct parallel:bool=False, # deprecated no-op; concurrent writers need `database(busy_timeout=...)`, which sets the wait on the connection chunk:int=5000, # rows per transaction**kw):
Embed chunks when embed is set, give each a content-hash id, and upsert them into store, chunk rows per transaction.
def sql_in( col, vals, # SQL IN clause; callers guard against empty vals):
get_store and get_tree create their tables if they do not exist yet, and search functions call them on every query. ensured remembers the table objects they built on this connection, so after the first call no CREATE TABLE statements run.
Forget the cached table objects and HNSW indexes for one store, or all of them, after dropping tables.
Stores
get_store creates a store, or opens it if it exists. By default the id column is an integer that SQLite assigns. With hash=True, id is a SHA-1 hash of the content instead, so inserting the same text twice leaves one row. That suits code and other text you re-ingest often. With ann=True, the store also gets an HNSW index for fast vector search, described below.
upsert_all writes rows with a single prepared statement per batch: rows whose primary key is new are inserted, and rows whose key already exists are updated.
def get_store( name:str='store', # table namehash:bool=False, # make `id` a SHA-1 hash of the content, so the same text is stored once ann:bool=False, # also keep a usearch HNSW index for this store, in a file beside the database ndim:int=None, # embedding dims for the ANN index (inferred on first sync if None) metric:str='cosine', # ANN distance metric (cosine,inner,sqeuclidean,divergence) dtype:type=float16, # number type of the stored vectors, recorded for the HNSW index connectivity:int=None, # HNSW connectivity (usearch default if None) expansion_add:int=None, # HNSW add expansion expansion_search:int=None, # HNSW search expansion index_path:str=None, # sidecar path (defaults to <db>.<name>.usearch; None db -> in-memory) tokenize:str=None, # FTS5 tokenizer chain (default: litesearch's cross-script chain, or 'porter' if it cannot be built) encoder:str=None, # the encoder whose vectors this store holds (`encoder_id`); checked on reopen**kw):
Create or open a store: a table of content, embedding and metadata with an FTS5 index, and optionally an HNSW index.
def upsert_all( tbl, # target Table, with `pk` declared as its primary key rows, # dicts of scalars, upserted on `pk` pk, # primary-key column name or tuple of them chunk:int=2000, # rows per `executemany` batch):
Insert rows, updating any whose pk already exists, with one prepared statement per batch.
A store records which encoder and which tokenizer created it, in a table called store_meta. Reopening it with a different one raises StoreMismatch.
The vectors from two encoders cannot be compared, even when they have the same length. potion-base-8M and potion-multilingual-128M both produce 256 numbers per text, so nothing about the vectors shows that they came from different models. Before this check, opening a store with the wrong encoder added vectors from another model to the same index, and searches returned wrong neighbours without any error. The tokenizer is recorded for the same reason: a query tokenized differently from the index looks for tokens the index does not contain. An encoder with no name records '', and an empty value never conflicts, because litesearch cannot tell what a custom encoder is.
from fastcore.test import test_eq, test_faildb = Database(':memory:') # `database()` comes later in this notebook; the stamp needs no extensionsdb.get_store('s', encoder='a/one')test_eq(db._store_meta('s')['encoder'], 'a/one')reopen =lambda**kw: (db.forget_ensured('s'), db.get_store('s', **kw))[1]reopen(encoder='a/one') # the same encoder reopenstest_fail(lambda: reopen(encoder='b/two'), contains="not 'b/two'")reopen() # an unnamed encoder never conflictstest_eq(db._store_meta('s')['encoder'], 'a/one')
When usearch’s HNSW index fails its probe (see postfix), get_index returns an ExactIndex instead. It has the methods of usearch’s Index that litesearch calls, holds the vectors as one float32 matrix, and searches all of them with numpy, so results are exact. _np_dist computes the four metrics as usearch does, and the numpy path of vec_search uses it too. ExactIndex.save writes no index. It leaves a .stale file beside the sidecar, so the next process with usearch on rebuilds the index from the table instead of loading one that misses the rows written meanwhile.
A numpy stand-in for usearch’s Index, used when usearch is off: the same calls, searched exactly.
An HNSW index is registered in a table called usearch_indices, one row per store, holding the vector length, the metric, the dtype and the path of the index file. get_index loads an index from that file the first time a connection asks for it and keeps it in memory; _save_index writes it back. When there is no file, or a .stale file says it is out of date, get_index builds the index from the table’s vectors and saves it. The vector length can be left out when the store is created, and it is then taken from the first vector added.
The HNSW indexes loaded on this connection, keyed by store name.
Searching one leg at a time
A hybrid search has two legs, a keyword search and a vector search. Each is available on its own, on any table, not only on stores.
fts_search runs an FTS5 query and returns rows ordered by bm25 rank. FTS5 reports bm25 as a negative number in the rank column, so lower is better.
vec_search is exact vector search. It computes the distance from the query vector to every row’s embedding in SQL and returns the nearest rows, with the distance in _dist. When usearch’s SQLite extension is off, it computes the same distances with numpy and returns the same rows; a where that names _dist works only in SQL. It reads every row, so it is always correct and gets slower as the table grows; up to a few thousand rows it is fast. ann_search, further down, is the approximate alternative for large stores.
Both legs take the vector’s number type as dtype, and getting it wrong is silent in SQL. model2vec encoders return float32 and FastEncode returns float16. If float32 bytes are searched as float16, the distance function reads them as twice as many float16 numbers, many of them invalid, and returns NULL for every row. The vector leg then comes back in table order, and a hybrid search quietly becomes a keyword search. _dtype_check warns when the dtype differs from the one the store’s HNSW index recorded, or when every distance comes back NULL or 0.
def fts_search( q:str, # query string columns:list|None=None, # columns to return order_by:str|None=None, # order by clause limit:int|None=None, # limit on number of results offset:int|None=None, # offset for results where:str|None=None, # additional where clause where_args:dict|None=None, # args for where clause quote:bool=False, # quote FTS query to disable special chars include_rank:bool=True, # include rank column)->list:
Run an FTS5 query on any table with a full-text index, best bm25 rank first unless order_by says otherwise.
FTS5 keeps its index current through triggers that run on every insert, update and delete. bulk_load removes those triggers for the duration of a with block and rebuilds the full-text index once at the end. Use it for large offline loads. During the block the full-text index does not see the new rows, and for a small update a full rebuild costs more than the triggers would have.
bulk_load
def bulk_load():
Remove this table’s FTS5 triggers for the block, then restore them and rebuild the full-text index once.
Rows similar to a row
ann_neighbors finds the rows nearest an existing row, such as “more like this” for a search result. It uses the vector the HNSW index already holds for that row, so it loads no model and embeds no text: the call is one index lookup and one query for the matching rows. That makes it cheap enough to run every time a user moves to a new result.
ann_vec returns the stored vector for a row’s key (its rowid), or None when the index does not hold it. ann_neighbors leaves the starting row out of its results unless you pass include_self=True.
def ann_neighbors( key:int, # usearch key (rowid) to search from limit:int=15, # neighbours to return columns:list=None, # columns to return (rowid always included) where:str=None, # additional where clause where_args:dict=None, # args for where clause include_self:bool=False, # keep the anchor row in the results dtype:type=float16, # embedding dtype):
The rows nearest an already-indexed row, found from the vector the HNSW index holds for it.
def ann_vec( key:int, # usearch key (the row's rowid) dtype:type=float16, # fallback dtype when the registry has none):
The vector the HNSW index holds for key, or None when the index does not hold it.
Reciprocal Rank Fusion
rrf_all merges ranked lists into one. Each row earns weight / (k + position) from every list it appears in, counting positions from 0, and the amounts add up. With the default k=60, the top row of a list earns 1/60 and the row at position 60 earns half that. A row that appears in two lists collects two amounts, so it usually beats a row that tops only one. Because RRF uses positions only, it can merge lists whose scores are not comparable, such as bm25 ranks and vector distances. Rows are matched across lists by id_key, rowid by default. weights gives each list more or less influence.
def rrf_all( lists, # ranked result lists to fuse k:int=60, # rank at which a hit is worth half the top spot limit:int=50, # results returned id_key:str='rowid', # the key rows are joined on weights:NoneType=None, # one weight per list; None means all equal)->list:
Merge any number of ranked lists into one with Reciprocal Rank Fusion, highest combined score first.
from litesearch.core import rrf_all, rrf_mergefts = [dict(rowid=1, t='a'), dict(rowid=2, t='b'), dict(rowid=3, t='c')]vec = [dict(rowid=3, t='c'), dict(rowid=4, t='d')]# rowid 3 is 3rd on one list and 1st on the other, which beats anything ranked oncefused = rrf_all([fts, vec])assert [r['rowid'] for r in fused][:2] == [3, 1]assert {r['rowid'] for r in fused} == {1, 2, 3, 4}assert rrf_merge(fts, vec) == fused# a weight is a thumb on the scale: doubling the vector leg lifts its top hit past the FTS top hitassert [r['rowid'] for r in rrf_all([fts, vec], weights=[1.0, 2.0])][:2] == [3, 4]
Sharing one connection between threads
apsw, the SQLite driver underneath fastlite, raises ThreadingViolationError when one thread calls into a connection while another thread is using it. apsw cursors are lazy: a thread reading rows keeps the connection busy between rows, so even two readers collide.
Conn wraps a connection so threads can share it. Each statement runs and is read to the end under one lock, and a thread that opens a transaction keeps the lock until the transaction ends. Rows holds the rows a statement returned, with the parts of the apsw cursor interface that apswutils uses: iteration, fetchone, fetchall, row_trace and the column description. Cur is the cursor Conn.cursor() returns.
Reading every row at once costs memory on a large SELECT, which is why sharing is off by default and database(shared=True) turns it on. One caveat: apswutils’ Table.insert reads last_insert_rowid() in a second statement, and another thread’s insert can run between the two. If you read last_pk after an insert on a shared connection, hold db_lock(db) around both.
The rows of one statement, read in full under the connection lock, behind the cursor methods apswutils calls.
Opening a database
database() opens a connection and prepares it for search. It registers apsw’s FTS5 tokenizers and litesearch’s sanskrit tokenizer, and checks that the default chain works. It does not load usearch’s SQLite extension, which provides the vector distance functions: vec_search loads it into the connection on first use, through Database.usearch_sql(), after the probe in postfix has passed. Keyword search never loads it. Call db.usearch_sql() yourself before calling distance_* functions in your own SQL; it returns False when sem_search=False or the probe failed.
database() turns on WAL mode by default, which lets readers continue while a writer writes.
Tokenizers are registered on the connection, not stored in the file, because FTS5 looks a tokenizer up by name every time it reads or writes the index. Any connection that opens a litesearch store must register them first, and database() does.
def database( pth_or_uri:str=':memory:', # the database name or URL wal:bool=True, # use WAL mode sem_search:bool=True, # allow usearch's SQL distance functions, loaded on first use busy_timeout:int=None, # ms to wait for a locked database (None: apsw's 100 ms); set it when several connections write shared:bool=False, # wrap the connection in `Conn` so threads can share it**kw)->apswutils.db.Database: # additional args to pass to apswutils database
Open a SQLite database with litesearch’s FTS5 tokenizers registered; usearch’s distance functions load on the first vector search.
With shared=True, database() wraps the connection in Conn, and db_lock returns that connection’s lock, so write_txn and every statement wait on the same lock. In the test below, four reader threads count the rows in a store while a writer adds four transactions of 50 rows, each written in five batches. On an ordinary connection the readers raise ThreadingViolationError. On a shared one they run, and every count they see is a multiple of 50, so no reader ever saw a transaction half written.
import os, tempfile
_sh = database(os.path.join(tempfile.mkdtemp(), 'shared_conn.db'), shared=True, sem_search=False)_shs = _sh.get_store(name='store', hash=True)assert db_lock(_sh) is _sh.conn.lock_seen, _sherrs, _shdone =set(), [], threading.Event()def _shread():try:whilenot _shdone.is_set(): _seen.add(_sh.q('select count(*) n from store')[0]['n'])exceptExceptionas e: _sherrs.append(type(e).__name__)def _shwrite():try:for b inrange(4):with write_txn(_sh): process_content(_shs, [{'content': f'b{b}-r{i}'} for i inrange(50)], embed=False, chunk=10)exceptExceptionas e: _sherrs.append(type(e).__name__)finally: _shdone.set()_shts = [threading.Thread(target=_shread) for _ inrange(4)] + [threading.Thread(target=_shwrite)]for t in _shts: t.start()for t in _shts: t.join()assertnot _sherrs, _sherrsassertall(n %50==0for n in _seen), _seensorted(_seen)
[0, 50, 100, 200]
A transaction opened on one thread keeps the lock until it ends, whether it was opened with with conn: or with BEGIN. A statement from another thread waits for the commit instead of running inside the transaction and reading a row that is not committed yet.
The next tests cover writing from several threads, each with its own connection. apswutils turns on apsw’s recommended settings, which give a new connection a 100 ms busy timeout and run PRAGMA optimize while it opens. When several threads opened connections and wrote at once, that 100 ms ran out, BusyError was raised, and whole batches were lost without notice.
import threading, tempfile, os, apsw, apsw.bestpractice_orig = apsw.bestpractice.connection_busy_timeout.__defaults__# write_txn commits, rolls back on error, and nests without opening a second transaction_wdb = database(sem_search=False); _wt = _wdb.get_store(name='w', hash=True)with write_txn(_wdb): _wt.insert({'content':'a'})try:with write_txn(_wdb): _wt.insert({'content':'b'});raiseValueError('boom')exceptValueError: passassertlen(_wt()) ==1, 'a failed write_txn must roll back'with write_txn(_wdb):with write_txn(_wdb): _wt.insert({'content':'c'}) # nested is a no-opassertlen(_wt()) ==2andnot _wdb.conn.in_transaction
Nested busy_window blocks keep the raised timeout until the outermost one exits, and only that exit restores the original value.
with busy_window(30_000):assert apsw.bestpractice.connection_busy_timeout.__defaults__ == (30_000,)with busy_window(30_000): passassert apsw.bestpractice.connection_busy_timeout.__defaults__ == (30_000,), 'inner exit must not restore'assert apsw.bestpractice.connection_busy_timeout.__defaults__ == _orig
Eight threads writing to one database need only busy_timeout= on each database() call. The connection opens inside a busy_window, so PRAGMA optimize waits instead of failing, and every later write waits on the timeout the connection keeps. The BEGIN IMMEDIATE batches are then safe without another window. Opening is the step that fails: with the default 100 ms timeout, this test passed 9 of 15 runs.
_cp = os.path.join(tempfile.mkdtemp(), 'parallel.db')_cmain = database(_cp, sem_search=False, busy_timeout=30_000); _cmain.get_store(name='store', hash=True)_cerrs, _cconns, _cesc = [], [], []_prev_hook = threading.excepthookthreading.excepthook =lambda a: _cesc.append(type(a.exc_value).__name__) # thread errors never reach us otherwisedef _cingest(tid):try: _d = database(_cp, sem_search=False, busy_timeout=30_000); _cconns.append(_d) process_content(_d.t['store'], [{'content':f't{tid}-r{i}', 'metadata':'{}'} for i inrange(200)], embed=False, parallel=True, chunk=50)exceptExceptionas e: _cerrs.append((tid, type(e).__name__))_cts = [threading.Thread(target=_cingest, args=(i,)) for i inrange(8)]for _t in _cts: _t.start()for _t in _cts: _t.join()threading.excepthook = _prev_hookassertnot _cerrs andnot _cesc, (_cerrs, _cesc)assertlist(_cmain.conn.execute('SELECT count(*) FROM store'))[0][0] ==8*200, 'rows lost under contention'assert apsw.bestpractice.connection_busy_timeout.__defaults__ == _orig, 'busy_window leaked'
# `db_lock` is what makes the read-modify-write paths safe when threads share *one* connection.# The busy_window test above does not reach it: there every thread opens its own connection, so# they contend in SQLite rather than in python. Here the hash-id upserts interleave on the same# handle, which is the case the lock exists for._sp = os.path.join(tempfile.mkdtemp(), 'shared.db')_smain = database(_sp, sem_search=False); _smain.get_store(name='store', hash=True)_sst = _smain.t['store']assert db_lock(_smain) is db_lock(_smain), 'one lock per connection, not one per call'_serrs, _sesc = [], []_prev_hook = threading.excepthookthreading.excepthook =lambda a: _sesc.append(type(a.exc_value).__name__)def _singest(tid):try: process_content(_sst, [{'content':f's{tid}-r{i}', 'metadata':'{}'} for i inrange(100)], embed=False, chunk=25)exceptExceptionas e: _serrs.append((tid, type(e).__name__))_sts = [threading.Thread(target=_singest, args=(i,)) for i inrange(8)]for _t in _sts: _t.start()for _t in _sts: _t.join()threading.excepthook = _prev_hookassertnot _serrs andnot _sesc, (_serrs, _sesc)assertlist(_smain.conn.execute('SELECT count(*) FROM store'))[0][0] ==8*100, 'rows lost on a shared connection'assert apsw.bestpractice.connection_busy_timeout.__defaults__ == _orig, 'busy_window leaked'
# A thread may hold `db_lock` and wait on workers reading the same connection, as `Kosha.context` does.# The workers' first `get_index`, `ann_search` and `usearch_sql` must not wait on that lock: on 0.1.47 they did, forever._lp = os.path.join(tempfile.mkdtemp(), 'held.db')_le = {c: np.eye(4, dtype=np.float16)[i] for i,c inenumerate('ABC')}_lemb =lambda ts: [_le[t] for t in ts]database(_lp).get_store('st', hash=True, ann=True).sync([dict(content=c) for c in'ABC'], emb_fn=_lemb)Path(f'{_lp}.st.usearch').unlink() # the workers build the index from the table, and save it_ld, _lout = database(_lp), []def _leg(): _lt = _ld.t['st'] _lout.append((_ld.get_index('st').size, len(_lt.ann_search(_le['A'].tobytes(), limit=2)), len(_lt.vec_search(_le['A'].tobytes(), limit=2))))with db_lock(_ld): _lts = [threading.Thread(target=_leg, daemon=True) for _ inrange(4)]for _t in _lts: _t.start()for _t in _lts: _t.join(30)assertnotany(_t.is_alive() for _t in _lts), 'a worker waited on the db_lock its caller holds'test_eq(_lout, [(3, 2, 2)]*4)assert Path(f'{_lp}.st.usearch').exists() == usearch_ok('index')# one index per store, however many threads ask for it at once_ld2 = database(_lp); _lidx = []_lts = [threading.Thread(target=lambda: _lidx.append(_ld2.get_index('st'))) for _ inrange(8)]for _t in _lts: _t.start()for _t in _lts: _t.join()assertall(i is _ld2.ann_indices['st'] for i in _lidx) and _lidx[0].size ==3
# Table.sync forwards chunk down to process_content, so hash-diff syncs (what kosha's package# ingestion uses) get the same chunked BEGIN IMMEDIATE writes as a direct process_content._sdb = database(sem_search=False, busy_timeout=30_000); _sst = _sdb.get_store(name='sy', hash=True)_srows = [{'content': f'row {i}', 'metadata': '{}'} for i inrange(120)]assert _sst.sync(_srows, embed=False, parallel=True, chunk=25) == {'changed':120,'same':0,'removed':0}assertlen(_sst()) ==120assert _sst.sync(_srows, embed=False, parallel=True, chunk=25) == {'changed':0,'same':120,'removed':0} # idempotentassert _sst.sync(_srows[:60], embed=False, parallel=True, chunk=25) == {'changed':0,'same':60,'removed':60}assertlen(_sst()) ==60
Building and ranking keyword queries
Query strings
An FTS5 query is a small language of its own. Words are combined with AND, OR and NOT, text in double quotes is a phrase, * after a word matches any word starting with it, and column : (...) restricts a match to one column. Text typed by a user can contain a quote, a hyphen or the word OR, which FTS5 then reads as syntax, and the query fails or means something else.
fts_or, fts_and and fts_phrases build FTS5 queries from a list of terms. Each term is put in double quotes, so whatever it contains is matched as text, and repeated or empty terms are dropped.
fts_or matches rows containing any of the terms. Use it for recall: a word together with its spelling variants or synonyms, such as ["bhisma", "bhishma"]. column= limits the match to one column or a list of columns.
fts_and matches rows containing all of the terms. With prefix_last=True the last term also matches as a prefix, for search-as-you-type, where the last word is usually unfinished.
fts_phrases matches rows containing any of the phrases “term suffix”. Passing ("is", "means") as suffixes finds sentences that define a term, such as “comparison is knowing by likeness”.
These differ from pre() in litesearch.data, which Database.search applies to a raw query: pre() drops stopwords, adds a prefix wildcard to every word and joins them with OR.
Ranking by query terms
bm25 has two weaknesses for this kind of search. A short row that repeats a common query word can outrank a long row that contains a rare one, and bm25 scores from two different FTS tables are on different scales and cannot be compared. rank_fts re-orders hits by three keys in turn: how many of the query’s terms a hit contains, then the summed IDF of those terms (IDF, inverse document frequency, is higher for terms that appear in fewer rows), and then bm25 relative to the best hit in its own list. VedicReader’s Ask feature fetches 200 candidates per table (fts_depth) before this re-ranking. Together with correct, it raised the number of questions with a hit from its library from 69 to 84 out of 100.
def fts_phrases( terms, # terms, each followed by every suffix suffixes, # words that follow a term: `('is', 'means')` finds definitions column:NoneType=None, # a column name or a list of them)->str:
An FTS5 query matching rows that contain any phrase term suffix, for every term and suffix.
def fts_or( terms, # terms or phrases; each is quoted column:NoneType=None, # a column name or a list of them to restrict the match to)->str:
An FTS5 query matching rows that contain any of terms, each quoted so it is matched as text.
from fastcore.test import test_eqtest_eq(fts_or(['bhisma', 'bhishma', 'bhisma', '']), '"bhisma" OR "bhishma"')test_eq(fts_or(['a'], 'meaning'), '"meaning" : ("a")')test_eq(fts_or(['a'], ['title', 'body']), '{"title" "body"} : ("a")')test_eq(fts_or([]), '')test_eq(fts_and(['krsna', 'arj']), '"krsna" AND "arj"')test_eq(fts_and(['krsna', 'arj', 'krsna'], prefix_last=True), '"krsna" AND "arj"*')test_eq(fts_and([]), '')test_eq(fts_phrases(['comparison'], ('is', 'are')), '"comparison is" OR "comparison are"')# a quote, a hyphen or a bare OR inside a term is text, not syntax_fd = database(sem_search=False); _fs = _fd.get_store('store')_fs.insert_all([dict(content=c) for c in ['he said "hello" there', 'self-taught or not', 'Comparison is knowing by likeness']])_m =lambda q: [r['rowid'] for r in _fd.q('select rowid from store_fts where store_fts match ?', [q])]test_eq(_m(fts_or(['said "hello"', 'self-taught'], 'content')), [1, 2])test_eq(_m(fts_phrases(['comparison'], ('is', 'means'))), [3])test_eq(_m(fts_or(['OR'])), [2])test_eq(_m(fts_and(['comparison', 'kno'], prefix_last=True)), [3])test_eq(_m(fts_and(['comparison', 'kno'])), [])
fts_vocab returns every term in a table’s full-text index with two counts: the number of rows it appears in and the number of times it appears. It reads an FTS5 fts5vocab virtual table, created in the temp schema so that reading it never writes to the database file. The result is cached until this connection writes or another connection commits. The terms are the index’s own tokens after stemming and folding, so you see duti for “duties”, and krsna beside krishna.
fts_tokens runs any text through the table’s own tokenizer chain and returns one tuple per word position, with the unfolded token first and its folded copies after it. Use it to turn a query into the same tokens the index holds, for example before looking them up in fts_vocab.
Every term in this table’s full-text index, as {term: (rows containing it, occurrences)}.
_vd = database(sem_search=False); _vs = _vd.get_store('store')_vs.insert_all([dict(content=c) for c in ['the duties of a king', 'the nature of nature', 'Krishna spoke']])_v = _vs.fts_vocab()test_eq(_v['the'], (2, 2)); test_eq(_v['natur'], (1, 2)); test_eq(_v['duti'], (1, 1))assert'krsna'in _v and'krishna'in _vassert _vs.fts_vocab() is _v # cached_vs.insert(dict(content='a king again'))test_eq(_vs.fts_vocab()['king'], (2, 2)) # a write invalidates ittest_eq(_vs.fts_tokens('Krishna duties'), [('krishna', 'krsna'), ('duti',)])assertnot _vd.q("select name from sqlite_master where name like '%vocab%'") # nothing written to main
fts_sig returns a short signature of a table’s full-text index. It is a hash of FTS5’s structure record (row 10 of the <fts>_data table), which changes on every write to the index from any process. A write to a column the index does not cover leaves it unchanged. Use it as the key for anything you compute from the index and want to keep beyond one connection, such as the arrays litesearch.store.stored writes to disk.
fts_merge merges the index into a single segment. FTS5 adds a new segment for each batch of writes, and a query has to look in every segment, so an index built by many small writes gets slower to query. Merging an index that is already merged writes nothing.
no_open_optimize stops apsw from running PRAGMA optimize whenever a connection opens. That pragma may run ANALYZE and write to the WAL file, which a process that only reads may not want. It applies to every connection the process opens afterwards.
A short hash of this table’s full-text index that changes on every write to it from any process, or '' without an index.
_sd = database(sem_search=False); _ss = _sd.get_store('store')_s0 = _ss.fts_sig(); _ss.insert(dict(content='one'))_s1 = _ss.fts_sig();assert _s1 != _s0for c in ['two', 'three', 'four']: _ss.insert(dict(content=c))_ss.fts_merge(); _s2 = _ss.fts_sig()_ss.fts_merge(); test_eq(_ss.fts_sig(), _s2) # merging a merged index writes nothing_sd.execute('create table plain(x)'); test_eq(_sd.t.plain.fts_sig(), '') # no FTS indexno_open_optimize(); no_open_optimize() # idempotentassertnotany(apsw.bestpractice.connection_optimize in (c.cell_contents ifisinstance(c.cell_contents, list) else ())for h in apsw.connection_hooks for c in (getattr(h, '__closure__', None) or ()))
rank_fts takes the hits of one FTS query, or a list of hit lists from several tables. Each entry in terms is either one key or a set of keys that count as one term, such as a word together with its corrections or synonyms. keys turns a hit’s text into keys of the same kind; by default it folds each word with fold_token, so Bhīṣma becomes bhisma. df gives each key’s frequency: a row count, an occurrence count, or the (rows, occurrences) pair that fts_vocab returns. n is the number of rows in the corpus and defaults to the sum of the counts in df; when two hits contain the same number of terms, its value does not change their order. Hits that contain none of the terms go last, or are left out with drop=True.
In the test below, plain bm25 puts the short row “nature nature nature” second and the long row containing the rare word “comparison” fifth. rank_fts moves the long row to second, because “comparison” is the rarer term.
def rank_fts( hits, # FTS rows, or a list of lists of them, one per table terms, # query terms: each a key or a set of keys counted once df:NoneType=None, # key -> document count, occurrence count or `(documents, occurrences)` n:NoneType=None, # corpus size for IDF; None sums `df` text_col:str='content', # the column the terms are found in keys:NoneType=None, # text -> set of keys; None folds each word with `fold_token` rank_col:str='rank', # bm25 column, lower is better drop:bool=False, # leave out hits holding none of the terms)->list:
Order FTS hits by how many query terms they contain, then those terms’ summed IDF, then bm25 relative to the best hit in their list.
# bm25 puts the short rows saying the common word above the long one saying the rare word; rarity lifts it to second_rd = database(sem_search=False); _rs = _rd.get_store('store')_rows = ['nature nature nature', 'comparison, which is the knowing of a thing by its likeness to another thing already known to the one who knows it','the nature of fire', 'the nature of water', 'a river runs', 'the sky is blue', 'stones are hard', 'birds fly south', 'the sun rises','rain falls', 'comparison of nature']_rs.insert_all([dict(content=c) for c in _rows])_bm = _rs.fts_search(fts_or(['nature', 'comparison']), ['rowid', 'content'], 'rank')test_eq([r['rowid'] for r in _bm], [11, 1, 3, 4, 2])_ts, _v = _rs.fts_tokens('nature comparison'), _rs.fts_vocab()_tk =lambda t: {k for p in _rs.fts_tokens(t) for k in p}test_eq([r['rowid'] for r in rank_fts(_bm, _ts, {t[0]: _v[t[0]] for t in _ts}, _rs.count, keys=_tk)], [11, 2, 1, 3, 4])# holding more of the terms beats rarity_rr = rank_fts([dict(content='nature comparison', rank=-1), dict(content='comparison', rank=-9)], ['natur', 'comparison'],dict(natur=50, comparison=1), keys=_tk)test_eq(_rr[0]['content'], 'nature comparison')# one list per table: each bm25 is read against its own best_a = [dict(content='fire', rank=-30.0), dict(content='fire fire', rank=-20.0)]_b = [dict(content='fire', rank=-2.0)]test_eq([r['rank'] for r in rank_fts([_a, _b], ['fire'])], [-30.0, -2.0, -20.0])# hits holding no term go last, or go_h = [dict(content='water', rank=-5), dict(content='fire', rank=-1)]test_eq([r['content'] for r in rank_fts(_h, ['fire'])], ['fire', 'water'])test_eq([r['content'] for r in rank_fts(_h, ['fire'], drop=True)], ['fire'])test_eq(rank_fts([], ['fire']), [])
correct suggests what a query word was meant to be when it is not in the index. Pass it the vocabulary from fts_vocab. It returns [word] when the word is indexed, and otherwise up to top index terms, most frequent first, that start with the same letter and are close to it:
terms that are the word with two neighbouring letters swapped (comparsion to comparison), at any length;
if there are none and the word has at least min_len letters (7 by default), terms within edits insertions, deletions, substitutions or swaps;
if there are still none, skeleton=True and the word has at least five letters, terms with the same first letter and the same consonants in the same order (bhishma and bheeshma both reduce to bhshm).
Edits wait for seven letters because a short word one edit away from an indexed word is usually a different word, such as router and route.
def correct( word, # a query word, folded the way `vocab`'s terms are vocab, # term -> count or `(documents, occurrences)`, or an iterable of terms swaps:bool=True, # try two adjacent letters swapped edits:int=1, # edits allowed when no swap is found min_len:int=7, # shortest word edits apply to top:int=2, # corrections returned skeleton:bool=False, # also terms of five letters or more sharing `word`'s consonant skeleton)->list:
The index terms word may be a misspelling of, most frequent first, or [word] when it is indexed.
_vc =dict(comparison=40, anger=30, route=50, router=5, bhisma=12, bhishma=3, comparisons=2)test_eq(correct('comparsion', _vc), ['comparison']) # swapped letterstest_eq(correct('anegr', _vc), ['anger']) # swaps at any lengthtest_eq(correct('comparisn', _vc), ['comparison']) # one edit, nine letterstest_eq(correct('compparison', _vc), ['comparison']) # comparisons is two edits awaytest_eq(correct('routr', _vc), []) # five letters: no editstest_eq(correct('routr', _vc, min_len=5), ['route', 'router']) # most frequent firsttest_eq(correct('anger', _vc), ['anger']) # indexed: itselftest_eq(correct('naeger', _vc), []) # the first letter staystest_eq(correct('comparsion', _vc, swaps=False, edits=0), [])test_eq(correct('comparisonss', _vc, edits=2, top=1), ['comparison'])test_eq(correct('comparsion', {'comparison'}), ['comparison'])test_eq(correct('natrue', _vs.fts_vocab()), []) # works on fts_vocab directlytest_eq(correct('kign', _vs.fts_vocab()), ['king'])
Reranking
A cross-encoder is a small model that reads the query and one candidate passage together and scores how well they match. It is more accurate than comparing embeddings, which are computed for the query and the passage separately, and much slower, so it is used only on the top few dozen candidates of a fast search. rerank_hits reorders hits this way with flashrank, which must be installed separately. RERANK_FANOUT (30) is the number of candidates doc_search and Index.search give it.
Database.search is the hybrid search on one store. It runs the keyword leg (FTS5, by default with the query passed through pre()) and the vector leg (exact vec_search, or ann_search with ann=True), then merges them with RRF. You supply the query text and the query’s embedding, as bytes in the store’s dtype. With rrf=False it returns both lists unmerged, which is useful for debugging a ranking. It returns None for an empty query.
There are three search methods at different levels, and they differ in what they know about your documents:
Database.doc_search, in litesearch.tree, runs this search on a store with a document tree, merges neighbouring hits from the same section into one, and adds each hit’s heading path.
Index.search, in litesearch.api, calls doc_search with its own encoder, so you pass only the query text.
def search( q:str, # query string emb:bytes, # embedding vector columns:list=None, # columns to return where:str=None, # additional where clause where_args:dict=None, # args for where clause limit:int|None=50, # limit on number of results offset:int|None=None, # offset for results (ignored when rrf=True) table_name:str='store', # table name emb_col:str='embedding', # embedding column name emb_metric:str='cosine', # embedding distance metric (cosine,sqeuclidean,inner,divergence) rrf:bool=True, # merge the two lists with RRF; False returns {'fts': [...], 'vec': [...]} rrf_k:int=60, # RRF k parameter dtype:type=float16, # embedding dtype id_key:str='rowid', # key to join RRF results on quote:bool=True, # quote FTS query to disable special chars (ignored when fts_pre=True) fts_pre:bool=True, # send the FTS leg through `pre()`: keywords, wildcards, OR ann:bool=False, # use the HNSW index for the vector leg; with `where` set, the exact scan is used instead reranking:bool=False, # reorder the merged hits with a flashrank cross-encoder (needs rrf=True) rerank_model:str=None, # flashrank model name (None -> fast default) term_rank:bool=False, # reorder the FTS leg by `rank_fts`: query terms held, their rarity, then bm25 fts_depth:int=None, # FTS candidates read before `term_rank` keeps the best `limit`; None means `limit`):
Hybrid search on a store: run the FTS5 and vector legs and merge them with RRF, or return both lists.
Database.search runs its two legs one after the other on the same connection. Running them in two threads would need two connections, since apsw refuses two threads inside one, and it saved nothing: most of the time goes into reading the vector leg’s rows, not into starting the queries. To search from several threads at once, give each thread its own connection.
def _v(i, n=8): return (np.full(n, i, dtype=np.float16)/16).tobytes()_db = database(str(Path(mkdtemp())/'p.db'))_st = _db.get_store()_st.insert_all([dict(content=f'rank fusion over leg {i}', embedding=_v(i)) for i inrange(200)])_seq = _db.search('rank fusion', _v(3), ['content'], limit=5)assert _seq, _seq_mem = database(':memory:'); _mem.get_store().insert_all([dict(content='rank fusion', embedding=_v(3))])assert _mem.search('rank fusion', _v(3), ['content'], limit=5)
/var/folders/kg/9vdw4mdd1fs58svgh4k1qhr09x7dqh/T/ipykernel_91989/3569258371.py:28: UserWarning: 'store': every distance came back null or 0, which usually means dtype='f16' does not match how the embeddings were stored (e.g. float32 vectors from model2vec searched as float16). The ranking is meaningless.
warnings.warn(f'{tbl.name!r}: every distance came back null or 0, which usually means dtype={want!r} '
By default the keyword leg passes the query through pre(), which removes stopwords, adds a prefix wildcard to every remaining word and joins them with OR. The alternative, quoting every word, makes FTS5 require all of them, so a query with one word that appears nowhere in the store matched nothing. In the test below, “assistance” is in neither row: with fts_pre=False the keyword leg finds nothing, and with pre() it finds the row about the Pathfinder.
_d = database(); _s = _d.get_store('store')_s.insert_all([dict(content="The Pathfinder's transition activities shall help all types of researchers", embedding=np.zeros(4, dtype=np.float16).tobytes()),dict(content='Value added tax shall be chargeable on the supply of goods', embedding=np.ones(4, dtype=np.float16).tobytes())])_qv = np.zeros(4, dtype=np.float16).tobytes()_q ='Pathfinder researchers assistance'# 'assistance' is absent from both rowsassert _d.search(_q, _qv, columns=['content'], rrf=False, fts_pre=False)['fts'] == []_got = _d.search(_q, _qv, columns=['content'], rrf=False, fts_pre=True)['fts']assertlen(_got) ==1and'Pathfinder'in _got[0]['content']# a query of pure stopwords cannot be pre()-processed into anything; fall back rather than crashassert _d.search('of the', _qv, columns=['content'], limit=2) isnotNone
With term_rank=True, the keyword leg reads fts_depth candidates (by default limit) in bm25 order, re-orders them with rank_fts using the store’s own vocabulary, and keeps the best limit. A hit that matched only through a pre() wildcard contains none of the exact terms, so it goes after the hits that do. Set fts_depth well above limit: a rare-word match that bm25 ranks below the first fts_depth candidates never reaches the re-ranking, as the last line of the test shows. term_rank is off by default because evals/RESULTS.md has no measurement for it yet.
_td = database(); _tst = _td.get_store('store')_rows = ['nature nature nature', 'comparison, which is the knowing of a thing by its likeness to another thing already known to the one who knows it','the nature of fire', 'the nature of water', 'a river runs', 'the sky is blue', 'stones are hard', 'birds fly south', 'the sun rises','rain falls', 'comparison of nature']_tst.insert_all([dict(content=c, embedding=np.zeros(4, dtype=np.float16).tobytes()) for c in _rows])_qv = np.zeros(4, dtype=np.float16).tobytes()_fl =lambda**kw: [r['content'] for r in _td.search('nature of comparison', _qv, columns=['content'], rrf=False, **kw)['fts']]test_eq(_fl()[:2], [_rows[10], _rows[0]])test_eq(_fl(term_rank=True, fts_depth=50)[:2], [_rows[10], _rows[1]])test_eq(len(_fl(term_rank=True, fts_depth=50, limit=2)), 2)test_eq(_fl(term_rank=True, fts_depth=50, limit=2, offset=1), _fl(term_rank=True)[1:3])test_eq(_fl(term_rank=True, limit=2)[1], _rows[0]) # two candidates deep: the rare row never reaches the re-rank
With reranking=True, the fused hits are re-ordered by a flashrank cross-encoder. The test below is not run in CI, because it downloads a model: it checks that the cross-encoder puts the Eiffel Tower passage first even when the query vector points at the other row.
import numpy as np, json_rdb = database(); _rst = _rdb.get_store('rr', ann=True, ndim=4)_rst.insert_all([{'content':'The Eiffel Tower is in Paris, France.','embedding':np.array([1,0,0,0],dtype=np.float16).tobytes(),'metadata':json.dumps({'doc_id':'a'})}, {'content':'Cats are small mammals kept as pets.','embedding':np.array([0,1,0,0],dtype=np.float16).tobytes(),'metadata':json.dumps({'doc_id':'b'})}])_rst.rebuild_index()_q ='where is the eiffel tower'# query vector deliberately favors 'b'; the cross-encoder should still surface 'a' first_hits = _rdb.search(_q, np.array([0,1,0,0],dtype=np.float16).tobytes(), columns=['metadata'], limit=2, table_name='rr', reranking=True)assert json.loads(_hits[0]['metadata'])['doc_id']=='a'
Store names are quoted in SQL everywhere, so a name with a hyphen, such as client-old, works with exact search, index rebuilding and HNSW search.
# Table names are identifiers, including names such as a routed `client-old` shelf._hdb = database(); _hst = _hdb.get_store('client-old', ann=True, ndim=4)_hv = np.array([1,0,0,0], dtype=np.float16)_hst.insert(dict(content='hyphenated shelf', embedding=_hv.tobytes()))assert _hst.vec_search(_hv.tobytes(), ['content'])[0]['content'] =='hyphenated shelf'assert _hst.rebuild_index() ==1assert _hst.ann_search(_hv.tobytes(), ['content'])[0]['content'] =='hyphenated shelf'
The next test stores float32 vectors and searches them as float16. Every distance comes back NULL and SQL raises nothing, so litesearch warns. Searching with the matching dtype returns the right row and no warning.
import warnings_rng = np.random.default_rng(0)_nrm =lambda x: x/np.linalg.norm(x)_a, _b = (_nrm(_rng.normal(size=64)).astype(np.float32) for _ inrange(2))_d = database(); _s = _d.get_store('store'); _d.usearch_sql() # a first download warns; keep it out of the count_s.insert_all([dict(content='a', embedding=_a.tobytes()), dict(content='b', embedding=_b.tobytes())])with warnings.catch_warnings(record=True) as _w: warnings.simplefilter('always') _s.vec_search(_a.tobytes(), ['content'], dtype=np.float16)assertlen(_w) ==1and'distance came back'instr(_w[0].message), [str(x.message) for x in _w]with warnings.catch_warnings(record=True) as _w: # dtype matched: silent warnings.simplefilter('always')assert _s.vec_search(_a.tobytes(), ['content'], dtype=np.float32)[0]['content'] =='a'assertnot _w, [str(x.message) for x in _w]
A store with an HNSW index records the dtype of its vectors, so a search with a different dtype gets a specific warning instead of the general one.
_d2 = database(); _s2 = _d2.get_store('store', ann=True, ndim=64, dtype=np.float32)_s2.insert_all([dict(content='a', embedding=_a.tobytes())])with warnings.catch_warnings(record=True) as _w: warnings.simplefilter('always') _s2.vec_search(_a.tobytes(), ['content'], dtype=np.float16)assertlen(_w) ==1and'registered as'instr(_w[0].message), [str(x.message) for x in _w]
db = database()
Worked examples
db.usearch_sql() loads usearch’s distance functions into SQL. The cell below computes each metric between three 100-number vectors: all ones, all zeros, and all 0.25. Every function returns a distance, where smaller means closer, which is why inner (one minus the dot product) can be negative and why cosine between any vector and the zero vector is 1.
embs =dict( v1=np.ones((100,),dtype=np.float32).tobytes(), # vector of ones v2=np.zeros((100,),dtype=np.float32).tobytes(), # vector of zeros v3=np.full((100,),0.25,dtype=np.float32).tobytes() # vector of 0.25s)def dist_q(metric):return db.q(f''' select distance_{metric}_f32(:v1,:v2) as {metric}_v1_v2, distance_{metric}_f32(:v1,:v3) as {metric}_v1_v3, distance_{metric}_f32(:v2,:v3) as {metric}_v2_v3 ''', embs)if db.usearch_sql(): # False when usearch's SQLite extension failed its probefor fn in ['sqeuclidean', 'divergence', 'inner', 'cosine']: print(dist_q(fn))
db.get_store()if'store'in db.t: print('store is created')print('detected fts table: ',db.t.store.detect_fts())print('Search results:', len(db.search('h',np.zeros((100,)).tobytes()))) # there is no data yet, so should be 0
store is created
detected fts table: store_fts
Search results: 0
A store created with hash=True uses a hash of the content as its id, so the ids below are the same every time the same text is inserted. That makes it suitable for code search, where the same files are ingested again and again.
A search with rrf=False returns the two legs separately, as {'fts': [...], 'vec': [...]}. The two nearest toy vectors point in the same direction as the query, so both distances are 0, and litesearch warns that the distances look wrong. Here the warning is a false alarm, raised only because the example vectors are not real embeddings.
/var/folders/kg/9vdw4mdd1fs58svgh4k1qhr09x7dqh/T/ipykernel_91989/3569258371.py:28: UserWarning: 'my_store': every distance came back null or 0, which usually means dtype='f16' does not match how the embeddings were stored (e.g. float32 vectors from model2vec searched as float16). The ranking is meaningless.
warnings.warn(f'{tbl.name!r}: every distance came back null or 0, which usually means dtype={want!r} '
A query can match several rows. By default Database.search passes the query through pre(), which turns goodbye OR hi into goodbye* OR hi*, so both rows match the keyword leg, and RRF merges the two legs into one list. _rrf_score is the fused score.
db.search(q='goodbye OR hi', emb=np.full((100,),0,dtype=np.float16).tobytes(), columns=['content'], table_name='my_store',limit=2, quote=True)
# a query of operator characters alone used to raise AttributeError; the keyword leg finds nothing, the vector leg still runs_e = np.full((100,),0.25, dtype=np.float16).tobytes()r = db.search(q='***', emb=_e, columns=['content'], table_name='my_store', limit=2, rrf=False)assert r['fts'] == [] andlen(r['vec']) ==2assert db.search(q='""', emb=_e, columns=['content'], table_name='my_store', limit=2, term_rank=True) == db.search(q='***', emb=_e, columns=['content'], table_name='my_store', limit=2)
Approximate vector search with HNSW
Exact vector search reads every row, so its time grows with the store. An approximate nearest-neighbour (ANN) index answers the same question much faster on large stores by giving up a little accuracy: it may occasionally miss one of the true nearest rows. litesearch uses usearch’s HNSW index. HNSW (hierarchical navigable small world) is a graph in which each vector is linked to vectors near it, and a search follows those links towards the query instead of comparing it with everything.
get_store(ann=True) adds an HNSW index to a store. The SQLite table stays the source of truth. The index is kept in memory and, for a database on disk, saved to a separate file next to it, named <database>.<store>.usearch, which a later connection loads. You can delete that file at any time and rebuild it from the table with rebuild_index().
The index does not follow ordinary inserts. Add rows with Table.sync, which updates the index, or call rebuild_index() after inserting. sync takes the full set of chunks a store should hold. It compares their content hashes with the rows already there, deletes rows that are gone, embeds and inserts the new ones, and updates the index to match. With key_col, it compares only rows whose key_col value appears in the new chunks, so you can re-sync one file or one package without touching the rest.
ann_search searches the index. It fetches twice limit candidates from the index and then applies your where clause to them, so a selective filter can leave nothing. For that reason Database.search(ann=True) switches to the exact vec_search whenever you pass where.
The tests below use a small fixed embedder that maps a few strings to 4-number vectors.
# tiny deterministic embedder: content -> 4-d f16 vector_vecs = {'A':[1,0,0,0], 'B':[0,1,0,0], 'C':[0,0,1,0], 'B2':[0,1,1,0], 'D':[0,0,0,1]}def emb_fn(texts): return [np.array(_vecs.get(t, [0.01,0.01,0.01,0.01]), dtype=np.float16) for t in texts]adb = database()ast_ = adb.get_store('ann_store', hash=True, ann=True, metric='cosine') # ndim inferred on first syncassert'usearch_indices'in adb.t, 'registry table created'assert adb._ann_meta('ann_store')['ndim'] isNone, 'ndim unknown until first sync'stats = ast_.sync([dict(content=c) for c in ['A','B','C']], emb_fn=emb_fn)print('sync stats:', stats)assert stats ==dict(changed=3, same=0, removed=0)assert adb._ann_meta('ann_store')['ndim'] ==4, 'ndim inferred and persisted'assert adb.get_index('ann_store').size ==3, 'index has 3 vectors'
# smart update: A unchanged, B->B2 (changed), C removed, D newstats = ast_.sync([dict(content=c) for c in ['A','B2','D']], emb_fn=emb_fn)print('update stats:', stats)assert stats ==dict(changed=2, same=1, removed=2), stats # add B2,D ; remove old-B,Cassert {r['content'] for r in ast_()} == {'A','B2','D'}assert adb.get_index('ann_store').size ==3# D is now findable, C is goneqD = np.array([0,0,0,1], dtype=np.float16).tobytes()assert ast_.ann_search(qD, columns=['content'], limit=1)[0]['content'] =='D'
A chunk whose text is unchanged but whose metadata changed, such as a function pushed down a line by an edit above it, keeps its id, rowid and vector. sync rewrites the changed columns in place and does not call the embedder.
# force=True: skip the diff, re-ingest everything (nothing stale/removed, all re-embedded)stats = ast_.sync([dict(content=c) for c in ['A','B2','D']], emb_fn=emb_fn, force=True)assert stats ==dict(changed=3, same=0, removed=0), statsassert adb.get_index('ann_store').size ==3# re-adds overwrite existing keysassert ast_.ann_search(qD, columns=['content'], limit=1)[0]['content'] =='D'
# rebuild the index from the SQLite blobs (recovery path) -- drop cached index, rebuildadb.ann_indices.clear()n = ast_.rebuild_index()assert n ==3, nassert ast_.ann_search(qD, columns=['content'], limit=1)[0]['content'] =='D'
# persistence: on-disk db saves the .usearch sidecar; reopening loads it (no re-embed)import tempfile
# a torn sidecar (a save cut short) is rebuilt from the stored vectors on the next open, not raised_tp =str(Path(tempfile.mkdtemp())/'torn.db')_tdb = database(_tp); _tst = _tdb.get_store('ann_store', hash=True, ann=True)_tst.sync([dict(content=c) for c in ['A','C']], emb_fn=emb_fn)Path(_tdb._ann_meta('ann_store')['path']).write_bytes(b'\xd4\xa6'*512)_tdb2 = database(_tp)qC = np.array([0,0,1,0], dtype=np.float16).tobytes()test_eq(_tdb2.t.ann_store.ann_search(qC, columns=['content'], limit=1)[0]['content'], 'C')_tdb2.get_index('ann_store').size
p =str(Path(tempfile.mkdtemp())/'ann.db')d1 = database(p)s1 = d1.get_store('ann_store', hash=True, ann=True)s1.sync([dict(content=c) for c in ['A','B','C']], emb_fn=emb_fn)_sc = d1._ann_meta('ann_store')['path']assert Path(_sc).exists() if usearch_ok('index') else _stale(_sc).exists(), 'sidecar saved, or marked stale when usearch is off'd1.close()d2 = database(p) # fresh connection, index loaded lazily from sidecarassert d2.get_index('ann_store').size ==3, 'index restored from disk'assert d2.t.ann_store.ann_search(qC, columns=['content'], limit=1)[0]['content'] =='C'd2.close()
import numpy as np, json
# ANN on an id-pk (non-hash) store: rebuild_index + ann_search must not KeyError on 'rowid'_annt = database().get_store('annrowid', ann=True, ndim=4, metric='cosine')_vs = np.array([[1,0,0,0],[0,1,0,0],[0,0,1,0],[0,1,0,0]], dtype=np.float16)_annt.insert_all([{'content':f'c{i}','embedding':_vs[i].tobytes(),'metadata':json.dumps({'doc_id':f'd{i}'})} for i inrange(4)])assert _annt.rebuild_index() ==4_h = _annt.ann_search(np.array([1,0,0,0],dtype=np.float16).tobytes(), columns=['metadata'], limit=2)assert _h and json.loads(_h[0]['metadata'])['doc_id']=='d0'assert _h[0]['_dist'] isnotNone_h1= _annt.ann_search(np.array([0,1,0,0],dtype=np.float16).tobytes(), columns=['metadata'], where="json_extract(metadata, '$.doc_id')='d3'",limit=2)assert _h1 and json.loads(_h1[0]['metadata'])['doc_id']=='d3'assert _h1[0]['_dist'] isnotNone
# regression: ann_search must restrict to the ANN candidate keys even when `where` is None.# A precedence slip (`a + b if where else ''`) dropped the rowid filter entirely, turning every# unfiltered ANN query into a full-table scan -- correct top hit, but O(table) rows and latency._big = database().get_store('annbig', ann=True, ndim=4, metric='cosine')_bv = np.random.RandomState(0).randn(50,4).astype(np.float16)_bv[0] = np.array([1,0,0,0], dtype=np.float16)_big.insert_all([{'content':f'b{i}','embedding':_bv[i].tobytes()} for i inrange(50)])assert _big.rebuild_index() ==50_qb = np.array([1,0,0,0], dtype=np.float16).tobytes()_rb = _big.ann_search(_qb, columns=['content'], limit=2) # no where clauseassert _rb[0]['content'] =='b0', _rb[:1]assertlen(_rb) ==2, f'ann_search returned {len(_rb)} rows for limit=2'# where_args must reach db.q as query params (not as **kwargs, which raises TypeError)_rb2 = _big.ann_search(_qb, columns=['content'], limit=5, where='content = :c', where_args={'c':'b0'})assert [r['content'] for r in _rb2] == ['b0'], _rb2# _dist must be a plain python float: numpy scalars are not JSON-serializable, which breaks# any caller that json.dumps() the hits (kosha's --as-json, its daemon and MCP server).import json as _json_json.dumps(_rb[0])
'{"content": "b0", "rowid": 1, "_dist": 0.0}'
# regression: ANN + a selective `where` must not lose recall. usearch post-filters its candidate# window (Index.search has no predicate hook), so a match ranked past that window is invisible.# db.search now routes filtered vector queries to the exact vec_search (WHERE applied before ranking)._fdb = database()_fst = _fdb.get_store('annfilter', ann=True, ndim=4, metric='cosine')import json as _json_near, _far = np.array([1,0,0,0], dtype=np.float16), np.array([0,0,0,1], dtype=np.float16)# 20 rows sit exactly on the query vector; the single row we actually want is far from it._rows = [{'content':f'near{i}','embedding':_near.tobytes(),'metadata':_json.dumps({'tag':'other'})} for i inrange(20)]_rows.append({'content':'wanted','embedding':_far.tobytes(),'metadata':_json.dumps({'tag':'keep'})})_fst.insert_all(_rows)assert _fst.rebuild_index() ==21_qf = _near.tobytes()_wh ="json_extract(metadata,'$.tag')='keep'"# the bug: raw ANN post-filter starves recall -- none of the nearest limit*2 candidates carry tag='keep'assert _fst.ann_search(_qf, columns=['content'], limit=2, where=_wh) == [], 'ANN post-filter should miss the far match'# the fix: db.search(ann=True, where=...) falls back to the exact vec scan and surfaces the filtered match_res = _fdb.search('wanted', _qf, columns=['content','metadata'], where=_wh, table_name='annfilter', ann=True, rrf=False)assert [r['content'] for r in _res['vec']] == ['wanted'], _res['vec']
# ann_neighbors: nearest rows to an already-indexed row, no re-embedding_nb = database().get_store('nbstore', ann=True, ndim=4, metric='cosine')_nv = np.array([[1,0,0,0],[0.99,0.01,0,0],[0.7,0.7,0,0],[0,0,1,0]], dtype=np.float16)_nb.insert_all([{'content':f'n{i}','embedding':_nv[i].tobytes()} for i inrange(4)])assert _nb.rebuild_index() ==4assert _nb.ann_vec(1) isnotNoneand _nb.ann_vec(99) isNone, 'vector by key; None for an unknown key'_nn = _nb.ann_neighbors(1, limit=2, columns=['content'])assert [r['content'] for r in _nn] == ['n1','n2'], _nn # n0 itself excluded, n1 is its near-duplicateassert _nb.ann_neighbors(1, limit=1, columns=['content'], include_self=True)[0]['content'] =='n0'assert _nb.ann_neighbors(99) == [], 'unknown key returns nothing rather than raising'
When usearch is off
The test below runs every caller of the vector search on one store twice: once with usearch, and once after a probe that kills itself with SIGSEGV, as the crash on GitHub’s runners did. The second run gets one warning, uses numpy throughout, and finds the same rows at the same distances.
import litesearch.postfix as pffrom unittest.mock import patch
def _ann_paths():"Run each vector-search caller on one small store and return what each found." V = np.random.default_rng(1).normal(size=(40, 16)).astype(np.float16) rows = [dict(content=f'doc {i} word{i%5}', embedding=v.tobytes()) for i, v inenumerate(V)] d = database(); s = d.get_store('fb', hash=True, ann=True) s.sync(rows, embed=False) q, r3 = (V[3] +0.01).astype(np.float16).tobytes(), d.q("select rowid from fb where content='doc 3 word3'")[0]['rowid'] vec, ann = s.vec_search(q, ['content'], limit=5), s.ann_search(q, ['content'], limit=5) out =dict(kind=type(d.get_index('fb')).__name__, vec=[r['content'] for r in vec], ann=[r['content'] for r in ann], vec_d=[r['_dist'] for r in vec], ann_d=[r['_dist'] for r in ann], hyb=[r['content'] for r in d.search('word3', q, columns=['content'], table_name='fb', ann=True, limit=5)], exact=[r['content'] for r in d.search('word3', q, columns=['content'], table_name='fb', limit=5)], vec3=np.array_equal(s.ann_vec(r3), V[3]), nbr=[r['content'] for r in s.ann_neighbors(r3, limit=3, columns=['content'])], n=s.rebuild_index()) s.sync(rows[:3] + rows[4:], embed=False) out['gone'] = [r['content'] for r in s.ann_search(q, ['content'], limit=3)]return out_real = _ann_paths()with patch.dict(os.environ, LITESEARCH_CACHE=tempfile.mkdtemp()), patch.object(pf, '_verdict', None), \ patch.object(pf, '_PROBE', 'import os, signal; os.kill(os.getpid(), signal.SIGSEGV)'), warnings.catch_warnings(record=True) as _w: os.environ.pop('LITESEARCH_USEARCH', None) warnings.simplefilter('always') _fb = _ann_paths()_uw = [str(x.message) for x in _w if'usearch'instr(x.message)]assertlen(_uw) ==1and ('SIGSEGV'in _uw[0] or'does not import'in _uw[0]), [str(x.message) for x in _w] # macOS 14: usearch does not importtest_eq(_fb.pop('kind'), 'ExactIndex'); _real.pop('kind')for k, v in _real.items():if k.endswith('_d'): assert np.allclose(v, _fb[k], atol=1e-2), (k, v, _fb[k])else: test_eq(_fb[k], v)assert _fb['vec3'] and _fb['n'] ==40and'doc 3 word3'notin _fb['gone'] and _fb['vec'][0] =='doc 3 word3'
from litesearch.core import ExactIndex
# a session with usearch off marks the sidecar stale; the next session with it on rebuilds the index from the table_lp =str(Path(tempfile.mkdtemp())/'life.db')_dl = database(_lp); _dl.get_store('ann_store', hash=True, ann=True).sync([dict(content=c) for c in'ABC'], emb_fn=emb_fn)_sc = _dl._ann_meta('ann_store')['path']; _dl.close()with _swap(globals(), usearch_ok=lambda part='sql': False): _df = database(_lp)assertisinstance(_df.get_index('ann_store'), ExactIndex) and _df.get_index('ann_store').size ==3 _df.get_store('ann_store', hash=True, ann=True).sync([dict(content=c) for c in'ABD'], emb_fn=emb_fn)assert _stale(_sc).exists(); _df.close()if usearch_ok('index'): _dr = database(_lp) test_eq(_dr.get_index('ann_store').size, 3) test_eq(_dr.t.ann_store.ann_search(np.array([0,0,0,1], dtype=np.float16).tobytes(), ['content'], limit=1)[0]['content'], 'D')assert'C'notin [r['content'] for r in _dr.t.ann_store.ann_search(qC, ['content'], limit=3)] andnot _stale(_sc).exists()
Stores and search, step by step
The rest of this page goes through the same parts as a short tutorial: creating a store, how identifiers survive tokenization, how a hybrid search merges its two legs, and when to add an HNSW index.
get_store(): a table with a full-text index
db.get_store() creates a table, or opens it if it exists, with a content text column, an embedding blob column, a JSON metadata column and an uploaded_at timestamp. It also creates an FTS5 index over content and metadata, kept up to date by triggers. Calling it again is safe.
store = db.get_store() # idempotent — safe to call multiple timesstore.schema
'CREATE TABLE [store] (\n [content] TEXT NOT NULL,\n [embedding] BLOB,\n [metadata] TEXT,\n [uploaded_at] FLOAT DEFAULT CURRENT_TIMESTAMP,\n [id] INTEGER PRIMARY KEY\n)'
With hash=True, the id is a SHA-1 hash of the content. Inserting the same text again with upsert=True updates the existing row instead of adding a second one, which removes duplicates for free:
code_store = db.get_store(name='code', hash=True)code_store.insert_all([{'content':v} for v in ('hello world', 'hi there', 'goodbye now')], upsert=True, hash_id='id')code_store(select='id,content')
porter unicode61 fts_search running -> ft, search, run
porter simplify casefold 1 sanskrit unicodewords fts_search running -> fts_search, run
get_store uses the second chain unless you pass get_store(tokenize=...). tokenize='porter' gives the first.
FTS5 looks the tokenizer up by name each time it reads or writes the index, so every connection to the database must register it, and database() does. A plain sqlite3 connection can read the table, but a full-text query or a write to the store fails with error in tokenizer constructor.
db.search(): keywords and vectors, merged
db.search() runs an FTS5 keyword query and a vector search, then merges the two ranked lists with rrf_all. A row on both lists outranks a row on one. Pass rrf=False to get the two lists back separately.
The query embedding has to come from the same encoder as the stored embeddings, in the same dtype. Here the store holds no embeddings, so both lists are empty.
/Users/71293/code/litesearch/.venv/lib/python3.13/site-packages/tqdm/auto.py:21: TqdmWarning: IProgress not found. Please update jupyter and ipywidgets. See https://ipywidgets.readthedocs.io/en/stable/user_install.html
from .autonotebook import tqdm as notebook_tqdm
{'fts': [], 'vec': []}
Pass the dtype your vectors are stored in. model2vec encoders return float32, so either store and search with dtype=np.float32, or convert the vectors to float16 before storing them, as Index does. The default is float16.
For a schema of your own, skip get_store and call db.t['my_table'].fts_search(...) and db.t['my_table'].vec_search(emb, ...) yourself, then merge them with rrf_all([fts, vec]).
HNSW for large stores
The default vector search compares the query with every row, which is fast up to a few thousand rows. Beyond that, ann=True keeps an HNSW index in a separate file. The SQLite table remains the source of truth, and store.rebuild_index() rebuilds the index from it at any time.
store.sync(content, key_col=..., emb_fn=...) compares content hashes, deletes rows that are gone, embeds and inserts the new ones, and updates the index to match.
db.search(..., ann=True) uses the index instead of reading every row.
The cells below load 8,000 code snippets through static_code_embedder and compare the two kinds of vector search. They are not run in CI.
import timefrom litesearch.utils import doc_encoder, static_code_embedder, query_encoderemb = static_code_embedder() # model2vec potion-code-16M — 256-dim float16de, qe = doc_encoder(emb), query_encoder(emb) # -> emb_fn: list[str] -> vectorsadb = database()store = adb.get_store('code', hash=True, ann=True) # dtype defaults to float16, matching the embedder# 8,000 code snippets; sync embeds, upserts, and builds the HNSW index in one calldocs = [dict(content=f'def func_{i}(x, y={i}): "helper {i%97}"; return x*{i} + y - {i%13}') for i inrange(8000)]t = time.perf_counter(); stats = store.sync(docs, emb_fn=de)print(f'sync {stats} in {time.perf_counter()-t:.2f}s — index size {adb.get_index("code").size}')# sync {'changed': 8000, 'same': 0, 'removed': 0} in 0.57s — index size 8000
sync {'changed': 8000, 'same': 0, 'removed': 0} in 0.70s — index size 8000
q ='function that multiplies its input by a constant and adds an offset'qv = qe([q])[0].tobytes()def bench(fn, reps=20): t = time.perf_counter()for _ inrange(reps): fn()return (time.perf_counter()-t)/reps*1000# ms per callbf = bench(lambda: store.vec_search(qv, columns=['content'], limit=10)) # exact brute-force scanan = bench(lambda: store.ann_search(qv, columns=['content'], limit=10)) # HNSWprint(f'brute-force {bf:.3f} ms ann {an:.3f} ms speedup {bf/an:.1f}x')# brute-force 1.787 ms ann 0.100 ms speedup 17.8x# HNSW is approximate but recall is high — top-10 matches the exact scan hereexact = {r['content'] for r in store.vec_search(qv, columns=['content'], limit=10)}approx = {r['content'] for r in store.ann_search(qv, columns=['content'], limit=10)}print(f'recall@10: {len(exact & approx)}/10')# recall@10: 10/10
brute-force 3.406 ms ann 0.114 ms speedup 29.9x
recall@10: 10/10
# use it end-to-end via db.search — ann=True swaps in the HNSW index for the vector legadb.search(q, qv, columns=['content'], table_name='code', ann=True, limit=3)
These tests open a reader and a writer on the same file. While the writer’s write_txn is open, the reader sees neither the new row nor its full-text entry, and both appear once the writer commits. Inside bulk_load the difference shows: the new row is visible, the full-text index does not have it yet, old entries still match, and the rebuild at the end of the block adds it.