Big CSV exports with Rails: streaming FTW

One of the projects we're working at the moment had a heavy reporting requirement: years of sales and transaction data - all of which needed to be exportable as CSV files. Big CSV files.

Normally, I might look at queuing the export and executing it as a background process - but this time I found a quick win: using a combination of ActiveRecord's find_each, and Ruby's Enumerator to stream the CSV - in batches, and straight from the controller. As with most things Rails, it was really quite easy:

require 'csv'

class ReportsController < ApplicationController
  def index
    headers.delete("Content-Length")
    headers["Cache-Control"] = "no-cache"
    headers["Content-Type"] = "text/csv"
    headers["Content-Disposition"] = "attachment; filename=\"report.csv\""
    headers["X-Accel-Buffering"] = "no"

    response.status = 200

    self.response_body = csv_enumerator
  end

  private

  def csv_enumerator
    @csv_enumerator ||= Enumerator.new do |yielder|
      ReportItem.find_each do |row|
        yielder << CSV.generate_line([
          row.id,
          row.name,
          row.total
        ])
      end
    end
  end
end

Using find_each saves me from:

• Putting too much load on the database with a heavy query
• Holding too much data in memory

and passing an Enumerator to response_body forces the browser to keep the connection open while it pipes down whatever data is yielded. I also do the usual stuff like setting the content-type and disposition so that the browser still treats it like a file download.

It worked exceptionally for our use case, and required dramatically less effort than adopting a queue - both in terms of code and UX.

Comments

[Mohamed Ziata]

I had to add:

headers["Last-Modified"] = Time.now.httpdate.to_s

to make it work.

[Soundarapandian Rathinasamy]

@wakematta You saved my day! Thank you!