Turso Database Development

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

Turso Account: Sign up at turso.tech
Turso CLI: Install the Turso CLI tool
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: '[email protected]',
  // ... 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:

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
Sequential Execution: Each statement is executed in order with proper error handling
Transaction Safety: Failed statements properly roll back to maintain database consistency
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:

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

From D1 (if previously used)

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

# 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

Turso Documentation: docs.turso.tech
libSQL Documentation: github.com/libsql/libsql
Community Discord: discord.gg/turso
GitHub Issues: github.com/tursodatabase/turso-cli

Next Steps

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

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

On this page