Skip to content

DATA_MIGRATION_GUIDE.md

Latest commit

 

History

History
312 lines (232 loc) · 7.69 KB

DATA_MIGRATION_GUIDE.md

File metadata and controls

312 lines (232 loc) · 7.69 KB
Raw

Data Migration Guide - Supabase to Supabase

Overview

This guide explains how to migrate data (not just schema) from one Supabase database to another.

⚠️ Important Distinction

  • COMPLETE_SCHEMA_ADDITIONS.sql = Creates/updates database structure (tables, columns, indexes)
  • DATA_MIGRATION_SCRIPT.sql = Copies actual data from one database to another

Migration Methods

Method 1: Using Supabase Dashboard (Recommended for Small Datasets)

Best for: < 10,000 records per table

  1. Export from Source Database:

    • Go to Supabase Dashboard → SQL Editor
    • Run: SELECT * FROM table_name;
    • Export results as CSV

  2. Import to Target Database:

    • Go to Supabase Dashboard → Table Editor
    • Select your table
    • Click "Import" → Upload CSV
    • Map columns correctly

Method 2: Using pg_dump/pg_restore (Recommended for Large Datasets)

Best for: > 10,000 records or full database migration

# Step 1: Export from source database
pg_dump -h source-db-host.supabase.co \
        -U postgres \
        -d postgres \
        -t public.clients \
        -t public.locations \
        -t public.reviews \
        --data-only \
        --column-inserts \
        > migration_data.sql

# Step 2: Import to target database
psql -h target-db-host.supabase.co \
     -U postgres \
     -d postgres \
     -f migration_data.sql

Method 3: Using Application-Level Migration (Most Control)

Best for: Complex migrations, data transformation, validation

Create a Node.js script:

// migrate-data.ts
import { createClient } from '@supabase/supabase-js';

const sourceSupabase = createClient(
  'https://source-project.supabase.co',
  'source-service-key'
);

const targetSupabase = createClient(
  'https://target-project.supabase.co',
  'target-service-key'
);

async function migrateTable(tableName: string) {
  console.log(`Migrating ${tableName}...`);
  
  // Fetch from source
  const { data, error } = await sourceSupabase
    .from(tableName)
    .select('*');
  
  if (error) throw error;
  
  // Insert into target (in batches)
  const batchSize = 100;
  for (let i = 0; i < data.length; i += batchSize) {
    const batch = data.slice(i, i + batchSize);
    const { error: insertError } = await targetSupabase
      .from(tableName)
      .insert(batch);
    
    if (insertError) {
      console.error(`Error inserting batch ${i}:`, insertError);
    }
  }
  
  console.log(`✅ Migrated ${data.length} records from ${tableName}`);
}

// Run migration
async function main() {
  const tables = [
    'clients',
    'locations',
    'reviews',
    'metrics_impressions',
    'metrics_snapshot',
    'keyword_scans',
    'citations',
    'websites',
    // ... other tables
  ];
  
  for (const table of tables) {
    await migrateTable(table);
  }
}

main();

Method 4: Using Supabase CLI

# Install Supabase CLI
npm install -g supabase

# Link to source project
supabase link --project-ref source-project-ref

# Export data
supabase db dump --data-only -f migration.sql

# Link to target project
supabase link --project-ref target-project-ref

# Import data
supabase db reset
psql -f migration.sql

Migration Order (Important!)

Migrate tables in this order to respect foreign key dependencies:

  1. ✅ clients (no dependencies)
  2. ✅ memberships (depends on clients)
  3. ✅ locations (depends on clients)
  4. ✅ websites (depends on clients)
  5. ✅ reviews (depends on locations)
  6. ✅ metrics_impressions (depends on locations)
  7. ✅ metrics_snapshot (depends on locations)
  8. ✅ keyword_scans (depends on locations)
  9. ✅ citations (depends on locations)
  10. ✅ web_keywords (depends on websites)
  11. ✅ web_pages (depends on websites)
  12. ✅ web_keyword_ranks (depends on web_keywords)
  13. ✅ web_page_metrics (depends on web_pages)
  14. ✅ web_competitors (depends on websites)
  15. ✅ web_competitor_metrics (depends on web_competitors)
  16. ✅ web_audit_summary (depends on websites)
  17. ✅ web_audit_issues (depends on websites)
  18. ✅ web_backlink_summary (depends on websites)

