Inconsistent column data: Unexpected conversion failure from Number to BigInt error when using @prisma/adapter-pg

[chris-addison]

Hi all, I've hit an issue when trying out the @prisma/adapter-pg preview feature.

I'm using the latest v5.13.0 and I can resolve the issue by removing the "relationJoins" from the preview features

Here's the error I'm seeing in the console:

  20     include: { user: true },
  21 };
  22 
→ 23 const result = await this.#prisma.baseUser.findUnique(
Inconsistent column data: Unexpected conversion failure for field User.intId from Number(1374511782084.0) to BigInt.
    at In.handleRequestError (/**/node_modules/@prisma/client/runtime/library.js:122:6854)
    at In.handleAndLogRequestError (/**/node_modules/@prisma/client/runtime/library.js:122:6188)
    at In.request (/**/node_modules/@prisma/client/runtime/library.js:122:5896)
    at l (/**/node_modules/@prisma/client/runtime/library.js:127:11167)
    at PrismaUserRepository.getUnsafe (/**/src/infrastructure/repository/v2/prisma/prisma-user.repository.ts:23:24)

Here's roughly how I'm calling Prisma:

async function getUnsafe(id: string | bigint): Promise<User | null> {
    const query = {
        where: typeof id === 'bigint' ? { intId: id } : { id },
        include: { user: true },
    };

    const result = await prisma.baseUser.findUnique(query);
    if (!result) {
        return null;
    }

    return toUser(result);
}

type PrismaBaseUser = Prisma.BaseUserGetPayload<{ include: { user: true } }>;

function toUser(user: PrismaBaseUser): User {
    return {
        id: user.id,
        intId: user.intId,
        name: user.user.name,
        email: user.user.email,
        avatarUrl: user.user.avatar,
    };
}

Here's a subset of my schema:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["relationJoins"]
}

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

model BaseUser {
  id                String       @id @db.Uuid
  intId             BigInt       @unique
  createdAt         DateTime     @default(now())
  updatedAt         DateTime     @updatedAt
  favorites         Repository[] @relation("UserFavorites")
  ownedRepositories Repository[]
  user              User         @relation("userById", fields: [id], references: [id])
  userByIntId       User         @relation("userByIntId", fields: [intId], references: [intId])

  // Indexing intId for faster lookups
  @@index([intId(ops: Int8BloomOps)], type: Brin)
}

model User {
  id              String    @id @db.Uuid
  correlationId   String    @unique @db.Uuid
  intId           BigInt    @unique
  name            String
  email           String?
  locale          String?
  avatar          String?
  createdAt       DateTime  @default(now())
  updatedAt       DateTime
  deletedAt       DateTime?
  baseUser        BaseUser? @relation("userById")
  baseUserByIntId BaseUser? @relation("userByIntId")

  // Indexing intId for faster lookups
  @@index([intId(ops: Int8BloomOps)], type: Brin)
  // Indexing email for faster lookups
  @@index([email], type: Hash)
}

[Druue]

Hey @chris-addison,

I've tried to take a look but the snippets of the schema and typescript you've provided are missing information. Can you please elaborate the relations that you're using in User, BaseUser, and this toUser() function.

Your baseUser query specifically has an include on user which is not defined in your schema.

I cannot replicate this simply with just the BaseUser model as provided, and the current state of the User model is not helpful

Thanks in advance!

[chris-addison]

Hey @chris-addison,

I've tried to take a look but the snippets of the schema and typescript you've provided are missing information. Can you please elaborate the relations that you're using in User, BaseUser, and this toUser() function.

Your baseUser query specifically has an include on user which is not defined in your schema.

I cannot replicate this simply with just the BaseUser model as provided, and the current state of the User model is not helpful

Thanks in advance!

Apologies for the difficulty @Druue ! This should work for you:

Schema:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["relationJoins"]
}

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

model BaseUser {
  id                String       @id @db.Uuid
  intId             BigInt       @unique
  createdAt         DateTime     @default(now())
  updatedAt         DateTime     @updatedAt
  favorites         Repository[] @relation("UserFavorites")
  ownedRepositories Repository[]
  user              User         @relation("userById", fields: [id], references: [id])
  userByIntId       User         @relation("userByIntId", fields: [intId], references: [intId])

  // Indexing intId for faster lookups
  @@index([intId(ops: Int8BloomOps)], type: Brin)
}

model User {
  id              String    @id @db.Uuid
  correlationId   String    @unique @db.Uuid
  intId           BigInt    @unique
  name            String
  email           String?
  locale          String?
  avatar          String?
  createdAt       DateTime  @default(now())
  updatedAt       DateTime
  deletedAt       DateTime?
  baseUser        BaseUser? @relation("userById")
  baseUserByIntId BaseUser? @relation("userByIntId")

  // Indexing intId for faster lookups
  @@index([intId(ops: Int8BloomOps)], type: Brin)
  // Indexing email for faster lookups
  @@index([email], type: Hash)
}

