Giving Your AI Agent Memory with SQLite

Hey there, developers 🖖

Welcome back to part 2 of our AI Agent series! If you followed along with the first post, you should have a working MCP server that lets Claude manage a simple task list. Pretty cool, right?

After writing that post, I was feeling pretty good about myself. I had my agent running, I added a bunch of tasks, marked some as in-progress, and even asked Claude for a summary. Everything was working beautifully. Then I restarted the server.

Everything was gone 😅

Yeah. Every single task. Vanished. Because our entire task store was an in-memory Map that lives and dies with the Node.js process. I stared at my terminal for a good ten seconds before it hit me: “Of course it’s gone, Dan. You stored everything in RAM like it’s 1995.”

And honestly? That’s the exact kind of thing you only truly understand when it bites you. So today, we’re going to fix it. We’re going to give our agent persistent memory using SQLite, so it actually remembers your tasks across restarts, crashes, and even system reboots.

Let’s get into it, developers!

Why Persistence Matters for Agents

Before we jump into the code, let me share something that clicked for me while I was thinking about this problem. Persistence isn’t just about “saving data” — it’s about what separates a demo from something you’d actually use.

Think about it: would you trust an assistant that forgot everything every time you walked out of the room? That’s essentially what our agent was doing. You’d ask it to track your tasks, and the moment the process restarted, it had no idea who you were or what you’d asked it to do.

But here’s the part that really got me excited. Once your agent can persist data, you’re one step away from real agent memory. Not just storing tasks, but remembering context from previous conversations, learning user preferences, tracking patterns over time. The database we’re about to add is the foundation for all of that.

Baby steps though, developer. Today, we persist tasks. Tomorrow, we conquer the world 😄

What We’re Building

Our goal is simple: take the exact same MCP server from part 1 and swap out the in-memory Map for a SQLite database. The tools stay the same, the MCP interface stays the same, Claude doesn’t even notice the difference — but now our data survives restarts.

Why SQLite? A few reasons:

1. Zero configuration — no database server to install or manage. It’s just a file.
2. Perfect for single-user agents — our MCP server runs locally, so we don’t need a multi-user database.
3. Production-ready — don’t let its simplicity fool you. SQLite handles terabytes of data and is used in production by more applications than any other database engine in the world.
4. Easy to inspect — you can open the database file with any SQLite client and see exactly what’s stored.

For our use case, it’s honestly the perfect fit.

Setting Up SQLite

Alright, let’s get our hands dirty. Starting from the project we built in part 1 (agent-todo-mcp), we need to install one new dependency:

npm install better-sqlite3
npm install -D @types/better-sqlite3

We’re using better-sqlite3 instead of the built-in sqlite3 package because it’s synchronous, which makes our code much simpler. No callbacks, no promises for database operations — just straightforward function calls. Trust me, your future self will thank you.

That’s it for dependencies. Told you SQLite was low-ceremony.

Creating the Database Layer

Here’s where things get interesting. Instead of just replacing the Map with raw SQL calls everywhere, let’s create a clean database layer. This will keep our tool handlers nice and readable, and it’ll make it easy to swap out the database implementation later if we ever need to.

Create a new file src/database.ts:

import Database from "better-sqlite3";
import path from "path";
 
export interface Task {
  id: string;
  title: string;
  description?: string;
  status: "pending" | "in-progress" | "done";
  createdAt: string;
}
 
export class TaskDatabase {
  private db: Database.Database;
 
  constructor(dbPath?: string) {
    // Resolve relative to the script location, not the working directory
    const resolvedPath = dbPath || path.join(__dirname, "..", "tasks.db");
    this.db = new Database(resolvedPath);
 
    // Enable WAL mode for better performance
    this.db.pragma("journal_mode = WAL");
 
    // Create the tasks table if it doesn't exist
    this.db.exec(`
      CREATE TABLE IF NOT EXISTS tasks (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        description TEXT,
        status TEXT NOT NULL DEFAULT 'pending',
        created_at TEXT NOT NULL DEFAULT (datetime('now'))
      )
    `);
  }
 
  addTask(title: string, description?: string): Task {
    const stmt = this.db.prepare(
      "INSERT INTO tasks (title, description) VALUES (?, ?)"
    );
    const result = stmt.run(title, description || null);
 
    return this.getTask(String(result.lastInsertRowid))!;
  }
 
