agents-inc/api-baas-turso

Turso / libSQL Patterns

Quick Guide: Use @libsql/client for all Turso database access. Use execute() for single queries, batch() for atomic multi-statement operations (preferred over interactive transactions), and transaction() only when subsequent queries depend on prior results. For edge/serverless runtimes without filesystem access, import from @libsql/client/web. For zero-latency reads, configure embedded replicas with a local file URL + syncUrl. All writes are forwarded to the primary -- design for 15-50ms write latency. Turso is SQLite under the hood: single-writer model, no ALTER TABLE ... ADD CONSTRAINT, no stored procedures.

---

<critical_requirements>

CRITICAL: Before Using This Skill

All code must follow project conventions in CLAUDE.md (kebab-case, named exports, import ordering, import type, named constants)

(You MUST use batch() with a transaction mode for multi-statement atomic operations -- it is faster and safer than interactive transaction() because it executes in a single round trip)

(You MUST import from @libsql/client/web in edge/serverless runtimes that lack filesystem access (Cloudflare Workers, Vercel Edge Functions) -- the base @libsql/client import pulls in native bindings that fail in these environments)

(You MUST specify a transaction mode ("write", "read", or "deferred") as the second argument to batch() and transaction() -- the default is "deferred", which silently fails to acquire a write lock for INSERT/UPDATE/DELETE)

(You MUST call client.close() when the client is no longer needed in short-lived processes -- open clients hold connections and file handles)

(You MUST NOT access the local embedded replica database file directly while the client is running -- concurrent access causes data corruption)

</critical_requirements>

---

Auto-detection: Turso, libSQL, @libsql/client, createClient, turso.io, embedded replica, syncUrl, syncInterval, turso db, turso group, libsql, .turso.io, TURSO_DATABASE_URL, TURSO_AUTH_TOKEN

When to use:

• Querying a Turso-hosted SQLite database from any runtime (Node.js, edge, serverless)
• Setting up embedded replicas for zero-latency local reads synced from a remote primary
• Multi-tenant SaaS with database-per-tenant (Turso supports millions of databases)
• Serverless/edge functions needing a database without connection pooling complexity
• Running atomic multi-statement operations with batch() or interactive transaction()
• Managing database groups and multi-region placement via the Turso CLI

When NOT to use:

• Write-heavy workloads requiring strong multi-writer consistency (Turso is single-writer, writes forwarded to primary)
• Complex relational queries needing PostgreSQL features (CTEs with mutating subqueries, stored procedures, advanced constraints)
• Complex distributed transactions across multiple databases
• Large analytical datasets (SQLite row-size and concurrency limitations apply)

Detailed Resources:

• examples/core.md -- Client setup, execute, batch, transactions, import paths
• examples/embedded-replicas.md -- Local replicas, sync, offline mode, encryption
• reference.md -- Decision frameworks, type definitions, CLI commands, lookup tables

---

<philosophy>

Philosophy

Turso brings SQLite to the edge by hosting libSQL (a fork of SQLite) as a managed service with multi-region replication. The @libsql/client driver provides a unified API that works identically whether you are connecting to a remote Turso database, a local SQLite file, an in-memory database, or an embedded replica that syncs from a remote primary.

Core principles:

1. Batch over transaction -- batch() sends all statements in a single round trip and executes them in an implicit transaction. Interactive transaction() requires multiple round trips and holds a database lock (5-second timeout). Use batch() unless you need conditional logic between queries.
2. Writes always hit the primary -- Even with embedded replicas, writes are forwarded to the remote primary database. Write latency is 15-50ms depending on distance to the primary region. Design for this: optimistic UI, background sync, avoid write-heavy hot paths.
3. Embedded replicas for reads -- A local SQLite file synced from the remote primary. Reads are microsecond-level. Writes forward to remote. The local file updates after a successful write (read-your-writes semantics).
4. Two import paths -- @libsql/client includes native SQLite bindings for Node.js and supports file: URLs. @libsql/client/web is pure JS/WASM for edge runtimes (Cloudflare Workers, Vercel Edge Functions) and cannot open local files.
5. SQLite semantics -- Turso is SQLite. No ADD CONSTRAINT, no stored procedures, no LISTEN/NOTIFY, single-writer WAL mode. Know SQLite's limitations before choosing Turso.

</philosophy>

---

<patterns>

Core Patterns

Pattern 1: Client Setup

Create a client with createClient(). The url determines the connection type: libsql:// for remote Turso, file: for local SQLite (Node.js only), :memory: for in-memory (tests). Always use environment variables for authToken -- never hardcode credentials.

See examples/core.md for full setup patterns including singleton modules and bad examples.

---

Pattern 2: Executing Queries

execute() runs a single SQL statement. Always use parameterized queries with args -- never string interpolation.

