int8 Becomes String in PostgreSQL Node.JS
Tags: node, postgres, npm, algorithms
Published: 2024-01-19 07:39:45

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.

---


![Dealing with large number is not easy](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3t2v9kfyjc7e3gem82uh.png)

## 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](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Number#number_encoding), 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](https://github.com/brianc), 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