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

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:

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 :slight_smile: