如何用PostgreSQL、node-pg与ExpressJS收发ByteA类型图片

我的数据库表结构 #

这是PostgreSQL中的images表：

CREATE TABLE IF NOT EXISTS images (
    ----------------------------------------------------
    id  UUID        NOT NULL DEFAULT uuid_generate_v4(),
    ----------------------------------------------------
    pic BYTEA       NOT NULL,
    ext VARCHAR(10) NOT NULL,
    ----------------------------------------------------
    CONSTRAINT     images_pk
       PRIMARY KEY (id)
    ----------------------------------------------------
);

-- 插入测试数据
SELECT set_config(
    'default_pic_path',
    '/path/to/image.png',
    FALSE
);

INSERT INTO
    mi.images (
        pic,
        ext
    )
VALUES
    ------------------------------
    (
        bytea('default_pic_path'),
        'png'
    );
    ------------------------------

需求 #

需要实现两个接口：

• 一个接口用于将图片存入数据库
• 另一个接口用于获取图片，并以正确的扩展名（如.png、.jpg等）返回

目前找到的方案要么是把图片转文本（效率极低），要么是把node-pg返回的Buffer写入本地文件再用res.sendFile返回（同样低效）。有没有更优的实现方式？

附我当前的取图接口代码（带本地写入逻辑，存在编码问题）：

import { Request, Response } from "express";

export async function getImage(req: Request, res: Response) {
  try {
    const mainPool = new Pool({
      user: "postgres",
      password: "password",
      database: "main",
      host: "localhost",
      port: 5432,
    });

    const results = await mainPool.query(/* sql */ `
      SELECT pic, ext
      FROM   images
    `);

    const pic = results.rows.[0].pic as Buffer;
    const extension = results.rows.[0].ext as string;

    const filename = `default_pic.${extension}`;
    const filepath = join(
      __dirname,
      "../path/to/assets/folder/",
      `assets/${filename}`
    );

    // 文件写入存在问题，可能是编码错误导致
    writeFileSync(filepath, pic);

    res.sendFile(filepath);
  } catch (e) {
    console.log(e);
  }
}

最优实现方案 #

直接利用Express的响应对象处理Buffer，无需中间文件写入，同时优化数据库连接池的使用（不要每次请求新建池）。

1. 全局初始化数据库连接池 #

不要在接口内每次新建Pool，全局初始化一次复用：

// db.ts
import { Pool } from 'pg';

export const mainPool = new Pool({
  user: "postgres",
  password: "password",
  database: "main",
  host: "localhost",
  port: 5432,
});

2. 图片上传接口（存入数据库） #

使用multer处理文件上传，直接获取文件Buffer存入BYTEA字段：

import { Request, Response } from "express";
import multer from 'multer';
import { mainPool } from './db';

// 配置multer在内存中存储文件，不写入本地磁盘
const storage = multer.memoryStorage();
const upload = multer({ storage });

// 上传接口，接收名为image的单文件
export async function uploadImage(req: Request, res: Response) {
  try {
    if (!req.file) {
      return res.status(400).send('请上传图片文件');
    }

    // 提取文件扩展名，或根据mimetype推导
    const ext = req.file.originalname.split('.').pop()?.toLowerCase() || '';
    if (!['png', 'jpg', 'jpeg', 'gif'].includes(ext)) {
      return res.status(400).send('仅支持png/jpg/gif格式图片');
    }

    // 插入数据库
    const result = await mainPool.query(
      `INSERT INTO images (pic, ext) VALUES ($1, $2) RETURNING id`,
      [req.file.buffer, ext]
    );

    res.status(201).json({ imageId: result.rows[0].id });
  } catch (err) {
    console.error(err);
    res.status(500).send('上传失败');
  }
}

// 路由注册示例：
// app.post('/upload', upload.single('image'), uploadImage);

3. 图片获取接口（直接返回Buffer） #

无需写入本地文件，直接设置响应头后返回Buffer：

import { Request, Response } from "express";
import { mainPool } from './db';

export async function getImage(req: Request, res: Response) {
  try {
    // 从URL参数获取图片ID
    const { imageId } = req.params;
    if (!imageId) {
      return res.status(400).send('缺少图片ID');
    }

    const results = await mainPool.query(
      `SELECT pic, ext FROM images WHERE id = $1`,
      [imageId]
    );

    if (results.rows.length === 0) {
      return res.status(404).send('图片不存在');
    }

    const { pic, ext } = results.rows[0];
    // 根据扩展名设置正确的Content-Type
    const contentType = `image/${ext === 'jpg' ? 'jpeg' : ext}`;
    
    // 设置响应头
    res.setHeader('Content-Type', contentType);
    // inline表示浏览器直接显示，attachment则触发下载
    res.setHeader('Content-Disposition', `inline; filename="image.${ext}"`);

    // 直接返回Buffer，无需本地文件中转
    res.send(pic);
  } catch (err) {
    console.error(err);
    res.status(500).send('获取图片失败');
  }
}

// 路由注册示例：
// app.get('/image/:imageId', getImage);

关键优化点 #

• 消除本地文件IO：直接将数据库返回的Buffer通过res.send()发送，避免磁盘写入开销
• 复用连接池：全局初始化连接池，避免每次请求新建数据库连接的性能损耗
• 正确设置响应头：通过Content-Type告知浏览器图片格式，Content-Disposition控制显示/下载行为
• 高效处理上传：使用multer.memoryStorage()在内存中直接获取文件Buffer，无需本地存储中转

内容的提问来源于stack exchange，提问作者psygo