Rolling back migrations in Supabase - Mini Project: Build & Apply

Rolling Back Migrations in Supabase

📖 Scenario: You are managing a Supabase project where you have applied database schema changes using migrations. Sometimes, you need to undo a migration to fix mistakes or revert to a previous state.Imagine you deployed a migration that added a new table, but you realized it was incorrect. You want to roll back that migration safely.

🎯 Goal: Learn how to roll back the last applied migration in Supabase using the CLI commands and understand the migration files involved.

📋 What You'll Learn

Create a migration file with a specific name

Add a configuration variable for the migration directory

Use the Supabase CLI command to roll back the last migration

Verify the rollback command is correctly configured

💡 Why This Matters

🌍 Real World

Rolling back migrations is common when a database change causes errors or needs adjustment. It helps maintain data integrity and application stability.

💼 Career

Database migration management is a key skill for backend developers, DevOps engineers, and cloud architects working with modern cloud databases like Supabase.

1

Create a migration file named 001_create_users_table.sql

Create a migration file called 001_create_users_table.sql with the following SQL content exactly: CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL);

Supabase

# Create the migration file named 001_create_users_table.sql with the specified SQL content
# Your code here

Hint

Migration files in Supabase are SQL files named with a number prefix and description.

2

Add a configuration variable MIGRATION_DIR set to ./supabase/migrations

Add a variable called MIGRATION_DIR and set it to the string "./supabase/migrations" to specify where migration files are stored.

Supabase

001_create_users_table.sql:
CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL);

MIGRATION_DIR = "./supabase/migrations"
# Your code here

Hint

This variable helps you refer to the migrations folder easily in commands.

3

Use the Supabase CLI command to roll back the last migration

Write the Supabase CLI command to roll back the last migration using the supabase db reset --project-ref command. Use the exact command: supabase db reset --project-ref your-project-ref --force

Supabase

001_create_users_table.sql:
CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL);

MIGRATION_DIR = "./supabase/migrations"

# Write the rollback command below
# Your code here

Hint

The db reset command rolls back all migrations and reapplies them. The --force flag skips confirmation.

4

Verify the rollback command includes the correct project reference

Ensure the rollback command includes the project reference your-project-ref as shown: supabase db reset --project-ref your-project-ref --force

Supabase

001_create_users_table.sql:
CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL);

MIGRATION_DIR = "./supabase/migrations"

supabase db reset --project-ref your-project-ref --force

# Verify the command correctness
# Your code here

Hint

Double-check the command to avoid mistakes when rolling back migrations.

Practice

1. What does the command supabase db rollback do?

easy

A. It creates a new migration file.

B. It undoes the last applied database migration.

C. It applies all pending migrations to the database.

D. It deletes the entire database.

Solution

1. Step 1: Understand the purpose of rollback

   Rollback means undoing changes, so this command reverses migrations already applied.

2. Step 2: Identify the command effect

   supabase db rollback specifically undoes the last migration applied to the database.

3. Final Answer:

   It undoes the last applied database migration. -> Option B

4. Quick Check:

   Rollback = undo last migration [OK]

Hint: Rollback means undo last migration applied [OK]

Common Mistakes:

• Confusing rollback with applying migrations
• Thinking rollback creates new migrations
• Assuming rollback deletes the database

2. Which of the following is the correct syntax to rollback the last 3 migrations using Supabase CLI?

easy

A. supabase db rollback --steps 3

B. supabase db rollback -n 3

C. supabase rollback db 3

D. supabase db undo 3

Solution

1. Step 1: Recall the rollback command syntax

   The Supabase CLI uses --steps to specify how many migrations to rollback.

2. Step 2: Match the correct option

   supabase db rollback --steps 3 uses supabase db rollback --steps 3, which is the correct syntax.

3. Final Answer:

   supabase db rollback --steps 3 -> Option A

4. Quick Check:

   Use --steps to rollback multiple migrations [OK]

Hint: Use --steps to specify rollback count [OK]

Common Mistakes:

• Using incorrect flags like -n instead of --steps
• Wrong command order like 'rollback db'
• Using 'undo' instead of 'rollback'

3. Given the following commands run in order:
supabase db push
supabase db push
supabase db rollback --steps 1
What is the state of migrations after these commands?

medium

A. All migrations are rolled back except the last one.

B. No migrations are applied.

C. Both migrations are still applied.

D. Only the first migration is applied.

Solution

1. Step 1: Apply two migrations

   Running supabase db push twice applies two migrations in order.

2. Step 2: Rollback one migration

   The rollback with --steps 1 undoes the last applied migration, leaving the first migration applied.

3. Final Answer:

   Only the first migration is applied. -> Option D

4. Quick Check:

   Rollback 1 step removes last migration applied [OK]

Hint: Rollback with --steps 1 undoes last migration only [OK]

Common Mistakes:

• Assuming rollback removes all migrations
• Thinking rollback does not affect applied migrations
• Confusing push and rollback order

4. You run supabase db rollback --steps 2 but get an error saying no migrations to rollback. What is the most likely cause?

medium

A. There are fewer than 2 migrations applied to rollback.

B. The migrations folder is missing.

C. The database connection is not configured.

D. Rollback command requires a --force flag.

Solution

1. Step 1: Understand rollback error

   The error indicates no migrations to rollback, meaning none or fewer than requested are applied.

2. Step 2: Identify cause

   If fewer than 2 migrations are applied, trying to rollback 2 steps causes this error.

3. Final Answer:

   There are fewer than 2 migrations applied to rollback. -> Option A

4. Quick Check:

   Rollback steps > applied migrations causes error [OK]

Hint: Check applied migrations count before rollback steps [OK]

Common Mistakes:

• Assuming rollback always works without applied migrations
• Thinking rollback needs --force flag
• Ignoring database connection issues

5. You have applied 5 migrations but want to rollback only the 3rd migration without affecting others. Which approach is best?

hard

A. Delete the 3rd migration file and run supabase db push.

B. Use supabase db rollback --steps 3 to rollback only the 3rd migration.

C. Rollback all migrations from the 3rd onwards, then reapply the ones after it.

D. Directly edit the database to remove changes from the 3rd migration.

Solution

1. Step 1: Understand migration order

   Migrations apply in order; you cannot rollback a middle migration without undoing later ones first.

2. Step 2: Plan rollback and reapply

   Rollback migrations after the 3rd (steps 2), then rollback the 3rd, then reapply the later migrations.

3. Final Answer:

   Rollback all migrations from the 3rd onwards, then reapply the ones after it. -> Option C

4. Quick Check:

   Rollback middle migration requires undoing later ones first [OK]

Hint: Undo later migrations first, then rollback target migration [OK]

Common Mistakes:

• Trying to rollback a single middle migration directly
• Deleting migration files without rollback
• Editing database manually causing inconsistency