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. | Workload | Verdict | Why / route | |---|---|---| | Read-heavy, single-tenant, low-to-moderate writes | **SQLite/Turso** | One writer is plenty; reads are local and fast. | | Local-first / offline / edge app needing cloud durability | **Turso embedded replica** | Local file for reads, syncs to a remote primary. | | Database-per-user / per-tenant at scale | **Turso** | Unlimited databases on the free tier; cheap isolation. | | Many concurrent writers, complex roles, extensions | No → `postgresdb` (or `mysql`) | SQLite serializes writers; one writer at a time. | | Serverless Postgres with branching | No → `neon` | Turso is serverless *SQLite*, not Postgres. | | MySQL-compatible serverless | No → `planetscale` | Different engine and protocol. | | Auth + realtime + storage + Postgres bundled | No → `supabase` | You want a BaaS, not a raw SQL engine. | | Columnar OLAP / analytical aggregates | No → `duckdb` | SQLite/Turso is OLTP, row-oriented. | | Ephemeral cache / KV | No → `redis` | Not a durable relational store. | ## Three deployment shapes Every Turso/libSQL decision reduces to which of these three you are wiring. The URL scheme picks the transport automatically. | Shape | `createClient` config | Latency | When | |---|---|---|---| | **Local file** | `url: "file:local.db"` | In-process | Dev, CLI tools, single-node app, tests. | | **Remote edge DB** | `url: "libsql://<db>.turso.io"` + `authToken` | Network round-trip | Serverless/stateless functions; no local disk to sync. | | **Embedded replica** | `url: "file:local.db"` + `syncUrl` + `authToken` | Microsecond reads | Local-first/edge with cloud durability — the flagship. | `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`. ```ts // 1. Local file — dev, tests, single node import { createClient } from "@libsql/client"; const db = createClient({ url: "file:local.db" }); ``` ```ts // 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!, }); ``` ```ts // 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). ```sql 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. ```ts // 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. ```ts // 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](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 ```bash 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 | Anti-pattern | Why it bites | Do instead | |---|---|---| | Treating SQLite like multi-writer Postgres | One writer at a time → `SQLITE_BUSY` storms under load | Keep writes serial + short, or route to `postgresdb` | | Forgetting `PRAGMA journal_mode=WAL` | Default journal blocks readers during every write | Set WAL on local file / replica connections | | Embedded replica with a `libsql://` url | Not a replica — a confused remote client; reads aren't local | `url:` is a local `file:`, remote goes in `syncUrl` | | Hardcoding the auth token in source | Leaks a credential into VCS | Read `process.env.TURSO_AUTH_TOKEN` | | Calling `db.sync()` on a remote/HTTP client | Throws `LibsqlError` `SYNC_NOT_SUPPORTED` | Only sync embedded-replica clients | | Assuming FK constraints are enforced | `foreign_keys` is OFF by default, per-connection | `PRAGMA foreign_keys=ON` on every connection | | Designing a new account around edge replicas | Discontinued for new users | Use embedded replicas (the flagship) | | String-concatenated SQL | Injection + no statement caching | Parameterize with `?` / `$name` / tuple args | | Reaching for Turso for OLAP dashboards | Row-store, single writer — wrong shape | Use `duckdb` for columnar analytics | | Layering migrations/ORM logic into this skill | Out of scope; duplicates sibling rigor | `db-migrations` / `drizzle-orm` | ## Verify When you emit a connection/config file, gate it: ```bash 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.