How (and when!) to store images in your database with Elixir & Phoenix

Many developers store images in Amazon S3, a Git repo, or in a filesystem. I’m storing mine directly in my PostgreSQL database. As it turns out, that’s the ideal approach to the problem I’m solving…

In the examples below, we’ll be working with an existing Ecto model called Recommendation, which already contains a URL to an image we’d like to fetch and store in our database. To do this, the first step is to add a binary field to the DB schema.

Add a binary field to your DB schema

First, generate a new migration to a field to a table:

Then, we need to add a binary field to the schema. We’d also like to store several different kinds of images, so it’s a good idea to also add a string field to store the MIME type (e.g., image/jpeg) of the image:

The type of our new field is binary . If you’re using PostgreSQL like I am, behind the scenes Ecto will convert this to bytea, which is a binary column type appropriate for PostgreSQL. Now that we’ve added our database fields, we need to ensure our image is fetched and stored when we create a new Recommendation.

Edit the create controller action

When creating a recommendation, we don’t want to fetch an image and store the binary data in the create action. It might make the request time out! So, we’ll push this logic out of the request into a GenServer called ImageFetcher, which will fetch and store the images in the database asynchronously. Let’s update our RecommendationController create/2 action to show how this will work:

Create the ImageFetcher GenServer

This file will be located at lib/<app-name>/image_fetcher.ex. Our Recommendation model already has an image_url field. So, we will use HTTPoison to do the image fetching. We will store the resulting binary in our Ecto Repo, using the Content-Type header from the fetched image as our binary type. This work will be done asynchronously, so we’ll use cast/2 and its associated callback handle_cast/2 from GenServer. We’ll also register the name of the GenServer, :image_fetcher, to make accessing the GenServer process easier on ourselves.

Don’t forget to start up your new GenServer on application startup! Edit your application file lib/<my-app>.ex as follows to add the ImageFetcher worker to your application’s supervision tree:

Serve the binary data!

Now that we’ve stored our binary data in our database, we need to access it! We can do this easily with a new controller action dedicated to this purpose:

There’s a few things to note here:

  1. We need to add a “content-type” response header to our connection, representing the MIME type of the image we originally fetched.
  2. To send the response, we simply send the binary from the Ecto model with a 200 status.

Don’t forget to add your new controller action to your router:

Then you’ll be able to link to the image in your EEx template like this:

Why storing images in the database works (for me)

When I first started working on this application, I hotlinked directly to relevant images hosted by a third party. This resulted in some performance issues due to the latency of the third party. There were a few other options available to me:

  1. Store the images on Amazon S3. But I didn’t want to pay for hosting such a small number (< 30) of images!
  2. Store the images in a Git repo. I wanted to add my models dynamically without needing to deploy!
  3. Store the images in some other filesystem (e.g. Dropbox?). Hosting locally wasn’t an option; I’m using Heroku which has an ephemeral filesystem. I wanted to keep my images together with my app!

So, I decided to store the images in the database and serve them myself using the approach detailed above.

Why you (probably) shouldn’t do it

I’m only storing less than 30 images in my database, and half of those are small thumbnails. If you’re storing large images, or a large number of files, this might not be a great solution for you, for the following reasons:

  • Increased load on your app and database
  • Increased database backup size
  • Serving files now requires going through your application and database layers

Potential improvements to this approach

  • Use a Task instead of a GenServer, which seems like a good alternative. (I wanted to play with GenServer)
  • Normalize the database, creating a separate database table for the images.
  • The path/url generated by the router doesn’t have a file extension in it (e.g. the path to the image as is would be something like /image/123 ). It would be great to have a .jpg on the end, but the router doesn’t allow that. You could pass 123.jpg as the ID to our image action and parse it out, but it’s not really necessary in this case.

You can find all the source code for this project on GitHub, and you can find me on the Elixir Slack and on Twitter as paulfedory. Thanks for reading!

Software engineer. Elixir/Phoenix. Ruby/Rails. Television enthusiast. BBQ connoisseur.