SQL data streaming

If you ever need to provide a download stream based on data from Postgres SQL, there’s a great low level/optimized example on how to do it with Rails.

For my needs, I adapted it to work with ActionController::Live and send_stream. This example streams xlsx with xlsxtream, but can be repurposed for any generated data:

config/initializers/mime_types.rb

Mime::Type.register \
  'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
  :xlsx

app/controllers/concerns/sql_streaming.rb

module SqlStreaming
  def sql_stream(sql)
    raw_connection = ApplicationRecord.connection.raw_connection
    raw_connection.send_query(sql)
    raw_connection.set_single_row_mode
    raw_connection.get_result.stream_each do |record|
      yield record
    end
    raw_connection.get_result
  end
end

in the controller

include SqlStreaming


def index
  sql = Model.all.to_sql

  send_stream(filename: "file.xlsx") do |stream|
    xlsx = Xlsxtream::Workbook.new(stream)
    headings_written = false
  
    xlsx.write_worksheet(name: sheet_name, auto_format: true) do |sheet|
      sql_stream(sql) do |record|
        unless headings_written
          sheet.add_row headings
          headings_written = true
        end
  
        sheet.add_row make_row(record)
      end
    end
  
    xlsx.close
  end
end

---

Code snippets in this post are covered by 0BSD License.

---

---

---