Step-by-Step Process

Step 1: Prepare Target Database

-- Run schema additions first
-- Execute: COMPLETE_SCHEMA_ADDITIONS.sql

Step 2: Export Data from Source

Choose your method:

  • Small data: Use Supabase Dashboard export
  • Large data: Use pg_dump
  • Complex: Use application script

Step 3: Transform Data (if needed)

Common transformations:

  • UUID mapping (if IDs need to change)
  • User ID mapping (if auth.users differ)
  • Date format conversion
  • Null value handling

Step 4: Import Data to Target

Import in the correct order (see Migration Order above).

Step 5: Verify Migration

-- Check record counts
SELECT 
  'clients' as table_name, COUNT(*) as count FROM public.clients
UNION ALL
SELECT 'locations', COUNT(*) FROM public.locations
UNION ALL
SELECT 'reviews', COUNT(*) FROM public.reviews;

-- Check sample data
SELECT * FROM public.clients LIMIT 5;
SELECT * FROM public.reviews LIMIT 5;

Step 6: Test Application

  • ✅ Test login
  • ✅ Test dashboard loading
  • ✅ Test data queries
  • ✅ Verify relationships work

Important Considerations

1. User Authentication

Users are NOT automatically migrated!

Supabase users are in auth.users which requires special handling:

-- Option 1: Manual user creation in target
-- Users must sign up again or be imported via Supabase Auth API

-- Option 2: Export/import auth.users (requires admin access)
-- This is complex and may require Supabase support

2. Foreign Key Constraints

  • Disable foreign key checks during migration if needed:
SET session_replication_role = 'replica';
-- ... run inserts ...
SET session_replication_role = 'origin';

3. UUID Conflicts

If source and target have overlapping UUIDs:

  • Option 1: Generate new UUIDs (breaks relationships)
  • Option 2: Use same UUIDs (recommended)
  • Option 3: Create mapping table

4. Row Level Security (RLS)

After migration, verify RLS policies:

-- Check if RLS is enabled
SELECT tablename, rowsecurity 
FROM pg_tables 
WHERE schemaname = 'public';

-- Re-enable if needed
ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;

Troubleshooting

Error: "Foreign key constraint violation"

  • Solution: Migrate tables in correct order (see Migration Order)

Error: "Duplicate key value"

  • Solution: Use ON CONFLICT DO NOTHING or ON CONFLICT DO UPDATE

Error: "Permission denied"

  • Solution: Use service role key, not anon key

Error: "Column does not exist"

  • Solution: Run COMPLETE_SCHEMA_ADDITIONS.sql first

Slow Migration

  • Solution:
    • Use batch inserts (100-1000 records at a time)
    • Disable indexes temporarily
    • Use COPY instead of INSERT for large datasets

Quick Reference

# Export single table
pg_dump -h source.supabase.co -U postgres -d postgres \
  -t public.clients --data-only > clients.sql

# Import single table
psql -h target.supabase.co -U postgres -d postgres -f clients.sql

# Export all tables
pg_dump -h source.supabase.co -U postgres -d postgres \
  --data-only --schema=public > all_data.sql

# Import all tables
psql -h target.supabase.co -U postgres -d postgres -f all_data.sql

Next Steps

  1. ✅ Run COMPLETE_SCHEMA_ADDITIONS.sql on target database
  2. ✅ Choose migration method based on data size
  3. ✅ Export data from source database
  4. ✅ Import data to target database (in correct order)
  5. ✅ Verify migration success
  6. ✅ Test application functionality

Need Help?

  • Check Supabase documentation for pg_dump/pg_restore
  • Use Supabase Dashboard for small datasets
  • Create application script for complex migrations