RQ: Raw Query Improvement Epic

[matthewmueller]

Problem

Raw query support provides an important escape hatch for those instances when Prisma doesn't quite support what you need it to support. Over time, we've accumulated a number of raw query bugs. We'd like to fix these issues and start addressing them in a batch.

Goal

We do another pass over our raw query bugs. We understand and fix the ones we can. For the ones we can't, we document the limitation and look for ways to fix them in the future.

Tasks

• RQ: Go through issues and collect all the bugs #12368
• RQ: Prioritize the bugs as tasks based on impact and severity #12369

Umbrella Issues

• RQ: Properly coerce query raw input parameters in the Query Engine #12795
• RQ: Properly coerce query raw result in the Client #12796
• RQ: Send type-hinted query parameters for raw queries (Postgres) #12797

Issues

These issues came from the Bug Cluster Column

Confirmed Bugs

Legend:
🔴 - Blocked
🟡 - Fixable but breaking
🟢 - Fixable
🟤 - Unknown

Float input/output

🔴 Blocked by Float rework

• Floats as IDs are throwing error in raw queries #3665
• executeRaw using Prisma.Join damages float values #9195

Incorrect QE query parameter coercion

🟢 Fixable

• $queryRaw fails when passing bigint as parameter #8121 (bigint)
• Unable to insert Buffer into Bytes pg field using $executeRaw #11834 (buffer)

Incorrect Client result coercion

🟡 BREAKING: Rework needed to propagate type information from QE to Client

• Decimal becomes Number if queried with $queryRaw #9163 (decimal)
• BigInt becomes Number if queried with $queryRaw #7395 (bigint)
• $queryRaw returning numbers for bigint fields #12551 (bigint)
• Prisma returns sequence values with number type from Postgres while using queryRaw #12583 (bigint)
• Improve type conversion and responses for raw queries #4569

Enum input/output

🟤 Need to be discussed, not sure if there's a solution

• Enum values do not map to underlying raw values #9877

Geography type input

🔴 Blocked but a workaround was provided

• Raw query failed. Code: 42704. Message: db error: ERROR: type \"geography\" does not exist #2847

Void output (non existing type)

🔴 Blocked by rust pg driver (according to the issue comments, to be confirmed)

• Using $queryRaw with pg_sleep crashes #3530

Date output

🟤 We currently ignore the timezone when deserializing TimeTz type. Next step: figure out why

• Timezone not returned with queryRaw for timetz data type #7916

Unsupported native database type

🔴 Blocked by native database type support

• Deserializing error when running raw queries on SQL functions that return setof #10448
• Postgres fulltext search - Cannot query tsvector column with $queryRaw #12344
• SQL server - raw Query with GEOMETRY - FAIL #9933 (Point type)
• throw error when queryRaw with unsupported columns #12864

Incorrect PG query parameter type inferring

🟡 BREAKING: Rework needed to pass in the correct query parameters types

• $queryRaw to Postgres failing to correctly insert/cast numeric parameters #10424
• Postgres parameterized interval is passed incorrectly in raw query #4647 (interval)
• Casting error when using integers in raw query #7839
• queryRaw cannot handle some numbers #6818
• Parameterized ExecuteRaw breaks with Postgres Floats #9949
• $queryRaw doesn't allow for typecasts #9197
• Weird behaviour of raw query containing st_dwithin and radius parameter binding #7799
• Query raw run throws the following error incorrect binary data format in bind parameter 3 #7194
• Raw sql Is not working as expected  #9333

Needs text protocol support

🔴 Blocked by Text protocol support

• Some raw queries break on older versions of Postgres #5791

Wrong parameters

🟢 Fixable

• Cannot pass Prisma.empty to $executeRaw function #11233 (Prisma.empty) (minor impact)

Stored procedure output

⚠️ Still needs to be investigated

• Sql Server Exec Stored Procedure #11471 (sql server) (cannot reproduced)
• Prisma Version 3.x breaks calling Stored Procedure from $executeRaw/$queryRaw #9868
• PANIC in query-engine\connectors\sql-query-connector\src\query_ext.rs:58:43index out of bounds: the len is 0 but the index is 0 #8206 (unconfirmed)

SQL prepared statements (wrongly escaped expressions)

🟢 Fixable - Most of those aren't bugs, users just misunderstand prepared statements.

• (mysql) ORDER BY when building Prisma.sql string fails #12456
• $queryRaw template not working with Jest #11454
• rawQuery returns error on to_tsvector parameter #9055 (unconfirmed)
• $queryRaw doesn't work with single quotes #10051
• Error: Raw query failed. Code: 42P18. Message: db error: ERROR: could not determine data type of parameter $1 #12531
• rawQuery returns error on to_tsvector parameter #9055

NULLs in scalar lists

🟢 Fixable

• Getting a "Raw query failed. Code: N/A. Message: error deserializing column 0: a Postgres value was NULL``" error when using Postgres ARRAY_AGG with a nullable column #11339 (array)

No error code

• JSON.stringify loses much of error object information vs using console.log on it #10141

Unconfirmed Bugs

🟤 We can hardly reproduce those issues or they're unconfirmed

• executeRaw bulk insert N/A error  #11532 (too many query parameters, not sure we can do anything)
• PANIC: Unknown column type 243 in /root/.cargo/registry/src/github.com-1ecc6299db9ec823/mysql_common-0.26.0/src/constants.rs:498:18 #11508 (unknown MySQL function called, can't reproduce)
• PANIC: called Result::unwrap() on an Err value: Any { .. } in query-engine/connectors/sql-query-connector/src/error.rs:58:51 #10224 (No schema available, can't reproduce)
• prisma.$executeRaw(DELETE FROM "${table}";) does not work #7510
• Raw query failed when trying to insert batch of data #9326 (insert batch of data using raw)
• PANIC: called Result::unwrap() on an Err value: Any { .. } in query-engine/connectors/sql-query-connector/src/error.rs:58:51 #12641 (duplicate of 10224 - needs investigation)

New features

Not really a bug, it is something we don't support (yet)

• [Postgres] Support Unsupported columns in raw queries #13203
• Generating Prisma client without any model in its schema #11582 (schemaless prisma)
• Raw queries exception error message do not include "code snippet" #10230 (display snippet)
• whereRaw model query option for Prisma Client queries #5560
• Feature request: camelcase prisma.$queryRaw result automatically #4327
• Connection reusability for raw statements #3991
• Auto type raw result #3551
• raw queries - support SQL auto completion based on introspection #3550
• Support executing raw SQL using the Text Protocol #3337
• Provide mapping metadata to Model interfaces #5058
• Expose table/field names #2880
• $executeRaw could support multiple queries from a single string #2868

[idolize]

I've updated the issue #11454 with a repo showing a basic case of how to reproduce the issue: https://github.com/idolize/prisma-issue-11454