Elixir: converting a database table to CSV with Ecto

Christian Kruse,

You would basically build your query with the from/2 function like always. Then you can wrap that to Repo.stream and build your stream pipeline:

file = File.open!("test.csv", [:write, :utf8])

Repo.transaction(fn ->
  from(row in Table)
  |> Repo.stream()
  |> Stream.map(&build_csv_row/1)
  |> CSV.encode()
  |> Enum.each(&IO.write(file, &1))
end)

This piece of code does the following steps:

  • it opens a file for writing
  • it starts a transaction (DBMS like PostgreSQL and MySQL/MariaDB need the transaction for cursors)
  • it builds a query
  • it builds an Elixir stream from that query, fetching 500 rows at a time by default
  • it calls a function build_csv_row for each row
  • it then encodes each row to CSV using the CSV library
  • and last it unwraps the stream and writes each row, in order, to the file we opened above

Clean, simple and nice :-)