postgresql 使用 node-postgres 在 postgres 中存储文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13124711/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Storing a file in postgres using node-postgres
提问by Clive
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 = WHERE key = ', [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 的正确方法是什么？
回答by qooleot
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
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 ()',
[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});
});
});
回答by InternalFX
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.
- 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.
- Fast- Automatically caches hot data to save your database unneeded effort.
- 高效- 通过自动内联重复数据删除节省空间。
- 简单- 使用开发人员友好的 API 读取和写入文件，就像它们在磁盘上一样。
- 修订- 保留多个版本的文件。
- 字节范围有能力-支持字节范围，允许流媒体。
- 一致- 在写入文件时计算 Sha256 哈希值，并在读回时进行验证。
- 快速- 自动缓存热数据以节省数据库不必要的工作。
