Skip to content

turso.mdx

Latest commit

 

History

History
480 lines (344 loc) · 14.2 KB

turso.mdx

File metadata and controls

480 lines (344 loc) · 14.2 KB
title Turso Database Development
description Complete guide to using Turso distributed SQLite database with DeployStack Backend, including setup, configuration, and best practices.
sidebarTitle Turso Database

Overview

Turso is a distributed SQLite database service that provides global replication and edge performance. It's built on libSQL, an open-source fork of SQLite that adds additional features while maintaining full SQLite compatibility.

DeployStack integrates with Turso using the official @libsql/client driver through Drizzle ORM, providing excellent performance and developer experience.

Key Features

  • Global Replication: Automatic multi-region database replication
  • Edge Performance: Low-latency access from anywhere in the world
  • SQLite Compatibility: Full compatibility with SQLite syntax and features
  • Scalability: Automatic scaling based on usage patterns
  • libSQL Protocol: Enhanced SQLite with additional networking capabilities
**Migration Compatibility**: Turso requires SQL statements to be executed individually rather than in batches. DeployStack automatically handles this requirement by intelligently splitting migration files into individual statements during execution. You don't need to modify your migrations or write them differently - we handle the complexity for you.

Setup and Configuration

Prerequisites

  1. Turso Account: Sign up at turso.tech
  2. Turso CLI: Install the Turso CLI tool
  3. Database Creation: Create a Turso database instance

Installing Turso CLI

# macOS (Homebrew)
brew install tursodatabase/tap/turso

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

# Windows (PowerShell)
powershell -c "irm get.tur.so/install.ps1 | iex"

Creating a Turso Database

# Login to Turso
turso auth login

# Create a new database
turso db create deploystack-dev

# Get the database URL
turso db show deploystack-dev --url

# Create an authentication token
turso db tokens create deploystack-dev

Environment Configuration

Add the following environment variables to your .env file:

# Turso Configuration
TURSO_DATABASE_URL=libsql://your-database-name-your-org.turso.io
TURSO_AUTH_TOKEN=your_auth_token_here

Important Notes:

  • The database URL should start with libsql://
  • Keep your auth token secure and never commit it to version control
  • Use different databases for different environments (dev/staging/prod)

Database Setup in DeployStack

Using the Setup API

Once your environment variables are configured, use the DeployStack setup API:

# Setup Turso database
curl -X POST http://localhost:3000/api/db/setup \
  -H "Content-Type: application/json" \
  -d '{"type": "turso"}'

Verification

Check that the database is properly configured:

# Check database status
curl http://localhost:3000/api/db/status

Expected response:

{
  "configured": true,
  "initialized": true,
  "dialect": "turso"
}

Development Workflow

Schema Development

Turso uses the same SQLite schema as other database types. All schema changes are made in src/db/schema.sqlite.ts:

// Example: Adding a new table
export const projects = sqliteTable('projects', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  description: text('description'),
  userId: text('user_id').references(() => authUser.id),
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull().$defaultFn(() => new Date()),
  updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull().$defaultFn(() => new Date()),
});

Migration Generation

Generate migrations using the standard Drizzle commands:

# Generate migration files
npm run db:generate

# Apply migrations (automatic on server start)
npm run db:up

Note: While migrations use standard SQLite syntax with multiple statements and breakpoint markers, DeployStack automatically processes these for Turso compatibility. Each CREATE TABLE, CREATE INDEX, and other SQL statements are executed individually behind the scenes, ensuring smooth deployment without any manual intervention.

Database Operations

All standard Drizzle operations work with Turso:

// Example: Querying data
const users = await db.select().from(schema.authUser).all();

// Example: Inserting data
await db.insert(schema.authUser).values({
  id: 'user_123',
  username: 'john_doe',
  email: 'john@example.com',
  // ... other fields
});

// Example: Complex queries with joins
const usersWithTeams = await db
  .select()
  .from(schema.authUser)
  .leftJoin(schema.teamMembers, eq(schema.authUser.id, schema.teamMembers.userId))
  .where(eq(schema.authUser.active, true));

