Parameterized ExecuteRaw breaks with Postgres Floats

[aust1nz]

Bug description

Prisma's executeRaw function doesn't seem to work properly with Postgres's float types (both DoublePrecision and Real.) I've shared my findings and reproduction below.

How to reproduce

This repository contains a minimal reproduction: https://github.com/aust1nz/prisma-float-bugreport. You'll need to run npx prisma migrate and run the database with Docker Compose. Running npm start will run the src/insert.ts script.

Here's some more detail, pulled from the repo:

Prisma Schema:

model Employee {
  id           Int      @id @default(autoincrement())
  createdAt    DateTime @default(now())
  title        String   @db.VarChar(255)
  salary       Decimal  @db.Decimal(8, 2)
  fte          Float?   @db.DoublePrecision
  fteAlternate Float?   @db.Real
}

Insertion Issues

const employees = [
  {
    title: "Test Person Number 1",
    salary: 45000,
    fte: 1,
    fteAlternate: 1,
  },
  {
    title: "Test Person Number 2",
    salary: 45000,
    fte: 1,
    fteAlternate: 1,
  },
  {
    title: "Test Person Number 3",
    salary: 45000,
    fte: 1,
    fteAlternate: 1,
  },
  {
    title: "Test Person Number 4",
    salary: 45000,
    fte: 1,
    fteAlternate: 1,
  },
];

const insert = async () => {
  const prisma = new PrismaClient();

  // This works!
  const firstEmployee = await prisma.employee.create({
    data: employees[0],
  });

  // This works too!
  await prisma.$executeRaw`INSERT INTO "Employee" (title, salary, fte, "fteAlternate") VALUES ('Test Person Number 2', 45000, 1, 1)`;

  // This saves the record, but the fte field is stored as 5e-324.
  await prisma.$executeRaw`
    INSERT INTO "Employee"
                (title, salary, fte)
    VALUES (${employees[2].title}, ${employees[2].salary}, ${employees[2].fte});
  `;

  // This throws an error:
  // Raw query failed. Code: `22P03`. Message: `db error: ERROR: incorrect binary data format in bind parameter 3`
  await prisma.$executeRaw`
    INSERT INTO "Employee"
                (title, salary, "fteAlternate")
    VALUES (${employees[3].title}, ${employees[3].salary}, ${employees[3].fteAlternate});
  `;
};

Expected behavior

Inserting with the prisma API and directly inserting raw strings into the database both have the intended effect. I'd expect parameters in the executeRaw to insert accordingly.

Prisma information

Prisma info is shared above in the How to reproduce section.

Environment & setup

This occurs on MacOS. The database is a Postgres database running on Docker. Using Node 14.15.4.

Prisma Version

prisma                  : 3.3.0
@prisma/client          : 3.3.0
Current platform        : darwin
Query Engine (Node-API) : libquery-engine 33838b0f78f1fe9052cf9a00e9761c9dc097a63c (at node_modules/@prisma/engines/libquery_engine-darwin.dylib.node)
Migration Engine        : migration-engine-cli 33838b0f78f1fe9052cf9a00e9761c9dc097a63c (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine    : introspection-core 33838b0f78f1fe9052cf9a00e9761c9dc097a63c (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary           : prisma-fmt 33838b0f78f1fe9052cf9a00e9761c9dc097a63c (at node_modules/@prisma/engines/prisma-fmt-darwin)
Default Engines Hash    : 33838b0f78f1fe9052cf9a00e9761c9dc097a63c
Studio                  : 0.437.0

[aust1nz]

Hi! After a little more searching, it looks like two other issues may be dealing with a similar root issue
#9195
#3665

Looks like a Prisma team member may have identified a query engine issue https://github.com/prisma/prisma/issues/3665#issuecomment-696553632 and suggested a workaround to manually sanitize floats.

This does work.

  // This is a current workaround: manually sanitize floats and insert them directly
  // into an executeRawUnsafe call; parameterize the rest.
  await prisma.$executeRawUnsafe(
    `
    INSERT INTO "Employee"
                (title, salary, fte, "fteAlternate")
    VALUES ($1, $2, ${employees[3].fte}, ${employees[3].fteAlternate});
  `,
    employees[3].title,
    employees[3].salary
  );

[Weakky]

Hey,

This issue was fixed by 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 🙏