`raw` Interfaces are doing str variable substitution incorrectly for PostreSQL

[danigosa]

Bug description

When doing raw queries with ? placeholders for variable substitution, the str passed as value contains "" from code.

This makes code formatters like black useless, and IMHO the query build should not take into account if python literal is 'something' or "somthing", as in PostgreSQL is always 'something'. Looking at code it might be not the only type substitution incorrectly formatted, as we also detected arrays to have issues in raw queries with var substitution.

1657974564.603 12:29:24 Generated query: 
mutation {
  result: queryRaw
  (
    query: "\n    SELECT ?\n    "
    parameters: "[\"Hello world\"]"
  )
}

ERROR P2010: syntax error at end of input

How to reproduce

    res = await db.query_raw(
        """
        SELECT ?
        """,
        "Hello world",
    )

Expected behavior

Prisma information

    res = await tx.query_raw(
        """
        SELECT 'Hello World'
        """,
    )
    assert res == [{"?column?": "Hello World"}]   # True

Environment & setup

- OS: Ubuntu 20.04
- Database: PostgreSQL
- Python version: 3.8.10
- Prisma version: 0.6.6 and master 0.6.7a
prisma               : 3.13.0
prisma client python : 0.6.7a
platform             : debian-openssl-1.1.x
engines              : efdf9b1183dddfd4258cd181a72125755215ab7b
install path         : /home/danigosa/rbcode/.build/.virtualenvs/care-api-hvqas-MI/src/prisma/src/prisma
installed extras     : []

[RobertCraigie]

@danigosa The error you're getting is because of the ?, in PostgreSQL you have to use $N for parameter substitution. For example:

    res = await db.query_raw(
        """
        SELECT $1
        """,
        "Hello world",
    )

I admit this should be better documented, do you have any suggestions? Was there some particular part of the documentation that referenced using ? and wasn't clear that the syntax you need to use differs per database provider?

[danigosa]

@danigosa The error you're getting is because of the ?, in PostgreSQL you have to use $N for parameter substitution. For example:

    res = await db.query_raw(
        """
        SELECT $1
        """,
        "Hello world",
    )

I admit this should be better documented, do you have any suggestions? Was there some particular part of the documentation that referenced using ? and wasn't clear that the syntax you need to use differs per database provider?

Yes that works, and yes it should be documented that the raw format is provider dependant! Just a line saying that raw format and variable substitution depends on engine raw format would suffice.

[danigosa]

This is not working on excute_raw:

    orgId = 1
    await tx.execute_raw(
        f"""
        SET LOCAL jwt.claims.orgId TO $1;
        """,
        orgId,
    )

tests/test_db.py:31: in test_internals
    await tx.execute_raw(
../../.build/.virtualenvs/care-api-hvqas-MI/src/prisma/src/prisma/client.py:1121: in execute_raw
    resp = await self._execute(
../../.build/.virtualenvs/care-api-hvqas-MI/src/prisma/src/prisma/client.py:1209: in _execute
    return await self._engine.query(builder.build(), tx_id=self._tx_id)
../../.build/.virtualenvs/care-api-hvqas-MI/src/prisma/src/prisma/engine/query.py:198: in query
    return await self.request(
../../.build/.virtualenvs/care-api-hvqas-MI/src/prisma/src/prisma/engine/http.py:119: in request
    return utils.handle_response_errors(resp, errors_data)
../../.build/.virtualenvs/care-api-hvqas-MI/src/prisma/src/prisma/engine/utils.py:128: in handle_response_errors
    raise exc(error)
E   prisma.errors.RawQueryError: db error: ERROR: syntax error at or near "$1"

This works:

    orgId = 1
    await tx.execute_raw(
        f"""
        SET LOCAL jwt.claims.orgId TO '{orgId}';
        """
    )