Save Image Files as bytea in PostgreSQL and retrieving them to display on HTML template

Question

Yes I know it's a bad idea/bad practice to save images in a database, nevertheless I have to as those are the requirements of my professor which is why I have to achieve it anyways.

The setup: I have a user table with an img column of type bytea where I want to store the image blob and later retrieve them through fetch and display them on my html template as base64.

onFileSelect I save the uploaded image to a variable which gets saved to the database with a fetch put request. This works so far and looks like this:

function onFileSelect(event) {
  img_upload = event.target.files[0];
  console.log(img_upload);
}

When I retrieve the data from the database I get back a bytearray which I can convert back from bytearray to a string. I also get the same result back as it's stored in the database:

But shouldn't it look more like this? How do I convert it to such a data URL so I can add it to my img :src?

img src="data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAUAAAAFCAYAAACNbyblAAAAHElEQVQI12P4 //8/w38GIAXDIBKE0DHxgljNBAAO9TXL0Y4OHwAAAABJRU5ErkJggg=="

I tried all day to get this to work and I have no clue where my mistake lies with all this image conversion.. I've had to do a similar thing with MySQL Blob types once but that wasn't even remotely as difficult as this bytea stuff to figure out Your help would be appreciated

`encode()` returns `text` (a "string"), not a "byte array". So it seems there is something wrong in the way you process the result in JavaScript. And Postgres' `bytea` type should behave pretty much the same way as MySQL's BLOB type - unless your DB access layer doesn't work correctly with Postgres . — , Jun 08 '22 at 19:56

score 2 · Answer 1 · answered Jun 08 '22 at 23:25

I'm guessing all your user's images have W29iamVjdCBPYmplY3Rd in the database

This is because if you base 64 decode it, you get [object Object]

Instead of base64 encoding the object, you can use FileReader which will make the base64 encoded value available through .result

here's a mildly augmented example taken from https://developer.mozilla.org/en-US/docs/Web/API/FileReader/readAsDataURL

function previewFile() {
  const preview = document.querySelector('img');
  const file = document.querySelector('input[type=file]').files[0];
  const reader = new FileReader();

  reader.addEventListener("load", function () {
    // convert image file to base64 string
    preview.src = reader.result;
    b64.innerHTML = reader.result; // show in textarea
  }, false);

  if (file) {
    reader.readAsDataURL(file);
  }
}

<input type="file" onchange="previewFile()"><br>
<textarea id="b64" rows="13" cols="40"></textarea>
<img src="" height="200" alt="Image preview...">

score 0 · Accepted Answer · answered Jun 11 '22 at 18:52

Alright, thanks to Daniel I've figured it out. As he guessed correctly my encoding was incorrect and I always got [object Object] for each value.

Now it looks like this:

OnFileSelect (Probably better to use an async await function here to receive the reader.result but for my simple case using timeout works just fine):

function onFileSelect(event) {
  const reader = new FileReader();
  img_upload = event.target.files[0];
  reader.readAsDataURL(event.target.files[0]);
  setTimeout(function () {
    img_upload = reader.result;
    toggle_upload.value = true;
  }, 2000);
}

The value on img_upload is now a DataURL which I saved like that in my bytea column in my PostgreSQL user database.

On page load:

Fetching User and decoding the bytearray value:

let profile_picture = ref("");
function getUserImage() {
  const dec = new TextDecoder("utf-8");
  fetchUser(user.email).then((response) => {
    let img = response.img.data;
    profile_picture.value = dec.decode(new Uint8Array(img));
  });
}

Dynamically rendering the profile picture with Vues v-if conditional rendering. In case the user doesn't have an image yet, a default profile icon gets rendered:

    <img v-if="user.img === undefined || user.img === null" class="profile-img"
 src="../../assets/img/profile_default.png"/>
    
    <img v-else class="profile-img" :src="profile_picture" />

On your backend you may also have to increase the json payload limits from the default 100kb to a bigger value (~5mb+) to allow for the huger payloads to be processed.

In my case I use node.js Express for my backend. The command to increase the limit here is the following:

app.use(json({ limit: "5mb" }));

Save Image Files as bytea in PostgreSQL and retrieving them to display on HTML template

2 Answers2