Performance Considerations

Connection Management

Turso connections are managed automatically by the libSQL client:

  • Connection Pooling: Automatic connection pooling for optimal performance
  • Keep-Alive: Connections are kept alive to reduce latency
  • Automatic Reconnection: Handles network interruptions gracefully

Query Optimization

  • Prepared Statements: Use prepared statements for repeated queries
  • Batch Operations: Group multiple operations when possible (note: migrations are automatically split for compatibility)
  • Indexing: Add appropriate indexes for frequently queried columns
  • Migration Performance: Initial migrations execute individually for compatibility, but runtime queries maintain full performance
// Example: Batch operations
await db.batch([
  db.insert(schema.authUser).values(user1),
  db.insert(schema.authUser).values(user2),
  db.insert(schema.authUser).values(user3),
]);

Regional Performance

  • Edge Locations: Turso automatically routes queries to the nearest edge location
  • Read Replicas: Read operations are served from local replicas
  • Write Consistency: Writes are replicated globally with eventual consistency

Best Practices

Environment Management

# Development
TURSO_DATABASE_URL=libsql://deploystack-dev-your-org.turso.io
TURSO_AUTH_TOKEN=dev_token_here

# Staging
TURSO_DATABASE_URL=libsql://deploystack-staging-your-org.turso.io
TURSO_AUTH_TOKEN=staging_token_here

# Production
TURSO_DATABASE_URL=libsql://deploystack-prod-your-org.turso.io
TURSO_AUTH_TOKEN=prod_token_here

Security

  • Token Rotation: Regularly rotate authentication tokens
  • Environment Isolation: Use separate databases for each environment
  • Access Control: Use Turso's built-in access control features
  • Encryption: Data is encrypted in transit and at rest

Monitoring

# Monitor database usage
turso db show deploystack-prod

# View recent activity
turso db shell deploystack-prod --command ".stats"

# Check replication status
turso db locations deploystack-prod

Debugging and Troubleshooting

Common Issues

Connection Errors

Error: Failed to connect to Turso database
  • Verify TURSO_DATABASE_URL is correct and starts with libsql://
  • Check that TURSO_AUTH_TOKEN is valid and not expired
  • Ensure network connectivity to Turso servers

Authentication Errors

Error: Authentication failed
  • Regenerate the auth token: turso db tokens create your-database
  • Verify the token has proper permissions
  • Check that the token matches the database

Migration Errors

Error: SQL_MANY_STATEMENTS: SQL string contains more than one statement
  • Status: This error is automatically handled by DeployStack as of version 1.0+
  • Cause: Turso's libSQL client requires individual statement execution
  • Solution: DeployStack automatically splits and executes statements individually
  • If you encounter this error, ensure you're running the latest version of DeployStack
Error: Migration failed to apply
  • Check migration SQL syntax is valid SQLite
  • Verify no conflicting schema changes
  • Review migration order and dependencies
  • Ensure your Turso database has sufficient resources

Debug Logging

Enable detailed logging to troubleshoot issues:

# Enable debug logging
LOG_LEVEL=debug npm run dev

Look for Turso-specific log entries:

[INFO] Creating Turso connection
[INFO] LibSQL client created
[INFO] Turso database instance created successfully

Database Inspection

# Connect to database shell
turso db shell your-database

# Run SQL queries
turso db shell your-database --command "SELECT * FROM authUser LIMIT 5"

# Export database
turso db dump your-database --output backup.sql

Performance Monitoring

# Check database statistics
turso db show your-database

# Monitor query performance
turso db shell your-database --command "EXPLAIN QUERY PLAN SELECT * FROM authUser"

Technical Implementation Details

How DeployStack Handles Turso Migrations

