Retrieve and send a PostgreSQL bytea image

Question

I have an app which uses AWS Lambda functions to store images in a AWS PostgreSQL RDS as bytea file types.

The app is written in javascript and allows users to upload an image (typically small).

<input
  className={style.buttonInputImage}
  id="logo-file-upload"
  type="file"
  name="myLogo"
  accept="image/*"
  onChange={onLogoChange}
/>

The image is handled with the following function:

  function onLogoChange(event) {
    if (event.target.files && event.target.files[0]) {
      let img = event.target.files[0];
      setFormData({
        name: "logo",
        value: URL.createObjectURL(img),
      });
    }
  }

Currently I am not concerned about what format the images are in, although if it makes storage and retrieval easier I could add restrictions.

I am using python to query my database and post and retrieve these files.

INSERT INTO images (logo, background_image, uuid) VALUES ('{0}','{1}','{2}') ON CONFLICT (uuid) DO UPDATE SET logo='{0}', background_image='{1}';".format(data['logo'], data['background_image'], data['id']);

and when I want to retrieve the images:

"SELECT logo, background_image FROM clients AS c JOIN images AS i ON c.id = i.uuid WHERE c.id = '{0}';".format(id);

I try to return this data to the frontend:

    return {
        'statusCode': 200,
        'body': json.dumps(response_list),
         'headers': {
            "Access-Control-Allow-Origin" : "*"
         },
    }

I get the following error: Object of type memoryview is not JSON serializable.

So I have a two part question. First, the images are files being uploaded by a customer (typically they are logos or background images). Does it make sense to store these in my database as bytea files? Or is there a better way to store image uploads.

Second, how do I go about retrieving these files and converting them into a format usable by my front end.

I am still having issues with this. I added a print statement to try and see what exactly the images look like.

Running:

records = cursor.fetchall()
for item in records:
    print(item)

I can see the image data looks like <memory at 0x7f762b8f7dc0>

Here is the full backend function:

    cursor = connection.cursor()
    print(event['pathParameters'].get('id'))
    id = event['pathParameters'].get('id')
    postgres_insert_query = "SELECT name, phone, contact, line1, city, state, zip, monday_start, monday_end, tuesday_start, tuesday_end, wednesday_start, wednesday_end, thursday_start, thursday_end, friday_start, friday_end, saturday_start, saturday_end, sunday_start, sunday_end, logo, background_image FROM clients AS c JOIN address AS a ON c.id = a.uuid JOIN hours AS h ON c.id = h.uuid JOIN images AS i ON c.id = i.uuid WHERE c.id = '{0}';".format(id);
    query = postgres_insert_query;
    cursor.execute(query)
    records = cursor.fetchall()
    response_list= []
    for item in records:
        item_dict ={'name': item[0], 'phone': item[1], 'contact': item[2], 'address':{'line1': item[3], 'city': item[4], 'state': item[5], 'zip': item[6]}, 'hours':{'monday_start': item[7], 'monday_end': item[8], 'tuesday_start': item[9], 'tuesday_end': item[10], 'wednesday_start': item[11], 'wednesday_end': item[12], 'thursday_start': item[13], 'thursday_end': item[14], 'friday_start': item[15], 'friday_end': item[16], 'saturday_start': item[17], 'saturday_end': item[18], 'sunday_start': item[19], 'sunday_end': item[20]}, 'image': {'background_image': item[21], 'logo': item[22]}}
        response_list.append(item_dict)
    # print(response_list)    
    # connection.commit()      
    
    return {
        'statusCode': 200,
        'body': response_list,
         'headers': {
            "Access-Control-Allow-Origin" : "*"
         },
    }

Answer 1

A byte format is not always castable to JSON, likely characters are used that are not allowed in json. Return a different data format. return a different datatype to your frontend.

For example, if you look at quill rich editor you'll see that you can send a base64 image in a .html file that you can send from backend to frontend.

I would also suggest that you use Sqlalchemy (https://www.sqlalchemy.org/), this makes your application SQL injection proof and also offers support for special datatypes.

Workflow

Load the image and encode with base64

Source: https://stackoverflow.com/a/3715530/9611924

import base64

with open("yourfile.ext", "rb") as image_file:
    encoded_string = base64.b64encode(image_file.read())

Send in your API request

return {
    'statusCode': 200,
    'body': {"image":encoded_string },
     'headers': {
        "Access-Control-Allow-Origin" : "*"
     },
}

Frontend

Decode the image .. (with base64)

Answer 2

I know this is not the initial question. But have you consider storing images on a dedicated S3 bucket instead?

That would be cleaner and not complicated at all to implement IMHO. So you would store the actual image file on a S3 bucket and store its path in your DB. Your database would be lighter and front will load image based on the returned path.

I know it could sound like a lot of changes but the AWS SDK is very well done and that is not that long to do. This is what I personally use for my project and it works like a charm.

Answer 3

I tried https://www.backblaze.com/b2/cloud-storage.html.
Follow through doc, it's not that hard to upload a file. I mainly through command line, but the doc also offer other options.
After you upload, you can get all the uploaded file metadata.
So overall, you can upload file to backblaze(or other cloud storage) and insert all the metadata to database. Then when you retrieve the images, you retrieve through download url.