Database Switching System

The astro-basics project features a sophisticated database abstraction layer that enables seamless switching between database providers without code changes or complex configuration. Switch between Supabase (PostgreSQL) and Turso (LibSQL) with a single command while maintaining full backward compatibility.

The database switching system provides:

• One-command switching between database providers
• Automatic backups before any configuration changes
• Zero breaking changes to existing code
• Real-time provider detection without server restart
• Interactive setup wizard for non-technical users

npm run db:status

This shows your current database configuration, active provider, and available options.

npm run db:switch:turso

npm run db:switch:supabase

Each switch automatically:

1. Creates a backup of your current configuration
2. Tests connectivity to the new database
3. Updates your environment variables
4. Confirms successful switch

If you’re setting up databases for the first time:

npm run db:wizard

The interactive wizard will:

• Guide you through choosing database providers
• Help you obtain necessary credentials
• Test connections automatically
• Configure your .env file

The system uses a unified interface that abstracts database-specific implementations:

// All code uses the same interface regardless of provider
import { getDatabase } from '#libs/database'

const db = getDatabase()
const events = await db.getEvents({ limit: 10 })

Key Components:

• src/libs/database/ - Modular abstraction layer (v2.0.0)
  • index.ts - Public API entry point
  • factory.ts - Provider auto-detection and instantiation
  • providers/turso/ - Turso implementation (delegation pattern)
  • providers/supabase/ - Supabase implementation (delegation pattern)
  • shared/ - Shared utilities (field validation, security)
• src/libs/database-types.ts - Unified TypeScript interfaces
• src/libs/database.ts - Backward compatibility re-exports

Architecture Benefits:

• Modular Design: Feature-specific modules (events, users, clients, orders)
• Delegation Pattern: Provider classes delegate to operation classes
• Scalability: Easy to add new features without touching existing code
• Maintainability: Each module has single responsibility

Learn more: Database Architecture Guide

The system automatically selects databases using this priority:

1. Explicit Choice - DATABASE_PROVIDER=turso|supabase in .env
2. Supabase - If Supabase credentials are configured
3. Turso - If Turso credentials are configured
4. Error - If no providers are available

# Supabase Configuration
SUPABASE_URL=https://your-project-id.supabase.co
SUPABASE_ANON_KEY=eyJ... # For client operations
SUPABASE_SERVICE_ROLE_KEY=eyJ... # For server operations (required)

# Optional: Explicit provider selection
DATABASE_PROVIDER=supabase

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

# Optional: Explicit provider selection
DATABASE_PROVIDER=turso

# Configure both providers
# Supabase
SUPABASE_URL=https://your-project-id.supabase.co
SUPABASE_SERVICE_ROLE_KEY=eyJ...

# Turso
TURSO_DATABASE_URL=libsql://your-database-name.turso.io
TURSO_AUTH_TOKEN=eyJ...

# Choose which one to use (or omit for auto-detection)
DATABASE_PROVIDER=supabase

# Core Commands
npm run db:status              # Show current configuration and status
npm run db:wizard              # Interactive setup for new users
npm run db:manage              # Advanced database management CLI

# Database Switching (with automatic backups)
npm run db:switch:turso        # Switch to Turso database
npm run db:switch:supabase     # Switch to Supabase database

# Backup Operations
npm run db:backup              # Create configuration backup only
npm run db:restore             # Restore from previous backup

# Validation & Health
npm run db:schema              # Validate database schema compatibility

The npm run db:manage command provides additional operations:

# Test database connections
npm run db:manage test

# Run comprehensive health checks
npm run db:manage health

# List all tables in current database
npm run db:manage tables

# Interactive switching with prompts
npm run db:manage switch

Best for:

• Real-time subscriptions
• Advanced PostgreSQL features
• Row-level security (RLS)
• Built-in authentication integration

Configuration:

• Requires SUPABASE_URL and SUPABASE_SERVICE_ROLE_KEY
• Uses service role for server-side operations
• Supports real-time features and complex queries

Best for:

• Low latency applications
• Edge deployment
• SQLite compatibility
• Simple data models

Configuration:

• Requires TURSO_DATABASE_URL and TURSO_AUTH_TOKEN
• Distributed SQLite with edge replication
• Fast read operations with eventual consistency

Perfect for teams working with different database preferences:

# Each developer chooses their preferred database
npm run db:wizard

# Select Turso for fast local development
# or Supabase for advanced features

# Test with Turso
npm run db:switch:turso
npm run dev
# Run your tests...

# Switch and test with Supabase
npm run db:switch:supabase
npm run dev
# Run your tests...

# Restore original setup
npm run db:restore

For production environments, set explicit provider:

# Production .env
DATABASE_PROVIDER=supabase    # or turso
# ... other production configs

