Heroku - 10 Heroku postgre part 3

This third part of the Heroku Postgres tutorial picks up where the previous lesson left off: everything is now installed and configured, so we focus on actually creating a table and feeding it from a small Node.js application before deploying that application to Heroku. We use pgAdmin as the external client, since the Heroku Postgres dashboard does not let you modify the database directly: we need a real PostgreSQL tool to do that.

Creating the visits table with pgAdmin

We launch pgAdmin, right-click Servers and choose Create > Server. A dialog asks for the connection details, all of which come from the Heroku Postgres credentials page of the add-on: host, port, database name, username and password. Once the server is added, pgAdmin lists several databases (other tenants on the same Heroku Postgres cluster), but we only have permission on ours — it is the one without a small red cross on its icon.

Inside that database we right-click and create a new table called visit, designed to log one row per visit on the application. We add two columns: an id defined as a serial / auto-incremented integer that serves as the primary key, and a created_at timestamp to know when the visit happened. The table is now ready to receive data.

Building the Node.js app and deploying it

We then create a small Node.js project. npm init -y generates the package.json, after which we install the two required dependencies:

npm install pg
npm install express

In package.json, the start script is set to node app.js so Heroku knows how to launch the application. We create app.js and paste the example code (the focus is not on the code itself): the first part creates an Express server, the second opens the connection to the Postgres database, and the third inserts a row into the visit table on every HTTP request. To deploy, we log in with heroku login, initialize a Git repo with git init, then run git add ., git commit -m "first commit" and finally git push heroku master. Heroku detects the Node.js buildpack, installs every dependency declared in package.json and starts the application. The code is now live and writing visits into the Heroku Postgres database. That is all for this lesson — see you in the next one.

Summary

In this lesson, you learn how to connect to your Heroku PostgreSQL database using pgAdmin and create a database table to store application data. The lesson covers retrieving database credentials from the Heroku dashboard, establishing a secure connection to your remote PostgreSQL server, and designing a table schema with primary keys and timestamps. You then set up a Node.js application using Express and the pg driver to interact with the database, and finally deploy the complete application to Heroku using the Heroku CLI with Git.

Key points

  • Connect to Heroku PostgreSQL databases remotely using pgAdmin and database credentials from the Heroku dashboard
  • Create database tables with proper schema design including auto-incrementing primary keys and creation timestamps
  • Install and configure Node.js dependencies (Express framework and pg PostgreSQL client) via npm
  • Configure npm scripts in package.json to define how your application launches and connects to the database
  • Deploy your Node.js application to Heroku using Git push and the Heroku CLI
  • Identify and access only your own databases in shared Heroku PostgreSQL environments

FAQ

How do I find my database credentials to connect with pgAdmin?

Navigate to your Heroku dashboard, locate your PostgreSQL add-on in your app's resources section, and click on the database. The connection details including hostname, username, password, and database name will be displayed for use in pgAdmin configuration.

What npm packages are required to connect a Node.js application to Heroku PostgreSQL?

Install the pg package using npm install pg (PostgreSQL client for Node.js), and typically Express using npm install express for building the web server that queries the database and responds to requests.

What are the essential steps to deploy a Node.js application to Heroku?

First initialize a Git repository with git init, then add and commit your files (git add . and git commit), then use heroku login to authenticate, and finally push to Heroku using git push heroku master to deploy your application and its dependencies.