Database Troubleshooting

This comprehensive troubleshooting guide helps you diagnose and resolve issues with the database switching system. Whether you’re dealing with connection failures, switching problems, or configuration errors, this guide provides step-by-step solutions.

Start with these commands to understand your current system state:

npm run db:status

npm run db:manage test

npm run db:manage health

npm run db:restore

This error appears when the system can’t find valid database credentials.

Symptoms:

• API endpoints return 503 Service Unavailable errors
• Dashboard shows “Database service unavailable”
• npm run db:status shows ”❌ No database providers configured”

Database operations timeout or fail to connect to the server.

Symptoms:

• Database operations timeout
• “Failed to connect” errors in logs
• API endpoints return 500 Internal Server errors

Auto-detection doesn’t select the expected database provider.

Symptoms:

• Wrong database provider is selected automatically
• Inconsistent behavior between environments
• npm run db:status shows unexpected active provider

Solutions:

1. Set Explicit Provider (Recommended):

   # Set explicit provider in .env
   echo "DATABASE_PROVIDER=turso" >> .env
   # or
   echo "DATABASE_PROVIDER=supabase" >> .env

2. Clear Auto-Detection Issues:

   # Remove auto-detection, use explicit choice
   sed -i 's/DATABASE_PROVIDER=auto/DATABASE_PROVIDER=turso/' .env

3. Understand Priority Logic:

   • Priority: Explicit choice → Supabase → Turso → Error
   • Multiple providers? Set DATABASE_PROVIDER to override

Operations succeed but data doesn’t match expectations.

Symptoms:

• Missing tables or columns
• Type conversion errors
• Inconsistent data between providers

npm run db:schema
# Shows detailed schema comparison between providers

# ✅ Correct format:
TURSO_DATABASE_URL=libsql://database-name.turso.io

# ❌ Wrong format:
TURSO_DATABASE_URL=https://database-name.turso.io

# ❌ Don't use anon key for server operations
SUPABASE_ANON_KEY=eyJ...

# ✅ Use service role for server operations
SUPABASE_SERVICE_ROLE_KEY=eyJ...

Switching command fails before making configuration changes.

Symptoms:

• Switching stops with “backup creation failed”
• .env.backup file is not created
• File permission errors

Switching appears successful but database doesn’t change.

Diagnosis Steps:

1. Check if .env file is writable:

   ls -la .env

2. Verify current provider:

   npm run db:status | grep "Active Provider"

3. Check file contents:

   cat .env | grep DATABASE_PROVIDER

Solutions:

# Make .env writable
chmod 644 .env

# Retry switching operation
npm run db:switch:turso  # or supabase

# Update environment manually
echo "DATABASE_PROVIDER=turso" >> .env

# Or edit directly with your preferred editor
nano .env  # or vim, code, etc.

Switch completed but new database configuration doesn’t work.

1. Immediate Rollback:

   npm run db:restore

2. Verify Restoration:

   npm run db:status

3. Manual Rollback (if automatic fails):

   cp .env.backup .env
   npm run db:status  # Verify restoration

4. Diagnose Original Issue:

   # Test the problematic configuration
   npm run db:manage test
   npm run db:manage health --verbose

Symptoms:

• Import errors for database modules
• TypeScript compilation fails
• “Cannot find module” errors

Solutions:

Database configuration changes don’t take effect without server restart.

Expected Behavior: The system supports real-time provider switching without server restart.

Troubleshooting Steps:

1. Check Current Provider in real-time:

   # This should show changes immediately
   npm run db:status

2. Check the active provider for real-time switching:

   npm run db:status
   # Output should show the current active provider

3. Force Restart if needed:

   # Kill existing processes
   pkill -f "npm run"
   
   # Start fresh
   npm run start

Symptoms:

• Works locally but fails in production
• Different database provider selected in different environments

API endpoints report incorrect or inconsistent provider information.

Diagnosis:

1. Test the database connection:

   npm run db:status

2. Check System Status:

   npm run db:status

3. Compare Results: API response should match system status

Solutions:

Symptoms:

• No errors but operations don’t work
• Empty results from database queries
• API returns success but no data changes

1. Test Direct Database Connection:

   npm run db:manage test

2. Check API Logs in development:

   npm run dev
   # Look for console errors in terminal

3. Verify Database Tables:

   npm run db:manage tables

4. Check Permissions (Supabase):

   • Review RLS policies in dashboard
   • Verify service role permissions

Symptoms:

