Importing csv or json files into Heroku Postgres Databases

Recently there was a need to move data from Bubble to Heroku, as Bubble started to charge a lot of money for storing and accessing a large amount of data, so it was decided to move the project data to Heroku.

Data for movement

If you don't already have the Heroku CLI installed, install it by following the instructions at official website. Also don't forget to download PostgreSQL from official website.

The input data is as follows:
The JSON file obtained from Bubble looks like this:

[{"order_number":"41744565-0033-2","status":"delivered","unique id":"1713335321939x405889175018036500"}]

If your database dump is in JSON format, the process of importing the data into PostgreSQL on Heroku is a bit more complicated. You will need to convert the JSON into a format suitable for import into PostgreSQL (such as CSV).

Now let's move on to website to convert JSON to CSV. Everything is simple here, select the file and click the Convert JSON to CSV button.

Connections via the command line interface (CLI)

Enter the command C:\Users\12345>heroku login
We receive a message heroku: Press any key to open up the browser to login or q to exit:
Press ENTER, go to the Heroku website, log in, and then receive the message:
Logging in... done
Logged in as you_mail@mail.com

Next, go to the location of PSQL
C:\Users\12345>cd C:\Program Files\PostgreSQL\13\bin

We enter the command to connect to your database and receive a response about a successful connection

C:\Program Files\PostgreSQL\13\bin>heroku pg:psql -a youname
--> Connecting to postgresql-cubed-11111

Next we need to enter a command to copy our CSV file to Heroku and get a response about the amount of copied data

project_name::DATABASE=> \COPY orders(number, status, bubble_id) FROM 'C:\Users\12345\Downloads\test_orders.csv' WITH CSV HEADER;
COPY 811

You can check the availability of data in the database using the SQL command

project_name::DATABASE=> SELECT * FROM orders LIMIT 50;

Check if the data was recorded correctly and enjoy the result. Don't forget that you will need to download Heroku CLI and pg:psql

If you have any questions, you can always read the documentation with Heroku official website