saving ruby on rails attachments as blobs

today we want to speak about a rather controversial topic: saving your attachments for models as BLOBs into a SQL-database.

first let’s look at the reason to save files into a database instead of simply putting them on a normal filesystem.

  • easy accesscontrol => authenticated up/download
  • central backup for an application => one mysqldump/pg_dump saves the state of the whole app
  • replacement of nfs or similiar methods to achieve proper file locking on a central fileserver
  • easy to cluster
  • easy quota setting and enforcement

these are only a few of the advantages of saving user uploads to a SQL-database.

and of course where there is light, there is always shadow too. so let’s look at the downsides, which i don’t want to neglect here:

  • low performance => not suitable for high load environments
  • high ram usage with big uploads => the whole upload will be saved to the ram first
  • file size limits => 4GB mysql / 2GB postgresql
  • you must be able to edit the database settings (impossible on shared hostings)

as you can see, this truly is a debatable topic, which i already had a lot of discussions about. the important thing is to know the pros and cons before you decide to go this way with your application.

so in order to get started with this you should know, that mysql and postgresql by default have a very small maximum packet size for inserts and dumps. (i.e. 2mb). on a mysql server you should set

[mysqld]
max_allowed_packet = 200MB
[mysqldump]
max_allowed_packet = 200MB

now it’s time to think about your model. you should know something quite specific, namely SELECTs over several bigger BLOBs can be very very slow, and should be avoided at all times. otherwise your database server thinks your ram is an all you can eat buffet. so we have to save our blobs to an extra model. this means we have one model with all the information about a file (similar to paperclip) and another model holding the binary data itself. You have to be careful when choosing your model name, because “file” is used by ruby, “attachment” has been taken by paperclip and “document” is reserved by javascript (more on that later).

let’s look at our migrations

ruby script/generate migrations Upload filename:string content_type:string binary_id:integer size:integer
ruby script/generate model Binary data:binary

nothing to exciting here so let’s have a look into the app/models/binary.rb

class Binary < ActiveRecord::Base
  has_many :uploads
  validates_presence_of :data
  def file_data=(input_data)
    self.data = input_data.read
  end
end

the only thing noteworthy here is the reading of input_data to file_data
so what about the upload model app/model/upload.rb

class Upload < ActiveRecord::Base
  belongs_to :binary,  :dependent => :destroy
  attr_accessor :file_data
  def before_create
    input = self.file_data
    @binary = Binary.create(:file_data => input)
    self.binary_id = @binary.id
    self.filename = input.original_filename
    self.content_type = input.content_type.chomp
    self.size = @binary.data.size
  end
end

there is a lot more going on in this model. in theory the code should be pretty self explanatory. if you still have questions, please write a comment.

so what do we need to save files to our database? we can leave the scaffolded uploads_controller alone and instead look at app/views/uploads/new.html.erb

<% form_for(@upload, :html=> {:multipart=>true}) do |f| %>
    <%= f.error_messages %>
    <%= f.file_field :file_data %>
    <%= f.submit t :new_button %>
<% end %>

if you test this code you will discover a nasty little bug. all files, saved to the database are only 64kb big. but why? simple answer: ruby on rails filetype :binary equals a simple BLOB in mysql. so in order to save bigger files we have to use LONGBLOB:

ALTER TABLE `binaries` CHANGE `data` `data` LONGBLOB NULL DEFAULT NULL

now the upload to the database should work like a charm. but what about downloading those files from the database?
let’s look at the show method of app/controllers/uploads.rb

  def show
    @upload = Upload.find(params[:id])

    @data = @upload.binary.data
    send_data(@data, :type => @upload.content_type, :filename => @upload.filename, :disposition => 'download')
  end

and so your files find their way bay from the database. i hope you have seen enough to start tinkering on your own experiment.

i am always open to questions or suggestions.

enjoy.

little preview:
soon i’ll show you how to upload multiple files using ajax and accepts_nested_attributes_for

8 responses to “saving ruby on rails attachments as blobs”

  1. Josh C.

    Hi, I’m currently working on a project where I do not need to save the binary data in a seperate model, due to the ability of filestream attribute in sql server. With that said, how would I go about saving the binary data, file_name and file_type all in on model?

    Thanks for the help thus far, this tutotiral has brought me on step closer finishing this iteration.

    1. admin

      well that just makes it easier…you only have to leave out my Binary model and simply put the data field into your unified model.

  2. Alex Antonov

    Thanks! It’s very helpful to me.

    Especially with :multipart => true. I can’t find some about it earlier.

    1. Trish

      I was drawn by the hneotsy of what you write

  3. baash05

    I had to do this

    before_create :on_before_create
    def on_before_create

    end
    in app/model/upload.rb
    to run the create code.

    Apart from that.. thanks very much

  4. baash05

    Question.. do you know to send a file to this with restful api? I’d love to figure out the CURL commands to get c:\test.bmp into the DB.. for example

  5. Oscar Guerra

    Exelente aporte, la verdad ya tenia varioas dias tratando de hacerlo y gracias a este post pude lograrlo, solo modifique un poco ya que me daban algunos errores pero cosas muy pequeñas, Gracias por el aporte.

  6. uploading an image as blob with ajax and rails 4.0 | over9000 blog

    […] In a recent project i have to cope with image upload, but using a gem like paperclip or carrierwave seemed overloaded to be. It is just a simple logo upload for companies. So i decided to upload the logo as blob described in this post. […]

Leave a Reply to baash05 Click here to cancel reply.