如何用Prisma+PostgreSQL(Supabase)实现全文搜索？解决@@fulltext报错

Prisma的@@fulltext属性目前仅支持MySQL连接器，所以在PostgreSQL（Supabase基于该数据库）中使用会触发报错。以下是两种可行的实现方案：

方法一：通过Prisma Schema定义原生PostgreSQL全文索引 #

利用PostgreSQL原生的全文搜索能力，在Prisma Schema中直接定义优化搜索的GIN索引：

1. 先确保Companion模型包含要搜索的字段：

model Companion {
  id   Int    @id @default(autoincrement())
  name String
  // 其他业务字段...
}

2. 在模型末尾添加原生全文索引，使用to_tsvector将文本转换为搜索向量，搭配GIN索引提升性能：

model Companion {
  id   Int    @id @default(autoincrement())
  name String
  // 其他业务字段...

  @@index([name], name: "companion_name_fulltext", using: "GIN", include: { db.raw("to_tsvector('english', name)") })
}

3. 执行prisma migrate dev将索引同步到Supabase数据库。

方法二：使用Prisma Raw查询执行全文搜索 #

索引创建完成后，通过Prisma的$queryRaw直接调用PostgreSQL的全文搜索语法：

比如搜索包含指定关键词的Companion记录（支持前缀匹配）：

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function searchCompanions(query: string) {
  const results = await prisma.$queryRaw`
    SELECT * FROM "Companion"
    WHERE to_tsvector('english', "name") @@ to_tsquery('english', ${query}:*)
  `
  return results
}

进阶优化：预存tsvector字段（高搜索频率场景） #

如果搜索请求量较大，可以预存文本的向量值，避免每次搜索实时转换：

1. 更新Prisma Schema，新增存储向量的字段（用Unsupported标记Prisma未原生支持的tsvector类型）：

model Companion {
  id          Int    @id @default(autoincrement())
  name        String
  nameVector  Unsupported("tsvector") @default(db.raw("to_tsvector('english', name)"))
  // 其他业务字段...

  @@index([nameVector], name: "companion_name_vector_idx", using: "GIN")
}

2. 手动创建迁移文件并添加触发器，确保name字段更新时自动同步向量值：
   执行prisma migrate dev --create-only生成空迁移文件，写入以下SQL：

CREATE OR REPLACE FUNCTION update_name_vector()
RETURNS TRIGGER AS $$
BEGIN
  NEW."nameVector" = to_tsvector('english', NEW."name");
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_name_vector
BEFORE INSERT OR UPDATE ON "Companion"
FOR EACH ROW EXECUTE FUNCTION update_name_vector();

再执行prisma migrate dev应用触发器。

3. 搜索时直接使用预存的向量字段：

async function searchCompanions(query: string) {
  const results = await prisma.$queryRaw`
    SELECT * FROM "Companion"
    WHERE "nameVector" @@ to_tsquery('english', ${query}:*)
  `
  return results
}

内容的提问来源于stack exchange，提问作者Aviroop Jana