supabase-multi-env-setup

Supabase Multi-Environment Setup

Overview

Production Supabase deployments require separate projects per environment — each with its own URL, API keys, database, and RLS policies. This skill configures a three-tier environment architecture (local dev, staging, production) with safe migration promotion via supabase db push, environment-aware createClient initialization, database branching for preview deployments, and CI/CD pipelines that prevent accidental cross-environment operations.

When to use: Setting up a new project with multiple environments, migrating from a single-project setup to multi-env, adding staging to an existing dev/prod split, or configuring preview environments with database branching.

Prerequisites

• Three separate Supabase projects created at supabase.com/dashboard (dev, staging, production)
• Supabase CLI installed: npm install -g supabase or npx supabase --version
• @supabase/supabase-js v2+ installed in your project
• Node.js 18+ with framework that supports .env files (Next.js, Nuxt, SvelteKit, etc.)
• A secret management solution for CI (GitHub Actions Secrets, Vercel env vars, etc.)

Instructions

Step 1: Environment Files and Project Layout

Create one Supabase CLI project with shared migrations and per-environment credential files. Each .env.* file points to a different Supabase project.

Project structure:

my-app/
├── supabase/
│   ├── config.toml              # Local CLI config
│   ├── migrations/              # Shared migrations (all envs use the same schema)
│   │   └── 20260101000000_initial.sql
│   ├── seed.sql                 # Dev-only seed data (runs on db reset only)
│   └── functions/               # Edge Functions (deployed per env)
├── .env.local                   # Local dev → supabase start
├── .env.staging                 # Staging project credentials
├── .env.production              # Production project credentials
└── .gitignore                   # Must include .env.staging, .env.production

Environment files:

# .env.local — local development (safe defaults from supabase start)
NEXT_PUBLIC_SUPABASE_URL=http://127.0.0.1:54321
NEXT_PUBLIC_SUPABASE_ANON_KEY=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZS1kZW1vIiwicm9sZSI6ImFub24iLCJleHAiOjE5ODM4MTI5OTZ9.CRXP1A7WOeoJeXxjNni43kdQwgnWNReilDMblYTn_I0
SUPABASE_SERVICE_ROLE_KEY=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZS1kZW1vIiwicm9sZSI6InNlcnZpY2Vfcm9sZSIsImV4cCI6MTk4MzgxMjk5Nn0.EGIM96RAZx35lJzdJsyH-qQwv8Hdp7fsn3W0YpN81IU
DATABASE_URL=postgresql://postgres:[email protected]:54322/postgres
SUPABASE_ENV=local

# .env.staging — staging project
NEXT_PUBLIC_SUPABASE_URL=https://<staging-ref>.supabase.co
NEXT_PUBLIC_SUPABASE_ANON_KEY=eyJ...staging-anon-key
SUPABASE_SERVICE_ROLE_KEY=eyJ...staging-service-key
DATABASE_URL=postgres://postgres.<staging-ref>:<password>@aws-0-<region>.pooler.supabase.com:6543/postgres
SUPABASE_ENV=staging

# .env.production — production project (NEVER commit this file)
NEXT_PUBLIC_SUPABASE_URL=https://<prod-ref>.supabase.co
NEXT_PUBLIC_SUPABASE_ANON_KEY=eyJ...prod-anon-key
SUPABASE_SERVICE_ROLE_KEY=eyJ...prod-service-key
DATABASE_URL=postgres://postgres.<prod-ref>:<password>@aws-0-<region>.pooler.supabase.com:6543/postgres
SUPABASE_ENV=production

Critical .gitignore entries:

.env.staging
.env.production
# .env.local is safe to commit (contains only local dev keys)

Link each environment to the CLI:

# Local development
npx supabase start

# Link staging (stores ref in supabase/.temp/project-ref)
npx supabase link --project-ref <staging-ref>

# Link production (re-links, overwriting staging ref)
npx supabase link --project-ref <prod-ref>

Note: The CLI can only link one project at a time. Switch between environments by re-running supabase link with the target project ref before any db push or functions deploy operation.

Step 2: Environment-Aware Client and Safeguards

Build a createClient wrapper that selects the correct URL and keys based on the active environment, plus production safeguards that block destructive operations.

Environment detection (lib/env.ts):

export type Environment = 'local' | 'staging' | 'production';

export function getEnvironment(): Environment {
  // Explicit env var takes priority
  const explicit = process.env.SUPABASE_ENV;
  if (explicit === 'local' || explicit === 'staging' || explicit === 'production') {
    return explicit;
  }

  // Fallback: detect from URL
  const url = process.env.NEXT_PUBLIC_SUPABASE_URL ?? '';
  if (url.includes('127.0.0.1') || url.includes('localhost')) return 'local';
  if (url.includes('staging')) return 'staging';
  return 'production';
}

export function isProduction(): boolean {
  return getEnvironment() === 'production';
}

export function requireNonProduction(operation: string): void {
  if (isProduction()) {
    throw new Error(
      `[BLOCKED] "${operation}" is not allowed in production. ` +
      `Current SUPABASE_ENV=${process.env.SUPABASE_ENV}`
    );
  }
}

