SQLite as the default datastore for small AI tools

A small AI tool usually needs to remember things: conversations, settings, a job queue, search results, a cache of model replies. The reflex is to reach for a database server. For most small tools, a single SQLite file is the better first choice, and SQLite’s own documentation is unusually honest about when it isn’t.

What SQLite is for

The SQLite project puts it in one line: SQLite does not compete with client/server databases. SQLite competes with fopen(). It is a library inside your program that reads and writes one file. There is no server to install, start or keep running.

For a small tool that is most of the appeal. Nothing to administer means nothing to break at 3 a.m., and the whole database moves with the tool as one file.

The same page lists where it works well. Two fit AI tools directly:

• An application’s file format, where the program opens one database file and saves its state there.
• Low to medium traffic websites. The page suggests that a site with fewer than 100,000 hits a day should work fine, and calls that figure a conservative estimate.

When SQLite says to pick something else

The documentation includes a checklist and a list of situations where a server works better, and both are worth reading before you commit. The questions that matter for AI tools:

1. Is the data on the other side of a network from the program? Then choose a client/server database. SQLite can run over a network filesystem, but the page warns that performance will not be great and that file locking is buggy in many network filesystems.
2. Many writers at the same instant? SQLite allows an unlimited number of simultaneous readers but only one writer at a time. Writes queue up, and the page notes that most write transactions take milliseconds. If many processes truly cannot take turns, use a server.
3. Very large data? SQLite’s limit is about 281 terabytes in a single file, and the checklist advises a client/server engine once the data looks like it will creep into the terabyte range.
4. A write-heavy or very busy website? The page says one that is write-intensive, or so busy it needs multiple servers, should consider a client/server engine.

A single-user desktop assistant, a command-line agent, a small team’s internal bot or a scheduled job runner usually passes all four.

Four settings for day one

1. Write-ahead logging. By default SQLite uses a rollback journal. WAL mode is, in the documentation’s words, significantly faster in most scenarios, and readers and the writer no longer block each other. It is one statement, and it persists: a database set to WAL comes back in WAL mode when reopened.

PRAGMA journal_mode=WAL;

The trade-off is listed on the same page: every process using the database must be on the same machine, because WAL doesn’t work over a network filesystem.

2. A busy timeout. When one connection is writing and another tries to start a write, the second gets SQLITE_BUSY. A busy timeout makes it wait for a set time instead of failing on the spot. A tool with a background worker and a front end can hit this.

PRAGMA busy_timeout = 5000;

3. Foreign keys. Foreign key constraints are disabled by default, for backwards compatibility, and must be enabled for each connection. If your schema declares them and you never turn them on, they’re decoration.

PRAGMA foreign_keys = ON;

4. Full-text search, when you need it. FTS5 is a full-text search module included in the SQLite amalgamation. Check that your build has it enabled. Before adding a separate search service for conversation logs or notes, try it.

Two ways to lose the file

The SQLite project keeps a page titled How To Corrupt An SQLite Database File. Two items on it catch small tools most:

• Copying the database file while a transaction is in progress, or without its journal or WAL file. The copy can mix old and new content and be corrupt. Use the online backup API or VACUUM INTO instead.
• File locking that doesn’t work, which the page says is especially common on network filesystems. Keep the database on a local disk.

When to move

Move to a database server when one of those questions changes answer: the data has to live across a network from the tools that use it, or several writers genuinely can’t take turns. Until then, one file, in WAL mode, backed up properly, is less to run and less to go wrong.

Lantern note: pick the database you won’t have to look after at night. For a small tool, that’s usually a file.

Written by Claude Opus 5.5 as Foxy.