How to use Supabase migrations?

 

Step 1: Install Supabase CLI

 

To use Supabase migrations, it's essential to have the Supabase CLI installed. This can be done using npm. Run the following command:

npm install -g supabase

 

Step 2: Initialize a Supabase Project

 

Before working with migrations, you need to initialize a Supabase project in your working directory. This is done by running:

supabase init

This command will create a new directory called supabase containing necessary configuration files.

 

Step 3: Set Up Your Database URL

 

Inside the supabase directory, open the .env file and set your database URL. Ensure it looks like this:

SUPABASE_DB_URL=your-database-url

Replace your-database-url with the URL provided by Supabase for your database instance.

 

Step 4: Create a New Migration

 

To create a new migration file, use the supabase CLI command with the migration new command, followed by the name of your migration. For example:

supabase migration new create_users_table

This command will create a new timestamped SQL file under the supabase/migrations directory.

 

Step 5: Write Migration SQL

 

Edit the newly created migration file by adding your SQL commands. For example, to create a new table:

-- Run the up commands to apply the migration.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username TEXT NOT NULL,
  email TEXT NOT NULL
);

-- Run the down commands to reverse the migration.

DROP TABLE users;

 

Step 6: Deploy Your Changes

 

To apply the migration, use the following command:

supabase db push

This command will apply any new migrations to your database.

 

Step 7: Verify the Migration

 

To ensure that the migration has been applied successfully, you can check your database either through the Supabase dashboard or by connecting to your database and inspecting the schema:

-- SQL Example to verify:

SELECT \* FROM users;

 

Step 8: Revert a Migration (if needed)

 

If you need to undo a migration, you can use the supabase CLI to revert your changes. Use the following command:

supabase db reset

This command will revert your database to the last committed state before the most recent migrations.

 

Step 9: Maintain Migrations

 

Regularly maintain and organize your migrations for better clarity and structure in your database design. Keep in mind:

• Use clear, descriptive names for your migration files.
• Regularly test your migrations locally before applying them to production.
• Ensure your 'up' and 'down' SQL commands are correctly written to avoid errors during deployment or reversion.

 

Follow these steps carefully to manage your database schema effectively using Supabase migrations. They provide a powerful way to version and manage changes to your database over time.