Running migrations locally and remotely in Supabase - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ service_behavior

intermediate

What happens when you run supabase db push locally?

You run the command supabase db push on your local machine connected to your Supabase project. What is the expected behavior?

A It deletes all existing migrations and resets the database schema.

B It generates migration files locally but does not apply them to any database.

C It applies all unapplied migrations to the local development database only.

D It applies all unapplied migrations to the remote Supabase database immediately.

💡 Hint

Think about what 'push' means in the context of local development databases.

The supabase db push command applies unapplied migrations to the local development database, allowing you to test schema changes locally before pushing them remotely.

❓ Architecture

intermediate

How should you manage migrations when working with a team on Supabase?

Your team is collaborating on a Supabase project. What is the best practice for managing database migrations to avoid conflicts?

A Only the team lead runs migrations remotely; others do not run migrations locally.

B Each developer runs migrations independently without sharing migration files.

C Delete migration files after applying them to keep the project clean.

D Store migration files in version control and apply migrations sequentially on remote and local databases.

💡 Hint

Think about how version control helps teams coordinate changes.

Storing migration files in version control ensures all team members have the same migration history. Applying migrations sequentially prevents conflicts and keeps databases in sync.

❓ Configuration

advanced

What is the correct way to run migrations on a remote Supabase database?

You want to apply your local migration files to the remote Supabase database. Which command should you use?

A supabase migration up --project-ref <project_ref>

B supabase db reset --remote

C supabase db remote migrate --apply

D supabase db push --remote

💡 Hint

Look for the command that explicitly applies migrations remotely with project reference.

The supabase migration up --project-ref <project_ref> command applies unapplied migrations to the remote database identified by the project reference.

❓ security

advanced

What security risk exists if you run migrations remotely without proper access control?

Consider running migrations on your remote Supabase database without restricting access. What is the main security risk?

A The migrations will run slower due to network latency.

B Unauthorized users could modify the database schema causing data loss or corruption.

C Local migration files might get deleted accidentally.

D The remote database will automatically back up before migration.

💡 Hint

Think about who can run commands on your remote database.

If unauthorized users gain access to run migrations remotely, they can change or delete database structures, risking data integrity and security.

✅ Best Practice

expert

What is the recommended workflow to safely deploy database schema changes in Supabase from local to remote?

Choose the best sequence of steps to safely develop, test, and deploy database schema changes using Supabase migrations.

A Write migration files locally → Run <code>supabase db push</code> locally → Commit migration files → Run <code>supabase migration up --project-ref</code> remotely

B Commit migration files → Run <code>supabase db push</code> locally → Write migration files locally → Run <code>supabase migration up --project-ref</code> remotely

C Run <code>supabase migration up --project-ref</code> remotely → Write migration files locally → Commit migration files → Run <code>supabase db push</code> locally

D Write migration files locally → Run <code>supabase migration up --project-ref</code> remotely → Run <code>supabase db push</code> locally → Commit migration files

💡 Hint

Think about the order of writing, testing locally, committing, then deploying remotely.

The safe workflow is to write migration files locally, apply them to your local database to test, commit the migration files to version control, then apply them to the remote database.

Practice

1. What is the main reason to run database migrations locally before running them remotely?

easy

A. To update the live database directly without testing

B. To test changes safely before applying them to the live database

C. To delete all existing data before migration

D. To backup the remote database automatically

Solution

1. Step 1: Understand migration purpose locally

   Running migrations locally allows you to check if changes work without risk to live data.

2. Step 2: Understand migration purpose remotely

   Running migrations remotely updates the live database after testing is successful.

3. Final Answer:

   To test changes safely before applying them to the live database -> Option B

4. Quick Check:

   Test locally first = Safe migration [OK]

Hint: Always test migrations locally before live update [OK]

Common Mistakes:

• Running migrations directly on live without testing
• Assuming local migrations update remote automatically
• Thinking migrations delete all data by default

2. Which command correctly runs migrations on your local Supabase project?

easy

A. supabase db reset

B. supabase db push

C. supabase db migrate

D. supabase migration run

Solution

1. Step 1: Identify the correct Supabase CLI command

   The command supabase db reset applies all migrations to the local database by resetting it.

2. Step 2: Check other options

   supabase migration run and supabase db migrate are invalid; supabase db push updates the remote database.

3. Final Answer:

   supabase db reset -> Option A

4. Quick Check:

   Local migration command = supabase db reset [OK]

Hint: Use 'supabase db reset' to run and test local migrations [OK]

Common Mistakes:

• Using remote commands like 'supabase db push' for local
• Using incorrect commands like 'supabase migration run'
• Trying to run migrations without Supabase CLI installed

3. Given this sequence of commands, what is the expected result?

supabase db push
supabase db remote commit
supabase db remote apply

medium

A. The commands will cause an error due to invalid syntax

B. Migrations are only applied locally, remote stays unchanged

C. Remote migrations are applied without local testing

D. Migrations are applied locally and then committed and applied remotely

Solution

1. Step 1: Analyze each command

   supabase db push applies local schema changes to remote; supabase db remote commit generates a migration from remote schema differences; supabase db remote apply applies pending migrations on remote.

2. Step 2: Understand the flow

   All commands target the remote database; no local application occurs in this sequence.

3. Final Answer:

   Remote migrations are applied without local testing -> Option C

4. Quick Check:

   db push + remote commit/apply = remote only [OK]

Hint: db push + remote commit/apply affects remote without local changes [OK]

Common Mistakes:

• Thinking 'supabase db push' applies locally
• Expecting local database changes from remote commands
• Believing commands have invalid syntax

4. You ran supabase db reset locally but forgot to run supabase db remote apply. What issue will you face?

medium

A. Local database will reset to remote state

B. Remote database will automatically update

C. Migration files will be deleted

D. Local and remote databases will be out of sync

Solution

1. Step 1: Understand local reset effect

   supabase db reset reapplies all migrations to the local database only.

2. Step 2: Understand remote apply necessity

   Without supabase db remote apply, the remote database remains unchanged, causing out-of-sync databases.

3. Final Answer:

   Local and remote databases will be out of sync -> Option D

4. Quick Check:

   Missing remote apply = databases out of sync [OK]

Hint: Always run remote apply after local reset to sync [OK]

Common Mistakes:

• Assuming remote updates automatically after local reset
• Thinking local reset affects remote database
• Believing migration files get deleted after reset

5. You want to update your live Supabase database with a new table but keep your local database unchanged for now. Which approach is correct?

hard

A. Run supabase db remote apply without running supabase db reset

B. Run supabase db push only

C. Run supabase db push then supabase db remote apply

D. Run supabase db reset locally then supabase db remote apply

Solution

1. Step 1: Understand local vs remote migration commands

   supabase db reset affects local; supabase db remote apply applies pending migrations to remote only.

2. Step 2: Apply only remote changes

   Create/edit migration file for new table, then run supabase db remote apply; skip local commands like supabase db reset.

3. Final Answer:

   Run supabase db remote apply without running supabase db reset -> Option A

4. Quick Check:

   Remote apply only = live DB updated, local unchanged [OK]

Hint: Use remote apply after creating migration file to update only remote [OK]

Common Mistakes:

• Running local reset or push which updates local DB unnecessarily
• Resetting local DB when not needed
• Assuming remote apply requires local reset first