Every guide to agent memory on this site so far has been a choice: sqlite-vec vs. LanceDB vs. Qdrant, sqlite-vec vs. pgvector, and the exact point where you graduate off it. All useful, all comparisons. None of them hand you the build. This one does: a from-scratch agent memory in one Python file and one SQLite database, no framework, no server, nothing to deploy. If you can run sqlite3, you can run this.

Memory is two operations, not a product#

Strip the word "memory" of its mystique and it's a pair of functions:

That's it. Mem0 and the Claude memory tool add policy on top — what to keep, when to forget, how to summarize — but underneath every one of them is store a vector and find the nearest vectors. sqlite-vec gives you both, in a file, and gets out of the way.

1. Install and load — the entire setup#

pip install sqlite-vec openai

sqlite-vec ships the compiled vec0 extension inside the wheel, so there is no separate binary. You load it into an ordinary sqlite3 connection:

import sqlite3
import sqlite_vec

db = sqlite3.connect("memory.db")
db.enable_load_extension(True)
sqlite_vec.load(db)          # finds and loads vec0 from the pip wheel
db.enable_load_extension(False)

Three lines and you have vector search. No daemon started, no port opened, no connection string. memory.db is the whole store — copy the file and you've backed up your agent's memory.

2. Create the table#

A vec0 virtual table holds fixed-width float vectors. The width has to match your embedding model exactly — OpenAI's text-embedding-3-small returns 1536 dimensions, so:

db.execute("""
  CREATE VIRTUAL TABLE IF NOT EXISTS memories USING vec0(
    embedding float[1536]
  )
""")

# a plain companion table for the text + who it belongs to
db.execute("""
  CREATE TABLE IF NOT EXISTS memory_text (
    rowid   INTEGER PRIMARY KEY,
    user_id TEXT,
    text    TEXT,
    created TEXT DEFAULT CURRENT_TIMESTAMP
  )
""")

The vec0 table stores the vector; the ordinary table stores what the vector means. They share a rowid, which is how a search result becomes a sentence again. (sqlite-vec can also carry auxiliary +text columns inside the vec0 table itself — handy, but a separate table keeps the example plain and lets you JOIN against your real users table later.)

3. Remember — embed and insert#

from openai import OpenAI
client = OpenAI()

def embed(text: str) -> list[float]:
    r = client.embeddings.create(model="text-embedding-3-small", input=text)
    return r.data[0].embedding

def remember(user_id: str, text: str):
    vec = embed(text)
    cur = db.execute(
        "INSERT INTO memory_text (user_id, text) VALUES (?, ?)",
        (user_id, text),
    )
    rowid = cur.lastrowid
    db.execute(
        "INSERT INTO memories (rowid, embedding) VALUES (?, ?)",
        (rowid, sqlite_vec.serialize_float32(vec)),
    )
    db.commit()

sqlite_vec.serialize_float32 packs the Python list into the compact binary layout the vec0 column expects — you can pass a JSON array of floats instead, but serializing is smaller and faster. The two inserts share a rowid, so the vector and its text stay married.

4. Recall — one KNN query#

This is the line the six comparison posts never show you:

def recall(query: str, k: int = 5) -> list[str]:
    qvec = embed(query)
    rows = db.execute("""
        SELECT m.rowid, m.distance, t.text
        FROM memories AS m
        JOIN memory_text AS t ON t.rowid = m.rowid
        WHERE m.embedding MATCH ?
          AND k = ?
        ORDER BY m.distance
    """, (sqlite_vec.serialize_float32(qvec), k)).fetchall()
    return [text for (_id, _dist, text) in rows]

A MATCH against a vec0 table is a k-nearest-neighbor search: sqlite-vec computes the distance from your query vector to every stored vector, fills in the distance column, and ORDER BY distance puts the closest first. k = ? caps the result to the k nearest (equivalently, LIMIT ?). Because it's real SQL, the JOIN hands you the original text in the same round trip — no second lookup, no separate metadata store.

5. Wire it into the loop#

Memory is only useful bracketing the model call — recall before, remember after:

def turn(user_id: str, user_msg: str) -> str:
    memories = recall(user_msg, k=5)
    context = "\n".join(f"- {m}" for m in memories)

    reply = client.chat.completions.create(
        model="gpt-5.6",
        messages=[
            {"role": "system",
             "content": f"Relevant memory about this user:\n{context}"},
            {"role": "user", "content": user_msg},
        ],
    ).choices[0].message.content

    # keep what's worth keeping — here, the user's own statement
    remember(user_id, user_msg)
    return reply

That's a complete, persistent-memory agent. Kill the process, restart it tomorrow, and recall still returns what the user told you last week, because it's all sitting in memory.db. What you choose to remember — every message, only extracted facts, a rolling summary — is the actual product decision, and it's yours to make on top of a store that no longer costs you anything.

When to stop using this#

sqlite-vec searches by brute force: every recall compares the query against every stored vector. That is exact and it is genuinely fast — until it isn't. The line sits somewhere in the low hundreds of thousands of vectors, and it depends on your dimension count, your hardware, and how much latency your users will tolerate. The honest move is to measure recall latency as your table grows and act on the number, not the fear. When you cross it, you add an approximate-nearest-neighbor index or move to a hosted store — and we wrote the piece on exactly where that line is and how to know you've hit it. Until then, one file is not a compromise. It's the correct architecture, and it ships today.