This prevents auto-detection issues and ensures consistent behavior.

Every switching operation automatically:

1. Backs up current configuration to .env.backup
2. Tests new database connectivity before switching
3. Validates the switch after completion
4. Provides rollback option if issues occur

If something goes wrong during switching:

# Automatic rollback
npm run db:restore

# Manual rollback if needed
cp .env.backup .env
npm run db:status  # Verify restoration

Preview changes without applying them:

# See what would happen without making changes
node scripts/switch-database.js --to turso --dry-run

For quick issues, try these common solutions:

For detailed problem diagnosis and resolution, see our Complete Database Troubleshooting Guide:

• Error Message Diagnosis - Step-by-step solutions for all common errors
• Provider-Specific Issues - Turso and Supabase specific problem resolution
• Switching Operation Failures - Backup, rollback, and recovery procedures
• Development Environment Issues - Module imports, hot reload, production differences
• Performance Optimization - Speed and memory issue resolution
• Advanced Debugging Tools - Detailed diagnostic techniques

All existing API endpoints automatically work with both databases:

import { getDatabase } from '#libs/database'

export const GET: APIRoute = async () => {
  const db = getDatabase() // Automatically uses active provider
  const data = await db.getEvents({ limit: 10 })

  return new Response(
    JSON.stringify({
      success: true,
      provider: db.getProviderName(), // Shows which DB is active
      data,
    })
  )
}

Dashboard components automatically adapt to the active provider:

---
import { getDatabase } from '#libs/database'
import type { Event } from '#libs/database-types'

let events: Event[] = []
try {
  const db = getDatabase()
  events = await db.getEvents({ limit: 50 })
} catch (error) {
  console.error('Database error:', error)
}
---

<div>
  {
    events.map(event => (
      <div key={event.id}>
        <h3>{event.name}</h3>
        <p>{event.description}</p>
      </div>
    ))
  }
</div>

• Use turso for fast local development - Lower latency for frequent operations
• Switch to supabase for testing advanced features - Real-time, RLS, advanced queries
• Check npm run db:status when joining projects - Understand current configuration

• Set DATABASE_PROVIDER explicitly - Prevent auto-detection issues
• Use service role keys for Supabase - Never use anonymous keys server-side
• Monitor with npm run db:manage health - Regular health checks

• Document database choices per environment - Clear team guidelines
• Use backup/restore for experiments - Safe configuration testing
• Share setup with npm run db:wizard - Consistent team onboarding

The abstraction layer is designed for minimal overhead:

• < 5ms additional latency measured in production
• Zero performance impact on database operations themselves
• Provider-specific optimizations preserved through abstraction
• Connection pooling and caching handled by underlying providers

• .env.backup files excluded from git automatically
• Service role keys are server-side only for Supabase operations
• Credential validation before switching prevents invalid configurations
• Database switching requires filesystem access - consider for production deployment

If you have an existing project using only Turso or Supabase:

1. Install the abstraction layer (already done in astro-basics)
2. Update imports to use getDatabase() instead of direct clients
3. Test existing functionality to ensure compatibility
4. Configure second database when ready to switch

The system is designed to be 100% backward compatible - existing code continues working without changes.

Validate schema compatibility between providers:

npm run db:schema              # Check current schema
npm run db:manage tables       # List available tables

Both providers should have compatible table structures for seamless switching.

Unlike traditional database switching that requires server restarts, this system supports real-time switching:

// Database provider changes automatically without restart
const db = getDatabase() // Always returns currently configured provider

The system provides context-aware error messages:

try {
  const db = getDatabase()
  await db.insertClient(data)
} catch (error) {
  // Error includes provider information for better debugging
  console.error(`Database operation failed (${db.getProviderName()}):`, error)
}

Built-in schema validation ensures compatibility:

# Validate schema before switching
npm run db:schema

# Reports:
# - Missing tables
# - Column mismatches
# - Type incompatibilities
# - Required migrations

The database abstraction system is designed to support:

• Additional providers (MySQL, MongoDB, etc.)
• Migration generation tools
• Schema synchronization between providers
• Advanced backup strategies (scheduled, remote storage)
• Performance monitoring and optimization

If you encounter issues with the database switching system:

1. Check status: npm run db:status for current configuration
2. Test connections: npm run db:manage test for connectivity issues
3. Health check: npm run db:manage health for comprehensive diagnostics
4. Reset configuration: npm run db:wizard to start fresh

For detailed troubleshooting, see the comprehensive troubleshooting guide in the project documentation.

The database switching system transforms database management from a complex, error-prone process into a simple, safe operation that any team member can perform confidently. Whether you’re a solo developer or part of a large team, the system adapts to your workflow while maintaining the flexibility to switch providers as your needs evolve.