Creating migrations in Supabase - Visual Walkthrough

Process Flow - Creating migrations

Write migration SQL file

↓

Run migration command

↓

Supabase applies changes

↓

Database schema updated

↓

Migration recorded in history

↓

Ready for next migration

This flow shows how you write a migration, run it, and Supabase updates the database and records the change.

Execution Sample

Supabase

supabase migration new add_users_table
-- edit migration SQL file
supabase db push

Create a new migration file, edit it to add a users table, then apply it to update the database.

Process Table

Step	Action	Command/SQL	Result	Notes
1	Create new migration file	supabase migration new add_users_table	New SQL file created	File named with timestamp and description
2	Edit migration file	CREATE TABLE users (id UUID PRIMARY KEY, name TEXT);	SQL ready to run	Defines new users table
3	Run migration	supabase db push	Migration applied	Database schema updated
4	Record migration	Internal Supabase process	Migration logged	Prevents reapplying same migration
5	Verify schema	SELECT * FROM users;	Empty users table exists	Schema change confirmed
6	Exit	No more migrations	Process complete	Ready for next migration

💡 All migration steps completed and database schema updated

Status Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
migration_file	none	created empty file	contains CREATE TABLE SQL	unchanged	unchanged	exists with SQL
database_schema	original schema	unchanged	unchanged	users table added	unchanged	users table present
migration_history	empty	unchanged	unchanged	unchanged	new migration recorded	contains migration record

Key Moments - 3 Insights

Why do we need to edit the migration file after creating it?

Creating the migration file only makes an empty template. You must add SQL commands to define the changes you want, as shown in step 2 of the execution_table.

What happens if you run 'supabase db push' without editing the migration file?

No changes will be applied because the migration file has no SQL commands. The database schema stays the same, as implied between steps 1 and 3.

How does Supabase prevent applying the same migration twice?

Supabase records each applied migration in its history (step 4). This log ensures migrations are only applied once.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result after running 'supabase migration new add_users_table'?

A Database schema updated

B Migration applied

C New SQL file created

D Migration logged

💡 Hint

Check step 1 in the execution_table for the result of the migration new command.

At which step does the database schema get updated with the new users table?

A Step 2

B Step 3

C Step 4

D Step 5

💡 Hint

Look at the 'Result' column in the execution_table to find when the schema changes.

If you forget to add SQL commands to the migration file, what will happen when you run 'supabase db push'?

A Migration applies but no schema changes

B Migration applies and updates schema

C Migration fails with error

D Migration file is deleted

💡 Hint

Refer to key_moments about running migration without editing the file.

Concept Snapshot

Creating migrations in Supabase:
1. Run 'supabase migration new <name>' to create a new migration file.
2. Edit the file to add SQL commands for schema changes.
3. Run 'supabase db push' to apply changes to the database.
4. Supabase records the migration to avoid duplicates.
This process updates your database schema safely and trackably.

Full Transcript

Creating migrations in Supabase involves making a new migration file with 'supabase migration new', editing it to add SQL commands like creating tables, then running 'supabase db push' to apply these changes to the database. Supabase updates the schema and records the migration to prevent reapplying it. This step-by-step process ensures your database evolves in a controlled way.

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