int8 Becomes String in PostgreSQL Node.JS

It is a brief explanation of why int8 data type in PostgreSQL is converted to string instead of number in Node.JS. tl;dr it is a feature, not bug.

But First, Little Story

It can happen to anyone. Business requirement stated a transaction may be worth many billions in certain currencies. So, I needed to update SQL column data type from int4 to int8. That was it? I supposed so. Turned out not. People were furious and they had thrown me into dark pit of debugging.

I used Typescript but somehow it slipped through. I realized when taking a look on log and I saw long string like 8233800739007328003173000. It's enough clue that number addition became string concatenation. The returned query turned out as string.

But how?

I was baffled for few moments, then regained composure. The chaos was fixed by casting those string back to number. After that, business flows as usual, people cheered, and I reclaimed my state of sanity.

Why It's There, and Why It's Not A Bug

The question lingers, though? Why did it turned into string? I did a little research and found out that it is expected behaviour. Yes, PostgreSQL int8 will always be converted to Node.JS string. The culprit here is package called node-postgres or pg.

If you work with PostgreSQL in Node.JS, chances are there's pg in your node_modules. That's one of the packages that hold Node.JS community in one piece. Yet, exactly in this package the decision above was made. It is designed to handle one specific problem: Javascript number. Now, let me explain a bit on number before coming back.

Javascript number data type is 64-bit. Meanwhile, PostgreSQL int8 is also 64-bit. However, they are different. The first one is IEEE 754 double precision, while the later is signed integer. Javascript number has bits dedicated for precision and sacrifices range. Now let's compare.

• max value of Javascript number (or Number.MAX_SAFE_INTEGER) is 2^53 - 1 or 9.007.199.254.740.991.
• max value of PostgreSQL int8 is 2^63 - 1 or 9.223.372.036.854.775.807

Then, what will happen when the stored value in PostgreSQL is bigger than Javascript number max value? Javascript will read the binary representation and churn out incorrect value.

That is problem. BIG problem.
That's where pg package comes in and saves the day. The author recognized this problem, then decided to cast the result as Javascript string, which does not alter the output value.

For me it is beautiful trade-off. First, it handles all cases. Second, it avoids worst output. Third, it gives simple resolve once the value is safe in Javascript.

So, hats-off for Brian C, the author of pg.

Rerefences

• https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Number
• https://github.com/brianc/node-postgres/issues/811