typescriptpostgresqlprismaprisma-graphql

How to perform Prisma Query on multi-column index?


I am trying to implement a multi-column index using Postgres and Prisma.

I have defined a multi column index as shown in the Prisma documentation

model MyModel {
  fieldA String!
  fieldB String!
  @@index(fields: [fieldA, fieldB])

  @@map("my_models")
}

It appears to succeed since I can use Prisma to auto-generate a database migration which has a line something like this:

CREATE INDEX "my_models_fieldA_fieldB_idx" ON "my_models"("fieldA", "fieldB");

However, when I can't figure out how to use the Prisma client to query on this index. I have tried:

const content_node = await this.prismaService.myModel.findMany({
      where: {
        fieldA_fieldB: {
          fieldA: input.fieldA,
          filedB: input.fieldB,
        },
      },
    })

And get the error:

node_modules/.prisma/client/index.d.ts:4925:5 
    4925     where?: MyModelWhereInput      
             ~~~~~
    The expected type comes from property 'where' which is declared here on type ...
    (...) 

After reading through (the auto-generated) MyModelWhereInput I don't see any sign of the new index.

How can I use Prisma to query over this index?

EDIT: I can successfully do a filtered query like so:

const content_node = await this.prismaService.myModel.findMany({
      where: {
        fieldA: input.fieldA,
        filedB: input.fieldB,
      },
    })

But my investigation of the SQL commands that get executed showed 'QUERY PLAN': 'Seq Scan on my_models'. What abstraction level decides whether to use a sequential scan or an index scan? Should I expect my program to switch strategy once the database gets larger? (I currently have less than 100 items in the database).


Solution

  • I was having this same issue. I solved this by adding a unique constraint on fieldA and fieldB like @@unique([fieldA, fieldB]). I also added a named index to my model. In your scenario, it would look like the following:

    @@unique([fieldA, fieldB])
    @@index([fieldA, fieldB], name: "fieldA_fieldB")