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();
};
Prisma, Mysql and BigInts oh my
BigInt vs number
BigInt and Number - SQL Server
Prisma returning a bigint for an int field via $queryRaw
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))
So I wanted to share my journey with everyone and shine some light on a weird problem that is probably common but doesn't seem to be discussed much.
I am in the process of rewriting a large 15-year old PHP/jQuery/MySQL app in Nuxt. One of my goals is to support my largeish (60 tables) legacy MySQL database. I was really looking forward to leveraging Nuxt's API and directory magic to modernize my old app!
I had used mysql2 successfully in a Vue 3 project I built this past winter and wanted to use it in Nuxt. Turns out there wasn't a documented way to do so. So I set about figuring out a way to wire it up into Nitro myself. I documented my findings here but I wasn't really happy as my solution wasn't the "preferred" way to do things. Pooya says we should not be attaching things to the nitroApp instance.
Time passed and I started playing with Prisma as it has direct support for MySQL and Nuxt. I figured I needed to wade into learning Prisma anyway. It is pretty cool but seems unnecessary since I already know how to write good SQL. The code hints are nice I guess but I digress...
I managed to get Prisma to "npx prisma db pull" my database schema (even on an older MySQL 5.7 setup) and start querying data. That was quick and easy. I was impressed!
But I immediately ran into a problem with BigInt. It seems even though I had no columns defined as BigInts I was getting the error: "Do not know how to serialize a BigInt". Turns out the reason for this is that my Int() columns are unsigned (I wanted to double the potential number of max id's for my tables) and this causes Prisma to *think* the data is a BigInt even though the maximum number of records in any of my tables is less than 10,000. Strange behavior indeed.
On a side note, the npmjs mysql2 package has a way to specify that you want the package to return BigInts as a string since JSON is the one choking on them. I couldn't find a way to tell Prisma to do this.
What I have done to circumvent the issue for now is to force Prisma to return what it perceives as BigInts as a string value, which is fine for my purposes.
I added the following code to the top of the defineEventHandler() function call in my /server/api/{endpoint} file:
BigInt.prototype.toJSON = function () {
const int = Number.parseInt(this.toString());
return int ?? this.toString();
};Now I am able to call prisma functions as normal and return the results. Now I can set my auto-increment record id's to BigInts as much as I want. Happy me!
The drawback to this approach is that I will have to add this code to every endpoint I write in my app. I am sure I will discover a way to have Prisma execute this code during instantiation but I haven't found a way to do it yet.
So I just wanted to share my story for folks who may be beating the crap out of Google/DuckDuckGo looking for a solution to this issue.
Thanks and cheers!
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.
Hi All,
im trying to get the hang of using a CockroachDB and Prisma as an ORM. Im using express/js as my backend language.
When im attempting to make a Post request i get this error:
TypeError: Do not know how to serialize a BigInt
i found this solutions but have no idea how to apply it or where to use in my code.
./prisma/schema.prisma
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "cockroachdb"
url = env("DATABASE_URL")
}
model User {
id BigInt @id @default(autoincrement())
email String
name String?
}---
./index.js
const express = require('express')
const { PrismaClient } = require('@prisma/client')
var cors = require('cors')
const prisma = new PrismaClient()
const app = express()
app.use(express.json())
app.use(cors())
app.post(`/signup`, async (req, res, next) => {
try {
const result = await prisma.user.create({
data: {
name: req.body.name,
email: req.body.email
},
})
res.json(result)
} catch (error) {
next(error)
}
})
app.listen(3000, () => {
console.log(`Example app listening on port 3000`)
})Help would be appreciated thanks