turso-best-practices（图尔索最佳实践）

Turso & libSQL Best Practices

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
• Configuring encryption at rest
• Working with the Turso CLI or Platform API

Quick Reference

Installation & Setup

CLI Installation

# macOS
brew install tursodatabase/tap/turso

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

Authentication

# Sign up (opens browser)
turso auth signup

# Login (opens browser)
turso auth login

# Headless mode (WSL/CI)
turso auth login --headless

Create Your First Database

# Create database (auto-detects closest region)
turso db create my-db

# Show database info
turso db show my-db

# Get connection URL
turso db show my-db --url

# Create auth token
turso db tokens create my-db

# Connect to shell
turso db shell my-db

SDK Usage (TypeScript/JavaScript)

Installation

npm install @libsql/client
# or
pnpm add @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

Batch executes multiple statements in an implicit transaction:

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

For complex logic with conditional commits/rollbacks:

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

Local Development

Option 1: SQLite File (Simplest)

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

No auth token needed. Works with standard SQLite features.

Option 2: Turso Dev Server (Full Features)

# Start local libSQL server
turso dev

# With persistent file
turso dev --db-file local.db

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

Supports all libSQL features including extensions.

Option 3: Production Database Dump

# Export production data
turso db shell prod-db .dump > dump.sql

# Create local file from dump
cat dump.sql | sqlite3 local.db

Environment Variables Pattern

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

# 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

Embedded Replicas

Local database that syncs with remote Turso database. Reads are instant (local), writes go to remote.

Configuration

const client = createClient({
  url: 'file:replica.db', // Local file
  syncUrl: 'libsql://my-db.turso.io', // Remote primary
  authToken: '...',
  syncInterval: 60, // Auto-sync every 60 seconds
})

Manual Sync

await client.sync()

Offline Mode

const client = createClient({
  url: 'file:replica.db',
  syncUrl: 'libsql://my-db.turso.io',
  authToken: '...',
  offline: true, // Writes go to local, sync later
})

Important Notes

• Reads always from local replica
• Writes go to remote primary (unless offline mode)
• Read-your-writes guaranteed after successful write
• Don't open local file while syncing (corruption risk)
• One frame = 4KB (minimum write unit)

Vector Search (AI & Embeddings)

Native vector search without extensions.

Create Table with Vector Column

CREATE TABLE movies (
  id INTEGER PRIMARY KEY,
  title TEXT,
  embedding F32_BLOB(384)  -- 384-dimensional float32 vector
);

Vector Types

Insert Vectors

INSERT INTO movies (title, embedding)
VALUES ('Inception', vector32('[0.1, 0.2, 0.3, ...]'));

Similarity Search

SELECT title,
       vector_distance_cos(embedding, vector32('[0.1, 0.2, ...]')) AS distance
FROM movies
ORDER BY distance ASC
LIMIT 10;

Vector Index (DiskANN)

-- Create index
CREATE INDEX movies_idx ON movies(libsql_vector_idx(embedding));

-- Query with index (much faster for large tables)
SELECT title
FROM vector_top_k('movies_idx', vector32('[0.1, 0.2, ...]'), 10)
JOIN movies ON movies.rowid = id;

Index Settings

CREATE INDEX movies_idx ON movies(
  libsql_vector_idx(embedding, 'metric=cosine', 'compress_neighbors=float8')
);

Drizzle ORM Integration

Setup

npm install drizzle-orm @libsql/client
npm install -D drizzle-kit

Configuration

// drizzle.config.ts
import type { Config } from 'drizzle-kit'

export default {
  schema: './db/schema.ts',
  out: './migrations',
  dialect: 'turso',
  dbCredentials: {
    url: process.env.TURSO_DATABASE_URL!,
    authToken: process.env.TURSO_AUTH_TOKEN,
  },
} satisfies Config

Schema Definition

// db/schema.ts
import { text, integer, sqliteTable } from 'drizzle-orm/sqlite-core'

export const users = sqliteTable('users', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
})

Client Setup

import { drizzle } from 'drizzle-orm/libsql'
import { createClient } from '@libsql/client'

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

export const db = drizzle(turso)

Migrations

# Generate migrations
npm run drizzle-kit generate

# Apply migrations
npm run drizzle-kit migrate

Branching & Point-in-Time Recovery

Create Branch

turso db create feature-branch --from-db production-db

Point-in-Time Restore

turso db create restored-db --from-db production-db --timestamp 2024-01-15T10:00:00Z

CI/CD Branching (GitHub Actions)

name: Create Database Branch
on: create

jobs:
  create-branch:
    runs-on: ubuntu-latest
    steps:
      - name: Create Database
        run: |
          curl -X POST \
            -H "Authorization: Bearer ${{ secrets.TURSO_API_TOKEN }}" \
            -H "Content-Type: application/json" \
            -d '{"name": "${{ github.ref_name }}", "group": "default", "seed": {"type": "database", "name": "production"}}' \
            "https://api.turso.tech/v1/organizations/${{ secrets.ORG }}/databases"

Important Notes

• Branches are separate databases (no auto-merge)
• Need new token or group token for branch
• Count toward database quota
• Delete manually when done

Encryption at Rest

Generate Key

# 256-bit key for AEGIS-256/AES-256
openssl rand -base64 32

# 128-bit key for AEGIS-128/AES-128
openssl rand -base64 16

Create Encrypted Database

turso db create secure-db \
  --remote-encryption-key "YOUR_KEY" \
  --remote-encryption-cipher aegis256

Connect to Encrypted Database

turso db shell secure-db --remote-encryption-key "YOUR_KEY"

Supported Ciphers

SQLite Extensions

Preloaded (Always Available)

Enable Additional Extensions

turso db create my-db --enable-extensions

Common Mistakes

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
• Consider compress_neighbors for large vector indexes
• Use positional placeholders for frequently executed queries