How to use PGAdmin to connect to a Heroku database
Throughout my app development journeys I have had various instances where I have had to incorporate a database of some kind. I first started out using MongoDB and MLab as a way to store my data. However, I am use to using SQL Server Management Studio while at work and since MongoDB is NoSQL I found it difficult at times to be able to do what I wanted with the apps as some of my functionality required relationships. However, I have found an alternate solution to my issues, and that is PostGre SQL which is a free and open source relational database management system which uses pgadmin as a tool to manage your databases.
When writing APIs that act as middle men between my apps front end and the backend I found it cumbersome initially when setting up the way in which i would interact with my data using PG Admin so I thought I would write an article/tutorial to help others set this up as when i was looking for answers/guides on this topic I couldn’t find much! So here goes.
Note: I have already downloaded postgre sql and installed the necessary packages. You can find the download link for the software and documentation here: https://www.postgresql.org
After you have downloaded the necessary software you will need to create a heroku account if you haven’t already. (One thing with heroku is that it only lets you have up to 5 projects on your account if you don’t have a debit card or credit file associated with your account, otherwise I believe you can have up to 200 projects/databases on your heroku account)
You can sign up or login to heroku here:
Once you have created your account and logged in locate to your accounts dashboard (You can find this by clicking on square that is made of 9 dots to the left of your account profile picture and then clicking “dashboard”). You should then click the “new” button then click the button that shows labelled “create new app”. You will now need to enter a unique name for your app, I am just calling mine “testapp400” for now. Then click “create app”. This should then generate an app and showcase to you various tabs labelled “overview”, “resources”, “deploy”, “metrics”, “activity”, “access” and “settings”. Click on the tab “resources”.
In the add-ons search bar enter “PostgreSQL, click the top option that appears. A popup like the below should appear.
Choose what plan you want for the resource, I just choose the default one of hobby then click the button labelled “provision”.
This then attaches post-gre sql as at database to your app/project.
Still on the “resources” tab you should now see “Heroku Postgres” as an add on and the text “Attached as database”. Click on the add on icon, this will open another tab that will describe various elements regarding your database. If you click on the tab “settings” you will see a heading labelled “database credentials”, if you click the button to the right of the heading pieces of information such as host, username and password will show these are the credentials you will need to use when wanting to use the database via pgadmin.
Using PGAdmin
If you have installed pgadmin correctly then a tab at the top of your window should show an icon that looks like an elephant. Click this and then click “new pgadmin 4 window”.
A new tab in your browser should load with a pgadmin 4 link
Right click on the “servers” on the left and then click “create new server”.
This should then bring up a window like the below
You can set the name of the server to be whatever you like. Once you have set the name click on the tab “connection”.
All of the information including Host name/address, database, username and password are the unique credentials that were generated for your database app via heroku that can be found on the page you visited earlier when clicking on the “Heroku Postgres” icon in the add-ons section as you can see in the image below.
When you have entered the information, find the database name of your app it will most likely start with a d4, click on the “advanced” tab and then paste the database name into the textbox labelled DB restriction. This will filter out all of the databases that get loaded so only your database shows on this server as Heroku does something strange and loads all of the databases that have been made with it but nobody can access yours so no need to worry about unauthorized access. Click the save button and you should now have access to your heroku database using PGAdmin.
As you can see from the above I have a server setup and if you click on that server it should open various headers such as databases, group roles and table spaces. You can now create tables within this database and do whatever you like by clicking on the “schemas” heading.
There you have it, a Heroku app/database and the ability to edit it using PG Admin. I hope you found this article helpful and informative, in the next tutorial I will show you how can communicate with your database via a NodeJS API.
