How to create a trigger on a postgresql database managed by prisma?

Question

Good evening . I'm working on a chat application using nodejs, prisma, postgresql. I would like 24 hours after the last message is created on a specific chat to be immediately deleted from the postgresql database. For this, I created a trigger:

--function creation

create or replace function chat_expired() returns trigger as $delete_account$
begin 
delete from chat where current_timestamp - last.messages.createAt = 24*60*60;
return new;
end;
$delete_account$ language plpgsql ;

-- trigger creation

create trigger messages_added
after update 
on chat --I get error on this line
for each row 
execute procedure chat_expired();

from postgresql query tool but when I execute I get the error the cat relationship does not exist at the trigger definition level. I guess it's because the chat table hasn't been defined in my query tool space; but I didn't set it because the chat table was already created from prisma immigrations. So I deleted the folder of my prisma migrations, then I created a new migration and I modified the .sql file manually in order to insert my trigger there thus thinking that prisma would also create a trigger for me at the same time as database tables: --function creation

create or replace function "chat_expired"() returns trigger as $delete_account$
begin 
delete from "Chat" where current_timestamp - last.chatMessage.createdAt = 3*60;
return new;
end;
$delete_account$ language plpgsql ;

-- trigger creation

create trigger "messages_added"
after update 
on "chat" 
for each row 
execute procedure "chat_expired"();

Notes: The previous code contains a few more quotes than the first code. But it instead prevented the migration from applying and I got the error

Error: P3006

Migration `20220905231003_initial_migration` failed to apply cleanly to the shadow database. 
Error code: P1014
Error:
The underlying table for model `(not available)` does not exist.

which was only resolved after I removed the manually added rows. So I don't know how to create a trigger on a postgresql database managed by prisma. I've been thinking about the solution for several weeks. I even looked at this stackoverflow question and also this one but nothing. Thanks !

Answer 1

Prisma doesn't have support for triggers yet. Prisma has an excellent article on the topic. They advice writing Prisma middleware to achieve "triggers". There is also a feature request for it.