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);
}
}
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:
expresspgexpress-fileuploadTo 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.