Claude Skill

api-baas-turso

Edge-hosted SQLite database with libSQL driver and embedded replicas

LLM Mart · 0 points · 0 0 0

Virus-scanned Reviewed automatically before listing.

Full trust report

Download agents-inc-skills-dist_plugins_api-baas-turso_skills_api-baas-turso-3a51ef5.zip · 14 KB

agents-inc/skills 24 8 forks MIT Updated 23d ago

Part of agents-inc/skills — 130 skills

Install

skills CLI npx skills add https://github.com/agents-inc/skills/tree/main/dist/plugins/api-baas-turso/skills/api-baas-turso

Claude Code claude plugin marketplace add https://llmmart.ai/marketplace.json && claude plugin install agents-inc-skills@llmmart

Git git clone https://github.com/agents-inc/skills.git

The skills CLI installs just this skill, for any of its supported agents. Claude Code installs the whole agents-inc/skills collection as a plugin from our marketplace. Git is the plain clone.

Skill manifest

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

<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 = '$'`)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 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>

Files (skills)

examples

core.md 9.4 KB

# Turso -- Core Examples

> Client setup, execute, batch, transactions, and import path patterns. See [SKILL.md](../SKILL.md) for core concepts.

**Embedded replica patterns:** See [embedded-replicas.md](embedded-replicas.md).

---

## Pattern 1: Remote Client Setup

### Good Example -- Environment-Based Configuration

```typescript
// lib/db.ts
import { createClient } from "@libsql/client";

const client = createClient({
  url: process.env.TURSO_DATABASE_URL!,
  authToken: process.env.TURSO_AUTH_TOKEN,
});

export { client };
```

**Why good:** Credentials from environment variables, named export, single client instance reused across the application

### Bad Example -- Client Per Request

```typescript
// BAD: Creating a new client for every request
export async function getUsers() {
  const client = createClient({
    url: process.env.TURSO_DATABASE_URL!,
    authToken: process.env.TURSO_AUTH_TOKEN,
  });

  const result = await client.execute("SELECT * FROM users");
  // client.close() never called -- resource leak
  return result.rows;
}
```

**Why bad:** Creates a new client (and underlying connection) per call, never closes it, wastes resources and can exhaust connection limits

---

## Pattern 2: Parameterized Queries

### Good Example -- Positional and Named Parameters

```typescript
// Positional parameters with ?
const POST_LIMIT = 20;

async function getRecentPosts(authorId: string) {
  const result = await client.execute({
    sql: "SELECT id, title, created_at FROM posts WHERE author_id = ? ORDER BY created_at DESC LIMIT ?",
    args: [authorId, POST_LIMIT],
  });
  return result.rows;
}

// Named parameters with :prefix
async function createUser(name: string, email: string) {
  const result = await client.execute({
    sql: "INSERT INTO users (name, email) VALUES (:name, :email)",
    args: { name, email },
  });
  return { id: Number(result.lastInsertRowid) };
}
```

**Why good:** Parameterized queries prevent SQL injection, named constant for limit, named parameters are self-documenting, explicit `Number()` conversion for bigint rowid

### Bad Example -- String Concatenation

```typescript
// BAD: Building SQL with string interpolation
async function searchUsers(query: string) {
  return await client.execute(
    `SELECT * FROM users WHERE name LIKE '%${query}%'`,
  );
}
```

**Why bad:** SQL injection vulnerability, user input directly in SQL string, LIKE wildcards not escaped

---

## Pattern 3: Batch Operations

### Good Example -- Atomic Multi-Insert with Audit

```typescript
async function createTeamWithMembers(teamName: string, memberNames: string[]) {
  const statements = [
    {
      sql: "INSERT INTO teams (name) VALUES (?)",
      args: [teamName],
    },
    ...memberNames.map((name) => ({
      sql: "INSERT INTO team_members (team_id, name) VALUES (last_insert_rowid(), ?)",
      args: [name],
    })),
  ];

  const results = await client.batch(statements, "write");
  const teamId = Number(results[0].lastInsertRowid);

  return { teamId, membersCreated: memberNames.length };
}
```

**Why good:** All statements execute atomically in one round trip, `last_insert_rowid()` references the team INSERT, `"write"` mode specified, dynamic statement list from array

### Good Example -- Read-Only Batch for Dashboard Data

```typescript
async function getDashboardData(userId: string) {
  const [userResult, postsResult, statsResult] = await client.batch(
    [
      { sql: "SELECT name, email FROM users WHERE id = ?", args: [userId] },
      {
        sql: "SELECT id, title FROM posts WHERE author_id = ? ORDER BY created_at DESC LIMIT 5",
        args: [userId],
      },
      {
        sql: "SELECT count(*) as total FROM posts WHERE author_id = ?",
        args: [userId],
      },
    ],
    "read",
  );

  return {
    user: userResult.rows[0],
    recentPosts: postsResult.rows,
    totalPosts: statsResult.rows[0].total,
  };
}
```

**Why good:** `"read"` mode allows parallel execution on replicas, single round trip for three queries, destructured results for clarity

---

## Pattern 4: Interactive Transactions

### Good Example -- Conditional Logic Between Queries

```typescript
const MIN_STOCK = 0;