// Positional: args as array
await client.execute({
  sql: "SELECT * FROM users WHERE id = ?",
  args: [userId],
});

// Named: args as object (bare names match :name, @name, $name in SQL)
await client.execute({
  sql: "INSERT INTO users (name, email) VALUES (:name, :email)",
  args: { name, email },
});

Returns ResultSet with rows (Array<Row>), columns, rowsAffected, lastInsertRowid (bigint). See examples/core.md for typed result mapping and bad examples.

---

Pattern 3: Batch Operations

batch() executes multiple statements atomically in a single round trip. All succeed or all roll back. Always specify the transaction mode as the second argument.

const results = await client.batch(
  [
    { sql: "INSERT INTO users (name) VALUES (?)", args: ["Alice"] },
    {
      sql: "INSERT INTO audit_log (action, entity_id) VALUES (?, last_insert_rowid())",
      args: ["user_created"],
    },
  ],
  "write", // Required for INSERT/UPDATE/DELETE
);

Use "write" for mutations, "read" for SELECT-only (allows parallel execution), "deferred" to start read-only and escalate. last_insert_rowid() works across statements in the same batch. See examples/core.md for multi-insert and read-only batch patterns, and reference.md for the transaction mode comparison table.

---

Pattern 4: Interactive Transactions

Use transaction() only when subsequent queries depend on results of earlier queries. It holds a database lock (5-second idle timeout) and requires multiple round trips. Always use try/catch/finally with close().

const tx = await client.transaction("write");
try {
  const { rows } = await tx.execute({
    sql: "SELECT balance FROM accounts WHERE id = ?",
    args: [fromId],
  });
  // ... conditional logic based on results ...
  await tx.commit();
} catch (error) {
  await tx.rollback();
  throw error;
} finally {
  tx.close();
}

If all statements are known upfront with no conditional logic, use batch() instead. See examples/core.md for a complete purchase-with-stock-check example.

---

Pattern 5: Import Paths for Different Runtimes

// Node.js, Bun, Deno (has filesystem access, native bindings)
import { createClient } from "@libsql/client";

// Edge/serverless runtimes WITHOUT filesystem (Cloudflare Workers, Vercel Edge)
import { createClient } from "@libsql/client/web";

The base @libsql/client bundles native SQLite bindings that fail in edge runtimes. @libsql/client/web is pure JS/WASM but cannot open local file: URLs. See examples/core.md for a full Cloudflare Worker example.

---

Pattern 6: Embedded Replicas

A local SQLite file that syncs from a remote Turso primary. Reads are local (microseconds), writes forward to remote (15-50ms).

const client = createClient({
  url: "file:local-replica.db",
  syncUrl: process.env.TURSO_DATABASE_URL,
  authToken: process.env.TURSO_AUTH_TOKEN,
  syncInterval: 60, // Auto-sync every 60 seconds
});
await client.sync(); // Populate local replica before first read

Key points: file: URL for local replica, syncUrl for remote primary, call sync() on startup, reads are local, writes forward to remote with read-your-writes semantics.

When to use: VMs, VPS, containers, or any long-running process with filesystem access. Not available in serverless/edge runtimes without filesystem. See examples/embedded-replicas.md for full patterns including manual sync, offline mode, and encryption.

---

Pattern 7: Database Groups and Multi-Region

Turso organizes databases into groups. Each group has a primary region and optional replica locations. All databases in a group inherit its locations.

All writes go to the primary region regardless of which replica handles the read. Adding more locations improves read latency globally but does not reduce write latency. Write latency is determined by distance to the primary region.

See reference.md for the full Turso CLI command reference (turso db create, turso group create, turso db shell, etc.).

</patterns>

---

<decision_framework>

Decision Framework

batch() vs transaction()

Are all SQL statements known upfront (no conditional logic between them)?
+-- YES --> Use batch() (single round trip, implicit transaction, preferred)
+-- NO  --> Do later statements depend on results of earlier statements?
    +-- YES --> Use transaction() (interactive, multiple round trips, holds lock)
    +-- NO  --> Use batch()

Import Path Selection

What runtime environment?
+-- Node.js / Bun / Deno / VM / container
|   +-- Need embedded replicas (local file)?
|   |   +-- YES --> @libsql/client with file: URL + syncUrl
|   |   +-- NO  --> @libsql/client with libsql:// URL
+-- Cloudflare Workers / Vercel Edge / browser / serverless without filesystem
    +-- @libsql/client/web (remote connections only, no file: URLs)

Embedded Replica vs Remote-Only