Supabase client factory (lib/supabase.ts):

import { createClient, type SupabaseClient } from '@supabase/supabase-js';
import type { Database } from './database.types';
import { getEnvironment } from './env';

// Browser client (uses anon key, respects RLS)
export function createBrowserClient(): SupabaseClient<Database> {
  const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL!;
  const supabaseAnonKey = process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!;

  return createClient<Database>(supabaseUrl, supabaseAnonKey, {
    auth: {
      autoRefreshToken: true,
      persistSession: true,
    },
    global: {
      headers: { 'x-environment': getEnvironment() },
    },
  });
}

// Server client (uses service role key, bypasses RLS)
export function createServerClient(): SupabaseClient<Database> {
  const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL!;
  const serviceRoleKey = process.env.SUPABASE_SERVICE_ROLE_KEY!;

  return createClient<Database>(supabaseUrl, serviceRoleKey, {
    auth: {
      autoRefreshToken: false,
      persistSession: false,
    },
  });
}

Production safeguards:

import { requireNonProduction } from './env';
import { createServerClient } from './supabase';

// Seed data — only runs in local/staging
export async function seedTestData(): Promise<void> {
  requireNonProduction('seedTestData');
  const supabase = createServerClient();
  await supabase.from('test_users').insert([
    { email: '[email protected]', role: 'admin' },
    { email: '[email protected]', role: 'member' },
  ]);
}

// Destructive reset — only runs in local
export async function resetDatabase(): Promise<void> {
  requireNonProduction('resetDatabase');
  const supabase = createServerClient();
  await supabase.rpc('truncate_all_tables');
}

Environment-specific RLS policies:

-- supabase/migrations/20260115000000_env_rls.sql
-- Allow broader access in staging for QA testing
CREATE POLICY "staging_read_all" ON public.profiles
  FOR SELECT
  USING (
    current_setting('app.environment', true) = 'staging'
    OR auth.uid() = id
  );

-- Set environment in each request via the x-environment header
-- or via a Postgres config parameter in your connection string

Step 3: Migration Promotion and Database Branching

Promote migrations through environments (local -> staging -> production) and use database branching for preview deployments.

Migration promotion workflow:

# 1. Create migration locally
npx supabase migration new add_profiles_table
# Edit: supabase/migrations/20260120000000_add_profiles_table.sql

# 2. Test locally with full reset
npx supabase db reset          # Applies all migrations + seed.sql
npx supabase test db           # Run pgTAP tests if configured

# 3. Push to staging
npx supabase link --project-ref <staging-ref>
npx supabase db push           # Applies only new migrations
# Run integration tests against staging URL

# 4. Push to production (after staging verification)
npx supabase link --project-ref <prod-ref>
npx supabase db push           # Same migrations, production database
# Verify with health check endpoint

# 5. Generate types from the canonical source
npx supabase gen types typescript --local > lib/database.types.ts
# Or from linked project:
# npx supabase gen types typescript --linked > lib/database.types.ts

Database branching for preview environments:

# Create a branch for a feature (requires Supabase Pro plan)
npx supabase branches create feature-user-profiles \
  --project-ref <staging-ref>

# Each branch gets its own:
# - Database with current migrations applied
# - Unique API URL and keys
# - Isolated storage buckets

# List active branches
npx supabase branches list --project-ref <staging-ref>

# Connect preview deployment to the branch
# In your CI (e.g., Vercel preview deploys):
NEXT_PUBLIC_SUPABASE_URL=https://<branch-ref>.supabase.co
NEXT_PUBLIC_SUPABASE_ANON_KEY=<branch-anon-key>

# Delete branch after merge
npx supabase branches delete feature-user-profiles \
  --project-ref <staging-ref>

CI/CD per-environment deployment (.github/workflows/deploy.yml):

name: Deploy Supabase
on:
  push:
    branches: [develop, main]

jobs:
  deploy-staging:
    if: github.ref == 'refs/heads/develop'
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: supabase/setup-cli@v1
        with:
          version: latest
      - name: Push migrations to staging
        run: |
          npx supabase link --project-ref ${{ secrets.STAGING_PROJECT_REF }}
          npx supabase db push
        env:
          SUPABASE_ACCESS_TOKEN: ${{ secrets.SUPABASE_ACCESS_TOKEN }}
          SUPABASE_DB_PASSWORD: ${{ secrets.STAGING_DB_PASSWORD }}
      - name: Deploy Edge Functions
        run: npx supabase functions deploy --project-ref ${{ secrets.STAGING_PROJECT_REF }}
        env:
          SUPABASE_ACCESS_TOKEN: ${{ secrets.SUPABASE_ACCESS_TOKEN }}

  deploy-production:
    if: github.ref == 'refs/heads/main'
    runs-on: ubuntu-latest
    environment: production   # Requires approval in GitHub
    steps:
      - uses: actions/checkout@v4
      - uses: supabase/setup-cli@v1
        with:
          version: latest
      - name: Push migrations to production
        run: |
          npx supabase link --project-ref ${{ secrets.PROD_PROJECT_REF }}
          npx supabase db push
        env:
          SUPABASE_ACCESS_TOKEN: ${{ secrets.SUPABASE_ACCESS_TOKEN }}
          SUPABASE_DB_PASSWORD: ${{ secrets.PROD_DB_PASSWORD }}
      - name: Deploy Edge Functions
        run: npx supabase functions deploy --project-ref ${{ secrets.PROD_PROJECT_REF }}
        env:
          SUPABASE_ACCESS_TOKEN: ${{ secrets.SUPABASE_ACCESS_TOKEN }}

