Transform `BigInt` fields to `number` or `string` globally (maybe via Prisma Client extensions)?
Casting Postgres BigInt as TS Number
PostgreSQL: `Int` type should be able to be mapped to `@db.BigInt` (int8)
Changing a column from `Int` to `BigInt` creates an invalid migration `Postgres`
I am using prisma but have been using raw queries for a handful of my complex fetches while I migrate to using it fully. I'm running into an issue where, from a raw query, a field that is an Int in both my schema and database is returning as a bigint which is throwing an error when I try to serialize the result later on. The problematic field is country_prefix.
The schema model for the table that country is coming from:
model countries {
id Int (autoincrement()) .UnsignedInt
country_name String .VarChar(256)
country_prefix Int .UnsignedInt
...The query:
const countries = await prisma.$queryRaw\` SELECT pc.country_prefix, ct.city_name, ct.city_prefix, ...
The object I'm getting back:
{
country_prefix: 54n,
city_name: 'Villa Mercedes',
city_prefix: '2657',
...I have seen other people online running into this issue when executing a raw query with COUNT but I'm not using count at all, just some table joins where none of the tables have fields that use bigint. Interestingly I don't see the error when running the app via `npm run dev` but when I run a built version or deploy it to vercel it starts throwing the error. I am currently working around it by mapping the repsponse and casting the field to a number but that sucks and I'd like to know why it's happening.
If anyone has run into this before help would be much appreciated.
It worked for me. Add this code to the beginning of yours.
BigInt.prototype.toJSON = function () {
const int = Number.parseInt(this.toString());
return int ?? this.toString();
};
This is the most simple and secure way!!!
Modifying the prototype is likely to cause problems somewhere.
Just copy and paste the simple function below.
const json = (param: any): any => {
return JSON.stringify(
param,
(key, value) => (typeof value === "bigint" ? value.toString() : value) // return everything else unchanged
);
};
export default json;
And then you can use like this
import json from "../helper/json";
router.get("/", async (req: Request, res: Response) => {
const users = await prisma.user.findMany({
take: 15,
});
res.status(200).send(json(users));
});
This is how it works:
Maybe most of us just want to send those Prisma datas in JSON format using ExpressJS.
Whether you are using the library or not, you will inevitably go through JSON.stringify() at some point in your code.
Unfortunately, JSON.stringify() can't handle BigInt correctly.
So, we all must have to convert BigInt to String if you want to use it.
※ To ExpressJS users
Don't use res.json() method!
If you use, you will unintentionally wrap twice like below
JSON.stringify(JSON.stringify(something))
I am currently working on a microservice architecture using Nest.Js and PostgreSQL using Prisma as the ORM, and I am facing challenges with handling monetary values. Initially, I considered using the BigInt data type to represent monetary values in the smallest currency unit (MicroUSD), where 1 million equals $1. This approach worked well when sending BigInt data from Microservice 1 to Microservice 2, as the BigInt gets serialized to a string by Microservice 1's custom serializer, and Microservice 2 then transforms it back to a BigInt using a custom decorator in its DTO (called TransformAndValidateIsBigInt, that's a mouthful).
However, I encountered issues when Microservice 2 sends back a field with a BigInt in it. Although it correctly serializes it into a BigInt when sending it back, Microservice 1 receives a string since there's no way to transform it using decorators on incoming data. And it would obviously be impossible to know what strings were originally BigInts.
One solution I've considered is to create a ResponseDto and transform the raw data using the plainToClass function from class-transformer manually. However, this seems like a significant change and I'm not sure if it's the best approach.
I'm now wondering if I should abandon the BigInt approach altogether, despite its benefits over floating-point numbers. I'm also considering other PostgreSQL data types that might be more suitable for handling monetary values in this context.
Could anyone provide insights or recommendations on the best PostgreSQL data type to use for monetary values in a Nest.Js microservice architecture, considering these serialization and deserialization challenges?