async function purchaseItem(
  userId: string,
  productId: string,
  quantity: number,
) {
  const tx = await client.transaction("write");

  try {
    // Check stock -- result determines next query
    const { rows } = await tx.execute({
      sql: "SELECT stock, price FROM products WHERE id = ?",
      args: [productId],
    });

    if (rows.length === 0) {
      throw new Error("Product not found");
    }

    const stock = rows[0].stock as number;
    const price = rows[0].price as number;

    if (stock - quantity < MIN_STOCK) {
      throw new Error("Insufficient stock");
    }

    const totalCost = price * quantity;

    // These statements depend on the SELECT result above
    await tx.batch([
      {
        sql: "UPDATE products SET stock = stock - ? WHERE id = ?",
        args: [quantity, productId],
      },
      {
        sql: "INSERT INTO orders (user_id, product_id, quantity, total) VALUES (?, ?, ?, ?)",
        args: [userId, productId, quantity, totalCost],
      },
      {
        sql: "UPDATE users SET balance = balance - ? WHERE id = ?",
        args: [totalCost, userId],
      },
    ]);

    await tx.commit();
    return { orderId: "created", total: totalCost };
  } catch (error) {
    await tx.rollback();
    throw error;
  } finally {
    tx.close();
  }
}
```

**Why good:** Interactive transaction because UPDATE amounts depend on SELECT results, `tx.batch()` groups non-dependent writes within the transaction, explicit rollback on error, `tx.close()` in finally block releases the lock, named constant for stock threshold

**When to use:** When subsequent queries depend on results of earlier queries. If all statements are predetermined, use `client.batch()` instead.

---

## Pattern 5: Edge Runtime Client

### Good Example -- Cloudflare Worker

```typescript
import { createClient } from "@libsql/client/web";

interface Env {
  TURSO_DATABASE_URL: string;
  TURSO_AUTH_TOKEN: string;
}

export default {
  async fetch(request: Request, env: Env): Promise<Response> {
    const client = createClient({
      url: env.TURSO_DATABASE_URL,
      authToken: env.TURSO_AUTH_TOKEN,
    });

    try {
      const result = await client.execute(
        "SELECT id, name FROM users LIMIT 10",
      );

      return new Response(JSON.stringify(result.rows), {
        headers: { "Content-Type": "application/json" },
      });
    } finally {
      client.close();
    }
  },
};
```

**Why good:** `@libsql/client/web` import for edge runtime, credentials from environment bindings (not hardcoded), client.close() in finally block, typed Env interface

### Bad Example -- Wrong Import for Edge

```typescript
// BAD: Base import fails in edge runtimes
import { createClient } from "@libsql/client";
// Error: Cannot find module 'libsql' (native binding)

// BAD: Trying file: URL in edge runtime
import { createClient } from "@libsql/client/web";
const client = createClient({ url: "file:local.db" });
// Error: file: URLs not supported in @libsql/client/web
```

**Why bad:** Base import bundles native bindings unavailable in edge runtimes, `@libsql/client/web` cannot open local files

---

## Pattern 6: Row Type Safety

### Good Example -- Typed Query Results

```typescript
interface User {
  id: number;
  name: string;
  email: string;
  created_at: string;
}

