I switched to mysql2 and my ratings suddenly broke. The DECIMAL-as-string trap
After switching to mysql2, my average rating screen threw toFixed is not a function. Why DECIMAL comes back as a string, and how decimalNumbers fixed it.
#MySQL #Node #Troubleshooting #Backend
I switched the driver from mysql to mysql2 so I could use a connection pool. Most things were fine, but the moment I opened the restaurant ratings screen, this showed up in the console. TypeError: avgRating.toFixed is not a function It was clearly supposed to be a number, and yet it had no toFixed. Something that worked fine on mysql broke when I moved to mysql2. I had only changed the driver, and both the query and the screen code were untouched, so it took a while to find the culprit. This post is my write-up of that trap. The symptom For each restaurant I was computing the average star rating with AVG and showing it to one decimal place. The query looked like this. SELECT fp.*, (SELECT AVG(star) FROM FavPlaceStars WHERE favplace_id = fp.id) AS avgRating FROM FavPlace fp And the frontend looked like this. // show the average rating like 4.5 span {avgRating.toFixed(1)} /span toFixed is a method that only exists on numbers. In other words, avgRating was not a number. The cause: DECIMAL comes back as a string The reason was almost absurdly simple. mysql2 returns the DECIMAL type as a string by default. The result of AVG(star) is a DECIMAL, so what came over was not the number 4.5 but the string "4.5". Strings have no toFixed, so of course it broke. The old mysql driver used to hand this over as a number, but mysql2 returns a string on purpose to prevent loss of precision. A JavaScript number is a 64-bit floating point value, so it can't hold a value as is when it has many decimal places or is very large, and DECIMAL is the type meant for storing exactly those values. The moment you convert it to a number the accuracy can break, so mysql2 leaves it as a string and lets the user decide. The intent is good, but if you don't know about it, this is how it gets you. The fix: one line of pool config plus a second guard in the frontend If you pass the decimalNumbers: true option when creating the pool, DECIMAL comes back as a number. const pool = mysql.createPool({ host: process.env.DB_HOST, // ... // mysql2 returns DECIMAL as a string by default. // Receive it as a number so things like .toFixed() in the frontend don't break. decimalNumbers: true, }); One or two decimal places are enough for an average star rating, so precision wasn't a concern. If it had been a value where the digits matter, like money or coordinates, I would have taken it as a string and handled it separately. And in the frontend I wrapped it in Number() once as well, just in case, as a second guard. That way it won't break even if the backend setting somehow changes. span {Number(avgRating).toFixed(1)} /span Later, when I stored coordinates as DECIMAL in the travel planner, I nearly ran into the same trap again. That time I remembered this post and put in a line ahead of time that converts the query result with Number(). Did I actually switch, though? I wrote above that I switched, but if I open that project's package.json now, this is what's there. "dependencies": { "mysql": "^2.18.1", - never removed "mysql2": "^3.15.3" } The newly written code uses mysql2, and the old code still calls mysql. There have been two drivers running inside one project for months now. I fixed only the broken rating and didn't migrate the rest. The part I didn't migrate was running fine, so I couldn't find a reason to touch it, but at this rate I'll probably step on the same trap again later. With two drivers, the same column arrives as a number on some routes and as a string on others. If you're planning to move from mysql to mysql2, I'd recommend finding the queries that use DECIMAL columns before you do.