search

prisma / docs

📚 Prisma Documentation
https://www.prisma.io/docs
Apache License 2.0
981 stars 777 forks source link

Cannot query table in Postgres with `$queryRawUnsafe` without `@@map` #3798

Open jkomyno opened 2 years ago

jkomyno commented 2 years ago

Bug description

Querying a table/model in Postgres seems impossible, unless we explicitly set @@map("table_name") in the Prisma schema.

I wonder if it's about something that Prisma does under the hood. Consider a model Tag, explicitly renamed to tag via @@map, and a model Post, not renamed.

model Tag {
  id Int @id

  @@map("tag")
}

model Post {
  id Int @id
}

The following all fail with similar messages (see the snapshots in the attached TS code):

The following succeed.

Moreover, the error messages are misleading. E.g., for SELECT * FROM Post, it fails with an error e where:

How to reproduce

Expected behavior

Prisma information

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgres"
  url      = env("DATABASE_URL")
}

model Tag {
  id Int @id

  @@map("tag")
}

model Post {
  id Int @id
}
// index.test.ts

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function clean() {
  const cleanPrismaPromises = [prisma.post.deleteMany(), prisma.tag.deleteMany()]
  await prisma.$transaction(cleanPrismaPromises)
}

describe('queryRaw casing issue', () => {
  const n = 10

  beforeEach(async () => {
    await clean()
  })

  it('$queryRawUnsafe tag succeeds with @@map', async () => {
    await prisma.$queryRawUnsafe<unknown[]>(`SELECT * FROM tag`)
  })

  it('$queryRaw tag succeeds with @@map', async () => {
    await prisma.$queryRaw`SELECT * FROM tag`
  })

  it('$queryRawUnsafe "Post" succeeds', async () => {
    await prisma.$queryRawUnsafe<unknown[]>(`SELECT * FROM "Post"`)
  })

  it('$queryRawUnsafe public."Post" succeeds', async () => {
    await prisma.$queryRawUnsafe<unknown[]>(`SELECT * FROM public."Post"`)
  })

  it('$queryRawUnsafe post fails', async () => {
    expect.assertions(2)

    try {
      await prisma.$queryRawUnsafe<unknown[]>(`SELECT * FROM "post"`)
    } catch (error) {
      const e = error as Error
      expect(e.message).toMatchInlineSnapshot(`
        "
        Invalid \`prisma.$queryRaw()\` invocation:

        Raw query failed. Code: \`42P01\`. Message: \`relation \\"post\\" does not exist\`"
      `)

      // @ts-ignore
      expect(e.code).toEqual('P2010')
    }
  })

  it('$queryRawUnsafe public.post fails', async () => {
    expect.assertions(2)

    try {
      await prisma.$queryRawUnsafe<unknown[]>(`SELECT * FROM public."post"`)
    } catch (error) {
      const e = error as Error
      expect(e.message).toMatchInlineSnapshot(`
        "
        Invalid \`prisma.$queryRaw()\` invocation:

        Raw query failed. Code: \`42P01\`. Message: \`relation \\"public.post\\" does not exist\`"
      `)

      // @ts-ignore
      expect(e.code).toEqual('P2010')
    }
  })
})

Environment & setup

Prisma Version

prisma                  : 4.3.1
@prisma/client          : 4.3.1
Current platform        : darwin-arm64
Query Engine (Node-API) : libquery-engine c875e43600dfe042452e0b868f7a48b817b9640b (at ../../../../../Library/pnpm/store/v3/tmp/dlx-26846/node_modules/.pnpm/@prisma+engines@4.3.1/node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
Migration Engine        : migration-engine-cli c875e43600dfe042452e0b868f7a48b817b9640b (at ../../../../../Library/pnpm/store/v3/tmp/dlx-26846/node_modules/.pnpm/@prisma+engines@4.3.1/node_modules/@prisma/engines/migration-engine-darwin-arm64)
Introspection Engine    : introspection-core c875e43600dfe042452e0b868f7a48b817b9640b (at ../../../../../Library/pnpm/store/v3/tmp/dlx-26846/node_modules/.pnpm/@prisma+engines@4.3.1/node_modules/@prisma/engines/introspection-engine-darwin-arm64)
Format Binary           : prisma-fmt c875e43600dfe042452e0b868f7a48b817b9640b (at ../../../../../Library/pnpm/store/v3/tmp/dlx-26846/node_modules/.pnpm/@prisma+engines@4.3.1/node_modules/@prisma/engines/prisma-fmt-darwin-arm64)
Format Wasm             : @prisma/prisma-fmt-wasm 4.3.0-32.c875e43600dfe042452e0b868f7a48b817b9640b
Default Engines Hash    : c875e43600dfe042452e0b868f7a48b817b9640b
Studio                  : 0.473.0
tomhoule commented 2 years ago

I think you are missing quotes around the table names. What does SELECT * FROM "Post" return, without the @@map?

jkomyno commented 2 years ago

Thank you @tomhoule. While the misleading error part of the issue still stands, I appreciate that your suggestion indeed allows to query.

Beforehand, I had also tried with SELECT FROM 'Post', SELECT FROM `Post` (with backslash escaping, hard to show here in markdown), and the equivalent version with public. without success (also obtaining a syntax error with error code 42601 when using single quotes. I didn't think about using double quotes.

Maybe the docs could showcase examples of how to run raw queries in different providers?

janpio commented 2 years ago

How to quote identifiers and strings in raw queries might indeed be worth documenting some way, maybe via the Dataguide.