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
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.
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.
Thanks! It’s very helpful to me.
Especially with :multipart => true. I can’t find some about it earlier.
I was drawn by the hneotsy of what you write
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
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
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.
[…] 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. […]