A Comprehensive Guide to Heroku Postgres

This article was published as a part of the Data Science Blogathon.

Introduction

Recently I was doing an analysis, and in this analysis, I wanted to take the data from a remote database. I previously used Azure, but I had to delete the database after learning. Otherwise, they will deduct money from your Azure balance. This is not a good experience for students. So I am searching for a free database in which I don’t have to worry about the money. Then I came to know about Heroku Postgres.

Heroku offers a free plan for hosting the PostgreSQL database. This is so helpful when you are a beginner or you want a quickly hosted database for experiments. If you want to extend your database capacity, you have to pay a little amount for it, even a student can afford it.

In this article, I am going to explain –

• How to create a database using Heroku.

• How to get the credentials for accessing the database.

• How to create a table in this database and run queries.

So, without further ado, let’s get started.

Creating a Database in Heroku

To create a database in Heroku, just follow the below steps as mentioned:

Step 1: Log in to Heroku

Just go to https://www.heroku.com/. If you have an account already in Heroku, just press the Log-in button. Otherwise, press the Sign-up button and make a new account by following the instructions.

The Log-in page of Heroku

Step 2: Create a new App

After logging in, you have to create a new app. You can give any name to this app as this app is only used for accessing the database. So, the name doesn’t matter.

Step 3: Add a PostgreSQL database

After creating an app, open the app and go to the resource tab. From there you have to search for ‘Heroku Postgres’ in the add-ons section. After that, select that option and enable it.

Now you have successfully created a database in Heroku.

Getting the Credentials

Now to access this database, we have to know the credentials. For this, we have to click on the Heroku Postgres add-on. After that, we are navigated to another window where we have to go to the ‘Settings’ tab. Here we will find our credentials.

Note: I delete this database after writing this article for security purposes.

Adding Table in the Database

Now, it’s time to add data to our database. For this, you can use any IDE of your choice. I am using DataGrip, a cross-platform IDE for databases and SQL by Jetbrains. Unfortunately, this software is not free. You have to pay for this. But if you are a student, you can get it by subscribing to Github Student Developer Pack. If you don’t have DataGrip, don’t worry, I will explain it for Visual Studio code too.

Connecting the Database in DataGrip

If you have DataGrip, follow the below steps. If you don’t have DataGrip, just scroll down and read from Connecting the database from the Visual Studio Code Section:

Step 1: Create a project

At first, you have to create a new project. For this, double-click on the DataGrip icon on the desktop. You will see a dialog box. From there, click on the New Project option. Then you are asked for the name of the project. Give it a name as your wish and click OK. A new project will be created.

Step 2: Create a Data Source

After creating a project, you have to create a data source. For this, click on the Database Explorer from the left side of the screen. Then navigate to + -> Data Source -> PostgreSQL. You can see a dialog box asking for credentials. Fill those fields, click OK and wait for a while. If everything is fine, you will be connected to Heroku Postgres.

Step 3: Adding Table to Database Schema

If you are familiar with PostgreSQL, you will know that there is already a schema available in the PostgreSQL database named Public. See the below image.

Under the Public schema, you will also see a folder named tables. In this folder, we will create our table. If you right-click on this folder, you will see the Import Data from File option. Click on this option and select the file from which you want to import the table. The file format must be in .csv, other file formats will not work.

After selecting the file, you can see a dialog box which is shown below from where you can make some changes according to your needs and press Import. Voila! You successfully created a database in the Heroku Postgres database.

Connecting the Database from VS Code

Now I am using Visual Studio Code. This IDE is free for everyone. If you want to connect the Heroku Postgres database in VS code, follow the below steps.

Step 1: Install PostgreSQL extension in VS code

If you are already familiar with VS code, you will already know that without the extension, VS code is nothing. So, for connecting the database, we have to install the database named PostgreSQL by Chris Kolkman. See the image below.

The PostgreSQL extension

Just typing the keyword post, you can easily see that the extension is coming on the top in the search result. You can also install it by clicking this link. After that, restart the VS code and you are ready to go.

Step 2: Connecting to the database

Now you have to connect to the database. For this, you have to press Ctrl + Shift + P. it will open up the command palette of VS code. Here you have to type Postgresql, this will display the command related to the PostgreSQL extension. From there, you have to select the PostgreSQL: Add Connection command.

The Add Connection Command

After selecting this command, you are asked for the credentials one by one, just fill those fields and hit Enter. One additional field is asked here, the connection type – Secure Connection or Standard Connection. In this case, you have to select Secure Connection. If everything is fine, you don’t see any error and after that, you are successfully connected to the database.

Step 3: Making a table in this database

Now you have to click on the PostgreSQL Explorer from the left pane. If the database is connected, you will see something like the image below:

Right-click on the connection and select the New Query option. It will open a new file on the right side.

Now in this file, we can run any query. As we want to create a table, let’s run a query that creates a table in the Public database schema. Just write the below query, select it and run it by right-clicking on it.

CREATE TABLE IF NOT EXISTS chocolate_database(
   ref INT,
   company TEXT,
   company_location TEXT,
   review_date INT,
   country_of_bean_origin TEXT,
   bar_name TEXT,
   cocoa_percent TEXT,
   ingredients TEXT,
   most_memorable_characteristic TEXT,
   rating NUMERIC
);

If everything is fine, you will see that a table is created. This is a demo table. Try to do the experiment for a better understanding.

Now there is one thing in which you face difficulties. In DataGrip, you can easily import the data from a file by right-clicking on it, but here this is not the case. The PostgreSQL extension doesn’t give this functionality here. So, here you have to use the psql command-line tool. For this, open the Start menu and search for psql. You will get the result something like the below image.

You are asked for credentials after opening the psql command-line tool again one by one. After that, give the below command:

copy chocolate_database FROM  DELIMITER  CSV HEADER

See the below image to make it more clear.

If everything is fine, you will get an output COPY . If you want to use my file, follow this link and download the .csv file.

Don’t confuse with copy command for psql and COPY in PostgreSQL. You need superuser access to run the COPY command. But for copy, you don’t need that.

Conclusion to Heroku

Hurray! You made it. Now you can easily access Heroku Postgres from any IDE. The process for connecting to a remote database is nearly the same for all cloud database providers. So, if you understand the process of connecting a specific remote database, you don’t face difficulties with the others.

So, that’s all for now. I will come back again with another article. until then, stay tuned!

The media shown in this article is not owned by Analytics Vidhya and is used at the Author’s discretion.

Subhradeep

Recently pursuing M.Tech in Artificial Intelligence and love to do anything about Data Science, Machine Learning, and AI. I also like to share my knowledge through Blogs. Ask me anything about Data Science, Machine Learning, and AI at [email protected].