Agent Memory Testing with SQLite Snapshots: 10x Debugging Efficiency

At 1 a.m., a coworker dropped a screenshot into the team chat: after 20 turns of continuous conversation, our client’s AI customer-support agent had “forgotten” an order number that was confirmed earlier. Worse still, this was the third memory-loss incident this month. I stared at the pile of assert agent.memory.get("xxx") == "yyy" in the code and it hit me: we aren’t bad at writing tests; we just never figured out how to verify “persisted memory.”

Are you really testing that the memory hit the disk?

Most teams test agent memory the same way: spin up the agent, feed it a few messages, look it up in memory, call save() or trigger auto-persistence, then read it back and compare. The problem? How do you know the data actually came from disk and isn’t still hanging around as a Python object in RAM?

The most absurd case I’ve seen was a LangChain app where ConversationBufferMemory worked perfectly in tests—all green. In production, as soon as a user switched session IDs, every memory vanished. The reason was simple: the dev tests never broke the object reference on chat_history. They always directly asserted assert memory.buffer, and that attribute was the very same in-memory list. The tests were lying.

True persistence verification has to cross processes—or even separate runs. Write the data, kill the process, restart, and then confirm the data is still there. Writing such tests by hand is painful: you have to manage temporary files, control lifecycle, handle serialization formats, and guarantee isolation between test cases. The conventional approach isn’t impossible—it’s just too slow, too brittle, and never turns into a reusable pattern.

Why not mock, why not Redis?

Initially we thought about mocking the filesystem, but we quickly dropped it. You mock open(), the test passes, but what if the real environment has a write‑permission issue? Or if the serialization uses pickle and a version bump breaks everything? A mock can only prove “the code called a function”—it can’t prove “the data can actually be read back.”

We also considered Redis as a memory backend, connecting to a local Redis instance in tests. That does verify persistence, but the downsides are obvious: every developer machine needs Redis running, and CI requires a service container. A config mismatch leads to the eerie “green locally, red in CI” mystery. Plus Redis’s key expiry policies and persistence mechanisms (RDB/AOF) add another layer of complexity—you just want to test the Agent’s logic, but you end up having to understand the full Redis ops story.

In the end we picked SQLite. Three reasons:

1. Zero dependencies, zero configuration — the Python standard library ships with sqlite3, no external services needed, works straight out of the box in CI.
2. Real file-based persistence — data goes into a .db file. Kill the process, switch Python interpreters, it’s still readable.
3. Native “snapshot” semantics — treat the entire SQLite file as an immutable snapshot. The test framework only has to do one thing: assert that the current snapshot matches the expected snapshot.

That lets us use pytest’s snapshot testing pattern, validating the persistence behavior of agent memory just like frontend folks test UI components.

Core implementation: flattening memory into a SQLite snapshot

The overall idea has three steps: use a pytest fixture to create a temporary SQLite database and inject it into the agent’s memory backend; after the test runs, dump the entire database content into a comparable text snapshot; then use a custom assert_snapshot function to compare the current snapshot against the previously recorded “golden snapshot.”

1. Creating an injectable SQLite memory backend

This code solves a single problem: provide a lightweight memory backend that can replace production Redis/Postgres and fully adheres to the agent’s interface. Here I’ll use LangChain’s BaseChatMessageHistory as an example and implement a custom SQLiteChatHistory.

python
import sqlite3
import json
from typing import List
from langchain.memory.chat_message_histories.in_memory import BaseChatMessageHistory
from langchain.schema import BaseMessage, messages_from_dict, messages_to_dict

class SQLiteChatHistory(BaseChatMessageHistory):
    """基于 SQLite 的对话历史，每条 session 一张表，方便快照导出"""

    def __init__(self, db_path: str, session_id: str):
        self.db_path = db_path
        self.session_id = session_id
        self._init_table()

    def _init_table(self):
        with sqlite3.connect(self.db_path) as conn:
            conn.execute(f"""
                CREATE TABLE IF NOT EXISTS {self._table_name()} (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    message_json TEXT NOT NULL,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
                )
            """)

    def _table_name(self) -> str:
        # SQLite 表名不能以数字开头，加个前缀
        return f"session_{self.session_id.replace('-', '_')}"

    @property
    def messages(self) -> List[BaseMessage]:
        with sqlite3.connect(self.db_path) as conn:
            rows = conn.execute(
                f"SELECT message_json FROM {self._table_name()} ORDER BY id"
            ).fetchall()
        items = [json.loads(row[0]) for row in rows]
        return messages_from_dict(items)

    def add_message(self, message: BaseMessage) -> None:
        msg_dict = messages_to_dict([message])[0]