In this third post in the series, you’ll learn how to access a PostgreSQL database using Heroku Postgres and Node.js. By doing so, you’ll be using the most advanced open-source relational database within your applications, and you’ll be able to easily sync data with Salesforce using products like Heroku Connect.
We’ll be working with the conference-lwc-app from the previous blog post, so make sure to give it a read. I have pushed the source code in the heroku-examples/conference-api repo on GitHub; you can clone it using Git by running:
Introduction to npm
With every Node.js installation, you also get the
npm binary, which is used to install dependencies to your projects, such as in the previous blog post where we installed
fastify-cors. For this new project, we’ll install pg, the PostgreSQL driver for Node.js.
Creating a Heroku Postgres database
We’ll be using a PostgreSQL instance from Heroku, which means there’s no need to install the server on your local development machine. However, it is recommended to install
psql, the PostgreSQL client, so you can interact with the database from the CLI. The Heroku Dev Center provides instructions on how to set up PostgreSQL in your local environment.
To have a Heroku Postgres instance, you’ll need an application. So, let’s create one by running the following command from your
conference-api project folder:
Note: Make sure to replace <your-app-name> with a unique application name.
Next, add a Heroku Postgres add-on to your application by running:
Here you are using the
hobby-dev plan, which is free and perfect for development but has some limitations for production environments. If you want to learn more about the available plans, check the Heroku Postgres add-on page.
You can confirm that you have access to it by executing
psql through the Heroku CLI by running:
Now, you can execute a SQL query and do something like
SELECT now(); to get the current date and time.
Note: To use
heroku pg:psql, you’ll need to install the PostgreSQL client on your local machine.
The main advantage of using the Heroku CLI pg plugin is that you don’t need to worry about the authentication details; the CLI will take care of it for you.
Creating a database schema
You have connected to the PostgreSQL instance; now you need to create some tables to store information. Let’s take a look at the data schema that you need to create for the
The API built in the previous blog post returns a JSON structure composed of sessions and their speakers, so let’s create these two entity’s tables and their relation table using SQL
CREATE TABLE (see docs for more info).
Please copy the following SQL code into a
schema.sql file in your project folder. You’ll load it to the database using the Heroku CLI.
After saving it, you can either copy it directly to the
heroku pg:psql prompt, or you can run the following command to import the whole file:
Note: The previous command will work only on MacOS, Linux, or WSL. If you are using Windows, I recommend copying the file contents to the
heroku pg:psql prompt instead.
Now it is time for the refactor. In the following sections, you’ll do two things:
- Create a migration script to load the
sessions.jsondata into the database.
- Modify the
/api/sessionsroute to query this data and return it in JSON format.
Inserting data into the database
Your next job is to insert the data from the
data/sessions.json file into the database. Enter node-postgres!
pg, is the PostgreSQL driver for Node.js. You need this to interact with the PostgreSQL database from Node.js, and since this is an external dependency, you’ll need to install it in your project using npm:
To connect to a Postgres database, you’ll need the connection information and credentials. Since this service is managed by Heroku and attached to your application, you can use the Heroku CLI to get the
DATABASE_URL configuration and store it in a
.env file by running:
A Heroku application follows the 12-factor app methodology, which states that any configuration value needs to be stored in the environment. This is why the app uses a
.env file; those files are used to set up environment variables locally per project.
Note: Don’t forget to add the
.env file to your
.gitignore. This will prevent your credentials from being leaked to your version control system.
You’ll use the
dotenv package to load the
.env file automatically every time you execute a script. Be sure to install this as well by running:
After this, you’ll be able to access the environment variables stored in the
.env file by accessing the
process.env property in Node.js.
Now, let’s create an instance of the PostgreSQL client using the connection string from the environment:
Note: Since you are connecting to the database locally and on Heroku, it is recommended to set the
rejectUnauthorized property to
false to prevent issues with the TLS certificate during the PostgreSQL connection.
Client instance is good for single connections. For this section, you’ll create a
migrate.js script that will load the
sessions.json file into the database. The two methods you will use here are:
client.connect() to establish a connection to the database, and
client.query to perform SQL queries.
Let’s take a look at the final
migrate.js script. You can copy it into the
data directory from your project.
Querying data from the database
On the migration script, you used the
Client to perform the database connection. It makes sense in that context because it will be only one connection, but on a real web server like the API you are writing, it is better to use a connection pool to better handle the resources.
You will re-write the
/api/sessions route in
server.js to use a client
Pool instead. The connections parameters are the same as with the
Client. You can also define the max number of clients in the pool, but let’s use the default for this example. If you want to learn more about the
Pool class, take a look at the documentation.
Now you can perform queries using the client. This could be something simple like a regular SELECT from a specific table or something more complex like the query you need for the API route. As you can see, here we are leveraging the power of PostgreSQL to build JSON objects from a query using the JSON functions (supported in Postgres 9.6) and available in Heroku Postgres.
And that’s it; after you re-write the route, the last thing you’ll need to do is to commit your changes to Git and deploy to Heroku by running:
You have now learned how to access a PostgreSQL database from Node.js using node-postgres. Be sure to check the documentation to learn more features and configuration options.
In the next and final blog post in our Node.js series, you will learn how to integrate this Node.js application with a Salesforce app using Heroku Connect.
About the author
Julián Duque is a Lead Developer Advocate at Salesforce. If you want to get in contact with him, just run: