Local-First Databases for AI Apps

Where your database lives is a latency, offline, and isolation decision. Three tiers:

Tier	Reads	Writes	Offline
Pure remote	network hop	network hop	none
Embedded replica	local file	remote-first, then synced down	reads yes; writes need connectivity (unless offline mode)
Fully local + explicit sync	local	local	full; sync when you choose

For AI apps the stakes are concrete: an agent doing dozens of retrievals per task (rag-fundamentals) pays a network round-trip per query on tier 1, and microseconds on tiers 2–3.

Turso embedded replicas

A local SQLite file that mirrors a remote Turso database:

import { createClient } from "@libsql/client";

const client = createClient({
  url: "file:local.db",              // local replica file
  syncUrl: "libsql://mydb-org.turso.io",
  authToken: process.env.TURSO_AUTH_TOKEN,
  syncInterval: 60,                  // background pull every 60s
  offline: true,                     // offline writes (sync later)
});
await client.sync();                 // manual sync when you need it

Semantics worth memorizing:

Reads hit the local file — microsecond latency, no egress.
Writes are remote-first: forwarded to the primary, then reflected locally with a read-your-writes guarantee (you never read staler state than your own commit).
Sync transfers the WAL in 4KB frame units — the granularity that determines sync bandwidth.
Requires a real filesystem, so classic ephemeral serverless doesn't qualify; containers, VMs, and edge runtimes with disks do.

Turso Sync (2026)

Built on Turso's rewritten Rust engine (formerly codenamed "Limbo"): CDC-based (change-data-capture) sync with explicit push() / pull() instead of interval-based WAL-frame pulls. Ships less bandwidth (changes, not frames) and gives the app control over sync moments. Recommended for new offline-first projects; embedded replicas remain the read-scaling tool.

Database-per-user / database-per-agent

SQLite databases are cheap files, so Turso can provision one database per user or per agent — a pattern with real teeth for AI:

Agent memory isolation — each agent's scratch state, embeddings, and history live in a physically separate database; no cross-tenant leakage class of bugs (compare RLS-based logical isolation in supabase-for-app-devs).
Instant provisioning — creating a database is fast enough to do per-signup or per-agent-spawn (subagents each getting a private store).

sqlite-vec for plain SQLite

No Turso? sqlite-vec adds vector search to stock SQLite as an extension — runs anywhere SQLite runs, including WASM (vector search fully in the browser):

create virtual table vec_docs using vec0(embedding float[768]);

select rowid, distance
from vec_docs
where embedding match ?    -- query vector
order by distance
limit 5;

For libSQL-native vector columns and DiskANN indexes, see turso-vector-search.

Turso CLI essentials

turso db create myapp          # provision a database
turso db show myapp --url      # libsql:// URL for syncUrl
turso db tokens create myapp   # authToken

Choosing a tier

Multi-user relational app with server rendering → pure remote (or Supabase).
Read-heavy AI retrieval, server-side, latency-sensitive → embedded replica.
Offline-first client or per-agent memory → fully local with Turso Sync, or sqlite-vec if you're staying in stock SQLite.

Key terms

Local-first — architecture where the authoritative working copy is on-device, with sync to a remote as a background or explicit operation.
Embedded replica — a local SQLite file mirroring a remote Turso database: local reads, remote-first writes, background sync via syncInterval or manual client.sync().
Read-your-writes guarantee — after your write commits, your subsequent local reads reflect it, despite the remote-first write path.
WAL frame — the 4KB write-ahead-log unit in which embedded-replica sync transfers changes.
Turso Sync — 2026 CDC-based sync on Turso's rewritten engine (formerly "Limbo") with explicit push()/pull(); recommended for new offline-first projects.
CDC (change data capture) — syncing by shipping logical changes rather than raw storage frames; less bandwidth than frame-based sync.
Database-per-user pattern — provisioning one cheap database per user or agent for physical isolation and instant setup.
sqlite-vec — vector-search extension for plain SQLite (vec0 virtual tables, MATCH queries); runs anywhere including WASM.
offline: true — libSQL client option enabling offline writes on an embedded replica, synced when connectivity returns.