11

I'm trying to store a small file into a postgres db using the node-postgres module. I understand that I should use the bytea data type to do this. The problem I'm having is when I do some thing like:

fs.readFile path, (err, data) ->
    client.query 'UPDATE file_table SET file = $1 WHERE key = $2', [data, key], (e, result) ->
    ....

The contents of the file column in the db is: \x and nothing is stored. If I change the data buffer to hex i.e. data.toString('hex') the file is stored but all formatting is lost when I read the file back out.

What is the correct way of storing a file into postgres using the node-postgres module?

Clive
  • 405
  • 2
  • 5
  • 12
  • 1
    What will help you here is to examine the data that gets inserted into the database using `psql`. See if it's correct there. That will tell you if the problem is with inserting the data correctly, or with reading it back out. You also need to mention your Pg version; the default `bytea` format changed from `escape` to `hex` in 9.0. – Craig Ringer Oct 29 '12 at 23:52
  • 2
    What `node-postgres` version are you using? It looks like it supports bytea as of about a year ago (https://github.com/brianc/node-postgres/pull/38) so you should be able to just pass a buffer. – Craig Ringer Oct 29 '12 at 23:58
  • Thanks for the comments. I'm using node-postgres v. 0.8.6 and whatever version of postgres that Heroku uses. Presumably it's a 9.something. I looked at the db and the only data in the data column is a \x. I found a work around by changing the field to a text field and storing the file as a hex string. I don't think this is such a great idea though. – Clive Oct 30 '12 at 09:09
  • 1
    It looks like it *should* work; see the unit test here https://github.com/drdaeman/node-postgres/commit/f0aa7ccaea735dcd03c62ea017ebec903093c2bf . Maybe you need to make a self-contained compileable test case with DDL script and file a bug with node-postgres if you find you can reproduce it in the test case? – Craig Ringer Oct 30 '12 at 10:41
  • Thanks, I might do that. – Clive Oct 31 '12 at 09:11

1 Answers1

18

The trick is to encode as hex and prepend the file with \x. Reading it back out is indeed supported via parseByteA that returns a buffer:

https://github.com/brianc/node-postgres/blob/master/lib/textParsers.js

Here is what I did to read in an image from disk on postgres 9.2.2 and node.js 0.8.16 and node-postgres (npm package='pg') 0.11.2:

      fs.readFile(loc_on_disk, 'hex', function(err, imgData) {
        console.log('imgData',imgData);
        imgData = '\\x' + imgData;
        app.pgClient.query('insert into image_table (image) values ($1)',
                           [imgData],
                           function(err, writeResult) {
          console.log('err',err,'pg writeResult',writeResult);
        });
      });

and what I did to write it back out

app.get('/url/to/get/', function(req, res, next) {
  app.pgClient.query('select image from image_table limit 1',
                     function(err, readResult) {
    console.log('err',err,'pg readResult',readResult);
    fs.writeFile('/tmp/foo.jpg', readResult.rows[0].image);
    res.json(200, {success: true});
  });
});
qooleot
  • 409
  • 4
  • 9