Run a Node PostgreSQL App on Heroku

26 Sep 2011 | By Alex Young | Tags databases heroku node

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.


blog comments powered by Disqus