Function:

async function getUnsafe(id: string | bigint): Promise<User | null> {
    const query = {
        where: typeof id === 'bigint' ? { intId: id } : { id },
        include: { user: true },
    };

    const result = await prisma.baseUser.findUnique(query);
    if (!result) {
        return null;
    }

    return toUser(result);
}

type PrismaBaseUser = Prisma.BaseUserGetPayload<{ include: { user: true } }>;

function toUser(user: PrismaBaseUser): User {
    return {
        id: user.id,
        intId: user.intId,
        name: user.user.name,
        email: user.user.email,
        avatarUrl: user.user.avatar,
    };
}

[Druue]

Hey, @chris-addison, I took another look, but I still can't reproduce your issue :/

Could you please provide a minimal reproduction for this as I'm not sure what I'm missing
Maybe it's due to the kind of data that you're using? unsure really

I'm using the following schema

generator client {
  provider        = "prisma-client-js"
  output          = "../node_modules/.prisma/client"
  previewFeatures = ["driverAdapters", "relationJoins"]
}

datasource db {
  provider = "postgresql"
  url      = env("TEST_POSTGRES_URI")
}

model BaseUser {
  id          String @id @db.Uuid
  intId       BigInt @unique
  user        User   @relation("userById", fields: [id], references: [id])
  userByIntId User   @relation("userByIntId", fields: [intId], references: [intId])

  @@index([intId(ops: Int8BloomOps)], type: Brin)
}

model User {
  id              String    @id @db.Uuid
  correlationId   String    @unique @db.Uuid
  intId           BigInt    @unique
  baseUser        BaseUser? @relation("userById")
  baseUserByIntId BaseUser? @relation("userByIntId")

  @@index([intId(ops: Int8BloomOps)], type: Brin)
}

And the following typescript code. I tried both string and bigint variants without seeing any difference.

import { Prisma, PrismaClient, User } from '.prisma/client'

import { Pool } from "pg";
import { PrismaPg } from "@prisma/adapter-pg";

const connectionString = `${process.env.TEST_POSTGRES_URI as string}`;

const pool = new Pool({ connectionString });
const adapter = new PrismaPg(pool);

const prisma = new PrismaClient({
  adapter,
  log: ["query"],
});

const id = "123e4567-e89b-12d3-a456-426655440000";
const correlationId = "123e4567-e89b-12d3-a456-426655440001";

const populate = async () => {
  const intId = 1;
  await prisma.user.create({
    data: {
      id,
      intId,
      correlationId,
    },
  });

  await prisma.baseUser.create({
    data: {
      id,
      intId,
    },
  });
};

async function getUnsafe(id: string | bigint): Promise<User | null> {
  const query = {
    where: typeof id === "bigint" ? { intId: id } : { id },
    include: { user: true },
  };

  const result = await prisma.baseUser.findUnique(query);
  if (!result) {
    return null;
  }

  return toUser(result);
}

type PrismaBaseUser = Prisma.BaseUserGetPayload<{ include: { user: true } }>;

function toUser(user: PrismaBaseUser): User {
  return {
    id: user.id,
    correlationId: user.user.correlationId,
    intId: user.intId,
  };
}

async function test() {
  // const u = await getUnsafe("123e4567-e89b-12d3-a456-426655440000");
  const u = await getUnsafe(BigInt(1));
  console.log(u);
}

async function main() {
  await populate();
  return test();
}

main()
  .catch((e) => console.error(e))
  .finally(async () => {
    prisma.$disconnect;
  });

The following is when passing BigInt(1)

prisma:query INSERT INTO "public"."User" ("id","correlationId","intId") VALUES ($1,$2,$3) RETURNING "public"."User"."id", "public"."User"."correlationId", "public"."User"."intId"
prisma:query INSERT INTO "public"."BaseUser" ("id","intId") VALUES ($1,$2) RETURNING "public"."BaseUser"."id", "public"."BaseUser"."intId"
prisma:query SELECT "t1"."id", "t1"."intId", "BaseUser_user"."__prisma_data__" AS "user" FROM "public"."BaseUser" AS "t1" LEFT JOIN LATERAL (SELECT JSONB_BUILD_OBJECT('id', "t2"."id", 'correlationId', "t2"."correlationId", 'intId', "t2"."intId") AS "__prisma_data__" FROM "public"."User" AS "t2" WHERE "t1"."id" = "t2"."id" LIMIT $1) AS "BaseUser_user" ON true WHERE ("t1"."intId" = $2 AND 1=1) LIMIT $3
{
  id: '123e4567-e89b-12d3-a456-426655440000',
  correlationId: '123e4567-e89b-12d3-a456-426655440001',
  intId: 1n
}

and this with uuid

