Migra + Supabase

README.md

Motivation

Supabase migrations are imperative. We want to declaratively write DB definitions declaratively and generate migrations as diffs. This tool accomplishes this.

Tool

This is a basic script to diff a set of schemas to generate new migrations for a supabase project. The expected file layout is

- supabase/baseline/baseline.sql
- supabase/migrations/[timestamp]_[name].sql
- supabase/schema/[order]_[name].sql

In baseline, we put the files needed to generate a baseline supabase implementation. Something like this (although you may need to mock the types for other functions):

CREATE SCHEMA "auth";
CREATE TABLE
  "auth"."users" (
    "id" UUID NOT NULL,
  );
  
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE
OR REPLACE FUNCTION auth.uid () RETURNS UUID AS $$
BEGIN
  RETURN gen_random_uuid();
END;
$$ LANGUAGE plpgsql;

CREATE PUBLICATION supabase_realtime;

The migrations are the (imperatively defined) supabase migration files and the schema are your defined schema.

To generate the schema from existing migrations, you can run

./script/migra-diff.sh gen; wc supabase/schema/raw.sql.nogit

The wc command helps you meausre how much of the schema you have created.

To generate a diff, for a migration, simply run

./script/migra-diff.sh

That's it!

NB: The migra tool does not diff

• CREATE TRIGGER and DROP TRIGGER statements
• ALTER ... OWNER TO statements.
• GRANT UPDATE (col1, col2) ON TABLE ... statements. In fact, these are ignored and migra will ask that you add a REVOKE UPDATE ON TABLE ... to align the two schemas.

You will have to keep track of these manually.

migra-diff.sh

#!/bin/bash

# Exit immediately if a command exits with a non-zero status
set -e

# Check for the argument
if [ "$#" -eq 1 ]; then
  if [ "$1" == "gen" ]; then
    GEN=true
  else
    echo "Error: Invalid argument. Use 'gen' to generate initial table."
    exit 1
  fi
elif [ "$#" -gt 1 ]; then
  echo "Error: Too many arguments. Use 'gen' to generate initial table."
  exit 1
fi

run_psql() {
  psql -q "$@" 2> >(grep -Ev "already exists, skipping|does not exist, skipping|will be truncated to" >&2)
}

# Database connection details
DB_STR=postgresql://postgres:[email protected]:54322
CURRENT_DB=current
TARGET_DB=target

# Drop and create the current database
run_psql "$DB_STR/postgres" -c "DROP DATABASE IF EXISTS $CURRENT_DB;"
run_psql "$DB_STR/postgres" -c "CREATE DATABASE $CURRENT_DB;"

# Drop and create the target database
run_psql "$DB_STR/postgres" -c "DROP DATABASE IF EXISTS $TARGET_DB;"
run_psql "$DB_STR/postgres" -c "CREATE DATABASE $TARGET_DB;"

echo "##############################################"
echo "Applying baseline to the current database..."
run_psql $DB_STR/$CURRENT_DB < supabase/baseline/baseline.sql

echo "##############################################"
echo "Apply migrations to the current database..."
for sql_file in supabase/migrations/*.sql; do
  run_psql $DB_STR/$CURRENT_DB < "$sql_file"
done

echo "##############################################"
echo "Applying baseline to the target database..."
run_psql $DB_STR/$TARGET_DB < supabase/baseline/baseline.sql

echo "##############################################"
echo "Apply schema to the target database..."
for sql_file in supabase/schema/*.sql; do
  run_psql $DB_STR/$TARGET_DB < "$sql_file"
done

# Generate the diff using migra
if [ "$GEN" == true ]; then
  echo "Generating initial file..."
  bun run migra \
    $DB_STR/$TARGET_DB \
    $DB_STR/$CURRENT_DB \
    --unsafe \
    --schema public \
    --with-privileges \
    > supabase/schema/raw.sql.nogit
else
  echo "Diff against existing migration..."
  bun run migra \
    $DB_STR/$CURRENT_DB \
    $DB_STR/$TARGET_DB \
    --unsafe \
    --schema public \
    --with-privileges
fi