Running migrations locally and remotely in Supabase - Deep Dive

Overview - Running migrations locally and remotely

What is it?

Running migrations means applying changes to a database's structure, like adding tables or columns, in a controlled way. Doing this locally means applying these changes on your own computer's database, while remotely means applying them on a cloud database. This process helps keep your database organized and consistent as your app grows.

Why it matters

Without migrations, developers would have to manually change databases, which is slow and error-prone. This can cause apps to break or lose data. Running migrations both locally and remotely ensures that everyone on a team works with the same database setup, and that the live app's database stays up to date safely.

Where it fits

Before learning migrations, you should understand basic databases and SQL commands. After mastering migrations, you can explore continuous integration and deployment, where migrations run automatically during app updates.

Mental Model

Core Idea

Migrations are step-by-step instructions that safely change a database's structure in the same order everywhere.

Think of it like...

Imagine building a LEGO model with instructions. Running migrations is like following those instructions piece by piece, first on your own table (local), then on a friend's table far away (remote), so both models look the same.

┌───────────────┐       ┌───────────────┐
│ Local DB      │       │ Remote DB     │
│ (Developer)   │       │ (Cloud)       │
└──────┬────────┘       └──────┬────────┘
       │                       │
       │ Apply migration 1     │
       ├──────────────────────▶
       │                       │
       │ Apply migration 2     │
       ├──────────────────────▶
       │                       │
       │ ...                   │
       └──────────────────────▶

Build-Up - 7 Steps

1

Foundation

What are database migrations

Concept: Introduce the idea of migrations as controlled database changes.

A database stores app data in tables. When apps change, the database structure must change too. Migrations are files that describe these changes step-by-step, like adding a new table or column. They help track and apply changes safely.

Result

You understand migrations as a way to update databases without losing data or causing errors.

Understanding migrations prevents random, risky database edits that can break apps or lose data.

2

Foundation

Local vs remote databases explained

Concept: Explain the difference between local and remote databases.

A local database runs on your own computer. It’s fast and private for testing. A remote database runs on a server in the cloud, shared by many users and apps. Changes must be carefully applied to keep data safe and consistent.

Result

You can tell when to use local or remote databases and why both matter.

Knowing the difference helps you test changes safely before affecting real users.

3

Intermediate

Running migrations locally with Supabase CLI

🤔Before reading on: do you think running migrations locally changes your cloud database? Commit to your answer.

Concept: Learn how to apply migrations on your local Supabase database using the command line.

Supabase CLI lets you run commands like `supabase db push` to apply migrations locally. This updates your local database schema to match your migration files. It does NOT affect the remote database until you push changes there.

Result

Your local database structure matches your migration files, ready for testing.

Understanding local migration lets you safely test database changes before sharing them.

4

Intermediate

Running migrations remotely on Supabase cloud

🤔Before reading on: do you think remote migrations run automatically after local ones? Commit to your answer.

Concept: Learn how to apply migrations to the remote Supabase database in the cloud.

To update the remote database, you use commands like `supabase db remote commit` or deploy migrations through CI/CD pipelines. This applies your migration files to the live database, updating its structure for all users.

Result

The cloud database schema updates safely to match your migrations, affecting the live app.

Knowing how to run remote migrations ensures your live app stays stable and up to date.

5

Intermediate

Managing migration files and version control

🤔Before reading on: do you think migration files should be edited after applying them remotely? Commit to your answer.

Concept: Explain how migration files are stored and managed in code repositories.

Migration files are saved in your project folder and tracked with version control like Git. Each migration has a timestamp or number to keep order. Once applied remotely, you should not edit old migrations but create new ones for changes.

Result

Your team can share and apply migrations in the correct order without conflicts.

Proper migration file management prevents database conflicts and lost changes.

6

Advanced

Handling migration conflicts and rollbacks

🤔Before reading on: do you think migrations can be undone automatically? Commit to your answer.

Concept: Learn about conflicts when multiple people create migrations and how to undo changes.

If two developers create migrations that conflict, you must resolve them by merging or reordering. Supabase supports rollback commands to undo migrations if something breaks. Planning migration order and backups is critical.

Result

You can fix migration conflicts and safely revert database changes when needed.

Knowing rollback and conflict handling protects your database from errors during updates.

7

Expert

Automating migrations in CI/CD pipelines

🤔Before reading on: do you think automating migrations removes all risks? Commit to your answer.

Concept: Explore how migrations run automatically during app deployment using pipelines.

In professional setups, migrations run automatically in CI/CD pipelines after code merges. This ensures the remote database updates with app changes without manual steps. However, automation needs safeguards like backups and tests to avoid downtime.

Result

Your database updates smoothly with app releases, reducing manual errors and delays.

Understanding automation helps build reliable, fast deployment workflows while managing risks.

Under the Hood

