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

Question

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

548 Views Asked by psygo At 03 August 2023 at 14:55

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

Original Q&A

There are 1 best solutions below

**Codo** · Accepted Answer · 2023-08-05T22:05:37.457000

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.

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

There are 1 best solutions below

Related Questions in NODE.JS

Related Questions in TYPESCRIPT

Related Questions in POSTGRESQL

Related Questions in EXPRESS

Related Questions in NODE-PG-POOL

Trending Questions

Popular # Hahtags

Popular Questions