Supabase and Prisma workflow compilation

[NanoCode012]

Note: This is a updated copy from the discord's help channel to increase visibility. I hope this can also be a central source for pointing to Prisma issues and workaround. Cross-posted to prisma/prisma#14292 for Prisma's side.

Edit: For others having same issue, I recommend Subscribing for notifications in case I/someone else update the thread with a guide in the future.

Edit 2: Short answer: Hard setup. Slight to no hiccups after.

---

Hello! Has anyone successfully implemented Prisma to work with Supabase? It would be great if I could draw some insights on whether implementing Prisma has been worth it. I have been reading this past week on trying to get them to work together meeting several issues with workarounds.

1. No cross schema (Support querying across multiple schemas prisma/prisma#1122). auth.users cannot be accessed (Issues cross-referencing tables from different schemas using Prisma #1502). We need to use a trigger to update a public.users table. Integrating with prisma with support for local development and RLS #4051 (comment)

   • Note: It is possible to query auth.users from prisma prisma.$queryRawUnsafe('SELECT * FROM auth."users"') using postgres role by sb on discord. https://discord.com/channels/839993398554656828/1009277878736785439/1009668643065438208
   • Great news: This is currently in preview! 🎉 Support querying across multiple schemas prisma/prisma#1122 (comment) . It should theoretically work, however I do not have the time yet to test.

2. Creating that trigger in 1 requires a separate script run ourselves via psql (easy to forget or make mistakes). Integrating with prisma with support for local development and RLS #4051 (comment)

3. Using any function like auth.id() requires a "fake" function public.auth_id() to be used in-place, then replaced via psql like 2. Integrating with prisma with support for local development and RLS #4051 (reply in thread)

4. Incrementing ID primary key doesn't work . id BigInt @id @default(autoincrement()) does not create auto increment properly. Bug: Constraints not well-handled by Studio #7560

5. Grants are a bit messed up when resetting db during dev https://supabase.com/docs/guides/integrations/prisma#troubleshooting

6. Required to create a separate db user to not bypass RLS and to give grants Supporting session-dependent queries like Postgres' SET across queries of a request prisma/prisma#5128 (comment) Prisma database introspection #838 (comment)

7. Required to use a Prisma middleware to inject the current_user_id for each transactions (with no official documentation on this, only other users' code) Supporting session-dependent queries like Postgres' SET across queries of a request prisma/prisma#5128 (comment) OR implement helper for RLS Supporting session-dependent queries like Postgres' SET across queries of a request prisma/prisma#5128 (comment)

   • Prisma extension methods in Preview 🎉 Support for row-level security (RLS) prisma/prisma#12735 (comment)

8. We cannot add other schemas like the supa-audit extension which uses the audit schema. Need to apply manually.

9. Current issues with quotes in name Prisma migration with Quoted Types breaks realtime. #5685 (comment) Fixed Prisma migration with Quoted Types breaks realtime. #5685 (comment)

10. Use separate connection string for migrate vs normal use with PgBouncer (supabase already provides this) https://supabase.com/docs/guides/integrations/prisma#connection-pooling-with-supabase (for deployment: https://community.redwoodjs.com/t/guide-migrations-connection-pool-urls-supabase/2505)

11. RLS has to be manually created unlike in Supabase where we can Supabase Studio Supporting session-dependent queries like Postgres' SET across queries of a request prisma/prisma#5128 (comment)

12. Telemetry? Set CHECKPOINT_DISABLE=1

13. Extensions schema not seen by Prisma. Need to CREATE SCHEMA IF NOT EXISTS "extensions"; CREATE EXTENSION IF NOT EXISTS "uuid-ossp" SCHEMA extensions; for UUID Local Development - Using Extensions #2250 (reply in thread)

14. Trigger issue with Prisma-created tables Error: "permission denied for schema public" when trying to use a trigger function on a table created by Prisma #7094 (comment)

15. Cannot migrate to Supabase hosted db (unclear, I do not have this issue) Prisma unable to connect to Supabase database #7772 Cannot connect to Supabase database prisma/prisma#13807

16. Cannot change role to authenticated without a bit of changes (My solution is mimic authenticator role with our own prisma role OR reverting to use deprecated auth.role)

17. Using RLS with Prisma opens a hole in our db where users can connect directly via Postgrest and bypass our API. (My solution: use our own authenticated role)

18. Need to run psql in CI to prevent mix-matching env. However, CI env does not usually have psql available. Solutions I have found: 1. run a Github Actions CI (installing postgres and psql) to run build, push, migrate, and psql. 2. Manually and carefully run the psql comands from personal computer once. It should only require once and any other time need to cross-schema stuff.

19. Prisma Migrate only helps with schema-related SQL. For Supabase migrate, you could work on db then pull. My solution: write the pure SQL on .sql file then migrate reset till it works for you. Recommend have a seed script. Requires more familiarity with SQL.

20. Windows Prisma timeout with Supabase as reported by balabanshik (https://discord.com/channels/839993398554656828/1034600133121277972/1034603713807011940). Their fix was to add longer connect_timeout.

21. Using camelCase convention could break db. Use snake_case instead. https://discord.com/channels/839993398554656828/885237287280070708/988097402059780098

Up to this point, I am sure I have not discovered all the nitty gritty bugs still. Does anyone have any opinion or experience to share about using Prisma? I will also consolidate this info to one place to help future people facing these issues.

[MentalGear]

Thanks for this important documentation of flaws and bugs! This prevented me from embarking onto a long integration journey that probably would have just made me miserable and when I look at all the bugs/flaws that come from the increased complexity. I'd be great if supabase had a simple schema like Prisma (feat wink).

[edgahan]

I've been playing with Prisma and Supabase lately and really the only way I was able to get it "working" is by:

1. Create a User/Profile table in your own public schema.
2. Creating a trigger manually (e.g. in Supabase SQL editor) to update your own User/Profile table
3. Use Prisma with the public schema and ignore anything Supabase has in the auth schema

Things I tried that did not work, as of late 2022:

1. Using Prisma's new multi schema feature, e.g:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["multiSchema"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  schemas  = ["auth", "public"]
}

There were some problems with this. When running a prisma db pull the models where populated OK:

However the migrations had errors:

"confirmed_at" TIMESTAMPTZ(6) DEFAULT LEAST(email_confirmed_at, phone_confirmed_at),

cannot use column reference in DEFAULT expression

This seems fixable, but when thinking about it, maybe it's best to be considering Supabase's auth schema as "internals" and to not have to be syncing that a schema.prisma file.

2. I also tried to have the triggers ((1) above) in a Prisma migration file, but without doing a db pull. Even with multi schema enabled it gives error when running a migration:
   db error: ERROR: schema "auth" does not exist
   (It works with a full prisma migrate reset)
   I tried to ignore certain models (though realise this is more for the client generation, I was just trying for fun). Also didn't work.

model users {

  @@ignore
  @@schema("auth")
}

You might be able to do this by creating a new DB user as per: https://www.prisma.io/docs/concepts/components/introspection#introspecting-only-a-subset-of-your-database-schema

Overall I just feel like the whole Prisma and Supabase experience is disjointed as it stands. I was hoping to have everything working in the schema.prisma file or at the very least putting some manual SQL in a Prisma migration file, but I could not get these options to work. I'm not even thinking about things like getting RLS working at this point.

---

Some code that might be helpful, Trigger mentioned in (2):

-- inserts a row into public."User"
create or replace function public.handle_new_user() 
returns trigger as $$
begin
  insert into public."User" (id, email)
  values (new.id, new.email);
  return new;
end;
$$ language plpgsql security definer;

-- trigger the function every time a user is created
drop trigger if exists on_auth_user_created on auth.users;
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();


-- update a row in public."User" when the email is updated
create or replace function public.handle_updated_user()
returns trigger as $$
begin
  update public."User"
  set email = new.email
  where id = new.id::text;
  return new;
end;
$$ language plpgsql security definer;

-- trigger the function every time a user is updated
drop trigger if exists on_auth_user_updated on auth.users;
create trigger on_auth_user_updated
  after update on auth.users
  for each row execute procedure public.handle_updated_user();


-- delete a row from public."User" when the user is deleted
create or replace function public.handle_deleted_user()
returns trigger as $$
begin
  delete from public."User" where id = old.id::text;
  return old;
end;
$$ language plpgsql security definer;

-- trigger the function every time a user is deleted
drop trigger if exists on_auth_user_deleted on auth.users;
create trigger on_auth_user_deleted
  after delete on auth.users
  for each row execute procedure public.handle_deleted_user();

[MarceloPrado]

@riccardolardi what I did on my end was to write some db triggers that ensures my "users" table is up-to-date with Supabase's auth schema.

This is what I have:

-- Function to create user
CREATE OR REPLACE FUNCTION public.create_user() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public."User" (id, email)
  VALUES (NEW.id::text, NEW.email); -- cast id to text
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY definer;

-- Function to update user
CREATE OR REPLACE FUNCTION public.update_user() RETURNS TRIGGER AS $$
BEGIN
  UPDATE public."User"
  SET email = NEW.email, "updatedAt" = NEW.updated_at
  WHERE id = NEW.id::text; -- cast id to text
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY definer;

-- Function to delete user
CREATE OR REPLACE FUNCTION public.delete_user() RETURNS TRIGGER AS $$
BEGIN
  DELETE FROM public."User"
  WHERE id = OLD.id::text; -- cast id to text
  RETURN OLD;
END;
$$ LANGUAGE plpgsql SECURITY definer;

-- Triggers
DROP TRIGGER IF EXISTS create_user_trigger ON auth.users;
CREATE TRIGGER create_user_trigger AFTER INSERT ON auth.users
FOR EACH ROW EXECUTE FUNCTION public.create_user();

DROP TRIGGER IF EXISTS update_user_trigger ON auth.users;
CREATE TRIGGER update_user_trigger AFTER UPDATE ON auth.users
FOR EACH ROW EXECUTE FUNCTION public.update_user();

DROP TRIGGER IF EXISTS delete_user_trigger ON auth.users;
CREATE TRIGGER delete_user_trigger AFTER DELETE ON auth.users
FOR EACH ROW EXECUTE FUNCTION public.delete_user();

I run this migration on each deploy, giving me the benefits of Supabase's admin panel without having to pollute my Prisma schema.

[riccardolardi]

Thanks a lot @MarceloPrado - why do you have to re-run this migration on every deploy though? You mean on each prisma migrate dev?

[MarceloPrado]

@riccardolardi I run them on every deploy to ensure the DB is consistent, and yes, I mean the npx prisma migrate deploy, given I'm talking about a production env

The way I think about this is: if I generate a brand new Supabase project and run the prisma migrate deploy, will the database be in the correct state? I don't want to modify any db settings/tables/triggers/RLS from Supabase's UI, given you'll have to manually re-do that if you re-generate your project.

If everything lives in migration scripts, you don't have to worry about starting over and forgetting about a given change.

[kengru]

@MarceloPrado thank you very much for this code!

How could I run this script in the Supabase DB from the terminal?

[cipriancaba]

Hey @MarceloPrado, nice and elegant solution. How do you actually run the migration on every deploy? Do you run it as a raw SQL since otherwise it will get marked as run by prisma and it won't run again

[christopheragnus]

Were you able to fix the

db error: ERROR: schema "auth" does not exist

error?

[mausworks]

I solved it with this:

-- The Shadow database (used for migrations) does not have the `auth.jwt()` function set up, 
-- so we need to stub it here to not get an error when we try to create the RLS policies later.

CREATE SCHEMA IF NOT EXISTS auth;

DO $$
BEGIN
  CREATE FUNCTION auth.jwt()
  RETURNS jsonb 
  LANGUAGE SQL
  AS 'SELECT NULL::json;';
EXCEPTION
  WHEN duplicate_function 
  THEN NULL;
END; $$;

Add it to your "initial_migration" SQL script. 👍

If you reference anything else in the auth-schema in your migrations, you will need to stub that too probably.