DEV Community

Cover image for Streaming CSV files in Ruby On Rails
Faisal Shaikh
Faisal Shaikh

Posted on • Updated on

Streaming CSV files in Ruby On Rails

Here I will show you how to achieve the streaming of CSV files using Ruby On Rails and Postgres. We will use concerns for our model and controllers so that we can include them wherever needed. Model concern file generates the data for CSV and the controller concern file handles the file exporting on the client's end.

Model concern file generate_csv_data.rb contains this code as follows:

  module GenerateCsvData
   extend ActiveSupport::Concern
    class_methods do
     def stream_query_rows(sql_query, options="WITH CSV HEADER")
      conn = ActiveRecord::Base.connection.raw_connection
      conn.copy_data "COPY (#{sql_query}) TO STDOUT #{options};" do
      while row = conn.get_copy_data
       yield row
     end
    end
  end
 end
Enter fullscreen mode Exit fullscreen mode

Here stream_query_rows method accepts a query and options and yields the data. COPY (#{sql_query}) TO STDOUT #{options}; is a Postgres command which copies the data return by the query and output it to a specific format (here the format is CSV).

Controller concern file stream_file.rb contains this code as follows:

  module StreamFile
   extend ActiveSupport::Concern
    def stream_file(filename, extension)
     response.headers["Content-Type"] = "application/octet-stream"
     response.headers["Content-Disposition"] = "attachment; filename=# 
     {filename}-#{Time.now.to_i}.#{extension}"
     yield response.stream
   ensure
    response.stream.close
   end
  end
Enter fullscreen mode Exit fullscreen mode

Here stream_file method accepts a filename and extension of the file and then yields the response and sends the file as an attachment to the client.
We can then include our concerns wherever needed.

Example:
We want all the data from the product's table into a CSV file and send the file as an attachment to the client. We will include the model concern in the product model and controller concern in products controller respectively:

Product Model:

  class ProductMaster < ApplicationRecord
   include GenerateCsvData
  end
Enter fullscreen mode Exit fullscreen mode

Product Controller:

  class ProductsController < ApplicationController
   include StreamFile
   def export
    respond_to do |format|
    format.csv { stream_csv_report }
   end
  end

  private
  def stream_csv_report
    query = Product.some_query(query_params).to_sql
    query_options = "WITH CSV HEADER"
    # Note that if you have a custom select in your query
    # you may need to generate the header yourself. e.g.
    # => stream.write "Name,Created Date,Price,# of Products"
    # => query_options = "WITH CSV" # note the lack of 'HEADER'
    stream_file("products", "csv") do |stream|
      Product.stream_query_rows(query, query_options) do |row_from_db|
       # row_from_db will be ordered according to the select
       # e.g.
       # => Given: "COPY (SELECT product.name as name, price, created_at 
            FROM sales INNER JOIN ...) TO STDOUT WITH CSV"
       # => row_from_db will look like 'Bag, 500,2010-05-11 
            13:27:58.580456'
       stream.write row_from_db
      end
    end
   end
 end
Enter fullscreen mode Exit fullscreen mode

Benefits:

  • Generating CSV using a gem and ActiveRecord for large data of more than 1 lakh records would take lots of minutes and slow down the server until it finishes the process but with Postgres, we can achieve this under 2-3 minutes.
  • Postgres is fast generating large data in CSV.
  • Data is immediately downloaded on the client-side no need to save anywhere if you don't want to.

Latest comments (0)