Using CSV files with Postgres on Heroku

In this post we’re going to cover how to import CSV data into a Heroku Postgres database, and export data as CSV from a Heroku Postgres database.

It is often the case that you need to quickly insert several rows of data into a database, or indeed export the result of a database query. CSVs are an excellent tool for doing both of these actions.

What are CSV files

CSV files, or comma separated values files are text files with a standard format. As the filename would suggest, each value is separated by a comma.

Because these are text files, and there are known standards for how they should behave, they are perfect for tools that like looping over files to send or receive data, Postgres being no exception.

A CSV file might look something like this;

id, name, age
1,Sarah,28
2,Foz,82

Note the first row contains the names of the attributes we store in the CSV file. This isn’t a requirement, but is incredibly useful to both machines and humans.

Connected to Postgres from Heroku

The first thing we need to do if we’re going to play with CSVs on Heroku databases, is actually connect to a database.

In Heroku, the best way to do this is with their cli tool. This tool allows you to perform Heroku actions from your command line.

The command we want to run is heroku pg:psql --app your-app-name.

When you run this command, Heroku will look up your app based on what you passed into the --app flag, and will find the appropriate database to connect to. If this works you will see something like this;

your-app-name::HEROKU_POSTGRESQL_GREEN=>

The actual text will differ based on your application name and the internal name Heroku has given your database. If you only have one database linked to your application, this will almost certainly be correct, if you have several you should confirm you are connected to the right one.

Now that you’re seeing this prompt, you can run any postgres command. For example SELECT * FROM people; would select all the people records in your database, assuming you had a table called people.

Exporting Heroku postgres data to CSV

Running the above query in your terminal is fine, but the result only lives as long as your terminal session, but exporting it to CSV we can store it permanently.

The really great thing is that you can store the file on your local machine, you don’t have to worry about using Heroku’s ephemeral storage, or uploading it to somewhere like AWS’s S3.

The best way to explain this is with an example;

 \copy (SELECT * FROM people) TO '~/Desktop/people.csv' WITH CSV DELIMITER ';' HEADER

This command will do the same SELECT we did earlier, but instead of displaying the data on the screen, it will export it to a CSV file.

Lets go through each part of this line in turn;

• /copy this tells Postgres we want to copy some information, either from somewhere or to somewhere.
• (SELECT * FROM people) this is the data we want to copy, in our case, the result of a query. It doesn’t need to be a simple query like this, any valid postgres query will work.
• TO is a requirement of /copy, we’ve said we want to copy something from somewhere, now we need to say TO to begin saying where we should put the data we’ve copied.
• '~/Desktop/people.csv' this is the location we want to place the output. Note this is going onto my local computer, in this case my Desktop, in a file called people.csv.
• WITH CSV DELIMITER ';' HEADER this final part says to use CSV as the export mechanism, to use ‘;’ as a delimiter (which just means what to use to separate the values), and finally to include a HEADER which in our case will mean the first row will have the attributes we have stored in the CSV file.

When you run the command you should see some output like COPY 112. This means COPY has saved 112 lines of data.

Importing Heroku postgres data from CSV

What we’ve covered so far is great for extracting information, but it doesn’t help with importing it. /copy has a FROM as well as a TO, which I will demo now;

 \copy people(name, age) FROM '~/Desktop/upload.csv' DELIMITER ',' CSV HEADER;

Again, lets go through this bit by bit;

• /copy this tells Postgres we want to copy some information, either from somewhere or to somewhere.
• people(name, age) this tells Postgres we will be acting on the people table, specifically with the name and age attributes.
• FROM lets /copy know it has to gets its data from somewhere.
• '~/Desktop/upload.csv' is the location of our CSV file on our local computer.
• DELIMITER ',' CSV HEADER; in this example we’ve set the DELIMITER to be ‘,’, because this is the way the CSV file was formatted, we then tell Postgres to expect a CSV file, and let it know there is a HEADER as part of the file, if we didn’t it would expect the first row to contain data to insert.

When you run the command you should see some output like COPY 112. This means COPY has imported 112 lines of data.

Not just Heroku

It is worth noting that /copy is not unique to Heroku, this is a psql client command. When we called heroku pg:psql earlier we were asking it to run a psql client for us. Any time you are in a psql client you can use these commands.