Storing and retrieving files/images in a database from Shiny app

[iain]

Hi,

I would like to insert an image uploaded to a shiny app into a postgres database and retrieve it for display but have struggled to find anything to point me in the right direction.

I am currently using RPostgres package to insert a json object into a table and that is working really well.

  res<-dbExecute(pool, "INSERT INTO my_table (unique_id,created_by,report) VALUES ($1, 
                   $2,
                   $3::jsonb)", list( my_report_id,my_user,my_data_json))

Any pointers greatly appreciated

Thanks

Iain

[cole]

Sorry for the late reply here! This is a very interesting question! I love postgres and Shiny, and am sorry to say that I do not presently have time to build a working example. However, I am quite interested in the solution to this problem - my best guess is that it would involve a bytea column / data type. Basically, it is just raw bytes, which is pretty much how R understands images anyways. So the flow would be:

• get image from user
• serialize into byte data
• convert bytes to "hex" for Postgres input
• write bytes to database
• retrieve bytes (as hex) from database if / when necessary...
• serialize to a file for presentation if / when necessary

When writing to the database, I presume you would also want to store information about the file itself (maybe filename, file extension, etc.) for use / lookup later. I can't say for sure what this incantation would look like within R, but this reading on the bytea column type might be helpful, and this discussion on blob storage could also be useful:

https://wiki.postgresql.org/wiki/BinaryFilesInDB

I'm hopeful of having a chance to return for some hackery! I think most people end up writing files and just keeping track of the metadata / where the file is stored. However, the discussion on the PostgreSQL wiki notes some cases where that is less desirable. Sometimes it is nice to have everything in the database

[iain]

Hi Cole,

Thanks, that is exactly what I am trying to do. The wiki page is a great read and outlines my use case quite nicely. It is important for me that the uploaded file never gets out of sync with database, hence the desire of storing the file directly in the database.

I have made some progress based on your suggestions but am having some difficulty in figuring out exactly how to write the hex representation to the database, which I have tried to outline in the reprex below. It isn't clear to me what function I should use to encode the hex representation so that it can be inserted and retrieved from the database

Thanks again,

Iain

library(RPostgres)
library(hexView)
library(readr)

con <- dbConnect(RPostgres::Postgres(),host="host",
             port=5432,
             user= "user",
             password="password",
             dbname="dbname")

 x<-c("a,b,c","d,e,f")
write_lines(x,path="sample_text.txt")
z<-readRaw('sample_text.txt')
writeBin(z$fileRaw,'test_output.txt') #confirm identical file can be created

my_file_name<-"sample_text.txt"
my_file_type<-"txt"
my_report_id<-"c9f19a27-ff23-44a5-8217-40dc95a1594e"
my_file<-z$fileRaw

my_file
#>  [1] 61 2c 62 2c 63 0a 64 2c 65 2c 66 0a


res <-
 dbExecute(
con,
"INSERT INTO file_upload (file_name,file_type,report_id,file) VALUES ( $1,$2,
$3,$4)",
list(my_file_name,my_file_type,my_report_id,my_file)
)
#> Error: All parameters must have the same length.

[cole]

Awesome reprex!! Thank you!! That made my life so much easier And now we get a functional example!! (I think). I have to admit my testing is pretty minimal. I think we're super close though! This is exciting stuff!

You were trying to write a vector to the database, and RPostgres was complaining. Postgres wants a string representation of the hex (prefixed with \x), which I just did with a little paste(..., collapse = ""). I'm sure there are other ways to marshal all of this w/ string manipulation, hex / binary conversion, etc. It looks like it's working though!!

  
library(RPostgres)
library(hexView)
library(readr)

con <- dbConnect(RPostgres::Postgres(),host="localhost",
                 port=5432,
                 user= "postgres",
                 password="postgres",
                 dbname="postgres")

x<-c("a,b,c","d,e,f")
write_lines(x,path="sample_text.txt")
z<-readRaw('sample_text.txt')
writeBin(z$fileRaw,'test_output.txt') #confirm identical file can be created

my_file_name<-"sample_text.txt"
my_file_type<-"txt"
my_report_id<-"c9f19a27-ff23-44a5-8217-40dc95a1594e"
my_file<-z$fileRaw

my_file
#>  [1] 61 2c 62 2c 63 0a 64 2c 65 2c 66 0a


res <-
  dbExecute(
    con,
    "INSERT INTO file_upload (file_name,file_type,report_id,file) VALUES ( $1,$2,
$3,$4)",
    list(my_file_name,my_file_type,my_report_id,paste0("\\x", paste(my_file, collapse = "")))
  )

get_res <- dbGetQuery(con, "SELECT * from file_upload")

identical(get_res$file[[1]], my_file)
#> [1] TRUE

^{Created on 2019-04-22 by the reprex package (v0.2.1)}

[iain]

Awesome, thanks! That works perfectly for me. It even worked for a large powerpoint file