Run a Node PostgreSQL App on Heroku

I really like the pg PostgreSQL library by Brian Carlson, and considering the amount of attention we’ve given to Redis and MongoDB on DailyJS I thought it was time to give relational databases some coverage again.


Heroku is one of many services that supports Node. This tutorial will demonstrate how easy it is to get a simple Express and pg app running.


This tutorial is based on the following documentation:



The files for this tutorial can be found here: alexyoung / dailyjs-heroku-postgres.


Getting Started


An account at Heroku is required first. Next, install the Heroku client:



Once that’s installed, try typing heroku help in a terminal to see what the command-client client can do. Heroku obviously realised that us developers prefer using the command-line to a GUI — although some basic management features are available through Heroku’s web interface, almost everything is handled from the command-line tool.


Authentication is requried before progressing:


heroku login

I had to tell Heroku about my public SSH key too:


heroku keys:add ~/.ssh/id_rsa.pub

Module Installation


Heroku wisely supports npm, so our app begins with a package.json:


{
 "name": "dailyjs-heroku-postgres"
, "version": "0.0.1"
, "dependencies": {
   "express": "2.4.5"
 , "pg": "0.5.7"
 }
}

PostgreSQL Setup


Heroku uses environmental variables to supply database connection parameters. This is simply process.env.DATABASE_URL for PostgreSQL. Connecting to the database is as simple as this:


var pg = require('pg').native
 , connectionString = process.env.DATABASE_URL || 'postgres://localhost:5432/dailyjs'
 , client
 , query;

client = new pg.Client(connectionString);
client.connect();
query = client.query('SELECT * FROM mytable');
query.on('end', function() { client.end(); });

Notice how pg uses events — I’ve called client.end() so this script will exit gracefully when it’s finished. If you’ve got PostgreSQL installed locally you could try experimenting with this script.


Schema


There’s a few ways to change the database schema on Heroku. I’ve made a little schema creation script:


var pg = require('pg').native
 , connectionString = process.env.DATABASE_URL || 'postgres://localhost:5432/dailyjs'
 , client
 , query;

client = new pg.Client(connectionString);
client.connect();
query = client.query('CREATE TABLE visits (date date)');
query.on('end', function() { client.end(); });

I’ll explain how to run this on Heroku later.


Another option would be to use a library like node-migrate by TJ Holowaychuk. I haven’t actually used this before, but it seems like a sensible way to keep local schemas in sync as developers work on a project.


Typing heroku help pg shows the commands available for PostgreSQL, and this includes heroku pg:psql which can be used to open a remote connection to a dedicated database. This won’t be allowed for a shared database, but could be used to modify the schema.


Example App


Now we’ve got a package.json, we just need an app to run. Create a file called web.js that starts like this:


var express = require('express')
 , app = express.createServer(express.logger())
 , pg = require('pg').native
 , connectionString = process.env.DATABASE_URL || 'postgres://localhost:5432/dailyjs'
 , start = new Date()
 , port = process.env.PORT || 3000
 , client;

Notice how I use Heroku’s environmental variable for the database connection string and server port, or defaults for development purposes.


Now we can add the code required to connect to the database:


client = new pg.Client(connectionString);
client.connect();

A single Express route should suffice for this tutorial:


app.get('/', function(req, res) {
 var date = new Date();

 client.query('INSERT INTO visits(date) VALUES($1)', [date]);

 query = client.query('SELECT COUNT(date) AS count FROM visits WHERE date = $1', [date]);
 query.on('row', function(result) {
   console.log(result);

   if (!result) {
     return res.send('No data found');
   } else {
     res.send('Visits today: ' + result.count);
   }
 });
});

And we better start the app too:


app.listen(port, function() {
 console.log('Listening on:', port);
});

Procfile


The last thing we need is a file that tells Heroku what our main script is called. Create a file called Procfile:


web: node web.js

Deploying


Heroku uses Git for deployment, so set up a repo:


git init
git add .
git commit -m 'First commit'

Then run this command which creates a remote app on the service with a random name:


heroku create --stack cedar

It’ll give you the URL, but your app isn’t quite ready yet.


Now push the repo to make the magic happen:


git push heroku master

And tell Heroku you want to use a database:


heroku addons:add shared-database

And finally… run the schema creation script:


heroku run node schema.js

Hopefully you now have a little Node and PostgreSQL app running on Heroku!


If anything went wrong, Heroku’s documentation is excellent, and you can download my sample source here: alexyoung / dailyjs-heroku-postgres.