Sqlite Turso

SQLite & Turso/libSQL — decide, connect, operate

SQLite is an in-process, single-file, single-writer relational engine. Turso/libSQL is the fork that adds a remote edge database and — the part people actually want — embedded replicas: a local SQLite file that syncs to a remote primary, so reads are microsecond-local and writes still land durably in the cloud.

This skill is decide + connect + operate. It owns the engine truths (WAL, single writer, pragmas), the three deployment shapes, and wiring the current @libsql/client. It does NOT own schema modeling or type-safe queries (that is drizzle-orm), versioned migration files (that is db-migrations), or backup strategy as a primary task (that is backups).

Is SQLite/Turso the right tool?

Decide first. Picking SQLite for a high-write multi-writer workload is the mistake that wastes a week.

Three deployment shapes

Every Turso/libSQL decision reduces to which of these three you are wiring. The URL scheme picks the transport automatically.

libsql:// resolves to HTTPS/WSS. file: is native. https:/http: is Hrana HTTP, wss:/ws: is Hrana WebSocket. You rarely set the scheme by hand — you set the URL and the client chooses.

Connect with @libsql/client

Current client is @libsql/client v0.17.x. Never hardcode the auth token — read it from the environment. Convention: TURSO_DATABASE_URL and TURSO_AUTH_TOKEN.

// 1. Local file — dev, tests, single node
import { createClient } from "@libsql/client";

const db = createClient({ url: "file:local.db" });

// 2. Remote edge DB — serverless, no local disk
import { createClient } from "@libsql/client";

const db = createClient({
  url: process.env.TURSO_DATABASE_URL!,   // libsql://<db>.turso.io
  authToken: process.env.TURSO_AUTH_TOKEN!,
});

// 3. Embedded replica — local reads, syncs to the primary
import { createClient } from "@libsql/client";

const db = createClient({
  url: "file:local.db",                   // MUST be a local file
  syncUrl: process.env.TURSO_DATABASE_URL!,
  authToken: process.env.TURSO_AUTH_TOKEN!,
  syncInterval: 60,                        // seconds; or call db.sync() manually
});

Rule: an embedded replica's url is always a local file: — syncUrl is the remote. If you set syncUrl next to a libsql:// url you have not built a replica, you have a confused remote client. (verify.sh enforces exactly this.)

In serverless/edge runtimes the import is the same; just confirm the runtime exposes a writable temp path for the file: replica, or fall back to shape #2 (remote-only).

WAL and the pragmas you must set

For any local file or embedded replica connection, set these per-connection on open. They do not apply to a remote-only client (the primary already runs WAL server-side).

PRAGMA journal_mode = WAL;     -- concurrent readers + one writer; default rollback-journal blocks readers during writes
PRAGMA busy_timeout = 5000;    -- wait up to 5s for the write lock instead of erroring SQLITE_BUSY immediately
PRAGMA foreign_keys = ON;      -- FK enforcement is OFF by default and is PER-CONNECTION — set it every time
PRAGMA synchronous = NORMAL;   -- safe with WAL; far fewer fsyncs than the default FULL

journal_mode is persistent once set on the file; busy_timeout, foreign_keys, and synchronous are per-connection — re-issue them on every new connection (and in pooled environments, on checkout).

Writes, batches, transactions

Always parameterize. String-concatenated SQL is an injection hole and defeats statement caching.

// Bad — string interpolation
await db.execute(`INSERT INTO users (email) VALUES ('${email}')`);

// Good — positional ? args (named $name and tuple [sql, args] forms also work)
await db.execute({ sql: "INSERT INTO users (email) VALUES (?)", args: [email] });

batch runs many statements atomically in one transaction — all commit or all roll back.

// mode: "write" (default), "read", or "deferred"
const rs = await db.batch(
  [
    { sql: "INSERT INTO orders (user_id, total) VALUES (?, ?)", args: [userId, total] },
    { sql: "UPDATE users SET order_count = order_count + 1 WHERE id = ?", args: [userId] },
  ],
  "write",
);

Single-writer rule: SQLite allows exactly one writer at a time. Under concurrent writes you get SQLITE_BUSY / "database is locked". The fix is WAL + busy_timeout above, plus keeping write transactions short. If you genuinely need many simultaneous writers, that is the signal to route to postgresdb — do not paper over it with retries forever.

Embedded replica sync

A replica keeps a local copy of the data and pulls changes from the primary. Reads hit the local file; writes are sent to the primary and propagate back on the next sync.

• Periodic: pass syncInterval (seconds) to createClient and the client syncs in the background.
• On-demand: call await db.sync() yourself — it returns { frame_no, frames_synced } (the replication frame you are now at, and how many frames this call pulled).
• readYourWrites (default true) guarantees a connection sees its own writes immediately. Set it to false only when you specifically want a sync's effect to become observable on its own timeline.
• Gotcha: calling db.sync() on a remote-only or pure HTTP/WS client throws LibsqlError with code SYNC_NOT_SUPPORTED. Only embedded-replica clients (those with a local file: + syncUrl) can sync.

Deeper material — sync internals, frame numbers, readYourWrites semantics, offline/bidirectional writes, per-tenant database-per-user patterns, stale-read handling — lives in references/embedded-replicas.md.