Prisma.io + Full Text Search + PostgreSQL: Search is only working with exact match

Question

I have enabled full text search for prisma and I would like to search the email field returning all entries that match.

I got the following code:

const data = await this.prismaService.merchant.findMany({
  where: {
    email: {
      search: '12rwqg13tr222vqfgedvqrw22@someprovider.de',
    },
  },
});

This is working when I enter the exact email address. However, when I try to search for a part of it, i.e. 12rwqg13tr222vqfgedvqrw22@someprovider, I get no results.

Do I have to create indexes to accomplish this? In the docs it is mentioned that I only need indexes for PostgreSQL if I want to speed up the queries. Am I missing something here?

Answer 1

apparently I was looking at the wrong feature. contains is what I was looking for:

const res = await prisma.post.findMany({
  where: {
    author: {
      email: {
        contains: 'prisma.io',
      },
    },
  },
})

Edit: If you need case insensitive search, have a look at the prisma docs for case sensitivity

const res = await prisma.post.findMany({
  where: {
    author: {
      email: {
        contains: 'prisma.io',
        mode: 'insensitive',
      },
    },
  },
})

This would match Prisma.IO as well.