How to Send and Receive ByteA Images with PostgreSQL, node-pg and ExpressJS

Question

This is my images table in PostgreSQL:

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

-- Inserting Fake Data

SELECT set_config(
    'default_pic_path',
    '/path/to/image.png',
    FALSE
);

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

And I would like to have an endpoint for adding images to the DB and also another for retrieving them, sending them back with the proper image extension (e.g. .png, .jpg, etc.).

What is the recommended way of doing this in ExpressJS with node-pg?

So far, I've found tons of ways of doing this converting images to text, but that's highly inefficient... I've also found some solutions in which we turn the returned node-pg Buffer into a local file and then use res.sendFile(...), but that is also highly inefficient. Is there a better way?

Anyways, here's a sketch of an endpoint for getting an image (in TypeScript), with the local creation of a file I had criticized:

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

    // Doesn't really write the file properly, 
    // probably some error with encoding...
    writeFileSync(filepath, pic);

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

Codo · Accepted Answer · 2023-08-10T19:00:23.743

Here's a small sample. It's Javascript instead of Typescript. But it will make it clear anyways.

It uses a slightly modified schema:

CREATE TABLE IF NOT EXISTS images (
    id           UUID        NOT NULL DEFAULT gen_random_uuid(),
    image        BYTEA       NOT NULL,
    content_type VARCHAR(64) NOT NULL,
    CONSTRAINT   images_pk   PRIMARY KEY (id)
);

The main difference is that a content type instead of a file extension is used.

To insert images using SQL, the Postgres server needs to have access to the images. And you need to have the permissions to execute pg_read_binary_file():

INSERT INTO images (image, content_type)
VALUES (pg_read_binary_file('/var/images/rose.jpg'), 'image/jpeg');

INSERT INTO images (image, content_type)
VALUES (pg_read_binary_file('/var/images/banana.jpg'), 'image/jpeg');

The sample also assumes that a HTML file called upload.html is in a directory called public. It displays the UI for the file upload.

upload.html

<!DOCTYPE html>
<html>

<head>
  <meta charset="utf-8">
  <title>Image upload</title>
  <meta name="viewport" content="width=device-width, initial-scale=1">
</head>

<body>

  <p>Image Upload</p>
  <form action="/upload" method="post" encType="multipart/form-data">
    <input name="image" type="file" />
    <input type="submit" value="Upload" />
  </form>
  
</body>

</html>

The node.js/express part is quite compact. It assumes the following npm packages are installed:

express
pg
express-fileupload

To view an image, go to http://localhost:3000/img/cef95cc8-137a-4a06-9d0d-ccee0cb018be (replacing the UUID with the id of the image you want to view).

To upload an image, go to http://localhost:3000/upload.html

const { Pool } = require('pg')
const pool = new Pool();

const express = require('express')
const app = express()
const fileUpload = require('express-fileupload');

app.use(express.static('public'))
app.use(fileUpload());

app.get('/img/:id', async (req, res) => {
    const result = await pool.query(
      'SELECT * FROM images WHERE id = $1', 
      [req.params.id]
    )
    res.set('Content-Type', result.rows[0].content_type)
    res.send(result.rows[0].image)
})

app.post('/upload', async (req, res) => {
    const result = await pool.query(
      'INSERT INTO images (image, content_type) VALUES ($1, $2) RETURNING id', 
      [req.files.image.data, req.files.image.mimetype]
    )
    res.redirect(`/img/${result.rows[0].id}`)
})

const port = 3000
app.listen(port, () => {
  console.log(`Postgres app listening on port ${port}`)
})

The sample makes use of the fact that express-fileupload makes the uploaded file available as a Buffer. And the Postgres client accepts a Buffer for inserting the image data. It also returns a Buffer when querying the image column. And res.send() also accepts a Buffer.

Since the Buffer instance are held in memory, this approach is efficient. But the maximum image size is limited by memory.

Update

It the image should be uploaded using Javascript, minor modifications are needed. It still assumes that the image has been selected in a user interface using an input element of type file.

Additional server-side code

app.post('/upload2', async (req, res) => {
  const result = await pool.query('INSERT INTO images (image, content_type) VALUES ($1, $2) RETURNING id', [req.files.image.data, req.files.image.mimetype])
  res.json({
    imageId: result.rows[0].id,
    imageURL: `/img/${result.rows[0].id}`
  })
})

If the upload is successful, this endpoint returns JSON data instead of redirecting to the image URL.

upload2.html

<!DOCTYPE html>
<html>

<head>
  <meta charset="utf-8">
  <title>Image upload</title>
  <meta name="viewport" content="width=device-width, initial-scale=1">
</head>

<body>

  <p>Image Upload</p>
  <p>
    <input id="file" name="image" type="file" />
    <button id="upload-js">Upload JS</button>
  </p>
  <p><a id="image-link" href="#"></a></p>
  
</body>

<script>
  const fileInput = document.getElementById('file');
  const uploadButton = document.getElementById('upload-js');
  const imageLink = document.getElementById('image-link');

  uploadButton.addEventListener('click', async () => {
    const formData = new FormData();
    formData.append('image', fileInput.files[0]);
    try {
      const response = await fetch('/upload2', {
        method: 'POST',
        body: formData
      });
      
      const imageMetaData = await response.json();
      imageLink.href = imageMetaData.imageURL;
      imageLink.innerHTML = 'Uploaded Image';

    } catch (error) {
      console.error(error);
    }
  });
</script>

</html>

If the image is uploaded successfully, a link appears with the image URL. The URL was returned by the REST endpoint.

Thanks a lot! In your upload endpoint, is there a way of using template strings instead? I wonder how it would work, if it's possible, given that the image that is of type `Buffer`... — Philippe Fanaro, Aug 06 '23 at 20:49
I don't think you can do it with the *pg* Postgres client. With naive template strings you lose parameter binding (and the associated performance) and break non-trivial data types like buffers. But it looks as if [Postgres.js](https://github.com/porsager/postgres) provides tagged template functionality achieving it. — Codo, Aug 06 '23 at 21:00
Is there a way of uploading the image and other data with the JS `fetch` API? — Philippe Fanaro, Aug 10 '23 at 18:08
Thanks! Just found out you can also do `const form = document.querySelector('form'); const formData = new FormData(form)`, and then you basically don't need to use `.append`, it's all automatically done. — Philippe Fanaro, Aug 10 '23 at 20:10

How to Send and Receive ByteA Images with PostgreSQL, node-pg and ExpressJS

1 Answers1