Managing database schema migrations is one of the most misunderstood aspects of running self-hosted Supabase. Unlike Supabase Cloud, where migrations are handled through the dashboard and automatically tracked, self-hosted deployments require you to understand the underlying mechanics. Get it wrong, and you'll face sync errors, lost changes, or worse—production outages.
This guide covers everything you need to know about schema migrations for self-hosted Supabase: the tools, the workflow, and the pitfalls that trip up even experienced developers.
Why Migrations Matter for Self-Hosted Supabase
Database migrations are SQL statements that create, update, or delete your database schemas. They serve as a version control system for your database structure, letting you track changes over time and apply them consistently across environments.
For self-hosted Supabase, migrations become critical for several reasons:
Reproducibility: Without migrations, your production database schema exists only in production. If you need to spin up a staging environment or recover from a disaster, you're starting from scratch or relying on incomplete backups.
Team collaboration: When multiple developers work on the same project, migrations ensure everyone applies schema changes in the correct order. No more "it works on my machine" database issues.
Auditability: Migrations create a clear history of what changed, when, and (if you write good commit messages) why. This matters for compliance and debugging.
The challenge? Self-hosted Supabase doesn't automatically run migrations on existing databases when you update your Docker images. This catches many teams off guard.
The Core Problem: Migrations Don't Auto-Run
Here's the scenario that frustrates self-hosted users: You update your Supabase Docker images to get new features. You deploy. And nothing changes in your database.
Why? The official PostgreSQL Docker image only runs initialization scripts when there's no existing data directory. Once your database has data, subsequent container restarts skip all those scripts entirely. You'll see this message in your logs:
PostgreSQL Database directory appears to contain a database; Skipping initialization
This is by design—you don't want your database reinitialized every time you restart a container. But it means schema migrations embedded in newer Supabase versions won't apply automatically.
The solution is to manage migrations explicitly using the Supabase CLI.
Setting Up the Migration Workflow
Prerequisites
You'll need the Supabase CLI installed. If you haven't already set up your self-hosted instance, check out our deployment guide first.
# Install the Supabase CLI npm install -g supabase # Initialize in your project directory supabase init
This creates a supabase/ directory with a migrations/ folder where your migration files will live.
Connecting to Your Self-Hosted Database
Unlike Supabase Cloud projects, self-hosted databases require explicit connection parameters. Use the --db-url flag to connect:
# Format: postgresql://[user]:[password]@[host]:[port]/[database] supabase db push --db-url "postgresql://postgres:your-password@your-server:5432/postgres"
The first time you run a migration command, Supabase creates a supabase_migrations.schema_migrations table to track which migrations have been applied.
Creating Your First Migration
The golden rule: never change the remote database directly. All schema changes should flow through migration files.
Create a new migration:
supabase migration new add_user_profiles
This creates a timestamped file like 20260426120000_add_user_profiles.sql in your supabase/migrations/ directory. Add your SQL:
-- supabase/migrations/20260426120000_add_user_profiles.sql create table if not exists public.user_profiles ( id uuid references auth.users on delete cascade primary key, display_name text, avatar_url text, created_at timestamp with time zone default now(), updated_at timestamp with time zone default now() ); -- Enable RLS alter table public.user_profiles enable row level security; -- Create policies create policy "Users can view their own profile" on public.user_profiles for select using (auth.uid() = id); create policy "Users can update their own profile" on public.user_profiles for update using (auth.uid() = id);
For more on RLS policies, see our Row Level Security guide.
Pushing Migrations to Production
With your migration file ready, push it to your self-hosted database:
supabase db push --db-url "postgresql://postgres:your-password@your-server:5432/postgres"
This command:
- Checks which migrations have already been applied
- Runs only the new migrations in order
- Updates the migration history table
Unlike db reset (which drops everything and starts fresh), db push is safe for production—it only applies what's missing.
Pulling Schema Changes
Sometimes you need to capture existing schema from a database. Maybe you made changes through the Supabase Studio dashboard (we've all done it), or you're onboarding an existing project.
supabase db pull --db-url "postgresql://postgres:your-password@your-server:5432/postgres"
This creates a new migration file containing the current database schema. But here's the catch: any changes you made directly to the database now need to be represented in your migrations folder. Otherwise, future db push operations will fail with sync errors.
Diffing for Incremental Changes
The db diff command is your friend for capturing incremental changes. It compares your target database against a shadow database and generates the SQL needed to sync them:
supabase db diff --db-url "postgresql://postgres:your-password@your-server:5432/postgres" -f new_changes
This uses migra under the hood to detect schema differences and output them as a migration file.
Handling the Declarative Schema Approach
For complex objects like views and functions, traditional migrations become verbose. Every change requires rewriting the entire object. Supabase supports a declarative approach where you define these objects in place:
-- supabase/schemas/public/views/active_users.sql create or replace view public.active_users as select id, email, last_sign_in_at from auth.users where last_sign_in_at > now() - interval '30 days';
Changes to this file are detected and applied automatically. This is particularly useful for:
- Views
- Functions and procedures
- Triggers
- Custom types
Common Pitfalls and How to Avoid Them
Pitfall 1: Direct Database Edits
The most common mistake. You use the Supabase Studio SQL editor to "quickly" add a column. Now your migrations are out of sync, and the next db push fails.
Solution: Always create a migration file, even for small changes. The few extra seconds save hours of debugging.
Pitfall 2: Missing Supabase System Migrations
Your app's supabase/migrations aren't the only migrations that matter. The Supabase PostgreSQL Docker image contains embedded migrations that set up auth, storage, and realtime schemas.
When you upgrade Supabase versions, these internal migrations don't auto-apply. You need to run them manually or understand that your self-hosted instance may be missing features.
Solution: After major version upgrades, compare your schema against a fresh Supabase installation to identify missing system tables or functions. Our version migration guide covers this in detail.
Pitfall 3: Environment Drift
Your local development database drifts from staging which drifts from production. Each environment has slightly different schemas.
Solution: Use the same migration files everywhere. Run db push as part of your CI/CD pipeline. Never apply migrations manually to just one environment.
Pitfall 4: Destructive Migrations Without Rollback Plans
-- Don't do this without a plan drop table users;
Solution: For destructive changes, create a backup first. Consider using our backup and restore procedures to test migrations against a copy of production data.
Integrating Migrations into CI/CD
For production deployments, migrations should be automated. Here's a basic GitHub Actions workflow:
name: Deploy Migrations
on:
push:
branches: [main]
paths:
- 'supabase/migrations/**'
jobs:
migrate:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v4
- name: Setup Supabase CLI
uses: supabase/setup-cli@v1
with:
version: latest
- name: Push migrations
run: |
supabase db push --db-url "${{ secrets.DATABASE_URL }}"
For a complete CI/CD setup, see our CI/CD pipelines guide.
Alternative Tools
While the Supabase CLI works well, some teams prefer dedicated migration tools:
- Atlas: A modern schema management tool that integrates with Supabase
- Flyway: Battle-tested migration runner used by enterprises
- sqitch: Change management for databases with dependency tracking
These tools can replace the CLI's migration functionality while still working with your self-hosted Supabase instance.
Managing Migrations with Supascale
If migration management sounds like overhead you'd rather avoid, Supascale can help. While we don't replace the Supabase CLI for schema migrations (those are developer workflow tools), we handle the operational challenges that make self-hosting complex:
- Automated backups before risky migrations
- One-click restore if a migration goes wrong
- Environment management to keep multiple projects in sync
- Monitoring to catch schema-related performance issues
For teams running multiple self-hosted Supabase projects, the operational burden adds up quickly. Check our pricing page to see how Supascale simplifies management at $39.99 for unlimited projects.
Conclusion
Database schema migrations for self-hosted Supabase require more hands-on management than the cloud version. The key takeaways:
- Use migration files for all schema changes—no exceptions
- The Supabase CLI's
db pushcommand is your primary tool for applying migrations to self-hosted databases - Migrations don't auto-run when you update Docker images—you must push them explicitly
- Integrate migrations into CI/CD to prevent environment drift
- Always have a backup before running destructive migrations
With a solid migration workflow, your self-hosted Supabase database becomes as manageable as any cloud-hosted solution—with the added benefits of full control and predictable costs.