Database storage for Paperclip-Pat Shaughnessy

Database storage for Paperclip

February 19, 2009 · 8 comments

Update April 2009:
I just rewrote this to use the same database table as the target model to save the file data, and not a separate database table. The “create_table” migration I describe here below will no longer work; instead you should create one or more columns in the same table as the target model to hold the file data. Please read this update for more details.

Paperclip from Thoughtbot is a fantastic bit of code that allows you to easily upload files to your Rails app and later manage them as just another attribute on your model object. If you’re not familiar with Paperclip you should start by reading Thoughtbot’s Paperclip intro page; Ryan Bates also did a screen cast on Paperclip usage. By default it supports saving the file attachments on the server file system, and also there’s an option for saving the files in Amazon’s S3 service. One reason I decided to use Paperclip in a recent project was that the implementation and usage were both much simpler and cleaner than attachment_fu, the other popular Rails plugin for file upload and management.

One thing that Thoughtbot decided not to implement was the ability to store files in a database table, rather than on the file system. It doesn’t make a lot of sense to do this for most normal web application deployments, since serving files via Apache directly from the file system is obviously much faster and avoids the need to call your Rails stack at all for download requests. However, I work in an Enterprise IT environment that has a lot of experience with Oracle, and finds it easier to manage file attachments with a database table. I also have requirements around file encryption, security, etc.

Since Paperclip doesn’t include a database storage option, I decided to write one. Here’s what I came up with: http://github.com/patshaughnessy/paperclip

I added a new storage module called Paperclip::Storage::Database. See lib/paperclip/storage.rb for details; Paperclip::Storage::Database is at the bottom of the file. I’d love any feedback or suggestions about the usage/design of how the database storage option would work with your application, or on the implementation itself.

I’ll be blogging here in the coming weeks with a detailed explanation of how Paperclip database storage works, and a working sample application that illustrates how to use it.

For now, here’s the usage description from lib/paperclip/storage.rb for specifying database storage for your Paperclip app. You need to follow these steps in addition to the standard Paperclip setup steps from Thoughtbot.

In your model specify the "database" storage option; for example:
```
  has_attached_file :avatar, :storage => :database
```
The files will be stored in a new database table named with the plural attachment name by default, "avatars" in this example.
You need to create this new storage table with at least these columns:
- file_contents
- style
- the primary key for the parent model (e.g. user_id)

Note the "binary" migration will not work for the LONGBLOG type in MySQL for the file_contents column. You may need to craft a SQL statement for your migration, depending on which database server you are using. Here's an example migration for MySQL:
```
create_table :avatars do |t|
  t.string :style
  t.integer :user_id
  t.timestamps
end
execute 'ALTER TABLE avatars ADD COLUMN file_contents LONGBLOB'
```
You can optionally specify any storage table name you want as follows:
```
  has_attached_file :avatar, :storage => :database,
                    :database_table => 'avatar_files'
```
By default, URLs will be set to this pattern:
```
  /:relative_root/:class/:attachment/:id?style=:style
```
Example:
```
  /app-root-url/users/avatars/23?style=original
```
The idea here is that to retrieve a file from the database storage, you will need some controller's code to be executed. Once you pick a controller to use for downloading, you can add this line to generate the download action for the default URL/action (the plural attachment name), "avatars" in this example:
```
  downloads_files_for :user, :avatar
```
Or you can write a download method manually if there are security, logging or other requirements. If you prefer a different URL for downloading files you can specify that in the model; e.g.:
```
  has_attached_file :avatar, :storage => :database,
                    :url => '/users/show_avatar/:id/:style'
```
Add a route for the download to the controller which will handle downloads, if necessary. The default URL (/:relative_root/:class/:attachment/:id?style=:style) will be matched by the default route:
```
map.connect ':controller/:action/:id'
```

Tags:paperclip·paperclip·rails·rails

8 responses so far ↓

1 ruby licious // Mar 24, 2009 at 06:08 AM

I've been looking for exactly this! I hear paperclip is really good, but I currently use attachement_fu since I like my pictures as replicated blobs. Would you call this stable?
2 pat // Mar 24, 2009 at 08:54 AM

Yes and no: I use it on a production app at my day job with no trouble at all. My implementation uses a separate database table for the file attachments, internally using has_many to associate the parent model and the files in the DB. This is often what you want to do, since you probably want to handle the table containing the BLOBs differently, and you don’t necessarily want to load the BLOB contents every time you access a record in your model. As I explain above, you need to create that separate table in your migrations and generally be aware of it and what it’s for.

However, I’m planning to see if I can refactor my code to optionally save the file attachments in the same table, just to keep things as simple as possible: one table is better than two, and this was the original idea behind Paperclip… to be a simpler alternative to attachment_fu. I’ll keep you posted here on my progress on that.

So: if you’re happy keeping your files in a separate table then go and give this a try… and let me know what you think and if you run into any problems. If you’re not in a rush then hang on for a few weeks until I have time to get it to work with one table which might be simpler.
3 Pratik Khadloya // Jan 18, 2010 at 02:21 PM

This is what i was looking for. Thanks! Even i think, it will be simpler to store the file in the same table as avatars. Maybe an additional column called "file".
4 pat // Jan 19, 2010 at 10:24 AM

Yup... this is how it works now, using a column named "avatar_file" by default. For more details see: http://patshaughnessy.net/2009/4/14/database-storage-for-paperclip-rewritten-to-use-a-single-table
5 Miguel // Feb 25, 2010 at 11:06 AM

I have been using your patch for storage... But i cant get to show the pictures i have uploaded... it just shows /avatars/2?style=thumb&1267113019 any information on how can i do to display the image? thanks.
6 pat // Feb 25, 2010 at 10:42 PM

Can you send me some of your code to look at? Maybe paste your code into pastie.org or into an email to me. “/avatars/2…” looks incorrect; if you’re using the user/avatar example you should have a URL similar to “/users/2/avatar…” Possibly you have the wrong :url option for has_attached_file? Are you using code similar to image_tag @user.avatar.url in your view?
7 Jarl Friis // Jul 08, 2010 at 08:30 AM

I have used this work as well for storing images in the database, it seems to work alright. But I have to warn others: the processing time for each image is going to be long.

There are two parts causing this.

First rails logs all your SQL statements (including binary blobs), but that can be workaround (with this: http://lightyearsoftware.com/blog/2008/02/the-sql_logging-debugging-plug-in/, this: http://lightyearsoftware.com/blog/2008/01/a-sql-log-silencer/, or maybe even ActiveRecord::Base::silence.

However the second part is that rails does not exploit prepared statements, hence all inserts (as well as selects) happens using quoted parameters, this is normally not a problems, but it adds unecessary processing first in ActiveRecord::Base (to escape all non-ASCII bytes), the by the database parser to convert it back to binary data. That is very time consuming and may very well render your app slow.

Jarl
8 pat // Jul 08, 2010 at 10:38 AM

Great points Jarl.

Yea I’ve noticed that all of the file contents are unnecessarily included in the log file; thanks a lot for posting the link to the log silencer info.

With regard to prepared statements: do you know of any way to patch or extend ActiveRecord or the database adapters to use prepared statements? I assume the syntax would be different for each database. I’ve never tried that sort of thing before. Thanks - pat

Pat Shaughnessy