Creating migrations in Supabase - Try It Yourself

Creating migrations

📖 Scenario: You are working on a Supabase project to manage your database schema changes safely and consistently. You want to create a migration file that adds a new table to your database.

🎯 Goal: Create a migration file that defines a new table called profiles with columns id (UUID primary key), username (text, unique), and created_at (timestamp with time zone, default to now).

📋 What You'll Learn

Create a migration file with the correct SQL syntax

Define the profiles table with the specified columns and constraints

Use best practices for UUID primary keys and timestamps

Ensure the migration is valid and deployable in Supabase

💡 Why This Matters

🌍 Real World

Database migrations help you safely change your database schema over time without losing data or causing errors.

💼 Career

Knowing how to write migrations is essential for backend developers and cloud engineers working with databases and Supabase.

1

Create the migration file header

Create a migration file starting with the CREATE TABLE profiles ( statement to begin defining the table.

Supabase

# Your code here
-- Start the migration with CREATE TABLE profiles (

Hint

Start your migration with the SQL command to create a table named profiles.

2

Add the id column

Add a column called id of type uuid that is the primary key and uses gen_random_uuid() as the default value.

Supabase

CREATE TABLE profiles (
  -- Add id column here
  
)

Hint

Use uuid type, PRIMARY KEY constraint, and DEFAULT gen_random_uuid() for the id column.

3

Add the username column

Add a column called username of type text that must be unique.

Supabase

CREATE TABLE profiles (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  -- Add username column here
)

Hint

Use text type and UNIQUE constraint for the username column.

4

Add the created_at column and close the table

Add a column called created_at of type timestamp with time zone that defaults to now(). Then close the table definition with a closing parenthesis and semicolon.

Supabase

CREATE TABLE profiles (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  username text UNIQUE,
  -- Add created_at column and close table here
)

Hint

Use timestamptz for timestamp with time zone and DEFAULT now(). Don't forget to close the table with );.

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