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

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):

SELECT * FROM post

await prisma.$queryRawUnsafe<unknown[]>(`SELECT * FROM post`)

SELECT * FROM public.post

await prisma.$queryRawUnsafe<unknown[]>(`SELECT * FROM public.post`)

SELECT * FROM Post

await prisma.$queryRawUnsafe<unknown[]>(`SELECT * FROM Post`)

SELECT * FROM public.Post

await prisma.$queryRawUnsafe<unknown[]>(`SELECT * FROM public.Post`)

The following succeed.

SELECT * FROM "Post"

await prisma.$queryRawUnsafe<unknown[]>(`SELECT * FROM "Post"`)

SELECT * FROM public."Post"

await prisma.$queryRawUnsafe<unknown[]>(`SELECT * FROM public."Post"`)

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

e.message is

    Invalid `prisma.$queryRaw()` invocation:


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

e.code is P2010 (so the error code 42P01 referenced above is confusing)
the query involves a normal table, not a relation (which also has a different case w.r.t. Post)
$queryRawUnsafe is used, not $queryRaw (although I understand they may re-use the same code flow)

How to reproduce

prisma db push
ts-node index.test.ts (requires jest)

Expected behavior

It should be possible to reference a table in a raw query without being forced to use @@map
The Raw query failed error message should report Invalid prisma.$queryRawUnsafe() invocation rather than Invalid prisma.$queryRaw() invocation

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

OS: Mac OS M1
Database: postgres:13
Node.js version: 18.8.0

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/@[email protected]/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/@[email protected]/node_modules/@prisma/engines/migration-engine-darwin-arm64)
Introspection Engine    : introspection-core c875e43600dfe042452e0b868f7a48b817b9640b (at ../../../../../Library/pnpm/store/v3/tmp/dlx-26846/node_modules/.pnpm/@[email protected]/node_modules/@prisma/engines/introspection-engine-darwin-arm64)
Format Binary           : prisma-fmt c875e43600dfe042452e0b868f7a48b817b9640b (at ../../../../../Library/pnpm/store/v3/tmp/dlx-26846/node_modules/.pnpm/@[email protected]/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

Sep 05 '22 14:09 jkomyno

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

Sep 05 '22 14:09 tomhoule

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?

Sep 05 '22 14:09 jkomyno

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

Sep 06 '22 22:09 janpio