Run a Node PostgreSQL App on Heroku

2011-09-26 00:00:00 +0100 by Alex R. Young

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 /

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

  "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);
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.


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);
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

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

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);

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) {

    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);


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


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

If anything went wrong, Heroku's
is excellent, and you can
download my sample source here: alexyoung /