Reference Component Rag

FAISS and SQLite Vector Search

A compact Python pattern for combining SQLite document storage with a FAISS nearest-neighbor index for local RAG retrieval.

Problem FAISS is fast at vector search, but applications still need durable document metadata, text chunks, and lookup tables.

Outcome A reusable wrapper that stores embedding provenance in SQLite and uses FAISS to retrieve the matching document text.

This gives you a local vector retrieval component: SQLite keeps the document chunks and embedding mappings; FAISS performs nearest-neighbor search.

Code

import re
import sqlite3

import faiss
import numpy as np
import ollama


class SearchResult:
    def __init__(self, index, score, doc):
        self.index = index
        self.score = score
        self.doc = doc

    def __repr__(self):
        return f"Score: {self.score}, Index: {self.index}, Doc: {self.doc[:60]}..."


class FaissDB:
    def __init__(self, db_name="papers.db", embedding_model="mxbai-embed-large"):
        self.conn = sqlite3.connect(db_name)
        self.cursor = self.conn.cursor()
        self.embedding_model = embedding_model
        self.document_index = None

    def load_documents_from_db(self, max_documents=1000):
        self.cursor.execute("SELECT id, text FROM document_page_split LIMIT ?", (max_documents,))
        return [(row_id, self.clean_text(text)) for row_id, text in self.cursor.fetchall()]

    def get_embeddings(self, documents):
        embeddings = []
        for faiss_id, (document_page_split_id, text) in enumerate(documents, start=1):
            response = ollama.embeddings(model=self.embedding_model, prompt=text)
            vector = np.array(response["embedding"], dtype="float32")
            self.cursor.execute(
                """
                INSERT INTO document_embeddings (faiss_index_id, document_page_split_id, embedding)
                VALUES (?, ?, ?)
                """,
                (faiss_id, document_page_split_id, vector.tobytes()),
            )
            embeddings.append(vector)
        self.conn.commit()
        return np.array(embeddings, dtype="float32")

    def build_index(self, embeddings):
        dimension = embeddings.shape[1]
        self.document_index = faiss.IndexFlatL2(dimension)
        self.document_index.add(embeddings)
        return self.document_index

    def search(self, text, k=5):
        if self.document_index is None:
            raise ValueError("Build or set the FAISS index before searching.")

        query_embedding = self.get_embedding(text)
        distances, indices = self.document_index.search(query_embedding, k)
        return [
            SearchResult(idx, distances[0][rank], self.get_doc(idx))
            for rank, idx in enumerate(indices[0])
        ]

    def get_embedding(self, text):
        response = ollama.embeddings(model=self.embedding_model, prompt=text)
        return np.array([response["embedding"]], dtype="float32")

    def get_doc(self, faiss_index):
        self.cursor.execute(
            """
            SELECT text FROM document_page_split
            WHERE id IN (
                SELECT document_page_split_id
                FROM document_embeddings
                WHERE faiss_index_id = ?
            )
            """,
            (int(faiss_index),),
        )
        row = self.cursor.fetchone()
        return row[0] if row else ""

    @staticmethod
    def save(index, filename):
        faiss.write_index(index, filename)

    @staticmethod
    def load(filename):
        return faiss.read_index(filename)

    @staticmethod
    def clean_text(markdown):
        text = re.sub(r"\[.*?\]\(.*?\)", "", markdown)
        text = re.sub(r"#{1,6}\s*", "", text)
        text = re.sub(r"(```.*?```|`.*?`)", "", text, flags=re.DOTALL)
        text = re.sub(r"\*{1,2}|_{1,2}", "", text)
        return text.strip()

Usage

vector_store = FaissDB(db_name="papers.db")
documents = vector_store.load_documents_from_db(max_documents=500)
embeddings = vector_store.get_embeddings(documents)
index = vector_store.build_index(embeddings)

for result in vector_store.search("RAG database", k=5):
    print(result.score, result.doc[:500])

FaissDB.save(index, "papers.faiss")

Requirements

Install faiss-cpu, numpy, and ollama. You also need a local Ollama embedding model, for example ollama pull mxbai-embed-large.

Full explanation

For the full reasoning and original implementation context, read: Efficient Similarity Search with FAISS and SQLite in Python.