SQLite Is the Right Database for Most AI Agent Workloads
SQLite Is the Right Database for Most AI Agent Workloads
Every time someone builds an AI agent, the instinct is to reach for Postgres, Redis, or some distributed database cluster. The reasoning sounds smart - "what if we need to scale?" But for most agent workloads, SQLite is not just adequate - it is the better choice.
What Makes Agent Workloads Different
An AI agent session is fundamentally a single-writer workload. One agent runs, makes decisions, stores state, and moves on. There is no concurrent write pressure from other processes. There is no multi-region replication requirement. There is just one process writing structured data to track what it has done.
SQLite is designed exactly for this pattern. It is the most widely deployed database in the world precisely because most data is local, structured, and single-writer.
The overhead you pay for a traditional client-server database:
- A server process consuming 50-200MB of RAM before you write a single row
- Network round-trips adding 1-5ms per query (even on localhost)
- Connection pool management code
- Authentication and credential management
- Backup procedures that require stopping writes or using specialized tooling
For an agent session that runs for minutes or hours and stores kilobytes to megabytes of state, none of this overhead is justified.
Performance Is Better Than You Think
SQLite is fast. On modern NVMe storage, SQLite handles:
- 100,000+ simple reads per second
- 50,000+ writes per second with WAL mode enabled
- Queries against million-row tables in under 10ms with proper indexes
For comparison, a typical AI agent session generates hundreds to low thousands of database operations. SQLite is not close to being the bottleneck.
WAL (Write-Ahead Logging) mode is worth enabling for any agent that writes while running:
import sqlite3
def open_agent_db(path: str) -> sqlite3.Connection:
conn = sqlite3.connect(path)
conn.execute("PRAGMA journal_mode=WAL")
conn.execute("PRAGMA synchronous=NORMAL") # faster, still crash-safe
conn.execute("PRAGMA foreign_keys=ON")
return conn
WAL mode enables concurrent reads during writes and is especially useful if you want to inspect the database while an agent is running.
A Practical Schema for Agent Sessions
Here is a schema that works for most agent use cases:
-- Track the agent session itself
CREATE TABLE sessions (
id TEXT PRIMARY KEY,
task TEXT NOT NULL,
started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ended_at TIMESTAMP,
status TEXT DEFAULT 'running', -- running, completed, failed
final_output TEXT
);
-- Every tool call the agent makes
CREATE TABLE tool_calls (
id INTEGER PRIMARY KEY AUTOINCREMENT,
session_id TEXT REFERENCES sessions(id),
tool_name TEXT NOT NULL,
input JSON,
output JSON,
duration_ms INTEGER,
called_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
error TEXT
);
-- Important decisions and their reasoning
CREATE TABLE decisions (
id INTEGER PRIMARY KEY AUTOINCREMENT,
session_id TEXT REFERENCES sessions(id),
decision_type TEXT NOT NULL,
context TEXT,
reasoning TEXT,
chosen_action TEXT,
decided_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Artifacts produced during the session
CREATE TABLE artifacts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
session_id TEXT REFERENCES sessions(id),
artifact_type TEXT NOT NULL, -- file, url, data
path TEXT,
content TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_tool_calls_session ON tool_calls(session_id);
CREATE INDEX idx_decisions_session ON decisions(session_id);
This gives you a complete audit trail. After the session ends, you can replay exactly what the agent did, why, and what it produced.
Integration With Python Agent Code
A simple wrapper that makes the database invisible to the agent logic:
import sqlite3
import json
import time
from contextlib import contextmanager
from dataclasses import dataclass
@dataclass
class AgentDB:
conn: sqlite3.Connection
session_id: str
def log_tool_call(self, tool_name: str, input_data: dict,
output_data: dict, duration_ms: int, error: str = None):
self.conn.execute("""
INSERT INTO tool_calls (session_id, tool_name, input, output, duration_ms, error)
VALUES (?, ?, ?, ?, ?, ?)
""", (self.session_id, tool_name,
json.dumps(input_data), json.dumps(output_data),
duration_ms, error))
self.conn.commit()
def log_decision(self, decision_type: str, context: str,
reasoning: str, chosen_action: str):
self.conn.execute("""
INSERT INTO decisions (session_id, decision_type, context, reasoning, chosen_action)
VALUES (?, ?, ?, ?, ?)
""", (self.session_id, decision_type, context, reasoning, chosen_action))
self.conn.commit()
def instrument_tool(db: AgentDB, tool_func):
"""Wrap a tool function to automatically log calls."""
def wrapper(**kwargs):
start = time.time()
error = None
result = None
try:
result = tool_func(**kwargs)
except Exception as e:
error = str(e)
raise
finally:
duration = int((time.time() - start) * 1000)
db.log_tool_call(
tool_func.__name__,
kwargs,
result or {},
duration,
error
)
return result
return wrapper
The agent code calls instrumented_read_file(path="/etc/config") without knowing the database exists. You get complete observability for free.
The Session-Per-File Pattern
Create a new database file for each agent session, named by timestamp and task:
import uuid
from pathlib import Path
from datetime import datetime
def create_session_db(task: str, base_dir: str = "./agent-sessions") -> AgentDB:
Path(base_dir).mkdir(exist_ok=True)
session_id = str(uuid.uuid4())[:8]
timestamp = datetime.now().strftime("%Y%m%d-%H%M%S")
task_slug = task[:30].replace(" ", "-").lower()
db_path = f"{base_dir}/{timestamp}-{task_slug}-{session_id}.db"
conn = open_agent_db(db_path)
# run CREATE TABLE statements...
conn.execute(
"INSERT INTO sessions (id, task) VALUES (?, ?)",
(session_id, task)
)
conn.commit()
return AgentDB(conn=conn, session_id=session_id)
Session files accumulate in ./agent-sessions/. Each is a self-contained record of what happened. Backup is cp -r ./agent-sessions backup/. Archiving is tar czf sessions-2026-03.tar.gz agent-sessions/.
Cross-Session Queries
When you need to analyze patterns across sessions, a one-time aggregation script is faster to write than a shared database:
import sqlite3
import glob
def query_all_sessions(sql: str, base_dir: str = "./agent-sessions"):
"""Run a query across all session databases and combine results."""
results = []
for db_path in glob.glob(f"{base_dir}/*.db"):
conn = sqlite3.connect(db_path)
try:
rows = conn.execute(sql).fetchall()
results.extend(rows)
except sqlite3.OperationalError:
pass # table doesn't exist in older sessions
finally:
conn.close()
return results
# Example: find all tool calls that errored across all sessions
errors = query_all_sessions("""
SELECT tool_name, error, called_at
FROM tool_calls
WHERE error IS NOT NULL
""")
This runs in seconds for hundreds of session files. For larger archives, DuckDB can query SQLite files directly and run parallel scans.
When SQLite Is Not Enough
You genuinely need something else when:
- Multiple agents share mutable state simultaneously - agents reading each other's decisions and updating shared queues. SQLite's single-writer model becomes a bottleneck when writes need to be visible immediately across processes.
- Session data exceeds ~10GB - SQLite handles this but performance degrades and backup/restore becomes slow.
- You need real-time cross-session queries - if a monitoring dashboard needs sub-second updates across hundreds of concurrent sessions, a proper OLTP database makes more sense.
Notice how specific these conditions are. Most agent systems, even at meaningful scale, do not hit them. Build with SQLite until you have a concrete reason to switch.
Fazm is an open source macOS AI agent. Open source on GitHub.