Database storage for Paperclip: rewritten to use a single table

April 14th 2009 — Comments and Reactions

Back in February I wrote an implementation of a new storage module for Paperclip that supports saving file attachments in a database table. My original implementation saved the file attachments in a separate database table, which was internally managed using a “has_many” relationship from the target model.

This month I decided to rewrite the code to use a simpler, single table approach. Instead of saving the files in a separate table, each file is saved in a BLOB column in the same table as the target model. This makes the database storage module easier and more intuitive to use, and moves it closer to the original intent of the Paperclip design.

Code: http://github.com/patshaughnessy/paperclip

New usage:

Install and use my version of the plugin:

script/plugin install git://github.com/patshaughnessy/paperclip.git

In your model specify the "database" storage option; for example:
```
has_attached_file :avatar, :storage => :database
```
The file will be stored in a column called [attachment name]_file (e.g. "avatar_file") by default. To specify a different BLOB column name, use :column, like this:
```
has_attached_file :avatar,
                  :storage => :database,
                  :column => 'avatar_data'
```
If you have defined different styles, these files will be stored in additional columns called [attachment name]_[style name]_file (e.g. "avatar_thumb_file") by default. To specify different column names for each style, use :column in the style definition, like this:

has_attached_file :avatar,
                  :storage => :database,
                  :styles => { 
                    :medium => {
                      :geometry => "300x300>",
                      :column => 'medium_file'
                    },
                    :thumb => {
                      :geometry => "100x100>",
                      :column => 'thumb_file'
                    }
                  }

You need to create these new columns in your migrations or you'll get an exception. Example:

add_column :users, :avatar_file, :binary
add_column :users, :avatar_medium_file, :binary
add_column :users, :avatar_thumb_file, :binary

Note the migration for the "binary" column type will not work for LONGBLOBs in MySQL. Here's an example migration for MySQL:

execute 'ALTER TABLE users ADD COLUMN avatar_file LONGBLOB'
execute 'ALTER TABLE users ADD COLUMN avatar_medium_file LONGBLOB'
execute 'ALTER TABLE users ADD COLUMN avatar_thumb_file LONGBLOB'

To avoid performance problems loading all of the BLOB columns every time you access your ActiveRecord object, a class method is provided on your model called “select_without_file_columns_for.” This is set to a hash that will instruct ActiveRecord::Base.find to load all of the columns except the BLOB/file data columns, for example:
```
{:select=>"id,name,avatar_file_name,avatar_content_type,..."}
```
If you’re using Rails 2.3, you can specify this as a default scope:
```
default_scope select_without_file_columns_for(:avatar)
```
Or if you’re using Rails 2.1 or 2.2 you can use it to create a named scope:
```
named_scope :without_file_data, select_without_file_columns_for(:avatar)
```
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: :controller/:action/:id

For now, I’ve overwritten my code from February. I believe this implementation is cleaner and easier to use; if anyone did download and use my code from February let me know and I can help you migrate the file data from the separate table back to columns in primary table. I will be writing a script to do this for my own application.

When I have time in the next few days or weeks, I’ll post a sample application that illustrates all of these steps and shows exactly how to do all of this. If anyone has any questions or suggestions please let me know.