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:
> mix ecto.gen.migration add_image_binary_to_recommendations
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
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
create/2 action to show how this will work:
Create the ImageFetcher GenServer
This file will be located at
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:
- We need to add a “content-type” response header to our connection, representing the MIME type of the image we originally fetched.
- 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:
get "/image/:id", PageController, :image
Then you’ll be able to link to the image in your EEx template like this:
<img src="<%= page_path(@conn, :image, recommendation.id) %>" alt="<%= recommendation.name %>" />
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:
- Store the images on Amazon S3. But I didn’t want to pay for hosting such a small number (< 30) of images!
- Store the images in a Git repo. I wanted to add my models dynamically without needing to deploy!
- 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
.jpgon the end, but the router doesn’t allow that. You could pass
123.jpgas the ID to our
imageaction and parse it out, but it’s not really necessary in this case.