Database Migration Strategies for Supabase

Migrating to Supabase can transform your application, but it requires careful planning. This guide covers strategies for safe, successful migrations from various platforms.

Pre-Migration Planning

Before starting, assess:

• Current database size and complexity
• Data relationships and constraints
• Active users and usage patterns
• Acceptable downtime window
• Rollback requirements

Migration Approaches

1. All-at-Once Migration

For smaller databases or applications that can handle brief downtime:

# Export from current database
pg_dump -h old-host -U user -d database > dump.sql

# Import to Supabase psql -h db.supabase.co -U postgres -d postgres < dump.sql ```

Pros: Simple, fast Cons: Requires downtime

2. Dual-Write Migration

For zero-downtime migrations:

// Write to both databases
async function createUser(userData) {
  // Write to old database
  await oldDb.insert('users', userData)
  
  // Write to new Supabase database
  await supabase.from('users').insert(userData)
}

Process: 1. Start dual-writing 2. Backfill historical data 3. Verify data consistency 4. Switch reads to Supabase 5. Stop writing to old database

3. Logical Replication

For large databases:

-- On source database
CREATE PUBLICATION my_publication FOR ALL TABLES;

-- On Supabase CREATE SUBSCRIPTION my_subscription CONNECTION 'host=old-host dbname=mydb user=replication_user' PUBLICATION my_publication; ```

Schema Migration

Use Supabase migrations for schema changes:

# Create migration
supabase migration new create_users_table

# Write migration -- supabase/migrations/20240101000000_create_users_table.sql CREATE TABLE users ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), email TEXT UNIQUE NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW() );

# Apply migration supabase db push ```

Data Transformation

Transform data during migration:

// Example: Converting Firebase to Supabase
const migrateFirebaseUser = async (firebaseUser) => {
  const supabaseUser = {
    id: firebaseUser.uid,
    email: firebaseUser.email,
    email_verified: firebaseUser.emailVerified,
    created_at: new Date(firebaseUser.metadata.creationTime),
    updated_at: new Date(firebaseUser.metadata.lastSignInTime)
  }
  
  await supabase.from('users').insert(supabaseUser)
}

Handling Authentication

From Firebase Auth

import { createClient } from '@supabase/supabase-js'
import admin from 'firebase-admin'

async function migrateFirebaseUser(firebaseUid) { // Get Firebase user const firebaseUser = await admin.auth().getUser(firebaseUid) // Create in Supabase const { data, error } = await supabase.auth.admin.createUser({ email: firebaseUser.email, email_confirm: firebaseUser.emailVerified, user_metadata: { firebase_uid: firebaseUid, display_name: firebaseUser.displayName } }) } ```

From Auth0

async function migrateAuth0User(auth0User) {
  const { data, error } = await supabase.auth.admin.createUser({
    email: auth0User.email,
    email_confirm: auth0User.email_verified,
    user_metadata: {
      auth0_id: auth0User.user_id,
      ...auth0User.user_metadata
    }
  })
}

Data Validation

Verify data integrity after migration:

async function validateMigration() {
  // Count records
  const oldCount = await oldDb.count('users')
  const { count: newCount } = await supabase
    .from('users')
    .select('*', { count: 'exact', head: true })
  
  console.log(`Old DB: ${oldCount}, New DB: ${newCount}`)
  
  // Sample and compare records
  const samples = await oldDb.select('users').limit(100)
  for (const sample of samples) {
    const { data } = await supabase
      .from('users')
      .select('*')
      .eq('id', sample.id)
      .single()
    
    if (!deepEqual(sample, data)) {
      console.error('Mismatch found:', sample.id)
    }
  }
}

File Storage Migration

Migrate files to Supabase Storage:

async function migrateFiles(bucket, files) {
  for (const file of files) {
    // Download from old storage
    const fileData = await oldStorage.download(file.path)
    
    // Upload to Supabase
    const { data, error } = await supabase.storage
      .from(bucket)
      .upload(file.path, fileData, {
        contentType: file.contentType,
        cacheControl: '3600'
      })
  }
}

Rollback Strategy

Always have a rollback plan:

// Save checkpoint before migration
async function createCheckpoint() {
  await oldDb.transaction(async (tx) => {
    await tx.execute('CREATE TABLE migration_checkpoint AS SELECT NOW() as timestamp')
  })
}

// Rollback if needed async function rollback() { // Switch application back to old database process.env.DATABASE_URL = OLD_DATABASE_URL // Stop dual-write await disableDualWrite() } ```

Performance Optimization

Speed up large migrations:

// Batch inserts
const batchSize = 1000
for (let i = 0; i < data.length; i += batchSize) {
  const batch = data.slice(i, i + batchSize)
  await supabase.from('table').insert(batch)
}

// Parallel processing const chunks = chunkArray(data, 1000) await Promise.all( chunks.map(chunk => supabase.from('table').insert(chunk)) ) ```

Post-Migration Tasks

After successful migration:

1. Update connection strings 2. Remove old database references 3. Update documentation 4. Monitor performance 5. Clean up old resources 6. Verify all features work

Common Pitfalls

• Not testing with production-like data
• Ignoring foreign key constraints
• Forgetting to migrate indexes
• Not accounting for timezone differences
• Skipping validation steps

Best Practices

• Test migration on staging first
• Have a detailed rollback plan
• Monitor during and after migration
• Communicate with stakeholders
• Keep old database for a grace period
• Document the process

Conclusion

Database migration requires careful planning and execution. Start with a clear strategy, test thoroughly, and always have a rollback plan. With the right approach, you can migrate to Supabase safely and efficiently.