Why migrations version your database schema in Supabase - Visual Breakdown

Process Flow - Why migrations version your database schema

Start Migration

↓

Check Current Schema Version

↓

Compare with Migration Version

↓

If Newer

→Apply Migration Changes

↓

Update Schema Version

↓

End Migration

↓

If Not Newer

→Skip Migration

↓

End Migration

The migration process checks the current schema version, applies changes only if the migration is newer, then updates the version to keep track.

Execution Sample

Supabase

-- Migration script example
BEGIN;
-- Add new column
ALTER TABLE users ADD COLUMN age INT;
-- Update version
INSERT INTO schema_migrations (version) VALUES ('20240601_add_age');
COMMIT;

This migration adds a new column and records its version to track schema changes.

Process Table

Step	Action	Current Version	Migration Version	Decision	Result
1	Start migration	20240530_initial	20240601_add_age	Check versions	Proceed
2	Compare versions	20240530_initial	20240601_add_age	Migration is newer	Apply changes
3	Apply changes	20240530_initial	20240601_add_age	Add column 'age'	Column added
4	Update version	20240530_initial	20240601_add_age	Record new version	Version updated
5	End migration	20240601_add_age	20240601_add_age	Migration complete	Schema up to date
6	Start migration	20240601_add_age	20240530_initial	Check versions	Skip migration
7	End migration	20240601_add_age	20240530_initial	Migration older or same	No changes applied

💡 Migration stops when the migration version is not newer than the current schema version.

Status Tracker

Variable	Start	After Step 2	After Step 4	Final
Current Schema Version	20240530_initial	20240530_initial	20240601_add_age	20240601_add_age
Migration Version	20240601_add_age	20240601_add_age	20240601_add_age	20240601_add_age
Schema Changes Applied	No	No	Yes	Yes

Key Moments - 2 Insights

Why do migrations check the current schema version before applying changes?

To avoid reapplying the same changes or applying older migrations, ensuring the database schema stays consistent as shown in steps 1 and 2 of the execution table.

What happens if a migration version is older than the current schema version?

The migration is skipped to prevent overwriting newer schema changes, as seen in steps 6 and 7.

Visual Quiz - 3 Questions

Look at the execution table, what is the Current Schema Version after step 4?

A 20240601_add_age

B 20240530_initial

C Not updated yet

D Migration version

💡 Hint

Check the 'Current Schema Version' column at step 4 in the execution table.

At which step does the migration decide to skip applying changes?

A Step 5

B Step 6

C Step 2

D Step 3

💡 Hint

Look for the step where the decision is 'Skip migration' in the execution table.

If the migration version was the same as the current version, what would happen?

A Migration applies changes again

B Migration version updates to a new value

C Migration skips applying changes

D Migration fails with error

💡 Hint

Refer to the logic in the concept flow and steps 6-7 in the execution table.

Concept Snapshot

Migrations track schema changes by versioning.
They check current schema version before applying.
Only newer migrations apply changes.
Version updates prevent repeated or outdated changes.
This keeps database schema consistent and safe.

Full Transcript

Migrations version your database schema to keep track of changes over time. When a migration runs, it first checks the current schema version stored in the database. If the migration's version is newer, it applies the schema changes like adding or modifying tables or columns. After applying, it updates the stored version to mark the schema as up to date. If the migration version is older or the same, it skips applying changes to avoid conflicts or repeated work. This process ensures the database schema evolves safely and consistently as your application grows.

Practice

1. Why do migrations use version numbers or timestamps to track database schema changes?

easy

A. To keep changes in the correct order and avoid conflicts

B. To make the database run faster

C. To reduce the size of the database

D. To hide the changes from users

Solution

1. Step 1: Understand the purpose of versioning in migrations

   Version numbers or timestamps help keep track of the order in which changes are applied to the database.

2. Step 2: Recognize the benefit of ordered changes

   Applying changes in the correct order prevents conflicts and ensures the database schema updates safely.

3. Final Answer:

   To keep changes in the correct order and avoid conflicts -> Option A

4. Quick Check:

   Versioning = Order and safety [OK]

Hint: Version numbers keep changes in order to avoid conflicts [OK]

Common Mistakes:

• Thinking versioning speeds up the database
• Believing versioning reduces database size
• Assuming versioning hides changes from users

2. Which of the following is the correct way to name a migration file to ensure proper versioning?

easy

A. 001_create_users_table.sql

B. create_users_table.sql

C. users_table.sql

D. migration.sql

Solution

1. Step 1: Identify naming conventions for migrations

   Migrations should include a version number or timestamp prefix to keep them ordered.

2. Step 2: Compare options

   Only 001_create_users_table.sql uses a numeric prefix to version the migration file properly.

3. Final Answer:

   001_create_users_table.sql -> Option A

4. Quick Check:

   Number prefix = correct versioning [OK]

Hint: Use numbers or timestamps at start of migration file names [OK]

Common Mistakes:

• Omitting version numbers in file names
• Using generic names without order
• Naming files without clear versioning

3. Given these migration files applied in order: 001_init.sql, 002_add_email.sql, 003_update_password.sql. What happens if you try to apply 003_update_password.sql before 002_add_email.sql?

medium

A. The migration will apply successfully without issues

B. The database will automatically reorder the migrations

C. The migration will fail because the schema is not in the expected state

D. The migration will skip and do nothing

Solution

1. Step 1: Understand migration order importance

   Migrations must be applied in the correct order to keep the database schema consistent.

2. Step 2: Analyze applying migration 003 before 002

   Applying 003 before 002 means the database misses changes from 002, causing errors or failure.

3. Final Answer:

   The migration will fail because the schema is not in the expected state -> Option C

4. Quick Check:

   Out-of-order migration = failure [OK]

Hint: Always apply migrations in version order to avoid errors [OK]

Common Mistakes:

• Assuming migrations auto-reorder
• Thinking migrations can skip dependencies
• Believing migrations always succeed

4. You see this error when running migrations: ERROR: relation "users" does not exist. What is the most likely cause related to migration versioning?

medium

A. The database is full and cannot create new tables

B. A migration that creates the users table was not applied before this migration

C. The migration file has a syntax error

D. The users table was deleted manually after migration

Solution

1. Step 1: Understand the error meaning

   The error means the migration expects a users table that does not exist yet.

2. Step 2: Connect error to migration order

   If the migration creating the users table was not applied first, later migrations fail due to missing table.

3. Final Answer:

   A migration that creates the users table was not applied before this migration -> Option B

4. Quick Check:

   Missing prior migration = table not found error [OK]

Hint: Check if earlier migrations creating tables were applied [OK]

Common Mistakes:

• Blaming database storage issues
• Assuming syntax error without checking order
• Ignoring migration sequence

5. Your team is working on a Supabase project with multiple developers. Why is it important that migrations are versioned and shared in the project repository?

hard

A. To speed up the database performance during development

B. To allow each developer to create their own independent database schema

C. Because Supabase requires versioned migrations to run queries

D. So all developers apply database changes in the same order and avoid conflicts

Solution

1. Step 1: Understand team collaboration on database schema

   Multiple developers need to keep the database schema consistent to avoid conflicts.

2. Step 2: Role of versioned migrations in collaboration

   Versioned migrations shared in the repository ensure everyone applies changes in the same order safely.

3. Final Answer:

   So all developers apply database changes in the same order and avoid conflicts -> Option D

4. Quick Check:

   Shared versioned migrations = team sync [OK]

Hint: Share versioned migrations to keep team databases consistent [OK]

Common Mistakes:

• Thinking developers should have independent schemas
• Believing versioning is only for Supabase requirements
• Assuming versioning improves performance directly