CLI: supabase db diff doesn't handle custom domains

[davidaventimiglia-professional]

Bug report

• I confirm this is a bug with Supabase, not with my own application.
• I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

The Supabase CLI supabase db diff command to to a DB diff and generate migration files, does not seem to handle PostgreSQL custom domains (https://www.postgresql.org/docs/current/sql-createdomain.html). Specifically, what I observed is the following. If I create a custom domain, then create a table with a column using that custom domain, the do supabase db diff, the DDL for creating the table is generated, but not the DDL for creating the domain. Consequently, a migration file created in this way is invalid and will fail. I tried it with the default, with --use-migra, with --use-pg-diff, and with --use-pgadmin and got the same result.

To Reproduce

Steps to reproduce the behavior, please provide code snippets or a repository:

1. Install the Supabase CLI.
2. Initialize a project directory with supabase init.
3. Start local development with supabase start.
4. Connect to the local db with psql: psql $(supabase status --output json 2>/dev/null | jq -r '.DB_URL')
5. Create a custom domain: create domain foo as text
6. Create a table with the domain: create table bar (name foo)
7. Exit to a shell and generate a diff: supabase db diff
8. Observe that the diff has the create table statement but no create domain statement.

Expected behavior

I expected at least one of the diff tools to detect that DDL for custom domains needs to be emitted.

Screenshots

no applicable screenshots

System information

• OS: Ubuntu 24.0.2
• Version of supabase-js: 2.15.8
• Version of Node.js: (running supabase cli binary)

Additional context

no other context

[Hallidayo]

Hi, I've moved this issue over from the supabase repo as it seems like a cli issue.

[softmarshmallow]

Having the same issue.

on the cloud, I ran.

CREATE EXTENSION IF NOT EXISTS citext;

CREATE DOMAIN email AS citext
  CHECK (
    VALUE ~ '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$'
  );

--
ALTER TABLE public.customer
ALTER COLUMN email TYPE email;

after locally supabase db pull,

alter table "public"."customer" alter column "email" set data type email using "email"::email;

(But it does not comes with the type.)

supabase db reset

ERROR: type "email" does not exist (SQLSTATE 42704)                                          
At statement 5:                                                                              
alter table "public"."customer" alter column "email" set data type email using "email"::email

which the custom domain email is not properly populated with the migration file.

cli version: 2.19.7

---

I'm not sure I'm allowed to do this, but I modified the ts_remote_schema.sql my self,
having

CREATE DOMAIN email AS citext
  CHECK (
    VALUE ~ '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$'
  );

It works, but am I even allowed to modify this? (hope things does not break in the future.)

[Fabricio-191]

Sadly migra has not recieved updates in 3 years and doesn't support custom types/domains (it says it does, but there are reports and you can clearly test it doesn't)

pg-schema-diff is too new and doesn't yet support views and custom types

The only viable alternative is pgAdmin diff tool, but the one used by supabase is a 3 year old version, and it doesn't work anymore

So the only thing that could solve this is to use a new version of the pgAdmin diff tool