Unable to implement Postgres full text search via Prisma

Question

Prisma's full text search support is still in preview stage. Hence I'm following this guide to implement full text search using custom migration script. However, after modifying the create-only migration script as said in the guide and running the npx prisma migrate dev command, it prompts to create a new migration file which rolls back all the modifications made in the previous script. I guess this occurs because prisma thinks the current schema is not inline with the migration scripts.

So, how to avoid this from happening? What am I missing here?

Here's the create-only migration script after modification,

-- AlterTable
ALTER TABLE "Event" ADD COLUMN "searchVector" TSVECTOR
    GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(title, '')), 'A') || 
        setweight(to_tsvector('english', coalesce(description, '')), 'B') || 
        setweight(to_tsvector('english', coalesce(city, '')), 'C') || 
        setweight(to_tsvector('english', coalesce(state, '')), 'C') || 
        setweight(to_tsvector('english', coalesce(country, '')), 'C')
    ) STORED;

-- CreateIndex
CREATE INDEX "Event_searchVector_idx" ON "Event" USING GIN ("searchVector");

Here's the new script added by prisma automatically after running prisma migrate dev,

-- DropIndex
DROP INDEX "Event_searchVector_idx";

-- AlterTable
ALTER TABLE "Event" ALTER COLUMN "searchVector" DROP DEFAULT;

-- CreateIndex
CREATE INDEX "Event_searchVector_idx" ON "Event"("searchVector");

Here's my Event model,

model Event {
  id                   Int                      @id @default(autoincrement())
  slug                 String                   @unique
  createdAt            DateTime                 @default(now())
  updatedAt            DateTime                 @updatedAt
  title                String
  description          String
  date                 DateTime
  registrationDeadline DateTime
  price                Float?
  addressLine1         String?
  addressLine2         String?
  city                 String?
  state                String?
  country              String?
  zipCode              String?
  bannerImgUrl         String?
  eventMode            Int
  language             String?
  searchVector         Unsupported("TSVECTOR")?
  userId               Int
  user                 User                     @relation(fields: [userId], references: [id], onDelete: Cascade)
  speakers             EventSpeaker[]
  comments             EventComment[]

  @@index([searchVector])
}

Answer 1

I've finally fixed this problem. Found that due to an issue with Prisma it prevents us from setting up generated columns. Hence I used triggers to perform the same as below. (Add this trigger to the same migration file)

-- Create the trigger function
CREATE OR REPLACE FUNCTION update_event_search_vector() RETURNS TRIGGER AS $$
BEGIN
    NEW."searchVector" :=
        setweight(to_tsvector('english', coalesce(NEW.title, '')), 'A') || 
        setweight(to_tsvector('english', coalesce(NEW.description, '')), 'B') || 
        setweight(to_tsvector('english', coalesce(NEW.city, '')), 'C') || 
        setweight(to_tsvector('english', coalesce(NEW.state, '')), 'C') || 
        setweight(to_tsvector('english', coalesce(NEW.country, '')), 'C');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Attach the trigger function to the Event table
CREATE TRIGGER event_search_vector_trigger
    BEFORE INSERT OR UPDATE ON "Event"
    FOR EACH ROW
    EXECUTE FUNCTION update_event_search_vector();

The reason why GIN index was getting reverted was because in recent versions Prisma has added support to it. Hence adding GIN index directly in it's schema as below fixed it.

@@index([searchVector], type: Gin)