使用node-postgres在postgres中存储文件

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:

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

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.

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

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

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

2 个解决方案

#1

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:

诀窍是编码为十六进制并使用\ x前置文件。通过返回缓冲区的parseByteA确实支持将其读回来:

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:

这是我在postgres 9.2.2和node.js 0.8.16以及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});
  });
});

#2

I suggest you take a look as SQLGrid

我建议你看看SQLGrid

From the readme:

从自述文件:

Efficient - Save space with automatic inline deduplication.

高效 - 使用自动内联重复数据删除节省空间。

Easy - Read and write files as if they were on disk with developer friendly APIs.

简单 - 读取和写入文件,就好像它们位于具有开发人员友好API的磁盘上一样。

Revisions - Keeps multiple versions of files.

修订 - 保留多个版本的文件。

Byte-range Capable - Supports byte ranges to allow for streaming media.

字节范围能力 - 支持字节范围以允许流媒体。

Consistent - Sha256 hashes are calculated when the file is written, and verified when read back out.

一致 - Sha256哈希值在写入文件时计算,并在读回时进行验证。

Fast - Automatically caches hot data to save your database unneeded effort.

快速 - 自动缓存热数据以节省数据库不必要的工作量。

#1