Turso & libSQL Best Practices

Reference Files

| File | When to read | |------|-------------| | references/advanced-features.md | Working with embedded replicas, vector search/AI embeddings, Drizzle ORM, database branching, encryption at rest, or SQLite extensions |

---

Overview

Turso is a fully managed SQLite-compatible database platform built on libSQL, a fork of SQLite. It provides edge distribution, embedded replicas, native vector search, branching, and point-in-time recovery. Core principle: SQLite simplicity with cloud-scale distribution.

When to Use

• Building applications needing SQLite with cloud features
• Implementing embedded replicas for offline-first apps
• Adding vector search/AI embeddings to applications
• Setting up local development with Turso
• Managing database migrations and branching

Quick Reference

| Task | Command/Pattern | | --------------------- | --------------------------------------------------------------------------- | | Install CLI (macOS) | brew install tursodatabase/tap/turso | | Install CLI (Linux) | curl -sSfL https://get.tur.so/install.sh \| bash | | Login | turso auth login | | Create database | turso db create my-db | | Connect to shell | turso db shell my-db | | Get credentials | turso db show my-db --url and turso db tokens create my-db | | Local dev server | turso dev | | Local with file | turso dev --db-file local.db |

Installation & Setup

CLI Installation

# macOS
brew install tursodatabase/tap/turso

# Linux / Windows (WSL)
curl -sSfL https://get.tur.so/install.sh | bash

Authentication

turso auth signup     # Sign up (opens browser)
turso auth login      # Login (opens browser)
turso auth login --headless  # Headless mode (WSL/CI)

Create Your First Database

turso db create my-db          # Auto-detects closest region
turso db show my-db            # Show database info
turso db show my-db --url      # Get connection URL
turso db tokens create my-db   # Create auth token
turso db shell my-db           # Connect to shell

SDK Usage (TypeScript/JavaScript)

Installation

npm install @libsql/client

Basic Connection

import { createClient } from '@libsql/client'

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

Execute Queries

// Simple query
const result = await client.execute('SELECT * FROM users')

// Positional placeholders
const result = await client.execute({
  sql: 'SELECT * FROM users WHERE id = ?',
  args: [1],
})

// Named placeholders (:, @, or $)
const result = await client.execute({
  sql: 'INSERT INTO users (name, email) VALUES (:name, :email)',
  args: { name: 'Alice', email: 'alice@example.com' },
})

Response Structure

interface ResultSet {
  rows: Array<Row>         // Row data (empty for writes)
  columns: Array<string>   // Column names
  rowsAffected: number     // Affected rows (writes)
  lastInsertRowid?: bigint // Last inserted row ID
}

Batch Transactions

const results = await client.batch(
  [
    { sql: 'INSERT INTO users (name) VALUES (?)', args: ['Alice'] },
    { sql: 'INSERT INTO users (name) VALUES (?)', args: ['Bob'] },
  ],
  'write' // Transaction mode: "write" | "read" | "deferred"
)

Interactive Transactions

const transaction = await client.transaction('write')

try {
  const balance = await transaction.execute({
    sql: 'SELECT balance FROM accounts WHERE id = ?',
    args: [userId],
  })

  if (balance.rows[0].balance >= amount) {
    await transaction.execute({
      sql: 'UPDATE accounts SET balance = balance - ? WHERE id = ?',
      args: [amount, userId],
    })
    await transaction.commit()
  } else {
    await transaction.rollback()
  }
} catch (e) {
  await transaction.rollback()
  throw e
}

Transaction Modes

| Mode | SQLite Command | Description | | ---------- | ---------------------------- | ------------------------------------------------ | | write | BEGIN IMMEDIATE | Read/write, serialized on primary | | read | BEGIN TRANSACTION READONLY | Read-only, can run on replicas in parallel | | deferred | BEGIN DEFERRED | Starts as read, upgrades to write on first write |

Local Development

Option 1: SQLite File (Simplest)

const client = createClient({ url: 'file:local.db' })

Option 2: Turso Dev Server (Full Features)

turso dev                    # Start local libSQL server
turso dev --db-file local.db # With persistent file

const client = createClient({ url: 'http://127.0.0.1:8080' })

Environment Variables Pattern

# Production
TURSO_DATABASE_URL=libsql://my-db-org.turso.io
TURSO_AUTH_TOKEN=eyJ...

# Development
TURSO_DATABASE_URL=file:local.db
# No auth token needed

Common Mistakes

| Mistake | Fix | | ------------------------------------------ | ------------------------------------------- | | Using @libsql/client/web with file URLs | Use @libsql/client for local files | | Long-running write transactions | Keep writes short, they block other writes | | Opening local file during sync | Wait for sync to complete | | Forgetting to sync embedded replicas | Call sync() or use syncInterval | | Hardcoding credentials | Use environment variables | | Not using transactions for related writes | Use batch() or transaction() |

Performance Tips

• Use batch() for multiple related operations
• Use read transactions for read-only queries (parallel on replicas)
• Set appropriate syncInterval for embedded replicas
• Use vector indexes for tables with >1000 rows
• Use positional placeholders for frequently executed queries