Migrations are SQL scripts or commands stored as files that describe database changes. When run, these scripts execute in order on the database engine, altering tables, columns, indexes, or data. Supabase tracks which migrations have run using a special table, preventing repeats. Locally, migrations run on your local Postgres instance; remotely, they run on the cloud Postgres instance. The CLI or API sends these commands securely to the database server.

Why designed this way?

Migrations were designed to solve the problem of managing database changes safely and consistently across environments. Before migrations, developers manually edited databases, causing errors and inconsistencies. Using ordered scripts with tracking ensures changes apply once and in the right order. Supabase builds on Postgres and adds CLI tools to simplify this process for developers, balancing control and automation.

┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│ Migration 1   │──────▶│ Migration 2   │──────▶│ Migration 3   │
└──────┬────────┘       └──────┬────────┘       └──────┬────────┘
       │                       │                       │
       ▼                       ▼                       ▼
┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│ Local DB      │       │ Remote DB     │       │ Migration Log │
│ (Postgres)    │       │ (Postgres)    │       │ (Tracking)    │
└───────────────┘       └───────────────┘       └───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does running migrations locally automatically update the remote database? Commit to yes or no.

Common Belief: Running migrations locally also updates the remote database automatically.

Reality: Local migrations only change your local database. Remote databases must be updated separately.

Why it matters: Assuming local changes affect remote can cause confusion and bugs when the live app uses an outdated schema.

Quick: Can you safely edit old migration files after they run remotely? Commit to yes or no.

Common Belief: You can edit old migration files anytime to fix mistakes.

Reality: Editing old migrations after applying them remotely can cause inconsistencies and errors. Instead, create new migrations.

Why it matters: Changing old migrations breaks the migration history and can corrupt the database state.

Quick: Are migrations guaranteed to never fail once written? Commit to yes or no.

Common Belief: Once a migration is written, it will always run without problems.

Reality: Migrations can fail due to conflicts, syntax errors, or unexpected data states. Testing and rollbacks are necessary.

Why it matters: Ignoring failure risks can cause downtime or data loss in production.

Quick: Does automating migrations remove the need for backups? Commit to yes or no.

Common Belief: Automated migrations mean backups are unnecessary.

Reality: Backups are still essential because automated migrations can introduce bugs or data loss.

Why it matters: Skipping backups risks permanent data loss if migrations go wrong.

Expert Zone

1

Migration order matters deeply; even small reordering can cause subtle bugs or data loss.

2

Supabase migration logs track applied migrations but do not prevent manual database changes outside migrations, which can cause drift.

3

Rollback support varies; some migrations are irreversible, so planning and testing are critical before applying.

When NOT to use

Migrations are not suitable for large bulk data transformations or real-time schema changes. For those, consider database refactoring tools or feature flags. Also, avoid migrations for trivial or temporary schema tweaks better handled by views or application logic.

Production Patterns

Teams use branching strategies where migrations are developed in feature branches, tested locally, then merged to main branches triggering CI/CD pipelines that run remote migrations. Backups and monitoring alert teams to migration failures. Some use blue-green deployments to minimize downtime during schema changes.

Connections

Version Control Systems

Migrations build on version control by tracking database changes as code files.

Understanding version control helps grasp how migrations keep database changes organized and reversible.

Continuous Integration / Continuous Deployment (CI/CD)

Migrations integrate into CI/CD pipelines to automate database updates during app deployment.

Knowing CI/CD concepts clarifies how migrations fit into modern automated software delivery.

Project Management

Migrations require coordination like project tasks to avoid conflicts and ensure smooth progress.

Appreciating project management principles helps teams plan and communicate database changes effectively.

Common Pitfalls

#1 Running migrations locally and assuming the remote database is updated.

Wrong approach:

supabase db push
# Assumes remote updated too

Correct approach:

supabase db push
supabase db remote commit
# Local then remote migration

Root cause:

Misunderstanding that local and remote databases are separate environments.

#2 Editing old migration files after they have been applied remotely.

Wrong approach:

-- Edit migration_001_add_users.sql after remote apply
-- Change column type directly

Correct approach:

-- Create new migration file migration_005_change_column_type.sql
-- Apply new migration

Root cause:

Not realizing migration history must be immutable to keep database state consistent.

#3 Skipping backups before running migrations on production.

Wrong approach:

# Run migration directly on production without backup
supabase db remote commit

Correct approach:

# Backup production database first
supabase db remote backup
supabase db remote commit

Root cause:

Underestimating risks of migration failures and data loss.

Key Takeaways

Migrations are step-by-step scripts that safely update database structure in order.

Running migrations locally lets you test changes without affecting the live app.

Remote migrations update the cloud database and must be done carefully to avoid downtime.

Migration files must be managed with version control and never edited after remote application.

Automating migrations in deployment pipelines speeds updates but requires backups and testing to stay safe.