How do I save an image to the database using caveman and sxql?

I am trying to build a website that takes an uploaded image and saves it in the PostgreSQL database.

From caveman I can do:

(caveman2:request-raw-body caveman2:*request*)

Which gives me a circular stream: CIRCULAR-STREAMS:CIRCULAR-INPUT-STREAM.

I suppose I can then use a read-sequence to put the contents into a byte array

(let ((buffer (make-array 5 :adjustable t :fill-pointer 5)))
    (read-sequence buffer (caveman2:request-raw-body caveman2:*request*))
    (add-picture-to-db buffer))

The problem occurs when I try to save this byte array into the database using sxql.

(defun add-picture-to-db picture
  (with-connection (db)
    (datafly:execute
     (sxql:update :testpictures
       (sxql:set= :picture picture)
       (sxql:where (:= :id 1))))))

I guess the reason why it is failing might be because ultimately, sxql will generate a string which won't work well with binary data. Is there something here that I'm missing? How can I make this work?

Ideally, the way to verify the solution would be to retrieve the saved image from the db, serve it as the response of a http request and see if the client gets the image.

Solution

It would be much better to use Postmorden for this as it supports the handling of byte data with PostgreSQL

However, it is possible to work around the limitations of sxql. The first thing to understand is that sxql will ultimately generate an SQL query string, which will cause problems if you insert byte data directly into it.

It is necessary to convert the bytes of the file you want to store into HEX so that it can be used in sxql.

(format nil "~{~2,'0X~}" list-of-bytes-from-file)

Running this function through all the bytes of the file will give you a string composed of two digit HEX for each byte. This is important because other methods of converting bytes to HEX may not maintain the two digit padding, leading to an odd number of HEX.

For example:

(write-to-string 0 :base 16)

This will return a single digit HEX.

Next, you store the resulting string as you normally would into a bytea type column in the db using sxql.

When retrieving the file from the database, you get a byte array that represents the HEX string.

Using this function you can convert it back to a HEX string.

(flexi-streams:octets-to-string byte-array :external-format :utf-8)

Next step is to split the resulting string into pairs of HEX, e.g: ("FF" "00" "A2")

Then convert the pair back into a byte using this function on each pair:

(parse-integer pair :radix 16)

Store those bytes into an array of type unsigned byte, and finally return that array as the body of the response in caveman2 (not forgetting to also set the corresponding content-type header).