SQLite or Postgres: Choosing Your MCP Context Store

For a single machine running Claude Code or Claude Desktop, SQLite is the right default for open-context's MCP context store — it needs no install and comfortably handles the write volume one user generates. Move to Postgres only once more than one process needs to write to the same store at the same time, which usually means running the store across multiple machines or sharing it between the HTTP server, the MCP server, and a teammate's setup at once.

The real question isn't size, it's concurrency

It's tempting to frame this as a scale decision — "how many contexts before I need a real database" — but that's not where SQLite actually runs out of room. SQLite's own documentation puts it plainly: a site or application doing fewer than 100,000 hits a day is a conservative fit, and SQLite has been used successfully as the on-disk format for desktop apps, CLI tools, and browsers handling far more data than an MCP context store ever will (SQLite: Appropriate Uses For SQLite). The real constraint is that SQLite allows only one writer at a time per database file. For a single person running the MCP server, the HTTP server, and the CLI against the same store, that's rarely a problem — those writes are small and sequential. It becomes a problem when multiple independent processes, on multiple machines, try to write at once.

When SQLite is the right call

If you're running open-context on one machine — Claude Code or Claude Desktop talking to a local MCP server, maybe with the web UI open in a tab — SQLite is a strict upgrade over the default JSON file with none of the operational cost of a client/server database. Two things make it the recommended step:

• No install. open-context's SQLite driver uses node:sqlite, which ships with Node — there's no package to add and no server process to run.
• Writes only what changed. Unlike the JSON adapter, which rewrites the whole file on every save, SQLite writes the delta. That matters once you have thousands of saved contexts and are running the HTTP and MCP servers at the same time.

Point OPENCONTEXT_DB_URL at a local file to switch:

export OPENCONTEXT_DB_URL="sqlite:///path/to/opencontext.db"

That's it — no schema to write by hand, no service to keep running in the background. If you outgrow it later, the migration path below carries your data forward.

When to move to Postgres

Postgres becomes the better fit the moment more than one writer needs the same store at the same time. Postgres handles that with MVCC (multi-version concurrency control): each statement works from a snapshot of the data, and reads never block writes or vice versa (PostgreSQL Documentation: 13.1. Introduction). That's the concrete difference from SQLite's single-writer file lock, and it's what makes Postgres the right choice once you're sharing context across machines — a team saving to a common store, a server-side deployment where the MCP server and HTTP API run as separate processes and containers, or an install behind a managed database you already operate.

Switching is the same one-line change, just a different scheme:

export OPENCONTEXT_DB_URL="postgres://user:pass@host:5432/opencontext"

This requires the pg peer dependency (npm i pg) — open-context's drivers are optional peer dependencies, so nothing is installed until you actually pick a backend that needs it. Managed Postgres — Neon, Supabase, RDS, Aurora, Azure Database for PostgreSQL, CockroachDB, Timescale — all work through the same postgres:// connection string; just append ?sslmode=require.

Switching later doesn't mean starting over

You don't have to pick correctly up front. open-context's CLI has a three-command flow built specifically for moving between backends without losing anything already saved:

# Try the connection first, without committing to it
opencontext db test "postgres://user:pass@localhost:5432/opencontext"

# Switch the active backend
opencontext db use "postgres://user:pass@localhost:5432/opencontext"

# Copy everything from the old store into the new one
opencontext db migrate --to "postgres://user:pass@localhost:5432/opencontext"

The migration only ever reads from the source store — it writes exclusively to the target — so a failed migration halfway through leaves your original SQLite (or JSON) store untouched and you can just run it again. Bubbles are copied before contexts, so context-to-bubble links land correctly on the new backend, and IDs are regenerated by the target rather than copied verbatim. The same flow works in reverse, or between any two of the 15 supported backends, not just SQLite and Postgres.

What actually stays the same

This is the part worth trusting before you switch: every backend, from the default JSON file to SQLite to Postgres to DynamoDB, is validated against the same conformance test suite — around 50 tests covering ordering, search semantics, and edge cases like an unset field coming back as undefined rather than null. save_context, recall_context, and the rest of the MCP tools behave identically regardless of which one is active. The one real difference is where search filtering happens: SQL backends like SQLite and Postgres push case-insensitive substring matching down into the database, while document and key-value backends without a portable substring predicate filter in memory after reading the collection. Results come back identical either way — on a very large store, the SQL backends are just faster at it. And because credentials live in a connection string, they're worth handling the same way regardless of backend — see securing a local MCP server for how open-context redacts and stores them.

FAQ

Does open-context support Postgres for the MCP context store?

Yes. Set OPENCONTEXT_DB_URL to a postgres:// connection string and install the pg peer dependency (npm i pg). Postgres is one of 15 supported backends, and the CLI, web UI, and MCP tools all behave identically once it's connected.

Can I move from SQLite to Postgres without losing data?

Yes. Run opencontext db migrate --to "postgres://user:pass@host:5432/db" and every context and bubble is copied across. The source store is only ever read, so if the migration fails partway through, your original SQLite file is untouched and you can just retry.

Do I need to install anything to use SQLite with open-context?

No. SQLite support uses Node's built-in node:sqlite module, so there's no npm package to install and no separate database server to run. It's the recommended first step up from the default JSON file.

Is search slower on one backend than the other?

No — every backend passes the same conformance test suite and returns identical results in identical order. SQL backends like SQLite and Postgres push search filtering into the database itself, which is typically faster than the in-memory filtering that key-value backends like Redis or DynamoDB fall back to on very large stores.

What happens to my existing contexts.json file if I don't set up a database?

Nothing changes. OPENCONTEXT_DB_URL is optional — with no configuration at all, open-context keeps reading and writing the same JSON file at ~/.opencontext/contexts.json it always has.