Fast CSV downloading for Rails

admin_foo_models.rb

ActiveAdmin.register FooModel do
  extend CsvDumpableResource
end

concerns_csv_dumpable.rb

module Concerns::CsvDumpable
  extend ActiveSupport::Concern

  class_methods do
    # @param io [IO | ActionController::Live::Buffer] dump to
    # @param batch_size [Integer] fetch size
    # @return [IO | ActionController::Live::Buffer]
    # @example
    #   Model.dump_csv(response.stream).close           # dump all records to streaming buffer
    #   Model.dump_csv(File.open('dump.csv','w')).close # dump all records to file IO
    #   Model.limit(100).dump_csv($stdout)              # dump 100 records to stdout IO
    #   Model.limit(100).dump_csv.string                # dump 100 records to string IO, and get the string
    # @note CSV specifications:
    #   - Encoding: Shift_JIS
    #   - Datetime format: ISO 8601 without time zone, and the time zone is UTC (e.g. +2018-01-01 10:00:00+)
    def dump_csv(io = StringIO.new, batch_size: 10000)
      field_struct = Struct.new(:name, :select, keyword_init: true)
      fields = columns.map do |column|
        field_struct.new \
          name: column.name,
          select: case column.sql_type_metadata.type
            when :datetime then %[DATE_FORMAT(#{column.name}, "%Y-%m-%d %T") AS #{column.name}]
            else column.name
          end
      end
      headers = fields.map(&:name)
      selects = fields.map(&:select)

      csv_options = { encoding: 'Shift_JIS' }
      io.write CSV.generate(csv_options) { |row| row << headers }

      select(selects).unscope(:order).in_batches(of: batch_size) do |relation|
        csv = CSV.generate(csv_options) do |row|
          records = ActiveRecord::Base.connection.select_all(relation.to_sql)
          records.each do |record|
            row << headers.map { |h| record[h] }
          end
        end
        io.write csv
      end
      io
    end
  end
end

concerns_csv_dumpable_resource.rb

module CsvDumpableResource
  # @see Sharing code between ActiveAdmin resources · Please be careful http://tmichel.github.io/2015/02/22/sharing-code-between-activeadmin-resources/
  def self.extended(base)
    base.instance_eval do
      controller do
        include ActionController::Live
      end

      basename = config.resource_name.plural

      # @see ActionController::Live https://api.rubyonrails.org/classes/ActionController/Live.html
      # @see Railsで大きなファイルを扱う際のポイント https://techracho.bpsinc.jp/baba/2014_10_08/19139
      # @see S3からRailsを介して大きなファイルをストリーミングダウンロードさせる - なんとなく日々徒然と http://yoshitsugufujii.github.io/blog/2017/06/29/large-s3-file-relay/
      collection_action :csv, method: :get do
        filename = Time.zone.now.strftime("#{basename}_%Y-%m-%dT%H-%M-%SZ.csv")
        response.headers['Content-Type'] = 'text/csv'
        response.headers['Content-Disposition'] = "attachment; filename=#{filename}"
        find_collection.unscope(:limit, :offset).dump_csv(response.stream)
      rescue ActionController::Live::ClientDisconnected
        # noop
      ensure
        response.stream.close
      end

      action_item :csv, only: :index do
        filter_params = params[:q]&.permit!
        link_to I18n.t('activeadmin.action_item.download_csv'), send("csv_admin_#{basename}_path", q: filter_params)
      end
    end
  end
end

foo_model.rb

class FooModel < ApplicationRecord
  include Concerns::CsvDumpable
end

nginx.conf

upstream app {
    server app:3000;
}

server {
    listen 80 default_server;
    server_name _;

    # Required for streaming downloading
    # Nginx communicates to an origin server by HTTP 1.0 by default
    proxy_http_version 1.1;
  
    proxy_set_header Host              $host;
    proxy_set_header CLIENT_IP         $remote_addr;
    proxy_set_header X-Real-IP         $remote_addr;
    proxy_set_header X-CSRF-Token      $http_x_csrf_token;
    proxy_set_header X-Forwarded-Proto $http_x_forwarded_proto;
    proxy_set_header X-Forwarded-For   $proxy_add_x_forwarded_for;

    location / {
        proxy_pass http://app;
    }
}

Environments

• Ruby 2.5.1
• Rails 5.2.1
• MySQL 5.7
• Active Admin 1.3.1