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
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
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
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
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.
Top comments (0)