First things first, let's get the Supabase CLI installed. Open up your terminal and type:

npm install -g supabase

Head over to your project directory and kick off the Supabase setup:

supabase init

Make a directory for your database migrations if you don't have one already. Usually, it's supabase/migrations:

mkdir -p supabase/migrations

Use the CLI to whip up a new migration script. This will add a timestamp to keep things in order.

supabase migration new <migration-name>

Swap out <migration-name> with something that describes your migration.

Go to the new file in the supabase/migrations directory and write your SQL commands for this migration:

-- Example: Create a new table
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR (50) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

Make sure your database connection string is set up. You can configure this in the .env or supabase/config.toml file.

[DATABASE]
url = "postgres://username:password@localhost:5432/mydb"

Apply the migrations to your local Supabase database:

supabase migration up

If you want to automate this (like in a CI/CD pipeline), add these steps to your pipeline script:

1. Install Supabase CLI:
   ```sh
   npm install -g supabase
   ```

2. Pull the latest code and dependencies:
   ```sh
   git pull origin main
   npm install
   ```

3. Run Migrations:

\`\`\`sh
supabase migration up
\`\`\`

Check your database to make sure the migration went through. You can do this by inspecting the changes or running a query:

SELECT * FROM users;