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](examples/core.md) -- Client setup, execute, batch, transactions, import paths
• [examples/embedded-replicas.md](examples/embedded-replicas.md) -- Local replicas, sync, offline mode, encryption
• [reference.md](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](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](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](examples/core.md) for multi-insert and read-only batch patterns, and [reference.md](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](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 filesyste