tianhuil · October 22, 2024 14:29 · Oct 22, 2024 · Sep 16, 2024 · Sep 16, 2024 · Sep 16, 2024
diff --git a/README.md b/README.md
@@ -48,5 +48,6 @@ 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.
diff --git a/README.md b/README.md
@@ -1,3 +1,7 @@
+## 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
 
 ```
@@ -41,4 +45,8 @@ To generate a diff, for a migration, simply run
 
 That's it!
 
-**NB:** it looks like migra does not diff `CREATE TRIGGER` and `DROP TRIGGER` statements.  You have to keep track of these manually.
+**NB:** The migra tool does not diff
+- `CREATE TRIGGER` and `DROP TRIGGER` statements
+- `ALTER ... OWNER TO` statements.
+
+You will have to keep track of these manually.
diff --git a/README.md b/README.md
@@ -39,4 +39,6 @@ To generate a diff, for a migration, simply run
 ./script/migra-diff.sh
 ```
 
-That's it!  NB: it looks like migra does not diff `CREATE TRIGGER` and `DROP TRIGGER` statements.  You have to keep track of these manually.
+That's it!
+
+**NB:** it looks like migra does not diff `CREATE TRIGGER` and `DROP TRIGGER` statements.  You have to keep track of these manually.
diff --git a/README.md b/README.md
@@ -0,0 +1,42 @@
+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):
+
+```sql
+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
+```bash
+./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
+```bash
+./script/migra-diff.sh
+```
+
+That's it!  NB: it looks like migra does not diff `CREATE TRIGGER` and `DROP TRIGGER` statements.  You have to keep track of these manually.
diff --git a/migra-diff.sh b/migra-diff.sh
@@ -0,0 +1,74 @@
+#!/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:postgres@127.0.0.1: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
No results found