prisma:query INSERT INTO "public"."User" ("id","correlationId","intId") VALUES ($1,$2,$3) RETURNING "public"."User"."id", "public"."User"."correlationId", "public"."User"."intId"
prisma:query INSERT INTO "public"."BaseUser" ("id","intId") VALUES ($1,$2) RETURNING "public"."BaseUser"."id", "public"."BaseUser"."intId"
prisma:query SELECT "t1"."id", "t1"."intId", "BaseUser_user"."__prisma_data__" AS "user" FROM "public"."BaseUser" AS "t1" LEFT JOIN LATERAL (SELECT JSONB_BUILD_OBJECT('id', "t2"."id", 'correlationId', "t2"."correlationId", 'intId', "t2"."intId") AS "__prisma_data__" FROM "public"."User" AS "t2" WHERE "t1"."id" = "t2"."id" LIMIT $1) AS "BaseUser_user" ON true WHERE ("t1"."id" = $2 AND 1=1) LIMIT $3
{
  id: '123e4567-e89b-12d3-a456-426655440000',
  correlationId: '123e4567-e89b-12d3-a456-426655440001',
  intId: 1n
}

Setup Information:

Postgres                : 16.2 (Debian 16.2-1.pgdg120+2)
prisma                  : 0.0.0
@prisma/client          : 0.0.0
Computed binaryTarget   : darwin-arm64
Operating System        : darwin
Architecture            : arm64
Node.js                 : v21.7.3
Query Engine (Node-API) : libquery-engine 612a7f7142ab73baf44704be23cbac7b7df1f7fc (at ../../../packages/engines/libquery_engine-darwin-arm64.dylib.node)
Schema Engine           : schema-engine-cli 612a7f7142ab73baf44704be23cbac7b7df1f7fc (at ../../../packages/engines/schema-engine-darwin-arm64)
Schema Wasm             : @prisma/prisma-schema-wasm 5.14.0-9.612a7f7142ab73baf44704be23cbac7b7df1f7fc
Default Engines Hash    : 612a7f7142ab73baf44704be23cbac7b7df1f7fc
Studio                  : 0.500.0
Preview Features        : driverAdapters, relationJoins

[chris-addison]

Thank you for checking! I'll try to put something together this weekend and get back to you on Monday

[chris-addison]

@Druue here's the repro: https://github.com/chris-addison/PrismaReproInconsistentColumnData/tree/main

I'm using: debian/postgresql-16 16.2-1.pgdg120+2

Here's my system info:

npx prisma version
Environment variables loaded from .env
prisma                  : 5.13.0
@prisma/client          : 5.13.0
Computed binaryTarget   : darwin-arm64
Operating System        : darwin
Architecture            : arm64
Node.js                 : v20.12.2
Query Engine (Node-API) : libquery-engine b9a39a7ee606c28e3455d0fd60e78c3ba82b1a2b (at node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
Schema Engine           : schema-engine-cli b9a39a7ee606c28e3455d0fd60e78c3ba82b1a2b (at node_modules/@prisma/engines/schema-engine-darwin-arm64)
Schema Wasm             : @prisma/prisma-schema-wasm 5.13.0-23.b9a39a7ee606c28e3455d0fd60e78c3ba82b1a2b
Default Engines Hash    : b9a39a7ee606c28e3455d0fd60e78c3ba82b1a2b
Studio                  : 0.500.0
Preview Features        : driverAdapters, relationJoins

[Druue]

Okay! I can reproduce this! Thank you so much! ✨ (also that removing the relationJoins preview feature resolves the issue)

PrismaClientKnownRequestError: 
Invalid `prisma.baseUser.findUnique()` invocation in
/src/repros/PrismaReproInconsistentColumnData/main.ts:44:40

  41   include: { user: true },
  42 };
  43 
→ 44 const result = await prisma.baseUser.findUnique(
Inconsistent column data: Unexpected conversion failure for field User.intId from Number(1374511782084.0) to BigInt.
    at In.handleRequestError (/src/repros/PrismaReproInconsistentColumnData/node_modules/@prisma/client/runtime/library.js:122:6854)
    at In.handleAndLogRequestError (/src/repros/PrismaReproInconsistentColumnData/node_modules/@prisma/client/runtime/library.js:122:6188)
    at In.request (/src/repros/PrismaReproInconsistentColumnData/node_modules/@prisma/client/runtime/library.js:122:5896)
    at async l (/src/repros/PrismaReproInconsistentColumnData/node_modules/@prisma/client/runtime/library.js:127:11167)
    at async getUnsafe (/src/repros/PrismaReproInconsistentColumnData/main.ts:44:18)
    at async test (/src/repros/PrismaReproInconsistentColumnData/main.ts:53:13) {
  code: 'P2023',
  clientVersion: '5.13.0',
  meta: {
    modelName: 'BaseUser',
    message: 'Unexpected conversion failure for field User.intId from Number(1374511782084.0) to BigInt.'
  }
}