  getTask(id: string): Task | undefined {
    const stmt = this.db.prepare("SELECT * FROM tasks WHERE id = ?");
    const row = stmt.get(id) as any;
 
    if (!row) return undefined;
 
    return {
      id: String(row.id),
      title: row.title,
      description: row.description || undefined,
      status: row.status,
      createdAt: row.created_at,
    };
  }
 
  listTasks(status?: string): Task[] {
    let stmt;
 
    if (status && status !== "all") {
      stmt = this.db.prepare(
        "SELECT * FROM tasks WHERE status = ? ORDER BY id"
      );
      return (stmt.all(status) as any[]).map(this.rowToTask);
    }
 
    stmt = this.db.prepare("SELECT * FROM tasks ORDER BY id");
    return (stmt.all() as any[]).map(this.rowToTask);
  }
 
  updateTask(id: string, status: string): Task | undefined {
    const existing = this.getTask(id);
    if (!existing) return undefined;
 
    const stmt = this.db.prepare("UPDATE tasks SET status = ? WHERE id = ?");
    stmt.run(status, id);
 
    return this.getTask(id);
  }
 
  deleteTask(id: string): Task | undefined {
    const existing = this.getTask(id);
    if (!existing) return undefined;
 
    const stmt = this.db.prepare("DELETE FROM tasks WHERE id = ?");
    stmt.run(id);
 
    return existing;
  }
 
  getSummary(): {
    total: number;
    pending: number;
    inProgress: number;
    done: number;
  } {
    const stmt = this.db.prepare(`
      SELECT
        COUNT(*) as total,
        SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending,
        SUM(CASE WHEN status = 'in-progress' THEN 1 ELSE 0 END) as in_progress,
        SUM(CASE WHEN status = 'done' THEN 1 ELSE 0 END) as done
      FROM tasks
    `);
    const row = stmt.get() as any;
 
    return {
      total: row.total,
      pending: row.pending,
      inProgress: row.in_progress,
      done: row.done,
    };
  }
 
  close(): void {
    this.db.close();
  }
 
  private rowToTask(row: any): Task {
    return {
      id: String(row.id),
      title: row.title,
      description: row.description || undefined,
      status: row.status,
      createdAt: row.created_at,
    };
  }
}

Let me walk you through the key decisions here, developer:

1. CREATE TABLE IF NOT EXISTS — the database creates itself on first run. No migration scripts, no setup steps. Just start the server and it works.
2. AUTOINCREMENT — SQLite handles the IDs for us now, so we don’t need that nextId counter anymore.
3. WAL mode — this stands for Write-Ahead Logging. It’s a SQLite performance optimization that allows reads and writes to happen concurrently. One line of code, free performance. Why not?
4. Prepared statements — we’re using db.prepare() instead of string concatenation. This protects against SQL injection and is also faster for repeated queries. Always do this, developer. Always.
5. __dirname instead of process.cwd() — this one bit me. I originally used process.cwd() to resolve the database path, and kept getting no such table: tasks errors. Turns out, when Claude Desktop or Claude Code launches your MCP server, the working directory is not your project folder — it could be your home directory or anywhere else. So tasks.db was being created in some random location, and my server was opening a different empty file each time. Using __dirname resolves the path relative to the script itself, which is always predictable. Lesson learned the hard way 😅

Notice how the class methods mirror exactly what our MCP tools were doing before. That’s intentional — it makes the refactor dead simple.

Updating the MCP Server

Now comes the satisfying part. Let’s update src/index.ts to use our new database layer. You’ll see how little actually changes — that’s the beauty of having a clean separation between the data layer and the tool handlers.

Replace the entire contents of src/index.ts:

import { McpServer } from "@modelcontextprotocol/sdk/server/mcp.js";
import { StdioServerTransport } from "@modelcontextprotocol/sdk/server/stdio.js";
import { z } from "zod";
import { TaskDatabase } from "./database.js";
 
// Initialize the database (replaces our in-memory Map!)
const db = new TaskDatabase();
 
// Create the MCP server
// Notice the version bump to 1.1.0 — we did ship a real feature after all
const server = new McpServer({
  name: "todo-agent",
  version: "1.1.0",
});
 