Unlike SQLite which can execute multiple SQL statements in a single call, Turso's libSQL client requires each statement to be executed individually. DeployStack solves this transparently:

  1. Automatic Statement Splitting: Migration files are intelligently parsed to separate:

    • Statements divided by --> statement-breakpoint markers
    • Multiple statements on the same line (like consecutive CREATE INDEX commands)
    • Complex migrations with mixed DDL operations

  2. Sequential Execution: Each statement is executed in order with proper error handling

  3. Transaction Safety: Failed statements properly roll back to maintain database consistency

  4. Performance Impact: Migration execution is slightly slower than SQLite (milliseconds per statement), but this only affects initial setup and schema changes, not runtime performance

Migration File Compatibility

Your existing Drizzle migration files work without modification:

-- This standard Drizzle migration works perfectly
CREATE TABLE users (...);
--> statement-breakpoint
CREATE INDEX idx_users_email ON users(email);--> statement-breakpoint
CREATE UNIQUE INDEX idx_users_username ON users(username);

DeployStack automatically handles the parsing and execution, so you write migrations exactly as you would for SQLite.

Advanced Features

Multi-Region Setup

# Create database with specific regions
turso db create deploystack-global --location lax,fra,nrt

# Check current locations
turso db locations deploystack-global

# Add more locations
turso db locations add deploystack-global syd

Database Branching

# Create a branch for development
turso db create deploystack-feature --from-db deploystack-main

# Switch between branches
turso db shell deploystack-feature

Backup and Restore

# Create backup
turso db dump deploystack-prod --output backup-$(date +%Y%m%d).sql

# Restore from backup
turso db shell deploystack-dev < backup-20250104.sql

Integration with DeployStack Features

Global Settings

Turso works seamlessly with DeployStack's global settings system:

  • Batch Operations: Efficient batch creation of settings
  • Encryption: Settings are encrypted before storage
  • Performance: Optimized for Turso's distributed architecture

Plugin System

Plugins can extend the database schema with Turso:

// Example plugin with Turso-optimized tables
class MyPlugin implements Plugin {
  databaseExtension: DatabaseExtension = {
    tableDefinitions: {
      'my_table': {
        id: (builder) => builder('id').primaryKey(),
        data: (builder) => builder('data').notNull(),
        // Optimized for Turso's replication
        region: (builder) => builder('region'),
        created_at: (builder) => builder('created_at')
      }
    }
  };
}

Authentication

Lucia authentication works perfectly with Turso:

  • Session Management: Distributed session storage
  • User Data: Global user data replication
  • Performance: Fast authentication checks worldwide

Migration from Other Databases

From SQLite

Since Turso is SQLite-compatible, migration is straightforward:

  1. Export SQLite data: sqlite3 database.db .dump > export.sql
  2. Import to Turso: turso db shell your-database < export.sql
  3. Update environment variables: Switch to Turso configuration
  4. Test application: Verify all functionality works

From D1 (if previously used)

  1. Export D1 data: Use Wrangler to export data
  2. Convert to SQLite format: Ensure compatibility
  3. Import to Turso: Load data into Turso database
  4. Update configuration: Switch database type to Turso

Known Limitations and Solutions

Statement Execution

Limitation: Turso cannot execute multiple SQL statements in a single database call.

DeployStack Solution: Automatic statement splitting and sequential execution. This is completely transparent - you never need to think about it.

Migration Speed

Limitation: Migrations apply slightly slower than with local SQLite due to individual statement execution and network latency.

DeployStack Solution: Migrations are a one-time operation during setup or updates. Runtime performance is unaffected. For large migrations, DeployStack provides progress logging to track execution.

Cost Optimization

Usage Monitoring

# Check current usage
turso db show your-database

# Monitor over time
turso org show

Optimization Strategies

  • Query Efficiency: Optimize queries to reduce database load
  • Connection Reuse: Leverage connection pooling
  • Regional Placement: Choose regions close to your users
  • Data Archiving: Archive old data to reduce storage costs

Support and Resources

Next Steps

  1. Set up your Turso database following the configuration steps above
  2. Configure environment variables in your .env file
  3. Run the database setup using the DeployStack API
  4. Start developing with global SQLite performance
  5. Monitor and optimize your database usage

For more information about database management in DeployStack, see the Database Management Guide.