SQLite in Python: Persistent Memory for Agents

SQLite is a relational database built into Python. It stores data in a single file and requires no server. It is ideal for giving agents persistent memory.

Connecting

import sqlite3

conn = sqlite3.connect("memory.db")  # creates the file if it doesn't exist
cursor = conn.cursor()
conn.close()

Or using a context manager:

with sqlite3.connect("memory.db") as conn:
    cursor = conn.cursor()
    # do work...
# conn.close() is called automatically

Creating a table

def init_db(db_path: str = "chat_history.db"):
    with sqlite3.connect(db_path) as conn:
        conn.execute("""
            CREATE TABLE IF NOT EXISTS messages (
                id        INTEGER PRIMARY KEY AUTOINCREMENT,
                session   TEXT NOT NULL,
                role      TEXT NOT NULL,
                content   TEXT NOT NULL,
                created   TEXT DEFAULT (datetime('now'))
            )
        """)
        conn.commit()

Saving messages

def save_message(session: str, role: str, content: str, db_path: str = "chat_history.db"):
    with sqlite3.connect(db_path) as conn:
        conn.execute(
            "INSERT INTO messages (session, role, content) VALUES (?, ?, ?)",
            (session, role, content)
        )
        conn.commit()

Always use ? placeholders — they protect against SQL injection.

Loading history

def load_history(session: str, db_path: str = "chat_history.db") -> list[dict]:
    with sqlite3.connect(db_path) as conn:
        conn.row_factory = sqlite3.Row  # access columns by name
        rows = conn.execute(
            "SELECT role, content FROM messages WHERE session = ? ORDER BY id",
            (session,)
        ).fetchall()
    return [{"role": row["role"], "content": row["content"]} for row in rows]

Listing sessions

def list_sessions(db_path: str = "chat_history.db") -> list[str]:
    with sqlite3.connect(db_path) as conn:
        rows = conn.execute(
            "SELECT DISTINCT session, MIN(created) as started FROM messages GROUP BY session ORDER BY started DESC"
        ).fetchall()
    return [row[0] for row in rows]

Deleting a session

def delete_session(session: str, db_path: str = "chat_history.db"):
    with sqlite3.connect(db_path) as conn:
        conn.execute("DELETE FROM messages WHERE session = ?", (session,))
        conn.commit()

Full example: agent memory

import sqlite3
import anthropic

client = anthropic.Anthropic()
DB = "agent_memory.db"

def init_db():
    with sqlite3.connect(DB) as conn:
        conn.execute("CREATE TABLE IF NOT EXISTS messages (session TEXT, role TEXT, content TEXT)")

def chat(session: str, user_input: str) -> str:
    save_message(session, "user", user_input)
    history = load_history(session)

    response = client.messages.create(
        model="claude-sonnet-4-6",
        max_tokens=1024,
        messages=history
    )
    answer = response.content[0].text
    save_message(session, "assistant", answer)
    return answer