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.

libSQL today vs Turso Database (Rust) tomorrow

Two things wear the "Turso" name; do not confuse them.

• libSQL is the SQLite fork and the battle-tested foundation. For production today, use libSQL via @libsql/client. This is the official guidance — mission-critical workloads run here.
• Turso Database is a from-scratch rewrite of SQLite in Rust (formerly "Limbo"), adding MVCC concurrent writes and bidirectional offline sync. It is the future direction and where new features land, but it is not the default production target yet.

Separately: "edge replicas" are being discontinued for new users (data showed ~70% never used them), along with multi-DB schemas and ATTACH. Existing paid customers keep them. This is not the same as embedded replicas — embedded replicas remain the flagship. Do not design a new account around edge replicas.

Turso/libSQL also has native vector search (DiskANN). For a dedicated vector store, route to vector-db regardless.

Operate — the turso CLI

turso db create my-app                          # create a database
turso db show my-app --url                       # get the libsql:// URL for TURSO_DATABASE_URL
turso db tokens create my-app                    # mint an auth token for TURSO_AUTH_TOKEN
turso db shell my-app                            # interactive SQL shell against the primary

Pricing note: the free tier offers unlimited databases (post "Database Freedom Day", mid-2025), usage-based with generous read/storage allowances — exact GB and row-read numbers shift, so verify on turso.tech/pricing before quoting a figure. This is what makes database-per-user economical.

Route out: backups/restore strategy → backups; versioned migrations → db-migrations; type-safe schema and queries → drizzle-orm; generic SQL authoring (window functions, tuning) → sql.

Anti-patterns

Verify

When you emit a connection/config file, gate it:

bash scripts/verify.sh path/to/db.ts        # or a directory; defaults to cwd

It statically checks (no network): @libsql/client import + createClient use; that a syncUrl always sits next to a local file: url; no hardcoded token literal; and (advisory) WAL pragma presence on file-backed clients. Read-only; exits 0 on a clean/empty target.