Alright, let's get started with the Supabase CLI. It's a handy tool for managing data migrations. To install it, just pop open your terminal and run:

npm install -g supabase

Next, head over to your project directory. Once you're there, initialize your project with Supabase by typing:

supabase init

This will create a supabase folder in your project directory. Inside, you'll find configurations and migration templates. Pretty neat, right?

Now that we've got things set up, let's create a new migration file. Use this command, but swap out migration_name with something that describes your change:

supabase migration create migration_name

You'll see a new file pop up under supabase/migrations/ with a timestamp and the name you provided.

Time to get our hands dirty. Open up the new migration file, which you'll find at supabase/migrations/XXXXXXXXXXXXXX_migration_name.sql. Add your SQL commands here. You can create tables, modify columns, or make any other necessary changes. For example:

-- Example of a SQL command to create a table
CREATE TABLE example_table (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- Example of a SQL command to add a column
ALTER TABLE example_table ADD COLUMN created_at TIMESTAMPTZ DEFAULT NOW();

Let's apply that migration to your local Supabase database. Run these commands:

supabase start
supabase db reset

This will spin up a local Supabase instance and apply all available migrations to your local database. Easy peasy.

Ready to go live? First, log in to the Supabase CLI if you haven't already:

supabase login

Once you're logged in, link your project to the remote instance with:

supabase link --project_ref your_project_reference_id

Finally, push the migrations to your remote database:

supabase db push

This will send all your local migrations to the connected remote Supabase database.

Now, let's make sure everything went smoothly. Check the changes in the Supabase dashboard or use your favorite database query tool. Confirm that all migrations were applied successfully and that the database schema looks just right.

Sometimes things don't go as planned. If you need to roll back a change, create a new migration file with a script that undoes the previous changes. For example, if you added a column, your rollback script should drop it:

ALTER TABLE example_table DROP COLUMN created_at;

Then, follow Steps 4-7 to apply this rollback migration and revert the changes.

By following these steps, you can manage data migrations smoothly with Supabase, ensuring your database schema stays in tip-top shape both locally and in production.