Xata•3mo ago

A problem with Drizzle and Auth.js

Hey I try to use Auth.js together with Drizzle and as the DB Xata.io. I set everything up and then just copied the Drizzle Schema from the official Auth.js site (https://authjs.dev/getting-started/adapters/drizzle) . When I try to do push it to the DB (npx drizzle-kit push) I get the following error: error: relation "public.user" does not exist { length: 349, severity: 'ERROR', code: '42P01', detail: undefined, hint: undefined, position: undefined, internalPosition: undefined, internalQuery: undefined, where: 'SQL statement "ALTER TABLE "post" ADD CONSTRAINT "post_created_by_user_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."user"("id") ON DELETE no action ON UPDATE no action"\n' + 'PL/pgSQL function inline_code_block line 2 at SQL statement', schema: undefined, table: undefined, column: undefined, dataType: undefined, constraint: undefined, file: 'namespace.c', line: '449', routine: 'RangeVarGetRelidExtended' } I tried out a lot and found out that the error comes because of this lines of code:
userId: varchar("user_id", { length: 255 }) .notNull() .references(() => users.id, { onDelete: "cascade" }), <--- But I doesnt find a solution for that. Thank u a lot for ur help. Lukas

Auth.js | Drizzle

Authentication for the Web

4 Replies

tsg•3mo ago

I can reproduce this, the issue is that Drizzle Kit generates ALTER statements in DO blocks like this, aparently for exception handling:

DO $$ BEGIN
 ALTER TABLE "session" ADD CONSTRAINT "session_userId_user_id_fk" FOREIGN KEY ("userId") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "session" ADD CONSTRAINT "session_userId_user_id_fk" FOREIGN KEY ("userId") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

This unfortunately fails in our proxy parsing. I will raise it with the team, perhaps the fix for it is easy. In the mean time, a possible workaround is to edit the generated sql and remove the DO/END. So the above becomes just:

ALTER TABLE "session" ADD CONSTRAINT "session_userId_user_id_fk" FOREIGN KEY ("userId") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;

ALTER TABLE "session" ADD CONSTRAINT "session_userId_user_id_fk" FOREIGN KEY ("userId") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;

In this particular case, it seems that the migration is still idempotent (I can run npm run migrate multiple times without errors), although I'm not sure about all the implications of doing this. I will keep you up to date for when we fix the underlying issue with the DO blocks.

LukasOP•3mo ago

Thank u a lot for the fast replay. Just for me to know do u think the fix will be a matter of days or rather weeks?

tsg•3mo ago

hey, sorry, actually the fix should be already available. Can you give it a quick retry, please?

LukasOP•2mo ago

thank u a lot. I tried it again and as far as i can see everything works now.

Gaming

Programming

A problem with Drizzle and Auth.js