Reference Component Rag

Hybrid RAG Search with sqlite-vec

A small local RAG retriever that stores text and vectors in SQLite, searches with both FTS5 and sqlite-vec, then merges rankings with RRF.

Problem Keyword search and vector search each miss useful results in different ways.

Outcome A local hybrid retriever that combines lexical and semantic matches without running a separate vector database service.

This solves local hybrid retrieval for small and medium RAG systems: use SQLite FTS5 for exact terms, sqlite-vec for semantic matches, and Reciprocal Rank Fusion to combine the result lists.

Code

import sqlite3

import numpy as np
import requests
import sqlite_vec


class VectorDB:
    def __init__(self, db_name="pdf_vector.db", dims=1024, ollama_url="http://localhost:11434"):
        self.conn = sqlite3.connect(db_name)
        self.conn.enable_load_extension(True)
        sqlite_vec.load(self.conn)
        self.conn.enable_load_extension(False)
        self.cursor = self.conn.cursor()
        self.dims = dims
        self.ollama_url = ollama_url
        self.create_tables()

    def create_tables(self):
        self.cursor.execute(
            'CREATE VIRTUAL TABLE IF NOT EXISTS pdf_fts USING fts5(id UNINDEXED, content, tokenize="porter unicode61")'
        )
        self.cursor.execute(
            f"CREATE VIRTUAL TABLE IF NOT EXISTS pdf_vec USING vec0(embedding float[{self.dims}])"
        )
        self.cursor.execute(
            "CREATE TABLE IF NOT EXISTS pdf_lookup (id INTEGER PRIMARY KEY AUTOINCREMENT, content TEXT)"
        )
        self.conn.commit()

    def add_documents(self, rows):
        self.cursor.executemany("INSERT INTO pdf_fts (id, content) VALUES (?, ?)", rows)
        self.cursor.executemany("INSERT INTO pdf_lookup (id, content) VALUES (?, ?)", rows)
        for row_id, content in rows:
            embedding = self.generate_embeddings(content)
            self.cursor.execute(
                "INSERT INTO pdf_vec (rowid, embedding) VALUES (?, ?)",
                (row_id, self.serialize_f32(embedding)),
            )
        self.conn.commit()

    def search(self, query, top_k=5):
        fts_results = self.cursor.execute(
            "SELECT id FROM pdf_fts WHERE pdf_fts MATCH ? ORDER BY rank LIMIT ?",
            (self.or_words(query), top_k),
        ).fetchall()

        query_embedding = self.generate_embeddings(query)
        vec_results = self.cursor.execute(
            "SELECT rowid, distance FROM pdf_vec WHERE embedding MATCH ? AND K = ? ORDER BY distance",
            (self.serialize_f32(query_embedding), top_k),
        ).fetchall()

        return [
            {"id": row_id, "score": score, "content": self.lookup_row(row_id)}
            for row_id, score in self.reciprocal_rank_fusion(fts_results, vec_results)
        ]

    def generate_embeddings(self, text, model_name="mxbai-embed-large"):
        response = requests.post(
            f"{self.ollama_url}/api/embed",
            json={"input": text, "model": model_name},
            timeout=60,
        )
        response.raise_for_status()
        embeddings = response.json()["embeddings"]
        return embeddings[0] if embeddings and isinstance(embeddings[0], list) else embeddings

    def lookup_row(self, row_id):
        row = self.cursor.execute("SELECT content FROM pdf_lookup WHERE id = ?", (row_id,)).fetchone()
        return row[0] if row else ""

    @staticmethod
    def serialize_f32(vec):
        return np.array(vec, dtype=np.float32).tobytes()

    @staticmethod
    def reciprocal_rank_fusion(fts_results, vec_results, k=60):
        ranks = {}
        for rank, (row_id,) in enumerate(fts_results):
            ranks[row_id] = ranks.get(row_id, 0.0) + 1 / (k + rank + 1)
        for rank, (row_id, _distance) in enumerate(vec_results):
            ranks[row_id] = ranks.get(row_id, 0.0) + 1 / (k + rank + 1)
        return sorted(ranks.items(), key=lambda item: item[1], reverse=True)

    @staticmethod
    def or_words(query):
        return " OR ".join(query.split())

Usage

db = VectorDB(dims=1024)
db.add_documents([
    (1, "Artificial intelligence is transforming the world."),
    (2, "Quantum computing has the potential to revolutionize technology."),
    (3, "Electric vehicles are becoming more popular."),
])

for result in db.search("technology", top_k=3):
    print(result["score"], result["content"])

Requirements

Install sqlite-vec, numpy, and requests. Run Ollama locally with an embedding model whose output dimension matches dims.

Full explanation

For the full reasoning and SmolAgents retriever integration, read: DeepResearch Part 2: Building a RAG Tool for arXiv PDFs.