Creating migrations in Supabase - Performance & Efficiency

Time Complexity: Creating migrations

O(n)

Understanding Time Complexity

When we create migrations in Supabase, we want to know how the time it takes grows as we add more changes.

We ask: How does the work increase when we add more migration steps?

Scenario Under Consideration

Analyze the time complexity of the following migration creation process.

// Create a new migration
const { data, error } = await supabase.rpc('create_migration', {
  migration_name: 'add_users_table',
  sql_commands: 'CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT);'
});

// Apply migration
const { data: applyData, error: applyError } = await supabase.rpc('apply_migration', {
  migration_id: data.id
});

This sequence creates a migration record and applies it to the database.

Identify Repeating Operations

Look at what repeats when creating multiple migrations.

• Primary operation: Calling the remote procedure to create and apply each migration.
• How many times: Once per migration step added.

How Execution Grows With Input

Each migration requires one create call and one apply call.

Input Size (n)	Approx. API Calls/Operations
10	20 calls (10 create + 10 apply)
100	200 calls (100 create + 100 apply)
1000	2000 calls (1000 create + 1000 apply)

Pattern observation: The number of calls grows directly with the number of migrations.

Final Time Complexity

Time Complexity: O(n)

This means the time to create and apply migrations grows in a straight line as you add more migrations.

Common Mistake

[X] Wrong: "Creating multiple migrations happens all at once, so time stays the same no matter how many migrations."

[OK] Correct: Each migration requires separate calls and processing, so time adds up with each one.

Interview Connect

Understanding how migration steps add up helps you plan database changes efficiently and shows you can think about scaling processes clearly.

Self-Check

"What if we batch multiple migration commands into one call? How would the time complexity change?"

Practice

1. What is the main purpose of creating migrations in Supabase?

easy

A. To save and track changes made to the database structure

B. To create user accounts automatically

C. To deploy frontend code to the server

D. To monitor server uptime and performance

Solution

1. Step 1: Understand what migrations do

   Migrations are files that record changes to the database schema over time.

2. Step 2: Identify the purpose in Supabase context

   Supabase uses migrations to save and track database structure changes so they can be applied consistently.

3. Final Answer:

   To save and track changes made to the database structure -> Option A

4. Quick Check:

   Migrations = track database changes [OK]

Hint: Migrations always track database structure changes [OK]

Common Mistakes:

• Confusing migrations with user management
• Thinking migrations deploy frontend code
• Assuming migrations monitor server health

2. Which command creates a new migration file in Supabase?

easy

A. supabase migration up

B. supabase db push

C. supabase migration new

D. supabase start migration

Solution

1. Step 1: Recall Supabase migration commands

   The command to create a new migration file is specifically designed to generate the file for changes.

2. Step 2: Match command to action

   supabase migration new creates a new migration file, while others apply or push changes.

3. Final Answer:

   supabase migration new -> Option C

4. Quick Check:

   Create migration file = supabase migration new [OK]

Hint: New migration files use 'migration new' command [OK]

Common Mistakes:

• Using 'db push' to create migration files
• Confusing 'migration up' with creation
• Typing incorrect commands like 'start migration'

3. After creating a migration file, which command applies the changes to your remote database?

medium

A. supabase migration up

B. supabase db push

C. supabase migration new

D. supabase db pull

Solution

1. Step 1: Understand commands for applying migrations

   supabase db push applies schema changes to the remote database by pushing local migrations.

2. Step 2: Differentiate commands

   supabase migration up applies migrations locally, while supabase db push pushes changes remotely.

3. Final Answer:

   supabase db push -> Option B

4. Quick Check:

   Apply remotely = db push [OK]

Hint: Use 'supabase db push' to apply migrations remotely [OK]

Common Mistakes:

• Using 'supabase migration up' to apply migrations remotely
• Confusing 'migration new' as apply command
• Thinking 'db pull' applies migrations

4. You ran supabase migration up but got an error saying migrations are missing. What is the likely cause?

medium

A. You used the wrong command to apply locally

B. You need to restart the Supabase server

C. Your database connection is offline

D. You forgot to create migration files before applying

Solution

1. Step 1: Analyze the error about missing migrations

   The error indicates no migration files exist to apply locally.

2. Step 2: Identify the cause

   This usually happens if migration files were never created with supabase migration new.

3. Final Answer:

   You forgot to create migration files before applying -> Option D

4. Quick Check:

   Missing migrations = no files created [OK]

Hint: Create migration files before applying locally [OK]

Common Mistakes:

• Assuming connection issues cause missing migrations
• Restarting server without creating files
• Confusing local and remote apply commands

5. You want to update your remote Supabase database with new schema changes. Which sequence of commands should you run?

hard

A. supabase migration new -> supabase start -> supabase migration up

B. supabase db push -> supabase migration new -> supabase migration up

C. supabase migration up -> supabase migration new -> supabase db push

D. supabase migration new -> supabase migration up -> supabase db push

Solution

1. Step 1: Create migration files first

   Use supabase migration new to save schema changes as migration files.

2. Step 2: Apply changes locally

   Run supabase start to update your local database with these changes.

3. Step 3: Apply migrations remotely

   Finally, run supabase migration up to update the remote database.

4. Final Answer:

   supabase migration new -> supabase start -> supabase migration up -> Option A

5. Quick Check:

   Create -> local start -> remote up [OK]

Hint: Create migration, start locally, then up remotely [OK]

Common Mistakes:

• Applying migrations before creating files
• Applying remotely before local test
• Mixing order of commands