TL;DR:

• The double precision in Postgres is good enough for storing financial data up to a few decimal places. It takes up 8 bytes and provides 15 decimal digits precision (this includes all the digits, not just the fractional part). You can still use NUMERIC as best practice.
• A NUMERIC data type will appear as string when reading from the database using the pg JS library. This needs to be converted to a number or Decimal (requires 3rd party library).
• On JS side use decimal.js for absolute precision. However, for many applications it might be an unnecessary overhead. Use of Decimal requires more CPU. Instead do following: Use JS built-in number type but when you write data to Postgres convert the number to 2 decimal places (e.g., number 0.7999999 gets rounded off to 0.80 when writing to the database). This way you don’t have to use any 3rd party library and results should be accurate enough for most applications. Of course, test if application is working as expected.

Conventional wisdom dictates that one should use the NUMERIC data type to store financial data to protect from rounding errors that happen with floating point arithmetic (refer IEEE 754 standard). And on JS side we should use a library like decimal.js. However in practice I am finding the use of NUMERIC as unnecessary with Postgres 14. Here is what I did. Created a table with 3 columns:

create table test (
    row_id integer serial primary key,
    col1 double precision,
    col2 numeric
);

Start inserting some data into it:

insert into test(col1,col2) values(59.95,'59.95');
...

I finally have a table with:

postgres=# select * from test;
    col1     |    col2     | row_id
-------------+-------------+--------
       3.333 |       3.333 |      1
       4.444 |       4.444 |      2
     3.14159 |     3.14159 |      3
      5.5555 |      5.5555 |      4
    3033.333 |    3033.333 |      5
 4900060.339 | 4900060.339 |      6
 21051979.88 | 21051979.88 |      7
       59.95 |       59.95 |      8
       1.414 |       1.414 |      9
(9 rows)

Test 1

Inspect the table using:

select * from test;

don’t see any difference between col1 and col2.

Test 2

Print out the difference between col1 and col2 magnified by a billion:

select (col1-col2)*1000000000 from test;

returns 0s

Test 3

Query for a row doing equality test on col1 (double precision):

select * from test where col1 = 59.95;

I do get back an answer. So there was no loss of precision when storing the floating point number. I think this is the most stringent test and the reason why its recommended to use a NUMERIC column in the first place because the equality fails due to rounding off errors (according to the conventional wisdom of course).

Test 4

postgres=# select (sum(col1) - sum(col2))*1e9 from test;
 ?column?
----------
        0
(1 row)

Does that mean we should use double precision for storing financial data? No, I would still recommend using NUMERIC to be extra safe but above tests do go onto showing that its not necessary to use NUMERIC. You will probably be fine even if you used double precision.

What about JS? Do you need to use a library like decimal.js to handle financial data?

Yes if you want absolute precision. From node REPL run:

> x=0.7+0.1
0.7999999999999999
> x===0.8
false 

In general the number approximation is good enough. decimal.js will give absolute precision though:

> const Decimal = require('decimal.js');
undefined
> x1 = new Decimal(0.7)
0.7
> x2 = new Decimal(0.1)
0.1
> x3 = x1.add(x2)
0.8
> x3.toNumber() === 0.8
true

Final Notes on using Postgres with pg JS library

Remember when inserting data into a NUMERIC column you can pass either a number or a string. When you read a NUMERIC column you will get back a string that you should convert to a number or a Decimal object if using decimal.js library.

What about MySQL? I don’t know. Try it out and let me know.