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.

Overview

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

🔄 Easy Switching

Switch databases with npm run db:switch:turso or npm run db:switch:supabase - automatic backups included.

🛡️ Safe Operations

Built-in backup/restore system prevents configuration loss. Roll back anytime with npm run db:restore.

🎯 Auto-Detection

Smart provider detection automatically uses the best available database based on your configuration.

🧙‍♂️ Setup Wizard

Interactive wizard guides you through database setup with step-by-step instructions.

Quick Start

Check Current Status

npm run db:status

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

Switch Database Provider

Switch to Turso

npm run db:switch:turso

Switch to Supabase

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

First-Time Setup

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

Architecture

Database Abstraction Layer

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 messages = await db.getMessages({ limit: 10 })

Key Components:

• src/libs/database.ts - Main abstraction layer with provider auto-detection
• src/libs/database-types.ts - Unified TypeScript interfaces
• src/libs/turso.ts - Turso (LibSQL) implementation
• src/libs/supabase.ts - Supabase (PostgreSQL) implementation

Provider Selection Logic

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

Configuration

Environment Variables

Supabase Setup

# 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 Setup

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

# Optional: Explicit provider selection
DATABASE_PROVIDER=turso

Both Providers

# 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

Available Commands

Database Management

# 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

Advanced Management CLI

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

Database Providers

Supabase (PostgreSQL)

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

Turso (LibSQL)

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

Common Use Cases

Development Team Workflow

Perfect for teams working with different database preferences:

Developer Setup

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

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

Testing Workflow

# 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

Production Deployment

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.

Safety Features

Automatic Backups

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

Rollback Protection

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

Dry Run Mode

Preview changes without applying them:

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

Troubleshooting

For quick issues, try these common solutions:

🔧 Configuration Issues

“Database not configured” error: bash npm run db:status # Check what's missing npm run db:wizard # Run setup wizard

🔗 Connection Problems

Connection failures during switching: bash npm run db:manage test # Test connections npm run db:manage health # Run diagnostics

🎯 Provider Detection

Wrong provider selected: bash # Set explicit provider in .env echo "DATABASE_PROVIDER=turso" >> .env

🚨 Emergency Recovery

Something went wrong: bash npm run db:restore # Rollback to previous config

Comprehensive Troubleshooting

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

Integration with Existing Code

API Endpoints

All existing API endpoints automatically work with both databases:

src/pages/api/example.ts

import { getDatabase } from '#libs/database'

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

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

Astro Components

Dashboard components automatically adapt to the active provider:

src/components/dashboard/Example.astro

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

let messages: Message[] = []
try {
  const db = getDatabase()
  messages = await db.getMessages({ archived: false, limit: 50 })
} catch (error) {
  console.error('Database error:', error)
}
---

<div>
  {
    messages.map(message => (
      <div key={message.id}>
        <h3>{message.name}</h3>
        <p>{message.message}</p>
      </div>
    ))
  }
</div>

Best Practices

Development

• 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

Production

• 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

Teams

• 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

Performance Considerations

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

Security Notes

• .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

Migration Path

Existing Projects

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.

Schema Compatibility

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.

Advanced Features

Real-time Provider 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

Provider-Aware Error Handling

The system provides context-aware error messages:

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

Schema Validation

Built-in schema validation ensures compatibility:

# Validate schema before switching
npm run db:schema

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

Future Enhancements

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

Getting Help

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.