Creating Supabase Migrations from Remote Instance

Recently, I started managing multiple projects and environments with Supabase and was wondering if I would need a tool to manage migrations. Supabase has a fantastic migration tool built into its CLI that you can use locally or with remote instances.

Here's the process of generating and running database migrations with Supabase.

First things first you will need:

Docker installed and running
Supabase CLI installed (npm install -g supabase or brew install supabase/tap/supabase)
Access to your Supabase project

Initialize Supabase (if not already done)

supabase init

Link Your Project

First, you'll need your project's connection details:

Project ID (found in Project Settings > General)
Database Password
Project Reference ID

supabase link --project-ref your-project-ref

When prompted, enter your database password.

Generate Migration

Generate a migration file based on the differences between your local state and the remote database:

supabase db diff --use-migra -f migration-name --linked

Parameters:

--use-migra: Uses migra for more accurate SQL diff generation
-f: Specifies the migration file name
--linked: Uses the linked project instead of local database

The migration will be created in: supabase/migrations/[timestamp]_migration-name.sql

Review the Migration

Always review the generated migration file before applying it:

Check supabase/migrations/ directory
Open the newly created SQL file
Verify the changes match your expectations

Running Migrations

There are two ways to run migrations, depending on your target:

For Remote Database

supabase migration up --linked

This applies migrations to your linked Supabase instance.

For Local Database

supabase migration up

This applies migrations to your local development database.

Testing Migrations Locally

To test your migrations locally:

supabase db reset

This command:

Resets your local database to a clean state
Runs all migrations in order
Helps verify that your migrations work correctly from a fresh install

CLI Reference

Additional useful commands:

# List all linked projects
supabase projects list

# Show migration status
supabase db status

# Verify migrations
supabase db lint

# Generate a new empty migration file
supabase migration new migration-name