Categories
Vibe Coding

Supabase Development Environment Setup Guide

I have been prototyping a project on Lovable, but am ready to introduce new users to it. However, I don’t want to mess up their data and their experience with my tinkering. I needed to set up a staging environment with a separate database.

I worked with Claude to figure out how to get data from my production Supabase and push it to my new staging Supabase. We went back and forth, but finally figured it out.*

Prerequisites

  • Supabase CLI installed (npm install -g supabase)
  • PostgreSQL client tools (psql)
  • Access to both production and development Supabase projects

Step-by-Step Process

1. Identify Your Projects

You’ll need:

  • Production project – Your live database with real data
  • Development project – Your testing environment (can be empty)

Get the project references from your Supabase dashboard URLs:

  • Production: supabase.com/dashboard/project/[PROD_PROJECT_REF]
  • Development: supabase.com/dashboard/project/[DEV_PROJECT_REF]

2. Get Database Connection Strings

From each project’s dashboard:

  1. Go to Settings → Database
  2. Copy the Direct connection string (not connection pooling)
  3. Note the password for each database

3. Link CLI to Production Database

bashsupabase link --project-ref [PROD_PROJECT_REF]
# Enter your production database password when prompted

4. Export Schema from Production

bashsupabase db dump --file production_schema.sql

5. Export Data from Production

bashsupabase db dump --file production_data.sql --data-only

6. Combine Schema and Data

bashcat production_schema.sql production_data.sql > complete_dump.sql

7. Import to Development Database

bashpsql "postgresql://postgres.[DEV_PROJECT_REF]:[DEV_PASSWORD]@aws-0-us-east-2.pooler.supabase.com:5432/postgres" < complete_dump.sql

Note: Replace the connection string with your actual development database details from step 2.

Common Issues & Solutions

DNS Resolution Errors

If you get hostname lookup errors, try using the Session pooler connection string instead of Direct connection.

Version Mismatch Errors

If pg_dump fails with version mismatch:

bash# Add --no-sync flag
pg_dump --no-sync [connection_string] > dump.sql

Empty Data Dumps

  • Ensure you’re connected to the right project with supabase status
  • Use --data-only flag specifically for data export
  • Verify data exists: psql [connection_string] -c "SELECT COUNT(*) FROM [table_name];"

Verification

After import, verify your development environment:

bash# Connect to dev database
psql "postgresql://[dev_connection_string]"

# Check row counts
SELECT 'teams', COUNT(*) FROM teams
UNION ALL
SELECT 'users', COUNT(*) FROM auth.users;

Pro Tips

  • Always use development environment for testing – never test directly in production
  • Regular syncing – Re-run this process periodically to keep dev data current
  • Separate connections – Keep production and development connection strings clearly labeled
  • Reset dev first – Consider resetting your dev database before importing: supabase db reset

Security Notes

  • Never share production database credentials
  • Use environment variables for connection strings in your applications
  • Ensure development databases have appropriate access restrictions
  • Consider anonymizing sensitive data in development environments

Need help? Check the Supabase CLI documentation or reach out to your team’s database administrator.

* I then asked Claude to generate me a one-pager, based on this experience, that I can share with people that are looking to copy their production database to another environment for safe testing and development. This is that doc.