Is the process long-lived with filesystem access?
+-- YES --> Do you need sub-millisecond read latency?
|   +-- YES --> Embedded replica (file: URL + syncUrl)
|   +-- NO  --> Remote-only is simpler (libsql:// URL)
+-- NO (serverless, edge, short-lived)
    +-- Remote-only (@libsql/client/web, libsql:// URL)

Transaction Mode Selection

What operations will the batch/transaction perform?
+-- Only SELECT queries --> "read" (allows parallel execution on replicas)
+-- Any INSERT / UPDATE / DELETE --> "write" (acquires exclusive lock)
+-- Unsure at call time --> "deferred" (starts read, escalates if needed)

</decision_framework>

---

<red_flags>

RED FLAGS

High Priority Issues:

• Missing transaction mode in batch()/transaction() -- Omitting the second argument defaults to "deferred", which starts read-only and may silently fail to acquire a write lock for INSERT/UPDATE/DELETE. Always specify the mode explicitly.
• Using @libsql/client in edge runtimes -- The base package bundles native SQLite bindings that fail in Cloudflare Workers, Vercel Edge Functions, and similar environments. Use @libsql/client/web instead.
• String interpolation in SQL -- execute(\SELECT * FROM users WHERE id = '${id}'`)is a SQL injection vulnerability. Always use parameterized queries withargs`.
• Accessing embedded replica file directly -- Opening the local .db file with another SQLite client while the libSQL client is running causes data corruption. Only access through the client.

Medium Priority Issues:

• Using transaction() when batch() suffices -- Interactive transactions hold a database lock with a 5-second timeout, require multiple round trips, and block other writers. Use batch() for predetermined statement sets.
• Not calling client.close() -- In short-lived processes (CLI scripts, test teardown), forgetting to close the client leaves connections and file handles open.
• Ignoring write latency with embedded replicas -- Reads are microseconds (local), but writes are 15-50ms (forwarded to remote primary). Design accordingly -- avoid tight write loops.
• Setting syncInterval too low -- Each sync pulls all changed frames (4KB each). Sub-second intervals on write-heavy databases generate significant network and I/O overhead.

Common Mistakes:

• Wrong package name -- The package is @libsql/client, not libsql-client, @turso/client, or turso-client.
• Named parameter prefix in args object -- Args use bare names: { name: "Alice" } matches :name, @name, and $name in SQL. Do not include the prefix: { ":name": "Alice" } will not match.
• Expecting lastInsertRowid to be a number -- It is bigint | undefined. If you need a number, explicitly convert: Number(result.lastInsertRowid), but be aware of precision loss for very large rowids.
• Using executeMultiple() for atomic operations -- executeMultiple() runs raw SQL text (semicolon-separated) with no parameterization and no implicit transaction. Use batch() for atomic parameterized operations.

Gotchas & Edge Cases:

• batch() statements share a transaction but are NOT parallel -- They execute sequentially. last_insert_rowid() in a later statement reflects the previous statement's insert.
• transaction() has a 5-second idle timeout -- If no statement is executed within 5 seconds after the last one, the transaction is automatically rolled back. This matters on high-latency connections.
• Embedded replica sync() is not atomic with reads -- If you read immediately after sync(), another sync could start. The client handles this internally, but be aware that syncInterval syncs happen in the background.
• Frame-based sync overhead -- Embedded replica sync operates in 4KB frames. A 1-byte write still transfers a full 4KB frame. B-tree splits and WAL checkpoint operations can trigger unexpectedly large sync payloads.
• intMode affects how SQLite integers are returned -- Default is "number", which loses precision for integers > 2^53. Use "bigint" for large IDs or counters, or "string" for universal safety.
• SQLite type affinity -- Turso is SQLite. A TEXT column will happily store an integer without error. There is no strict type enforcement unless you use STRICT tables.
• No ALTER TABLE ... ADD CONSTRAINT -- SQLite (and Turso) do not support adding constraints after table creation. You must recreate the table.
• Single-writer model -- Only one write transaction can execute at a time across all clients. Concurrent write attempts queue behind the current writer. This is fundamental to SQLite/libSQL, not a Turso limitation.

</red_flags>

---

<critical_reminders>

CRITICAL REMINDERS

All code must follow project conventions in CLAUDE.md (kebab-case, named exports, import ordering, import type, named constants)

(You MUST use batch() with a transaction mode for multi-statement atomic operations -- it is faster and safer than interactive transaction() because it executes in a single round trip)

(You MUST import from @libsql/client/web in edge/serverless runtimes that lack filesystem access (Cloudflare Workers, Vercel Edge Functions) -- the base @libsql/client import pulls in native bindings that fail in these environments)

(You MUST specify a transaction mode ("write", "read", or "deferred") as the second argument to batch() and transaction() -- the default is "deferred", which silently fails to acquire a write lock for INSERT/UPDATE/DELETE)

(You MUST call client.close() when the client is no longer needed in short-lived processes -- open clients hold connections and file handles)

(You MUST NOT access the local embedded replica database file directly while the client is running -- concurrent access causes data corruption)

Failure to follow these rules will cause data corruption, runtime crashes in edge environments, or silent data inconsistency.

</critical_reminders>