Node.js, pg (postgresql) - put pure binary data into 'bytea' field

Question

my question is: what is right way to put binary data into 'INSERT' PostgreSQL (Node.js + pg) request ?

const req = "INSERT INTO public.product (" +
            "name, doc_name, purchase_price, selling_price, weight, " +
            "id_string, landing_url, img_data, img_name,  active) " +
            "VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10);";

const values = [
  obj.product_name, obj.product_doc_name,
  obj.product_purchase_price, obj.product_selling_price, obj.product_weight,
  obj.product_id_string, obj.product_landing_url,
  obj.product_image_data, obj.product_image_name, 'true'
];

'obj.product_image_data' is the Express request body field('req.body.product_image_data');

My 'test' store binary data (from 'req.body.product_image_data') to the local server side file is OK.

console.log("product_image_data lenght: " + obj.product_image_data.length);
//write img data to file
fs.writeFileSync('./TEST.PNG', obj.product_image_data, 'binary');

Of Course, pure binary data 'as is' has error result as on the picture. Image of error

Thanks!

Answer 1

A regular Node.js buffer of binary data should work for node pg and column type bytea.

> Buffer.from('\x00\x01\x02')
<Buffer 00 01 02>

Make sure the object is a proper binary string as a buffer. Using Buffer.from might do the trick.

const values = [
  obj.product_name, obj.product_doc_name,
  obj.product_purchase_price, obj.product_selling_price, obj.product_weight,
  obj.product_id_string, obj.product_landing_url,
  Buffer.from(obj.product_image_data), obj.product_image_name, 'true'
];