async function getUserById(id: string): Promise<User | null> {
  const result = await client.execute({
    sql: "SELECT id, name, email, created_at FROM users WHERE id = ?",
    args: [id],
  });

  if (result.rows.length === 0) {
    return null;
  }

  const row = result.rows[0];
  return {
    id: row.id as number,
    name: row.name as string,
    email: row.email as string,
    created_at: row.created_at as string,
  };
}
```

**Why good:** Explicit type mapping from Row (which returns `Value` for each column) to a typed interface, null check for missing rows, specific column selection (not `SELECT *`)

**When to use:** When you need type-safe access to query results. The libSQL `Row` type returns `Value` (null | string | number | bigint | ArrayBuffer) for each column, so casting is necessary for typed code. If using an ORM, the ORM handles this mapping.

---

## Pattern 7: executeMultiple for Raw SQL Scripts

### Good Example -- Schema Initialization

```typescript
async function initializeSchema() {
  await client.executeMultiple(`
    CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      email TEXT NOT NULL UNIQUE,
      created_at TEXT DEFAULT (datetime('now'))
    );

    CREATE TABLE IF NOT EXISTS posts (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      author_id INTEGER NOT NULL REFERENCES users(id),
      title TEXT NOT NULL,
      content TEXT,
      created_at TEXT DEFAULT (datetime('now'))
    );

    CREATE INDEX IF NOT EXISTS idx_posts_author ON posts(author_id);
  `);
}
```

**Why good:** `executeMultiple` is designed for DDL scripts (semicolon-separated SQL), `IF NOT EXISTS` makes it idempotent, foreign key references defined at table creation (cannot be added later in SQLite)

**When to use:** Schema setup, seed scripts, or running `.sql` dump files. Not for parameterized queries (use `execute` or `batch` instead). Not atomic -- each statement commits independently.

---

_For embedded replica patterns, see [embedded-replicas.md](embedded-replicas.md)._

embedded-replicas.md 7.2 KB

# Turso -- Embedded Replica Examples

> Local SQLite replicas synced from a remote Turso primary. See [SKILL.md](../SKILL.md) for core concepts.

**Prerequisites:** Understand client setup and execute/batch patterns from [core.md](core.md) first.

---

## Pattern 1: Basic Embedded Replica Setup

### Good Example -- Auto-Syncing Replica

```typescript
import { createClient } from "@libsql/client";

const SYNC_INTERVAL_SECONDS = 60;

const client = createClient({
  url: "file:data/replica.db",
  syncUrl: process.env.TURSO_DATABASE_URL,
  authToken: process.env.TURSO_AUTH_TOKEN,
  syncInterval: SYNC_INTERVAL_SECONDS,
});

// Populate local replica on startup
await client.sync();

// All reads are local -- microsecond latency
const users = await client.execute("SELECT * FROM users LIMIT 10");

// Writes forward to remote primary (15-50ms)
// Local replica auto-updates after write returns
await client.execute({
  sql: "INSERT INTO users (name) VALUES (?)",
  args: ["Alice"],
});
```

**Why good:** Named constant for sync interval, initial `sync()` ensures data is available before first read, local file path for replica, credentials from env vars

### Bad Example -- No Initial Sync

```typescript
const client = createClient({
  url: "file:replica.db",
  syncUrl: process.env.TURSO_DATABASE_URL,
  authToken: process.env.TURSO_AUTH_TOKEN,
});

// BAD: Reading before any sync -- replica may be empty or stale
const users = await client.execute("SELECT * FROM users");
// Returns empty results on first run, stale data on subsequent runs
```

**Why bad:** Without an initial `sync()` or `syncInterval`, the local replica may be empty (first run) or arbitrarily stale (subsequent runs with no sync)

---

## Pattern 2: Manual Sync Control

### Good Example -- Sync Before Critical Reads

```typescript
import { createClient } from "@libsql/client";

// No syncInterval -- manual sync only
const client = createClient({
  url: "file:data/replica.db",
  syncUrl: process.env.TURSO_DATABASE_URL,
  authToken: process.env.TURSO_AUTH_TOKEN,
});

