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

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
Create branch	turso db create branch-db --from-db my-db
Point-in-time restore	turso db create restored --from-db my-db --timestamp 2024-01-01T00:00:00Z
Database dump	turso db shell my-db .dump > dump.sql

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

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',
})

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

Type	Storage	Description
FLOAT64 / F64_BLOB	8D + 1 bytes	64-bit double precision
FLOAT32 / F32_BLOB	4D bytes	32-bit single precision (recommended)
FLOAT16 / F16_BLOB	2D + 1 bytes	16-bit half precision
FLOAT8 / F8_BLOB	D + 14 bytes	8-bit compressed
FLOAT1BIT / F1BIT_BLOB	D/8 + 3 bytes	1-bit binary

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')
);

Setting	Values	Description
metric	cosine, l2	Distance function
max_neighbors	integer	Graph connectivity
compress_neighbors	vector type	Compression for storage
search_l	integer	Search precision vs speed

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

Cipher	Key Size	Recommendation
aegis128l	128-bit	Recommended for speed
aegis256	256-bit	Recommended for security
aes128gcm	128-bit	NIST compliance
aes256gcm	256-bit	NIST compliance
chacha20poly1305	256-bit	AES alternative

SQLite Extensions

Preloaded (Always Available)

Extension	Description
JSON	JSON functions
FTS5	Full-text search
R*Tree	Spatial indexing
SQLean Crypto	Hashing, encoding
SQLean Fuzzy	Fuzzy string matching
SQLean Math	Advanced math
SQLean Stats	Statistical functions
SQLean Text	String manipulation
SQLean UUID	UUID generation

Enable Additional Extensions

turso db create my-db --enable-extensions

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()
Creating vector index on wrong column type	Column must be vector type (F32_BLOB, etc.)

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