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:postgres@127.0.0.1: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: 'test@example.com', role: 'admin' },
    { email: 'user@example.com', 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-re

---

*Content truncated.*