// Sync before reads that need fresh data
async function getFreshUserCount(): Promise<number> {
  await client.sync();
  const result = await client.execute("SELECT count(*) as total FROM users");
  return result.rows[0].total as number;
}

// Skip sync for reads that tolerate staleness
async function getCachedPosts() {
  // No sync -- reads from whatever the local replica has
  return await client.execute(
    "SELECT id, title FROM posts ORDER BY created_at DESC LIMIT 10",
  );
}
```

**Why good:** Manual sync gives control over when to pay the sync cost, fresh reads sync first, stale-tolerant reads skip sync, no background sync interval consuming resources

**When to use:** When you need fine-grained control over sync timing. Useful when some reads must be fresh and others can tolerate staleness.

---

## Pattern 3: Offline Mode

### Good Example -- Local-Only Writes

```typescript
import { createClient } from "@libsql/client";

const SYNC_INTERVAL_SECONDS = 300; // 5 minutes

const client = createClient({
  url: "file:data/offline.db",
  syncUrl: process.env.TURSO_DATABASE_URL,
  authToken: process.env.TURSO_AUTH_TOKEN,
  syncInterval: SYNC_INTERVAL_SECONDS,
  offline: true, // Writes go to local database, not forwarded to remote
});

// Writes are local-only -- no network latency
await client.execute({
  sql: "INSERT INTO events (type, data) VALUES (?, ?)",
  args: ["page_view", JSON.stringify({ url: "/home" })],
});

// Sync pushes local writes to remote when called
await client.sync();
```

**Why good:** `offline: true` enables local writes for scenarios like event logging, field data collection, or unreliable connectivity, named constant for sync interval, explicit sync to push changes

**When to use:** Applications that must work without connectivity (mobile apps, field devices, intermittent networks). Local writes accumulate and sync when connectivity is available.

---

## Pattern 4: Encryption at Rest

### Good Example -- Encrypted Local Replica

```typescript
import { createClient } from "@libsql/client";

const SYNC_INTERVAL_SECONDS = 120;

const client = createClient({
  url: "file:data/encrypted-replica.db",
  syncUrl: process.env.TURSO_DATABASE_URL,
  authToken: process.env.TURSO_AUTH_TOKEN,
  syncInterval: SYNC_INTERVAL_SECONDS,
  encryptionKey: process.env.TURSO_ENCRYPTION_KEY, // You generate and manage this key
});

await client.sync();
```

**Why good:** Encryption key from environment variable (not hardcoded), protects data at rest on the local filesystem, transparent to queries

**When to use:** When the local replica stores sensitive data and the filesystem is shared or the device could be compromised. You are responsible for generating, storing, and rotating the encryption key.

---

## Pattern 5: Read-Your-Writes Semantics

### Good Example -- Write Then Read Consistency

```typescript
// With embedded replicas, the client that performed a write
// always sees that write immediately -- no need to sync

async function createAndVerifyUser(name: string, email: string) {
  // Write forwards to remote primary
  const insertResult = await client.execute({
    sql: "INSERT INTO users (name, email) VALUES (?, ?)",
    args: [name, email],
  });

  const newId = Number(insertResult.lastInsertRowid);

  // Read-your-writes: this read sees the new user immediately
  // even though it reads from the local replica
  const verifyResult = await client.execute({
    sql: "SELECT id, name, email FROM users WHERE id = ?",
    args: [newId],
  });

  // verifyResult.rows[0] will contain the newly inserted user
  return verifyResult.rows[0];
}
```

**Why good:** Demonstrates that the writing client sees its own writes immediately without calling `sync()`, no stale-read risk for the writer, natural programming model

#### Important Caveat

Read-your-writes is guaranteed only for the client that performed the write. Other clients (on other machines or in other processes) will only see the write after their next `sync()` call or `syncInterval` tick. This is eventual consistency for readers other than the writer.

---

## Pattern 6: Graceful Startup with Fallback

### Good Example -- Handle First-Run Sync Failure

```typescript
import { createClient } from "@libsql/client";

