Introduction
Heroku is a hosting service that supports Node.js applications. It is easy to use and its functionality can be extended with add-ons. There are add-ons for various things, including messaging/queues, logging, metrics, and of course, data stores. The data store add-ons support popular databases, like PostgreSQL, Redis, and DynamoDB.
In this tutorial we'll be adding a PostgreSQL database to a Node application that shortens URLs. We will then deploy the application to Heroku and set up the PostgreSQL add-on.
PostgreSQL
If you don't have it already, you'll need to install Postgres on your machine. There are a few different ways to install it, depending on your OS. Visit the PostgreSQL downloads page for more information.
With PostgreSQL installed, we can create a database for the URL shortener application to use:
$ psql
psql (11.6)
Type "help" for help.
tomkadwill=#
And then use the CREATE DATABASE SQL command:
tomkadwill=# CREATE DATABASE urlshortener_development;
CREATE DATABASE
tomkadwill=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
--------------------------+------------+----------+-------------+-------------+-----------------------
urlshortener_development | tomkadwill | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
Here we create a database called urlshortener_development and then use \l to print a list of all PostgreSQL databases in the system.
Our new database urlshortener_development is there so we know it was created successfully. Also, note the database owner because we'll need it later on (yours will be different from mine).
Integrating Postgres into a Node App
The Node application that we will be working on is fairly simple. If you want to build it from scratch you can follow our guide, Deploying a Node.js App to Heroku, or you can download it from GitHub.
The Express app logic is all inside app.js:
const express = require('express');
const app = express();
const path = require('path');
const port = process.env.PORT || 3000;
const urlShortener = require('node-url-shortener');
const bodyParser = require('body-parser')
app.use(bodyParser.urlencoded({extended: true}));
app.use(express.urlencoded());
app.get('/', function(req, res) {
res.sendFile(path.join(__dirname + '/index.html'));
});
app.post('/url', function(req, res) {
const url = req.body.url
urlShortener.short(url, function(err, shortUrl){
res.send(shortUrl);
});
});
app.listen(port, () => console.log(`url-shortener listening on port ${port}!`));
You can run the application via npm start. Once started, browse to localhost:3000 and you should see the homepage:
The plan is to update app.js so that it stores each URL and shortened URL in a DB table and then display the last 5 results on the UI.
The first thing we need to do is install an ORM (Object Relation Mapper) library. Interacting directly with PostgreSQL is difficult because we'd have to write our own raw SQL queries.
An ORM allows us to interact with the database via simpler API calls. Note that there are some downsides to using ORMs, but I won't cover them in this tutorial.
There are a number of different ORM libraries for Node, in this case we'll be using Sequelize:
$ npm install --save sequelize
$ npm install --save pg pg-hstore
The first command installs Sequelize and the second installs the PostgreSQL driver for Node. Sequelize supports multiple databases so we need to specify which one to use and provide the Node driver.
Migrations
With Sequelize installed and configured, we can think about the database structure. We only need something simple, a single table with 3 columns: a unique ID, original URL, and shortened URL.
We could create the new database table manually, but that would make deployments painful. We'd have to remember our queries and run them on each environment.
A better way to handle database changes is via migrations, which is where database changes are codified inside the application. Luckily, Sequelize supports migrations out of the box. Let's write a migration to create a table for URLs.
First, we'll install the Sequelize CLI, which allows us to run migrations:
$ npm install --save sequelize-cli
Next we'll initialize Sequelize:
$ npx sequelize-cli init
This will create a config/config.json file and models, migrations, and seeders directories.
After that, we need to modify the config.json file so that it can connect to our PostgreSQL database:
{
"development": {
"username": "tomkadwill",
"password": "password",
"database": "urlshortener_development",
"host": "localhost",
"dialect": "postgres",
"operatorsAliases": false
}
}
Once the file is done, let's generate the migration using the Sequelize CLI. Here, we'll define our fields through the attributes flag. We won't be including the id field since it's automatically added:
$ npx sequelize-cli model:generate --name Url --attributes url:string,shortUrl:string
This will create a migration file that looks something like this:
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.createTable('Urls', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
url: {
type: Sequelize.STRING
},
shortUrl: {
type: Sequelize.STRING
},
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
}
});
},
down: (queryInterface, Sequelize) => {
return queryInterface.dropTable('Urls');
}
};
Migrations contain an up and down function. up is used to move the database forward and down is used to roll back.
In this case up creates a Urls table that has 5 fields. It has the url and shortUrl fields, as well as id, createdAt, and updatedAt, which are added by default.
The down migration will simply drop the Urls table.
Finally, let's run the migration:
$ npx sequelize-cli db:migrate
Once that's run we can query the database directly to check that everything worked:
$ psql -p 5432 "urlshortener_development"
psql (11.6)
Type "help" for help.
urlshortener_development=# \dt
List of relations
Schema | Name | Type | Owner
--------+---------------+-------+------------
public | SequelizeMeta | table | tomkadwill
public | Urls | table | tomkadwill
(2 rows)
urlshortener_development=# \d "Urls"
Table "public.Urls"
Column | Type | Collation | Nullable | Default
-----------+--------------------------+-----------+----------+------------------------------------
id | integer | | not null | nextval('"Urls_id_seq"'::regclass)
url | character varying(255) | | |
shortUrl | character varying(255) | | |
createdAt | timestamp with time zone | | not null |
updatedAt | timestamp with time zone | | not null |
Indexes:
"Urls_pkey" PRIMARY KEY, btree (id)
As you can see there are two database tables: SequelizeMeta and Urls. And if we inspect Urls, the expected fields are there.
Saving URLs
Now that we have PostgreSQL configured and we have created a database table with the correct structure, the next step is to update our application so that it persists URLs to the database. Recall that npx sequelize-cli model:generate created a model file, we'll use that to save URLs to the database.
First we need to import the models into app.js, by requiring models/index.js:
const db = require('./models/index.js');
The models/index.js file was generated by Sequelize and its purpose is to include all of the model files.
Next, we need to update the post route so that it creates a database record. We can use the findOrCreate() function so that each URL only has one unique database entry:
app.post('/url', function(req, res) {
const url = req.body.url
urlShortener.short(url, function(err, shortUrl) {
db.Url.findOrCreate({where: {url: url, shortUrl: shortUrl}})
.then(([urlObj, created]) => {
res.send(shortUrl)
});
});
});
When db.Url.findOrCreate() is called, it will try to find a record that matches the user-provided url and generated shortUrl. If one is found then Sequelize does nothing, otherwise, it creates a new record.
Displaying URLs in the UI
For user friendliness, let's update the app to display the last 5 persisted URLs.
To that end, we'll add a template engine so that Express can render the URLs dynamically. There are many template engines available but in this case we'll use Express Handlebars:
$ npm install --save express-handlebars
Check out our hands-on, practical guide to learning Git, with best-practices, industry-accepted standards, and included cheat sheet. Stop Googling Git commands and actually learn it!
After installing the package we can add it to app.js:
const exphbs = require('express-handlebars');
app.engine('handlebars', exphbs());
app.set('view engine', 'handlebars');
Next, we need to change the application directory structure. express-handlebars assumes that views are located in a views directory. It also assumes that we have a views/layouts/main.handlebars file:
.
├── app.js
└── views
├── index.handlebars
└── layouts
└── main.handlebars
So, let's move and rename the index.html file:
$ mv index.html views/index.handlebars
And finally, let's make a layout file, views/layouts/main.handlebars:
<html>
<head>
<title>Url Shortener</title>
</head>
<body>
{{{body}}}
</body>
</html>
There's a certain order in which the template files load: express-handlebars will render views/layouts/main.handlebars which then renders views/index.handlebars inside the {{{body}}} tag.
Now that we have the correct directory structure, let's add some HTML code to index.handlebars to dynamically display the URLs:
<ul>
{{#each urlObjs}}
<li>{{this.url}} -- <b>{{this.shortUrl}}</b></li>
{{/each}}
</ul>
Here we use Handlebars' each helper to iterate over each URL object and display the original URL and short URL.
The last thing we need to do is update the GET route in app.js to pass the URLs into the view:
app.get('/', function(req, res) {
db.Url.findAll({order: [['createdAt', 'DESC']], limit: 5})
.then(urlObjs => {
res.render('index', {
urlObjs: urlObjs
});
});
});
Let's walk through what's happening here. When / is requested, Sequelize queries the Urls table. The query is ordered by createdAt and limited to 5, which ensures that only the 5 most recent results are returned.
The result of the query is passed into res.render as the local variable urlObjs, which will be used by the template.
The rendered page looks like this:
If you have any issues, you can download the completed code from GitHub.
Deploying the App to Heroku
Now that we have the application running locally, this section will cover how to get it running on Heroku and how to connect the database to it once it is running.
First, let's deploy all of our changes to Heroku:
$ git push heroku master
See Deploying a Node.js App to Heroku for a detailed guide on deploying to Heroku.
Connecting the Database
Adding a database isn't hard, and all it requires is a single command line:
$ heroku addons:create heroku-postgresql:hobby-dev
This command creates the PostgreSQL add-on for Heroku and sets an environment variable called DATABASE_URL - we just need to tell Sequelize to use it by updating the config file:
{
"development": {
"username": "tomkadwill",
"password": "password",
"database": "urlshortener_development",
"host": "localhost",
"dialect": "postgres",
"operatorsAliases": false
},
"production": {
"username": "tomkadwill",
"password": "password",
"database": "urlshortener_production",
"host": "localhost",
"dialect": "postgres",
"operatorsAliases": false,
"use_env_variable": "DATABASE_URL"
}
}
Here we've added a new config section for production, it's the same as development except for the database name and use_env_variable field.
Sequelize uses use_env_variable to fetch the environment variable name to use for connecting to the database.
Heroku has NODE_ENV set to "production" by default, which means it will look for the production configurations. Let's commit and push to Heroku.
Next, we need to run the migrations on Heroku using sequelize db:migrate:
$ heroku run bash
Running bash on ⬢ nameful-wolf-12818... up, run.5074 (Free)
~ $ sequelize db:migrate
If we haven't created the migration before, we'd be running scripts manually now.
At this point, the application should be working in the browser.
Managing the Heroku Database
You will probably need to query or modify your production database at some point. For example, you may need to query some data to help diagnose a bug or you may need to modify some user data. Let's look at how to do that.
Running Sequelize Migration Tasks
The first thing you need to know is how to run Sequelize migrations on Heroku. This is how to view a list of available commands:
$ heroku run bash
Running bash on ⬢ nameful-wolf-12818... up, run.1435 (Free)
~ $ sequelize
These are some of the most useful:
sequelize db:migrate:undo: Rollback a single migrationsequelize db:migrate:undo:all: Rollback all migrations. Effectively revert back to a clean databasesequelize db:migrate:status: Check which migration your application is on
Using Sequelize Models in the Console
In addition to the CLI tasks, we can use Sequelize models directly in the Node console:
$ heroku run bash
~ $ node
Welcome to Node.js v12.14.0.
Type ".help" for more information.
>
Here are some code examples that can be run in the Node console. You may notice that they bear resemblance to the Node REPL.
Querying for an individual URL by ID:
db.Url.findByPk(1).then(url => {
console.log(
url.get({plain: true})
);
});
Querying for all urls:
db.Url.findAll().then(urls => {
urls.map(url => {
console.log(
url.get({plain: true})
);
});
});
Inserting a URL record:
db.Url.create({url: 'https://stackabuse.com/deploying-a-node-js-app-to-heroku', shortUrl: 'https://is.gd/56bEH3'});
Querying for a URL by ID and updating it:
db.Url.findByPk(1).then(url => {
url.shortUrl = 'example.com';
url.save();
});
Querying for a URL by ID and deleting it:
db.Url.findByPk(1).then(url => {
url.destroy();
});
Conclusion
There are numerous add-ons that can be used to extend Heroku. One of those add-ons is Heroku Postgres, which allows you to easily set up a database to store application data.
We've extended a simple Node and Express application so that it stores urls in a Postgres database, on Heroku.
