How to create large CSV file and send it to front end

user_7823702 0
Viewed 609

I'm building a project where the front end is react and the backend is ruby on rails and uses a postgres DB. A required functionality is the ability for users to export a large dataset. So they'll get a table view and click "export" and that will send a request to the backend which should create a CSV file and send it to the front end.

This is the query that displays the data in the table and how it's executed (using find_by_sql)

query = <<-SQL
    SELECT * FROM ORDERS WHERE ORDERS.STORE_ID = ? OFFSET ? LIMIT ?
SQL
query_result = Order.find_by_sql([query, store_id.to_i, offset.to_i, 50])

Now whenever the users click export, it's going to make a request to the same endpoint except it'll set a flag to notify the backend that it wants a CSV file and the limit will be much greater than 50...it could be hundreds of thousands to millions of records.

What is the best way to create a CSV to send to the front end, taking into account that the number of records will be large.

postgresql ruby-on-rails
1 Answers

You have a couple of options:

  1. Create a temporary file, use the standard CSV library to populate that temporary file, and then use send_file to dispatch that file to the user.

  2. Depending on the size of data and/or your server's ability to host large temporary files, spooling to a tempfile might take too long or be otherwise impractical. In that case, you might want to stream the CSV data as it's generated, which is more complicated to set up but lessens the impact on your server.

This article has some well thought out steps to set up an interface for streaming data. As a bonus, it also delegates the act of generating the CSV to PostgreSQL itself. This will give you the best possible performance, but at the expense of code readability. However, it should set you on your way.

user_13265180
Related