// Tool: Add a new task
server.registerTool(
  "add_task",
  {
    description: "Create a new task with a title and optional description",
    inputSchema: {
      title: z.string().describe("The title of the task"),
      description: z
        .string()
        .optional()
        .describe("A detailed description of the task"),
    },
  },
  async ({ title, description }) => {
    const task = db.addTask(title, description);
 
    return {
      content: [
        {
          type: "text",
          text: `Task created successfully!\n\nID: ${task.id}\nTitle: ${task.title}\nStatus: ${task.status}`,
        },
      ],
    };
  }
);
 
// Tool: List all tasks
server.registerTool(
  "list_tasks",
  {
    description: "List all tasks, optionally filtered by status",
    inputSchema: {
      status: z
        .enum(["pending", "in-progress", "done", "all"])
        .optional()
        .default("all")
        .describe("Filter tasks by status"),
    },
  },
  async ({ status }) => {
    const tasks = db.listTasks(status);
 
    if (tasks.length === 0) {
      return {
        content: [{ type: "text", text: "No tasks found." }],
      };
    }
 
    const taskList = tasks
      .map(
        (t) =>
          `- [${t.status === "done" ? "x" : " "}] #${t.id}: ${t.title}${
            t.description ? `\n  ${t.description}` : ""
          }`
      )
      .join("\n");
 
    return {
      content: [
        {
          type: "text",
          text: `Tasks (${tasks.length}):\n\n${taskList}`,
        },
      ],
    };
  }
);
 
// Tool: Update task status
server.registerTool(
  "update_task",
  {
    description: "Update the status of a task",
    inputSchema: {
      id: z.string().describe("The ID of the task to update"),
      status: z
        .enum(["pending", "in-progress", "done"])
        .describe("The new status of the task"),
    },
  },
  async ({ id, status }) => {
    const task = db.updateTask(id, status);
 
    if (!task) {
      return {
        content: [{ type: "text", text: `Task #${id} not found.` }],
        isError: true,
      };
    }
 
    return {
      content: [
        {
          type: "text",
          text: `Task #${id} updated to: ${status}\n\nTitle: ${task.title}`,
        },
      ],
    };
  }
);
 
// Tool: Delete a task
server.registerTool(
  "delete_task",
  {
    description: "Delete a task by its ID",
    inputSchema: {
      id: z.string().describe("The ID of the task to delete"),
    },
  },
  async ({ id }) => {
    const task = db.deleteTask(id);
 
    if (!task) {
      return {
        content: [{ type: "text", text: `Task #${id} not found.` }],
        isError: true,
      };
    }
 
    return {
      content: [
        {
          type: "text",
          text: `Task #${id} ("${task.title}") has been deleted.`,
        },
      ],
    };
  }
);
 
// Tool: Get a summary of all tasks
server.registerTool(
  "get_task_summary",
  {
    description: "Get a summary with counts of tasks by status",
    inputSchema: {},
  },
  async () => {
    const summary = db.getSummary();
 
    return {
      content: [
        {
          type: "text",
          text: `Task Summary:\n\n📋 Total: ${summary.total}\n⏳ Pending: ${
            summary.pending
          }\n🔄 In Progress: ${summary.inProgress}\n✅ Done: ${
            summary.done
          }\n\nCompletion rate: ${
            summary.total > 0
              ? Math.round((summary.done / summary.total) * 100)
              : 0
          }%`,
        },
      ],
    };
  }
);
 
// Graceful shutdown — close the database connection
process.on("SIGINT", () => {
  db.close();
  process.exit(0);
});
 
process.on("SIGTERM", () => {
  db.close();
  process.exit(0);
});
 
// Start the server using stdio transport
async function main() {
  const transport = new StdioServerTransport();
  await server.connect(transport);
  console.error("Todo MCP Server running on stdio (with SQLite persistence!)");
}
 
main().catch(console.error);

Take a good look at the tool handlers, developer. See how clean they are now? Each one is basically just:

1. Call a database method.
2. Format the result.
3. Return it.

All the data logic is neatly tucked away in database.ts. The MCP tools don’t know or care how tasks are stored — they just ask the database layer to do its thing. If we ever wanted to switch from SQLite to PostgreSQL, we’d only need to change database.ts. The tools wouldn’t need a single edit.

