Creating migrations in Supabase - Mechanics & Internals

Overview - Creating migrations

What is it?

Creating migrations means writing instructions to change your database structure step-by-step. These instructions help add, remove, or change tables and columns safely. Migrations keep track of what changes were made and when, so your database stays organized and consistent. They are especially useful when working with teams or updating apps over time.

Why it matters

Without migrations, changing a database can be risky and confusing. You might lose data or break your app if changes are done manually or inconsistently. Migrations solve this by making changes repeatable and reversible, so everyone’s database matches the app’s needs. This keeps apps reliable and easier to update.

Where it fits

Before learning migrations, you should understand basic databases and tables. After migrations, you can learn about database version control, deployment automation, and continuous integration. Migrations fit in the journey between database basics and advanced cloud deployment.

Mental Model

Core Idea

Migrations are like a step-by-step recipe that safely changes your database structure over time.

Think of it like...

Imagine building a LEGO model with instructions. Each migration is a new step telling you which pieces to add or remove, so the model grows correctly without mistakes.

┌───────────────┐
│ Initial State │
└──────┬────────┘
       │ Apply Migration 1 (Add Table)
       ▼
┌───────────────┐
│ Updated State │
└──────┬────────┘
       │ Apply Migration 2 (Add Column)
       ▼
┌───────────────┐
│ Updated State │
└───────────────┘

Build-Up - 7 Steps

1

Foundation

What is a database migration

Concept: Introduces the basic idea of migrations as instructions to change databases.

A migration is a file or script that tells the database how to change its structure. For example, adding a new table or changing a column type. It helps keep track of these changes so they can be applied in order.

Result

You understand that migrations are organized steps to update a database safely.

Understanding migrations as structured change instructions prevents random, risky database edits.

2

Foundation

Supabase migration basics

Concept: Shows how Supabase uses migration files to manage database changes.

Supabase stores migrations as SQL files in a folder. Each file has commands like CREATE TABLE or ALTER TABLE. Supabase runs these files in order to update the database schema.

Result

You know where migrations live in Supabase and what they look like.

Knowing the file-based nature of Supabase migrations helps you organize and track database changes.

3

Intermediate

Writing your first migration

🤔Before reading on: do you think a migration can only add tables, or can it also remove or change them? Commit to your answer.

Concept: Teaches how to write a migration that adds or modifies database objects.

To write a migration, create a new SQL file with commands like: CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT); You can also ALTER tables to add columns: ALTER TABLE users ADD COLUMN email TEXT; Save the file with a timestamp to keep order.

Result

You can create a migration file that changes the database structure.

Knowing that migrations can add, remove, or change tables lets you manage evolving database needs.

4

Intermediate

Applying and tracking migrations

🤔Before reading on: do you think migrations run automatically or need a command to apply? Commit to your answer.

Concept: Explains how to run migrations and how Supabase tracks which ones ran.

Use the Supabase CLI command `supabase db push` or `supabase db migrate` to apply migrations. Supabase records applied migrations in a special table so it won’t run the same migration twice. This keeps your database in sync with your migration files.

Result

You can apply migrations safely and know which changes are active.

Tracking applied migrations prevents accidental repeated changes and keeps databases consistent.

5

Intermediate

Rolling back migrations safely

🤔Before reading on: do you think migrations can be undone easily or is it risky? Commit to your answer.

Concept: Introduces the idea of reversing migrations to undo changes.

Good migrations include a way to undo changes, called rollback. For example, if a migration adds a table, the rollback drops it. Supabase supports rollback by running the opposite SQL commands. This helps fix mistakes or revert updates.

Result

You understand how to safely undo database changes using migrations.

Knowing rollback exists encourages safer database updates and easier error recovery.

6

Advanced

Handling data changes in migrations

🤔Before reading on: do you think migrations only change structure or can they also change data? Commit to your answer.

Concept: Shows how migrations can modify data, not just structure.

Migrations can include SQL commands to update data, like inserting default values or transforming columns. For example: UPDATE users SET email = '[e-mail]' WHERE email IS NULL; This lets you keep data consistent with schema changes.

Result

You can write migrations that safely update both structure and data.

Understanding data changes in migrations helps maintain database integrity during updates.

7

Expert

Managing complex migrations in teams

🤔Before reading on: do you think multiple developers can safely create migrations at the same time? Commit to your answer.

Concept: Discusses best practices for using migrations in team environments.

When many people write migrations, conflicts can happen if two add changes at once. Use clear naming with timestamps, review migration order, and test applying all migrations from scratch. Automate migration runs in CI/CD pipelines to keep everyone’s database consistent.

Result

You know how to avoid migration conflicts and keep team databases aligned.