const SYNC_INTERVAL_SECONDS = 60;

async function createReplicaClient() {
  const client = createClient({
    url: "file:data/replica.db",
    syncUrl: process.env.TURSO_DATABASE_URL,
    authToken: process.env.TURSO_AUTH_TOKEN,
    syncInterval: SYNC_INTERVAL_SECONDS,
  });

  try {
    await client.sync();
  } catch (error) {
    // First sync failed -- local file may be empty or stale
    // Log but don't crash: the replica will sync on next interval
    console.error("Initial sync failed, will retry on interval:", error);
  }

  return client;
}
```

**Why good:** First sync failure does not crash the application, subsequent `syncInterval` ticks will retry, useful for deployment scenarios where remote may be temporarily unreachable

**When to use:** Production services where a transient network issue during startup should not prevent the service from starting. The service starts with stale data and catches up on next successful sync.

---

_For client setup and query patterns, see [core.md](core.md)._

reference.md 7.6 KB

# Turso Reference

> Quick lookup tables, CLI commands, and type definitions. See [SKILL.md](SKILL.md) for core concepts and [examples/](examples/) for code examples.

---

## Client Interface (v0.17+)

```typescript
interface Client {
  execute(stmt: InStatement): Promise<ResultSet>;
  execute(sql: string, args?: InArgs): Promise<ResultSet>;
  batch(
    stmts: Array<InStatement | [string, InArgs?]>,
    mode?: TransactionMode,
  ): Promise<Array<ResultSet>>;
  transaction(mode?: TransactionMode): Promise<Transaction>;
  migrate(stmts: Array<InStatement>): Promise<Array<ResultSet>>;
  executeMultiple(sql: string): Promise<void>;
  sync(): Promise<Replicated>;
  close(): void;
  reconnect(): void;
  closed: boolean;
  protocol: string;
}
```

`mode` defaults to `"deferred"` for both `batch()` and `transaction()`. Always specify explicitly to avoid silent write failures.

---

## Core Types

```typescript
type InStatement = { sql: string; args?: InArgs } | string;
type InArgs = Array<InValue> | Record<string, InValue>;
type InValue = Value | boolean | Uint8Array | Date;
type Value = null | string | number | bigint | ArrayBuffer;
type TransactionMode = "write" | "read" | "deferred";
type IntMode = "number" | "bigint" | "string";
type Replicated = { frame_no: number; frames_synced: number } | undefined;
```

---

## ResultSet

| Property          | Type                  | Description                       |
| ----------------- | --------------------- | --------------------------------- |
| `rows`            | `Array<Row>`          | Result rows (empty for writes)    |
| `columns`         | `Array<string>`       | Column names in result order      |
| `columnTypes`     | `Array<string>`       | SQLite type names per column      |
| `rowsAffected`    | `number`              | Rows modified by write statements |
| `lastInsertRowid` | `bigint \| undefined` | Rowid of last inserted row        |

`Row` supports both index access (`row[0]`) and column-name access (`row.name`).

---

## createClient Config

| Option           | Type        | Default    | Description                                                           |
| ---------------- | ----------- | ---------- | --------------------------------------------------------------------- |
| `url`            | `string`    | (required) | Connection URL: `libsql://`, `file:`, `:memory:`                      |
| `authToken`      | `string?`   | —          | Auth token for remote databases                                       |
| `syncUrl`        | `string?`   | —          | Remote URL for embedded replica sync                                  |
| `syncInterval`   | `number?`   | —          | Auto-sync interval in seconds                                         |
| `readYourWrites` | `boolean?`  | `true`     | Apply writes to local replica immediately after remote write succeeds |
| `offline`        | `boolean?`  | `false`    | Enable local-only writes (embedded replicas)                          |
| `encryptionKey`  | `string?`   | —          | Encryption key for local SQLite file                                  |
| `intMode`        | `IntMode?`  | `"number"` | How to return SQLite integers                                         |
| `concurrency`    | `number?`   | `20`       | Max concurrent requests (`undefined` to disable)                      |
| `tls`            | `boolean?`  | `true`     | Enable TLS for remote connections                                     |
| `fetch`          | `Function?` | —          | Custom fetch implementation                                           |