I also added something we didn’t have in part 1: graceful shutdown. Those SIGINT and SIGTERM handlers make sure we close the database connection properly when the server stops. It’s a small thing, but it prevents potential data corruption. Good habits, developer!

The Moment of Truth

Alright, let’s see if this actually works. Build the project:

npx tsc

Now, restart Claude Desktop (or reconnect via Claude Code), and let’s run through the test.

Start by adding three tasks:

Add three tasks: Write the blog post about SQLite persistence, Review the PR for the auth service, and Deploy the staging environment

You should see Claude calling add_task three times, just like before. So far, so good.

Next, close Claude Desktop completely. Or if you’re using Claude Code, exit the session. This kills the MCP server process.

Open Claude Desktop again (or start a new Claude Code session) and ask:

List all my tasks

⏺ todo-agent - list_tasks (MCP)(status: "all")
  ⎿  Tasks (3):
 
     - [ ] #1: Write the blog post about SQLite persistence
     - [ ] #2: Review the PR for the auth service
     - [ ] #3: Deploy the staging environment

There they are. All three tasks, right where you left them ✌️

I’m not going to lie, the first time I saw this work, I just sat there grinning like an idiot. It’s such a simple thing, but it makes the agent feel real. Like it actually knows you and remembers what you asked it to do.

Inspecting the Database

One of the things I love about SQLite is that you can peek inside the database any time you want. The data lives in a file called tasks.db in your project directory. You can open it with any SQLite client, or just use the command line:

sqlite3 tasks.db

Then try:

sqlite> SELECT * FROM tasks;

1|Write the blog post about SQLite persistence||pending|2026-03-27 10:30:00
2|Review the PR for the auth service||pending|2026-03-27 10:30:00
3|Deploy the staging environment||pending|2026-03-27 10:30:00

sqlite> .schema tasks

CREATE TABLE tasks (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  description TEXT,
  status TEXT NOT NULL DEFAULT 'pending',
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

Being able to see and query your agent’s data directly is incredibly useful for debugging. No guesswork, no logging everything to the console — just open the database and look.

What I Learned

Building this was pretty straightforward, but it taught me a few things that I think are worth sharing:

The abstraction layer was worth it. I almost didn’t create the TaskDatabase class — I was tempted to just write SQL directly in the tool handlers. I’m glad I didn’t. Having that separation made the refactor clean and keeps the door open for future changes.

SQLite is seriously underrated. I’ve always reached for PostgreSQL or MongoDB by default, but for a local agent like this, SQLite is perfect. No Docker containers, no connection strings, no server to manage. Just a file. Sometimes the simplest solution really is the best one.

Persistence changes how you think about the agent. Once the data sticks around, you start thinking differently. “What if I add a completedAt timestamp?”, “What if I track how long tasks stay in progress?”, “What if the agent can analyze my productivity patterns?”. That’s the path to real agent memory, and it starts right here.

Next Steps

We’re building a solid foundation, developer. Our agent can now act (through MCP tools) and remember (through SQLite). But there’s still a lot more ground to cover:

1. MCP Resources and Prompts — our server only uses Tools right now, but MCP has two other powerful primitives we haven’t explored yet: Resources (data the model can read as context) and Prompts (reusable templates). These can make our agent much smarter about how it uses task data.
2. Multi-Tool Orchestration — what happens when we connect Claude to multiple MCP servers at once? Imagine an agent that can read your GitHub issues, create tasks from them, and then update a Notion board — all in one conversation.
3. Agent-to-Agent Communication (A2A) — the protocol that lets agents talk to each other. This is where things start getting really wild.

Which topic sounds most interesting? Your feedback actually shapes what I write next!

Useful Resources

• Post GitHub repo
• better-sqlite3 Documentation
• SQLite Documentation
• Model Context Protocol Documentation
• Part 1: Building Your First AI Agent with Claude and MCP

Conclusion

And that’s a wrap on Part 2, developers! We took our AI in-memory agent and turned it into something that actually remembers. The change was surprisingly small — just a new file, a few updated handlers, and one npm install — but the impact is significant.

Our agent now has the foundation for real memory. Today it’s tasks in a SQLite database. But the same pattern — persist, retrieve, reason — is exactly how production agents handle conversation history, user preferences, learned behaviors, and more. We’re building the right muscles here.

Stay focused, Developer!