Improve type conversion and responses for raw queries

[ivashog]

Bug description

I am using prisma.$queryRaw for creating mapbox vector tiles from db using PostgreSQL and PostGIS

I expect get node.js Buffer in mvt field but prisma query engine serialize the db response to text and I can't correctly convert it to a buffer

Buffer.from(data.mvt as string, 'binary')

is not help!

How to reproduce

Expected behavior

prism should return a Buffer in place of text for binary data

Prisma information

Environment & setup

• OS: PopOS(Ubuntu 20.10)
• Database: PostgreSQL
• Node.js version: v14.15.1
• Prisma version:

@prisma/cli          : 2.13.0
@prisma/client       : 2.12.1
Current platform     : debian-openssl-1.1.x
Query Engine         : query-engine 833ab05d2a20e822f6736a39a27de4fc8f6b3e49 (at node_modules/@prisma/engines/query-engine-debian-openssl-1.1.x)
Migration Engine     : migration-engine-cli 833ab05d2a20e822f6736a39a27de4fc8f6b3e49 (at node_modules/@prisma/engines/migration-engine-debian-openssl-1.1.x)
Introspection Engine : introspection-core 833ab05d2a20e822f6736a39a27de4fc8f6b3e49 (at node_modules/@prisma/engines/introspection-engine-debian-openssl-1.1.x)
Format Binary        : prisma-fmt 833ab05d2a20e822f6736a39a27de4fc8f6b3e49 (at node_modules/@prisma/engines/prisma-fmt-debian-openssl-1.1.x)
Studio               : 0.329.0

[pantharshit00]

Hello @ivashog

Can you please share the database schema of the above table with a sample row so that I can try reproducing the problem? Thanks!

[paulm17]

Sorry to barge in. But I have exact the same issue using $queryRaw.

I'm using ulid to generate my ids.

https://www.npmjs.com/package/ulid

My table:

model anki_lists {
id         Bytes        @id @db.Binary(26)
name       String?      @db.VarChar(255)
created_at DateTime?    @db.Timestamp(0)
updated_at DateTime?    @db.Timestamp(0)
deleted_at DateTime?    @db.Timestamp(0)
anki_cards anki_cards[]
}

I expect to get back something like:

+----------------------------+------------+
| id                         | name       |
+----------------------------+------------+
| 01esy4337ym3yknze675g3grhy | Pimsleur 1 |
| 01et07hamgxc9e92e72mq78wph | Pimsleur 2 |
+----------------------------+------------+
2 rows in set (0.023 sec)

Except when I console log the result of the raw query:

[
{ id: 'MDFlc3k0MzM3eW0zeWtuemU2NzVnM2dyaHk=', name: 'Pimsleur 1' },
{ id: 'MDFldDA3aGFtZ3hjOWU5MmU3Mm1xNzh3cGg=', name: 'Pimsleur 2' }
]

I should be receiving a buffer instead of an encoded string?

Let me know what you need to solve this issue.

Thanks

[pantharshit00]

Hey @paulm17

This is expected. Since your data is binary, raw query will return it as base64 encoded data. You will need to deserialize it like so:

import { PrismaClient } from "@prisma/client";

async function main() {
  const prisma = new PrismaClient();

  let data = await prisma.$queryRaw<Array<{ id: string; name: string }>>(
    "select * from anki_lists"
  );
  data = data.map((d) => ({
    ...d,
    id: Buffer.from(d.id, "base64").toString(),
  }));
  console.log(data);
  prisma.$disconnect();
}

main();

I would suggest you rather use the client api directly as it supports binary type and correctly encodes it into a buffer:

import { PrismaClient } from "@prisma/client";

async function main() {
  const prisma = new PrismaClient();

  let data = await prisma.anki_lists.findMany();
  let transformed = data.map((d) => ({
    ...d,
    id: d.id.toString(), // id is `Buffer` so converting into a string for printing
  }));
  console.log(transformed);
  prisma.$disconnect();
}

main();

[paulm17]

Thank you! I was using an incorrect buffer encoding. I tried all of them except base64 lol.

[pimeys]

We'll be extending the raw api into something that returns type info in the protocol back to the client. Talked with @timsuchanek we should always add the type to the return values, so the client could then wrap it into an object if needed. Now the migrate GA and native types GA are bringing too much heat, so we agreed that we'll just do this in the next sprint.

[pimeys]

Example:

let r#type = match &p_value {
    quaint::Value::Integer(_) => "integer",
    quaint::Value::Float(_) => "float",
    quaint::Value::Double(_) => "double",
    quaint::Value::Text(_) => "text",
    quaint::Value::Enum(_) => "enum",
    quaint::Value::Bytes(_) => "bytes",
    quaint::Value::Boolean(_) => "bool",
    quaint::Value::Char(_) => "char",
    quaint::Value::Array(_) => todo!(),
    quaint::Value::Numeric(_) => "numeric",
    quaint::Value::Json(_) => "json",
    quaint::Value::Xml(_) => "xml",
    quaint::Value::Uuid(_) => "uuid",
    quaint::Value::DateTime(_) => "datetime",
    quaint::Value::Date(_) => "date",
    quaint::Value::Time(_) => "time",
};
inner_object.insert(r#type.into(), Value::from(p_value));
object.insert(column_name, Value::Object(inner_object));

You get the point.

[pantharshit00]

Changed the title and labels to reflect this is a feature request now.

[Weakky]

Hey folks,

This feature was implemented in prisma/prisma-engines#2847. It will be available in the next release under the improvedQueryRaw feature flag.

Beware that enabling improvedQueryRaw is a breaking change. Release notes will contain information as to how to upgrade (so will the documentation).

Thanks for reporting 🙏