Raw query returning a bigint for an int field

[iGarym]

Bug description

When using prisma.$queryRaw to query data, columns of the Int.Unsigned type in MySQL will be converted to and returned as the Bigint type.

How to reproduce

Table Structure & Example Data:

CREATE TABLE `folder` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `pid` int unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_pid` (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin;

INSERT INTO `folder` (`id`, `name`, `pid`) VALUES
(1, 'folder-1', 0),
(2, 'folder-2', 0),
(3, 'folder-3', 0),
(4, 'folder-1-1', 1),
(5, 'folder-1-2', 1),
(6, 'folder-2-1', 2),
(7, 'folder-2-2', 2),
(8, 'folder-3-1', 3),
(9, 'folder-3-2', 3);

Execute the following query:

const list = await prisma.$queryRaw`
  WITH RECURSIVE folder_cte AS (
    SELECT id, name, pid
      FROM folder
      WHERE id IN (5, 7)
    UNION ALL
      SELECT f.id, f.name, f.pid
        FROM folder f
        INNER JOIN folder_cte fc ON f.id = fc.pid
  )
  SELECT DISTINCT * FROM folder_cte ORDER BY id ASC;
`

Returns rows:

[
  { "id": 1n, "name": "folder-1", "pid": 0n },
  { "id": 2n, "name": "folder-2", "pid": 0n },
  { "id": 5n, "name": "folder-1-2", "pid": 1n },
  { "id": 7n, "name": "folder-2-2", "pid": 2n },
]

Expected behavior

Int.Unsigned is 4-byte (32-bit). According to the documentation, it should return a Number type, not a BigInt.

Expected returns rows:

[
  { "id": 1, "name": "folder-1", "pid": 0 },
  { "id": 2, "name": "folder-2", "pid": 0 },
  { "id": 5, "name": "folder-1-2", "pid": 1 },
  { "id": 7, "name": "folder-2-2", "pid": 2 },
]

Prisma information

generator client {
  provider      = "prisma-client-js"
  binaryTargets = ["native", "rhel-openssl-1.0.x"]
}

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

model Folder {
  id        Int      @id @default(autoincrement()) @db.UnsignedInt
  name      String   @db.VarChar(64)
  pid       Int      @default(0) @db.UnsignedInt

  @@index([pid], map: "idx_pid")
  @@map("folder")
}

const prisma = new PrismaClient({
  datasourceUrl: `MY_DATASOURCE_URL`,
  log: ['warn', 'error'],
})

Environment & setup

• OS: macOS
• Database: MySQL@8.0
• Node.js version: v16.19.0

Prisma Version

prisma                  : 5.13.0
@prisma/client          : 5.13.0
Computed binaryTarget   : darwin
Operating System        : darwin
Architecture            : x64
Node.js                 : v16.19.0
Query Engine (Node-API) : libquery-engine b9a39a7ee606c28e3455d0fd60e78c3ba82b1a2b (at ../../node_modules/.pnpm/@prisma+engines@5.13.0/node_modules/@prisma/engines/libquery_engine-darwin.dylib.node)
Schema Engine           : schema-engine-cli b9a39a7ee606c28e3455d0fd60e78c3ba82b1a2b (at ../../node_modules/.pnpm/@prisma+engines@5.13.0/node_modules/@prisma/engines/schema-engine-darwin)
Schema Wasm             : @prisma/prisma-schema-wasm 5.13.0-23.b9a39a7ee606c28e3455d0fd60e78c3ba82b1a2b
Default Engines Hash    : b9a39a7ee606c28e3455d0fd60e78c3ba82b1a2b
Studio                  : 0.500.0

[Druue]

Hey @iGarym, I took a quick look and was able to confirm the following data with the query and schema you provided!

[
  { id: 1n, name: 'folder-1', pid: 0n }
  { id: 2n, name: 'folder-2', pid: 0n }
  { id: 5n, name: 'folder-1-2', pid: 1n }
  { id: 7n, name: 'folder-2-2', pid: 2n }
]

So I took a quick look at the MySQL documentation and the problem here comes from it being an unsigned 32-bit value rather than a signed 32-bit value which gives it an upper range target of 4294967295 which is beyond the scope of js' 32-bit number. So this isn't a bug per se, it really should be a bigint.

I definitely see where the confusion comes from, however, we should probable have another addition in the table you mentioned that clarifies where unsigned integers land.