---

## Transaction Modes

| Mode         | SQLite Equivalent | Behavior                                                           |
| ------------ | ----------------- | ------------------------------------------------------------------ |
| `"write"`    | `BEGIN IMMEDIATE` | Acquires write lock immediately; required for INSERT/UPDATE/DELETE |
| `"read"`     | `BEGIN READONLY`  | Read-only; can execute on replicas in parallel                     |
| `"deferred"` | `BEGIN DEFERRED`  | Starts read-only, escalates to write on first write statement      |

---

## URL Schemes

| Scheme                        | Runtime      | Use Case                              |
| ----------------------------- | ------------ | ------------------------------------- |
| `libsql://host.turso.io`      | Any          | Remote Turso database (WebSocket)     |
| `https://host.turso.io`       | Any          | Remote Turso database (HTTP)          |
| `file:path/to/db.db`          | Node.js only | Local SQLite file                     |
| `file:replica.db` + `syncUrl` | Node.js only | Embedded replica                      |
| `:memory:`                    | Any          | In-memory database (tests, ephemeral) |

---

## Turso CLI Quick Reference

```bash
# Install
curl -sSfL https://get.tur.so/install.sh | bash

# Authenticate
turso auth login

# Database management
turso db create <name>                     # Create database (auto-selects closest region)
turso db create <name> --group <group>     # Create in a specific group
turso db list                              # List all databases
turso db show <name>                       # Show database details
turso db show <name> --url                 # Get connection URL
turso db shell <name>                      # Open interactive SQL shell
turso db destroy <name>                    # Delete database

# Group management (multi-region)
turso group create <name>                  # Create group (auto-selects primary region)
turso group create <name> --location iad   # Create with explicit primary
turso group locations add <group> <loc>    # Add replica location
turso group locations remove <group> <loc> # Remove replica location
turso group list                           # List groups

# Tokens
turso db tokens create <name>              # Create auth token for a database
turso db tokens create <name> --expiration 7d  # Token with expiration
```

---

## Environment Variables

```bash
# Application
TURSO_DATABASE_URL=libsql://my-database-my-org.turso.io
TURSO_AUTH_TOKEN=eyJhbGciOiJFZERTQSIsInR5cCI6IkpXVCJ9...

# Embedded replica (in addition to above)
TURSO_SYNC_URL=libsql://my-database-my-org.turso.io  # Same as DATABASE_URL for sync target
```

---

## Parameter Syntax

| SQL Syntax       | Args Format | Example                   |
| ---------------- | ----------- | ------------------------- |
| `?` (positional) | `Array`     | `args: [1, "Alice"]`      |
| `:name`          | `Record`    | `args: { name: "Alice" }` |
| `@name`          | `Record`    | `args: { name: "Alice" }` |
| `$name`          | `Record`    | `args: { name: "Alice" }` |

Named parameter args use **bare names** (no prefix): `{ name: "Alice" }` matches all three prefixes.

---

## IntMode Behavior

| Mode                 | Returns  | Precision   | Use When                                  |
| -------------------- | -------- | ----------- | ----------------------------------------- |
| `"number"` (default) | `number` | Up to 2^53  | Most applications; JavaScript-native      |
| `"bigint"`           | `bigint` | Full 64-bit | Large IDs, counters, precise arithmetic   |
| `"string"`           | `string` | Full 64-bit | Universal safety; easy JSON serialization |

---

## Write Latency by Architecture

| Setup                    | Read Latency            | Write Latency                  |
| ------------------------ | ----------------------- | ------------------------------ |
| Embedded replica (local) | ~0.001ms (microseconds) | 15-50ms (forwarded to primary) |
| Remote (same region)     | 1-5ms                   | 1-5ms                          |
| Remote (cross-region)    | 5-50ms                  | 15-50ms (always to primary)    |

SKILL.md 16.5 KB

---
name: api-baas-turso
description: Edge-hosted SQLite database with libSQL driver and embedded replicas
---

# 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.

```typescript
// 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.

```typescript
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()`.

```typescript
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

```typescript
// 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](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).

```typescript
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](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](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 with`args`.
- **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>