• API endpoints timeout frequently
• Dashboard takes long time to load
• Database queries are slower than expected

Symptoms:

• Switching process crashes
• Out of memory errors during database operations

Solutions:

1. Use Dry-Run Mode first:

   node scripts/switch-database.js --to turso --dry-run

2. Stop Other Processes before switching:

   # Stop dev server and other resource-intensive processes
   pkill -f "npm run"

3. Switch Without Other Operations running:

   npm run db:switch:turso
   # Then restart other processes
   npm run start

Symptoms:

• Cannot read or write .env files
• Script execution fails with permission errors

1. Fix File Permissions:

   chmod 644 .env .env.backup
   chmod +x scripts/*.js

2. Check Directory Permissions:

   ls -la .
   # Verify you can read/write in current directory

3. Fix Ownership Issues (if needed):

   # Only if files are owned by different user
   sudo chown $USER:$USER .env .env.backup

Problem: Git wants to commit .env.backup files, causing merge conflicts.

1. Ensure Backup Files are Ignored:

   echo ".env.backup*" >> .gitignore
   echo ".env.*.backup" >> .gitignore

2. Remove from Git if already tracked:

   git rm --cached .env.backup
   git rm --cached .env.*.backup

3. Clean Up Repository:

   git add .gitignore
   git commit -m "Add .env backup files to .gitignore"

For detailed troubleshooting information:

# Enable debug logging for all database operations
DEBUG=1 npm run db:manage test
DEBUG=1 npm run db:switch:turso

# Get detailed output for all operations
npm run db:manage status --verbose
npm run db:manage health --verbose

Test database connections directly outside the abstraction layer:

node -e "
import { createClient } from '@libsql/client';
const client = createClient({
  url: process.env.TURSO_DATABASE_URL,
  authToken: process.env.TURSO_AUTH_TOKEN
});
console.log(await client.execute('SELECT 1 as test'));
"

node -e "
import { createClient } from '@supabase/supabase-js';
const client = createClient(
  process.env.SUPABASE_URL,
  process.env.SUPABASE_SERVICE_ROLE_KEY
);
const { data, error } = await client
  .from('clients')
  .select('count');
console.log('Result:', data, 'Error:', error);
"

Check for hidden characters or configuration issues:

# Check all database-related environment variables
env | grep -E "(DATABASE|TURSO|SUPABASE)" | sort

# Check for invisible characters or extra spaces
od -c .env | grep -E "(TURSO|SUPABASE|DATABASE)"

# Validate .env file format
npm run db:manage validate-config

If all else fails, reset the entire database configuration:

1. Backup Current State:

   cp .env .env.emergency-backup
   cp .env.backup .env.backup.emergency 2>/dev/null || true

2. Run Fresh Setup:

   npm run db:wizard
   # Follow prompts to reconfigure from scratch

3. Test New Configuration:

   npm run db:status
   npm run db:manage test

4. Restore if Needed:

   # If new setup doesn't work
   cp .env.emergency-backup .env

When reporting issues or asking for help, collect this information:

# Create comprehensive diagnostic report
{
  echo "=== System Information ==="
  echo "Node version: $(node --version)"
  echo "NPM version: $(npm --version)"
  echo "OS: $(uname -a)"
  echo

  echo "=== Database Status ==="
  npm run db:status
  echo

  echo "=== Health Check ==="
  npm run db:manage health
  echo

  echo "=== Environment Variables ==="
  env | grep -E "(DATABASE|TURSO|SUPABASE)" | sed 's/=.*/=***/' | sort
} > debug-report.txt

Add these to your development routine:

# Weekly health check
npm run db:status
npm run db:manage health

# Before major changes
npm run db:backup
npm run db:schema

1. Always Backup before experiments:

   npm run db:backup

2. Test Changes with dry-run first:

   node scripts/switch-database.js --to turso --dry-run

3. Validate Configuration after changes:

   npm run db:schema
   npm run db:manage test

4. Keep Emergency Backup:

   # Maintain a known-good configuration
   cp .env .env.known-good

# Always check before committing
git status | grep -E "(\.env|backup)" && echo "⚠️  Check .env files"

# Verify .gitignore is protecting secrets
git ls-files | grep -E "\.env" && echo "⚠️  .env files in git!"

This troubleshooting guide covers the most common issues you might encounter with the database switching system. For additional help or to report new issues, refer to the main database switching guide or check the project’s GitHub repository for the latest updates and community support.