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 );.