Environment-specific seed data:

-- supabase/seed.sql (runs ONLY on `supabase db reset`, never in production)
INSERT INTO public.profiles (id, email, role) VALUES
  ('00000000-0000-0000-0000-000000000001', '[email protected]', 'admin'),
  ('00000000-0000-0000-0000-000000000002', '[email protected]', 'member');

-- Insert test data for local development
INSERT INTO public.projects (name, owner_id) VALUES
  ('Test Project', '00000000-0000-0000-0000-000000000001');

Output

After completing this skill, you will have:

• Three isolated Supabase projects — each environment has its own URL, API keys, database, and storage
• Environment-specific .env files — .env.local, .env.staging, .env.production with correct credentials
• Environment-aware createClient — browser and server clients auto-configured from env vars with x-environment header tracking
• Production safeguards — requireNonProduction() guard blocks destructive operations outside local/staging
• Migration promotion pipeline — supabase db push promotes schema changes local -> staging -> production
• Database branching — preview environments get isolated database branches (Pro plan)
• CI/CD workflows — GitHub Actions deploys migrations and Edge Functions per environment with approval gates for production
• Generated TypeScript types — database.types.ts generated from local or linked project schema

Error Handling

Error	Cause	Solution
Cannot find project ref	CLI not linked to a project	Run npx supabase link --project-ref <ref> before db push
Migration has already been applied	Re-running an existing migration	Check supabase_migrations.schema_migrations table; migrations are idempotent by ref
Permission denied for schema public	Wrong database password	Verify SUPABASE_DB_PASSWORD matches the project's database password in dashboard
Seed data appeared in production	Ran supabase db reset on prod	seed.sql only runs on db reset — never reset production; use db push instead
Wrong environment keys in client	.env file mismatch	Check SUPABASE_ENV var and verify URL matches expected project ref
Branch creation failed	Free plan or branching not enabled	Database branching requires Supabase Pro plan; enable in project settings
Migration drift between envs	Skipped staging promotion	Always promote through staging first; compare with supabase migration list per project
Type generation mismatch	Types generated from wrong env	Regenerate from local (--local) or re-link to the canonical environment

Examples

Example 1 — Quick three-env bootstrap:

# Initialize Supabase in existing project
npx supabase init

# Start local
npx supabase start
# Copy output keys to .env.local

# Create staging + production projects in dashboard
# Copy their URLs and keys to .env.staging / .env.production

# Create first migration
npx supabase migration new create_users
# Edit the migration, then:
npx supabase db reset  # Test locally

# Promote to staging
npx supabase link --project-ref abcdefghijklmnop
npx supabase db push

# Promote to production
npx supabase link --project-ref qrstuvwxyz123456
npx supabase db push

Example 2 — Next.js middleware for environment validation:

// middleware.ts
import { NextResponse } from 'next/server';
import type { NextRequest } from 'next/server';

export function middleware(request: NextRequest) {
  const response = NextResponse.next();

  // Add environment header for observability
  const env = process.env.SUPABASE_ENV ?? 'unknown';
  response.headers.set('x-supabase-env', env);

  // Block admin routes in production unless authenticated
  if (env === 'production' && request.nextUrl.pathname.startsWith('/admin/seed')) {
    return NextResponse.json({ error: 'Not available in production' }, { status: 403 });
  }

  return response;
}

Example 3 — Verify environment before destructive operations:

import { getEnvironment, requireNonProduction } from '@/lib/env';

async function adminResetHandler(req: Request) {
  const env = getEnvironment();
  console.log(`[admin-reset] Running in ${env} environment`);

  requireNonProduction('admin-reset');

  // Safe to proceed — we're in local or staging
  const { error } = await supabase.rpc('reset_test_data');
  if (error) throw error;

  return Response.json({ status: 'reset complete', environment: env });
}

Resources

• Managing Environments — Supabase Docs
• Database Migrations — Supabase Docs
• Database Branching — Supabase Docs
• Supabase CLI Reference
• createClient — @supabase/supabase-js
• GitHub Actions with Supabase
• 12-Factor App — Config

Next Steps

• For authentication patterns across environments, see supabase-auth-storage-realtime-core
• For RLS policy testing and validation, see supabase-policy-guardrails
• For local development workflow optimization, see supabase-local-dev-loop
• For monitoring and observability across environments, see supabase-observability