Exporting From Rails on Heroku

Recently I needed to do a bunch of ad-hoc data exports from production. These exports were for exploration, so the format went through a few iterations of change. Using heroku run console and copy-pasting in a script got tedious, and its hard to capture the output. So here are two better ways of exporting data from a Rails app running on Heroku.

via PostgreSQL

If you want to export some tables, PostgreSQL has a neat built-in feature that can turn a query result into a CSV file. Running queries against the Heroku database can be done via heroku ps:psql which comes as part of the Heroku CLI. It does require you to have PostgreSQL installed on your local machine.

-- in a file named users.sql
COPY (
  SELECT
    u.id as "User ID",
    u.name as Name,
  FROM users u
) TO STDOUT WITH (FORMAT csv, HEADER)

# Write the results of users.sql to a file named users.csv
heroku pg:psql --app=your-app < users.sql > users.csv

via a Rails script

When you are wanting to save the result of a script run with cat script.rb | heroku run rails console, there are some problems. Namely that the script lines are echo’d into the output. To avoid this, you can use rails runner. If you are doing any logging in your script, you might need to set the logger to STDERR, so you can capture the script output and logging output separately.

# users.rb
report = csv.generate do |csv|
  csv << headers

  User.find_each do |u|
    csv << [u.id, u.name]
  end
end

STDOUT.puts report

# run the users.rb script and save the result to users.csv
cat users.rb | heroku run --no-tty --app=your-app -- bin/rails runner - > users.csv

# In my case, I need to remove a few NewRelic logging lines that are outputted when the rails console starts up.
cat users.rb | heroku run --no-tty --app=your-app -- bin/rails runner - | grep -v "\*\* \[NewRelic\]" > users.csv