Knowing team migration challenges prevents costly errors and deployment failures.

Under the Hood

Migrations work by running SQL commands in order on the database. Supabase keeps a special table that records which migrations have run. When you apply migrations, Supabase checks this table and runs only new migrations. Rollbacks run the opposite commands to undo changes. This system ensures the database schema matches the migration history exactly.

Why designed this way?

Migrations were designed to solve the problem of manually changing databases, which is error-prone and hard to track. Using files with ordered commands and a tracking table makes changes repeatable and reversible. This approach was chosen over manual scripts or GUI tools because it fits well with code versioning and team workflows.

┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│ Migration 1  ├──────▶│ Migration 2  ├──────▶│ Migration 3  │
└──────┬────────┘       └──────┬────────┘       └──────┬────────┘
       │                       │                       │
       ▼                       ▼                       ▼
┌─────────────────────────────────────────────────────────┐
│               Database Migration Table                  │
│  Records applied migrations with timestamps and status │
└─────────────────────────────────────────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do migrations automatically update your app code too? Commit to yes or no.

Common Belief: Migrations automatically update both the database and the app code.

Reality: Migrations only change the database structure; app code must be updated separately to match.

Why it matters: If you assume migrations update app code, your app might break because it expects a different database schema.

Quick: Can you run migrations in any order without problems? Commit to yes or no.

Common Belief: You can run migrations in any order because each is independent.

Reality: Migrations must run in the order they were created to avoid errors and keep database consistent.

Why it matters: Running migrations out of order can cause missing tables or columns, breaking the database.

Quick: Are migrations only for adding new tables? Commit to yes or no.

Common Belief: Migrations are only used to add new tables to the database.

Reality: Migrations can add, modify, or remove tables, columns, indexes, and even data.

Why it matters: Limiting migrations to adding tables restricts your ability to evolve the database as app needs change.

Quick: Do rollbacks always work perfectly? Commit to yes or no.

Common Belief: Rollbacks always undo migrations perfectly without any issues.

Reality: Rollbacks can fail if data was changed or deleted in ways that can’t be reversed automatically.

Why it matters: Assuming perfect rollbacks can lead to data loss or inconsistent states if not tested carefully.

Expert Zone

1

Migrations should be idempotent or carefully ordered to avoid errors when re-applied or partially applied.

2

Complex migrations that change data require careful planning to avoid locking tables or causing downtime.

3

Using descriptive migration names with timestamps helps trace changes and resolve conflicts in team environments.

When NOT to use

Migrations are not suitable for large bulk data transformations or real-time schema changes. For those, use dedicated data pipelines or database refactoring tools. Also, avoid manual database edits outside migrations to keep history consistent.

Production Patterns

In production, migrations are run automatically during deployment using CI/CD pipelines. Teams review and test migrations in staging environments first. Some use feature flags to deploy schema changes gradually without downtime.

Connections

Version Control Systems

Migrations build on the idea of tracking changes over time like version control tracks code changes.

Understanding version control helps grasp why migrations are stored as ordered files and why history matters.

Continuous Integration / Continuous Deployment (CI/CD)

Migrations are integrated into CI/CD pipelines to automate database updates alongside app deployments.

Knowing CI/CD shows how migrations fit into automated, reliable software delivery.

Legal Document Amendments

Migrations are like legal amendments that update a contract step-by-step, keeping a clear record of changes.

This connection highlights the importance of ordered, documented changes to avoid confusion or disputes.

Common Pitfalls

#1 Applying migrations without testing can break the database.

Wrong approach:

supabase db push --force

Correct approach:

Test migrations locally or in staging before running supabase db push in production.

Root cause:

Assuming migrations are always safe without verifying their effects.

#2 Editing migration files after they have been applied.

Wrong approach:

Changing an old migration SQL file and re-running migrations.

Correct approach:

Create a new migration file for any changes instead of modifying applied ones.

Root cause:

Not understanding that applied migrations are part of history and should remain immutable.

#3 Not including rollback commands in migrations.

Wrong approach:

Writing migrations that add tables but no DROP commands for rollback.

Correct approach:

Include matching rollback SQL commands to safely undo changes if needed.

Root cause:

Ignoring the need for reversibility in database changes.

Key Takeaways

Migrations are organized, step-by-step instructions to safely change a database structure over time.

Supabase uses ordered SQL files and tracks applied migrations to keep databases consistent and avoid repeated changes.

Writing migrations includes adding, modifying, or removing tables and data, with rollback support for safety.

Applying migrations in the correct order and testing them prevents errors and downtime in production.

In teams, clear naming, version control, and automation are essential to manage migrations effectively.