使用 node-postgres 在 postgres 中存储文件 [英] Storing a file in postgres using node-postgres

问题描述

我正在尝试使用 node-postgres 模块将一个小文件存储到 postgres 数据库中.我知道我应该使用 bytea 数据类型来做到这一点.我遇到的问题是当我做一些事情时:

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) ->
    ....

db 中文件列的内容是:\x，没有存储任何内容.如果我将数据缓冲区更改为十六进制，即 data.toString('hex') 文件将被存储，但当我读回文件时所有格式都将丢失.

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.

使用 node-postgres 模块将文件存储到 postgres 的正确方法是什么?

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

推荐答案

诀窍是编码为十六进制并在文件前加上 \x.通过返回缓冲区的 parseByteA 确实支持读取它:

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

这是我在 postgres 9.2.2 和 node.js 0.8.16 以及 node-postgres (npm package='pg') 0.11.2 上从磁盘读取图像的操作:

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});
  });
});

这篇关于使用 node-postgres 在 postgres 中存储文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！