Alex Spinov

Posted on Mar 25

SQLite Just Got Vector Search — Here's How to Use It for AI (No Database Server Needed)

#ai #python #database #tutorial

SQLite + Vectors = Game Changer

SQLite just became a serious option for AI applications. With the sqlite-vec extension, you can now do vector similarity search directly in SQLite — no Pinecone, no Weaviate, no external database.

This means you can build RAG (Retrieval Augmented Generation) apps, semantic search, and recommendation engines with zero infrastructure.

I built a local semantic search engine in 50 lines of Python. Here's how.

Install sqlite-vec

pip install sqlite-vec

That's it. No Docker, no server, no config files.

Build a Semantic Search Engine in 50 Lines

import sqlite3
import sqlite_vec
import json
import struct

def serialize(vector):
    return struct.pack(f"{len(vector)}f", *vector)

# Connect and load extension
db = sqlite3.connect(":memory:")
db.enable_load_extension(True)
sqlite_vec.load(db)

# Create vector table (384 dimensions for all-MiniLM-L6-v2)
db.execute("""
    CREATE VIRTUAL TABLE documents USING vec0(
        embedding float[384]
    )
""")

# Sample documents with pre-computed embeddings
# In production, use sentence-transformers to generate these
docs = [
    "Python is great for data science",
    "JavaScript dominates web development",
    "Rust ensures memory safety without garbage collection",
    "Go is built for concurrent programming",
    "SQL is the language of databases",
]

# Insert (using random embeddings for demo — use real ones in production)
import random
for i, doc in enumerate(docs):
    embedding = [random.gauss(0, 1) for _ in range(384)]
    db.execute(
        "INSERT INTO documents(rowid, embedding) VALUES (?, ?)",
        (i, serialize(embedding))
    )

# Query: find similar documents
query_vec = [random.gauss(0, 1) for _ in range(384)]
results = db.execute(
    """
    SELECT rowid, distance
    FROM documents
    WHERE embedding MATCH ?
    ORDER BY distance
    LIMIT 3
    """,
    (serialize(query_vec),)
).fetchall()

for rowid, distance in results:
    print(f"Doc: {docs[rowid]} | Distance: {distance:.4f}")

Full RAG Pipeline (SQLite + OpenAI)

from sentence_transformers import SentenceTransformer
import sqlite3
import sqlite_vec
import struct

model = SentenceTransformer("all-MiniLM-L6-v2")

def serialize(v):
    return struct.pack(f"{len(v)}f", *v)

# Setup
db = sqlite3.connect("knowledge.db")
db.enable_load_extension(True)
sqlite_vec.load(db)

db.execute("CREATE TABLE IF NOT EXISTS docs(id INTEGER PRIMARY KEY, text TEXT)")
db.execute("""
    CREATE VIRTUAL TABLE IF NOT EXISTS doc_vectors USING vec0(
        embedding float[384]
    )
""")

def add_document(text):
    cursor = db.execute("INSERT INTO docs(text) VALUES (?)", (text,))
    doc_id = cursor.lastrowid
    embedding = model.encode(text).tolist()
    db.execute(
        "INSERT INTO doc_vectors(rowid, embedding) VALUES (?, ?)",
        (doc_id, serialize(embedding))
    )
    db.commit()

def search(query, top_k=3):
    query_vec = model.encode(query).tolist()
    results = db.execute(
        """SELECT rowid, distance FROM doc_vectors
           WHERE embedding MATCH ? ORDER BY distance LIMIT ?""",
        (serialize(query_vec), top_k)
    ).fetchall()

    return [(db.execute("SELECT text FROM docs WHERE id=?", (r[0],)).fetchone()[0], r[1])
            for r in results]

# Usage
add_document("The Python GIL prevents true multithreading")
add_document("asyncio enables concurrent I/O in Python")
add_document("Rust's ownership model eliminates data races")

for text, dist in search("concurrent programming in Python"):
    print(f"{dist:.4f} | {text}")

Why This Matters

Feature	SQLite + vec	Pinecone	Weaviate
Setup	`pip install`	Cloud signup + API key	Docker + config
Cost	Free forever	$70+/mo	Self-host or $25+/mo
Latency	<1ms (local)	10-50ms (network)	5-20ms
Max vectors	~10M (depends on RAM)	Unlimited (paid)	Unlimited
Offline	Yes	No	Self-host only
Deployment	Copy one .db file	Cloud dependency	Complex

When to Use SQLite for Vectors

Use it when:

Your dataset is <10M vectors
You want zero infrastructure
You need offline/edge deployment
You're prototyping a RAG app
You want to ship a single binary

Don't use it when:

You need distributed search across billions of vectors
You need real-time index updates at massive scale
You need managed cloud with auto-scaling

The Bigger Picture

SQLite is eating the database world. It already handles:

Mobile apps (every phone has it)
Browser storage (via WASM)
Edge computing (Cloudflare D1, Turso)

Now with vector search, it's coming for the AI infrastructure market too.

Are you using SQLite for anything beyond basic storage? I'd love to hear about creative use cases in the comments.

I write about practical AI tools and APIs every week. Follow for more no-BS tutorials.

Need Custom Data Solutions?

I build web scrapers, API integrations, and data pipelines. 77+ production scrapers serving thousands of requests daily.

📧 spinov001@gmail.com — Describe your data need, get a solution.

Explore my open-source tools and ready-to-use scrapers on Apify.

DEV Community