Database storage for Paperclip

February 19th 2009 — Comments and Reactions

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'
```