Using Heroku's free Postgres DB with Node Express

Posted on Friday, May 1, 2015




I am going to create a basic Node Express web app that utilizes a Postgres database.  Then I am going to push this up to Heroku and make use of their free Postgres DB.  This document will be a little long winded, as I am going to go through the entire TDD and using my own git server as well.






Heroku DB offerings


Heroku provides access to a free Postgres DB you can use.  The free version is limited to 10K rows.  Upgrade to "Basic" for $9/mo and you  get 10M rows.   Beyond that you can pay for your own database server at Heroku and those come in different flavors and prices.     (The free and basic also have a 4 hour downtime / month).



Create a Basic Node Express Web App


I am going to create a very basic Express app using TDD (Test Driven Development), have it talk to a local Postgres DB, save it to a git repo, and push it to a git server I have.   Then I am going to figure out how to push it up to Heroku and make use of their free Postgres database

Create a new folder and run npm to initialize it.


  > mkdir myapp
  > cd myapp
  > npm init






After all that I should have a package.json.   Here is mine.


{
  "name": "myapp",
  "version": "1.0.0",
  "description": "",
  "main": "app.js",
  "scripts": {
    "test": "mocha --recursive test"
  },
  "author": "Patrick Bailey",
  "license": "ISC"
}




Edit the package.json file


  > vi package.json


Add the following


{
  "name": "myapp",
  "version": "1.0.0",
  "description": "",
  "main": "app.js",
  "private":"true",
  "scripts": {
    "test": "mocha --recursive test"
  },
  "author": "Patrick Bailey",
  "license": "ISC"
}


I am going to make this a private module (So it can't accidentally get published to npm). 


Install mocha/chai/chai2-json-schema/supagent


I am going to use mocha, chai, chai2-json-schema, and superagent for TDD (Test Driven Development).  I am going to install them for the development only.


Install chai with --save-dev


  > npm install --save-dev mocha
  > npm install --save-dev chai
  > npm install --save-dev chai2-json-schema
  > npm install --save-dev superagent






Install Express


Install Express


  > npm install -S express





Create bare bones express app


Create app.js


  > vi app.js


Place the following in it.


var express = require('express');

var app = express();
var server;

var start = exports.start = function start(port, callback) {
   
server = app.listen(port, callback);
};

var stop = exports.stop = function stop(callback) {
   
server.close(callback);
};

app.get('/', function sendResponse(req,res) {
    res.
status(200).send('Hello World!');
});



This is very basic.  It returns Hello World.  It also has a function for Starting and stopping the server (this will be used by the tests).



Create server.js for start up


If you don't define the "start" script in package.json it will use the default.  The default is


   "start": "node server.js"




Knowing this I am going to create a server.js file as my start script.

Create server.js


  > vi server.js


And place the following in it.


var app = require('./app');

app.start(process.env.PORT || 3000);





Test it



  > npm start







It works!






TDD Test the test


Create the test folder


  > mkdir test


Create test file


  > vi test/app.test.js


And place the following into it.


var chai = require('chai')
var assert = chai.assert;


describe('My App', function() {

   
describe('Testing equality', function() {
       
it('1 should equal 1', function () {
           
assert.equal(1, 1);
        });
    });
});




Run the test via npm


  > npm test






Write the test for the next desired feature


What is the next feature I want?

Feature:
/db should return a 200 status

Update app.test.js to the following  (remove the test to test the test J )


var chai = require('chai')
var assert = chai.assert;
var request = require('superagent');

describe('My App', function() {
   
var myApp = require('../app.js');
   
var port = 3000;
   
var baseUrl = 'http://localhost:' + port

   
before(function(done) {
       
myApp.start(port, done);
    });

    after(
function(done) {
       
myApp.stop(done);
    });


   
describe('When requested at /db', function () {
       
it('should return 200 code', function (done) {
           
request.get(baseUrl + "/db").end(function(err, res) {
               
assert.equal(res.status, 200);
                done();
            });
        });
    });

});


The before will start the server, the after will shut the server off.
And for the test I am just making sure /hello returns a 200 code.



Run the test


  > npm test




It fails



Implement the Feature


Edit app.js

Add the following function to it.


app.get('/db', function sendResponse(req,res) {
    res.
status(200).send("Database Data Placeholder");
});


Now run the test





Write the test for the next desired feature


What is the next feature I want? 


Feature:
Confirm app can connect to the database.
I need to think this on through some more… I don't think it really is a feature… you really should mock out your connections to the database so that you don't need a running database to run your TDD tests.   But I am not going to set up mocks for this simple example.  For this simple example I want to make sure I can connect to the database.






Set up a Postgres DB


Before I start down this path I need a database to connect to.  I am using Ubuntu 14.04.  

I want to make sure I will install 9.3 if I use apt-get.  To check that run the following command


  > apt-cache policy postgresql




Yep 9.3

OK, now install it.


  > sudo apt-get update
  > sudo apt-get install postgresql




Create the myapp database


I want to create
·         A database called "myapp"
·         A user called app_ro that has read only on the myapp DB
·         A user called app_rw that has read and write access to the myapp DB
·         A table called app_message that contains a few text messages in it.


Switch to the Postgres user (the only  user who can login at this point)


  > sudo su postgres


Create a script called script.sql


  > cd
  > vi script.sql




And place the following in it.


-- Add Users
CREATE USER app_ro WITH PASSWORD 'myPassword';
CREATE USER app_rw WITH PASSWORD 'myPassword';

-- Create DB
CREATE DATABASE myapp;

-- login to the new DB
\c myapp

-- Revoke all Privileges
REVOKE ALL ON DATABASE myapp FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;

-- Set up privileges for app_ro
GRANT CONNECT ON DATABASE myapp to app_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_ro;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO app_ro;
GRANT USAGE ON SCHEMA public to app_ro;


-- Set up privileges for app_rw
GRANT CONNECT ON DATABASE myapp to app_rw;
GRANT SELECT, UPDATE, INSERT, DELETE ON ALL TABLES IN SCHEMA public TO app_rw;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO app_rw;
GRANT USAGE ON SCHEMA public to app_rw;


GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO app_ro;
GRANT USAGE ON SCHEMA public to app_ro;

-- Set up privileges for app_ro (for new tables)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
   GRANT SELECT ON TABLES TO app_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
   GRANT SELECT ON SEQUENCES TO app_ro;

-- Set up privileges for app_rw (for new tables)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
   GRANT SELECT, UPDATE, INSERT, DELETE ON TABLES TO app_rw;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
   GRANT SELECT, UPDATE ON SEQUENCES TO app_rw;

--Create tables
CREATE TABLE message (id serial, msg varchar(1024));

--Insert some test data
INSERT INTO message (msg) VALUES ('First Test Message');
INSERT INTO message (msg) VALUES ('Second Test Message');


Run the following command to run the script.


  > psql -U postgres -d postgres -a -f script.sql




Run the following command to confirm the database has been created, the table is populated, and the app_ro can read from the database.



  > psql -h localhost -d myapp -U app_ro -c "select * from message;"


Enter your password "myPassword"




This Postgres setup is very basic, it requires passwords, only allows access from localhost.  If you want to do more check out my post on setting up a basic DB at http://www.whiteboardcoder.com/2015/04/install-and-setup-postgres-93-db-on.html





Install the config module


Before I can set up this test I need to figure out configuration files in node.  I need a config file to store my database login settings.  I want to be able to have settings for production, Staging, Testing.

npm has the config module https://www.npmjs.com/package/config [1]

Install the config module.


  > npm install -S config


Node config reads configurations files in the config directory

 
Create a config directory


  > mkdir config


Looking at the notes for the config module it looks like it works like this.

First read any configuration from the default.EXT.  EXT can be .yml, .yaml, .coffee, .cson, .properties, .json, .json5, .jhons, or .js.

I will create a default.json file


  > vi config/default.json


And place the following in it.


{
    "db": {
        "host": "localhost",
        "port": 5432,
        "name": "myapp",
        "user": "app_ro",
        "pass": "myPassword"
    }
}






Poking around…


How does config work.  

I am going to add a new method to app.js to test it out.  (don't forget to require the config library).



var config = require('config');

app.get('/config', function sendResponse(req,res) {
    res.
json({"db.host": config.get('db.host'),
       
"db.port": config.get('db.port'),
       
"db.name": config.get('db.name'),
       
"db.user": config.get('db.user'),
       
"db.pass": config.get('db.pass'),
    });
});



Run the app


  > npm start


And open





That worked just fine.



How do I override this file with environment specific configurations?  Looks like I can set the NODE_ENV variable.

Set the NODE_ENV


  > export NODE_ENV=production


Test it


  > echo $NODE_ENV




At this point I have not made a config/production.js config file yet.  If I run it now nothing should change.


  > npm start






got a warning, but it did start.




and I got the same results.


Create the config/production.js file


  > vi config/production.json


And place the following in it.


{
    "db": {
        "host": "db.example.com",
        "port": 9543,
        "pass": "other_pass"
    }
}



Start it again


  > npm start





That worked.  Now it is getting data from the config/production.js file first and if it is not defined there it gets the data from config/default.json.

Perfect.



Remove the NODE_ENV variable


  > unset NODE_ENV


Start it again


  > npm start



And open the page again










TDD test the Database


I have a Database and a config file set up.  Now I can set up a test that confirms I can connect to the database.

Install the npm module for connecting to Postgres


  > npm install -S pg







Write the test for the next desired feature


What is the next feature I want?

Feature:
Confirm that you can connect to the database listed in the config file.

Create a new test file at test/db.test.js


  > vi test/db.test.js





Put the following in it


var chai = require('chai')
var assert = chai.assert;
var request = require('superagent');
var pg = require('pg');
var config = require('config');

describe('DB Test', function() {
 
var conString = "postgres://" + config.get('db.user') + ":"
     
+ config.get('db.pass') + "@" + config.get('db.host')
      +
"/" + config.get('db.name');

 
describe('Test the connection to the DB', function () {
   
it('should connect to the DB', function (done) {
     
pg.connect(conString, function(err, client, db_done) {
       
if(err) {
         
assert.equal("can-connect", "did-not-connect");
        }
       
else {
         
assert.equal("can-connect","can-connect");
        }
        db_done(done());

      });
    });
  });
});






Run the test via npm


  > npm test





This passes, so no need to implement the feature.   To have done it more TDD I should have written this then added the Postgres database.  But se lève.





Write the test for the next desired feature


What is the next feature I want?

Feature:
Confirm that localhost:3000/db returns the first message from the message table with id = 1


Here is my updated test/app.test.js


var chai = require('chai')
var assert = chai.assert;
var request = require('superagent');
var pg = require('pg');
var config = require('config');

describe('My App', function() {
   
var myApp = require('../app.js');
   
var port = 3000;
   
var baseUrl = 'http://localhost:' + port;
   
var conString = "postgres://" + config.get('db.user') + ":"
     
+ config.get('db.pass') + "@" + config.get('db.host')
      +
"/" + config.get('db.name');


   
before(function(done) {
       
myApp.start(port, done);
    });

   
after(function(done) {
       
myApp.stop(done);
    });

   
describe('When requested at /db', function () {
       
it('should return 200 code', function (done) {
           
request.get(baseUrl + "/db").end(function(err, res) {
               
assert.equal(res.status, 200);
                done();
            });
        });
    });


   
describe('When requested at /db', function () {
       
it('should return message from db where id = 1', function (done) {
           
request.get(baseUrl + "/db").end(function(err, res) {
                getMessage(
1, function(msg) {
                   
assert.equal(res.text, msg);
                    done();
                });
            });
        });
    });

   
function getMessage(id, next){
       
pg.connect(conString, function(err, client, done){
           
if(err) {
                next(undefined);
            }
           
else {
                client.
query("select * from message where id = '" + id + "'", function(err, result) {
                   
if(err) {
                        next(undefined);
                    }
                   
else{
                        next(result.rows[
0].msg);
                    }
                });
            }
            done();
        });
    };
});




Run the test via npm


  > npm test




It fails






Implement the Feature


Edit app.js

Add the following function to it.


var express = require('express');
var config = require('config');
var pg = require('pg');

var app = express();
var server;
var conString = "postgres://" + config.get('db.user') + ":"
 
+ config.get('db.pass') + "@" + config.get('db.host')
  +
"/" + config.get('db.name');


var start = exports.start = function start(port, callback) {
   
server = app.listen(port, callback);
};

var stop = exports.stop = function stop(callback) {
   
server.close(callback);
};

app.get('/', function sendResponse(req,res) {
    res.
status(200).send('Hello World!');
});

app.get('/db', function sendResponse(req,res) {
    getMessage(
1, function(err, msg) {
       
if(err){
            res.
status(404).send("404: Error talking to database " + err);
        }
       
else{
            res.
status(200).send(msg);
        }
    });
});

app.get('/config', function sendResponse(req,res) {
    res.
json({"db.host": config.get('db.host'),
       
"db.port": config.get('db.port'),
       
"db.name": config.get('db.name'),
       
"db.user": config.get('db.user'),
       
"db.pass": config.get('db.pass'),
    });
});

function getMessage(id, next) {
   
pg.connect(conString, function(err, client, done){
       
if(err) {
            next(err, undefined);
        }
       
else{
            client.
query("select * from message where id='" + id + "'", function(err, result) {
               
if(err) {
                    next(err, undefined);
                }
               
else {
                    next(undefined, result.rows[
0].msg);
                }
            });
        }
    });
};




Run the test via npm


  > npm test





It passes!






Now onto Heroku


I want to start fresh and put this app up to Heroku and use it's free Postgres database (limit 10K rows).

If you have not already installed the Heroku tool belt, install it using this command.


  > wget -qO- https://toolbelt.heroku.com/install-ubuntu.sh | sh


Check the version


  > heroku --version







I need to "login" to my heroku account via the command line


  > heroku login








List any apps you may have running


  > heroku apps




I have one running I need to stop (I am on a free Heroku account and can only have one running at a time).

I could "destroy" the app (completely remove it) with this command


  > heroku apps:destroy --app lit-headland-5683


I am not sure is there is just a way to "stop" the app?  Maybe just set the dynos = 0.




OK it's gone.  So now I can add this one.






Git


Run git init


  > git init




Edit the .gitignore


  > vi .gitignore


Here is my .gitignore file (Based on these .gitignore files I found https://github.com/github/gitignore/blob/master/Node.gitignore [2]  https://github.com/github/gitignore/tree/master/Global [3]




#Intellij
*.iml
.idea/

# Linux
.directory
*~

# OS X
.DS_Store*
Icon?
._*

# Windows
Thumbs.db
ehthumbs.db
Desktop.ini

# npm
node_modules
logs
coverage
*.log
*.gz
.grunt




I think it's always a good idea to run git status before adding anything to your git repo to see what is going to be added (sometimes I screw up our .gitignore file or some odd file sneaks in there)


  > git status




Looks good, it's not adding the node_modules folder, but it is the test folder.




Do an initial commit.


  > git add --all
  > git status
  > git commit  -m "Initial commit"







Create an app


Run the following to create an app


  > heroku create





Looks like the app has been created

At  https://glacial-anchorage-5446.herokuapp.com and it already created an empty repo for me at Heroku.


When you run heroku create it adds in the heroku remotes to the git repo for you.  Run the following command to list the remotes.


  > git remote -v







Push it up


Just push it up!


  > git push heroku master


Oops… I screwed up one thing https://devcenter.heroku.com/articles/deploying-nodejs [4]

Looks like I need to add the "engines" field to package.json, to tell Heroku which version of node to use.


  > vi package.json






{
  "name": "myapp",
  "version": "1.0.0",
  "description": "",
  "main": "app.js",
  "private": "true",
  "scripts": {
    "test": "mocha --recursive test"
  },
  "engines":{
    "node":"0.12.x"
  },
  "author": "Patrick Bailey",
  "license": "ISC",
  "devDependencies": {
    "chai": "^2.2.0",
    "chai2-json-schema": "^1.2.0",
    "mocha": "^2.2.4",
    "superagent": "^1.2.0"
  },
  "dependencies": {
    "config": "^1.12.0",
    "express": "^4.12.3",
    "pg": "^4.3.0"
  }
}


Here is my updated  package.json


{
  "name": "MyApp",
  "version": "1.0.0",
  "description": "",
  "main": "app.js",
  "private": "true",
  "scripts": {
    "test": "mocha --recursive test"
  },
  "engines" : {
    "node" : "0.12.x"
  },
  "author": "Patrick Bailey",
  "license": "ISC",
  "dependencies": {
    "express": "^4.12.3"
  },
  "devDependencies": {
    "chai": "^2.2.0",
    "chai2-json-schema": "^1.2.0",
    "mocha": "^2.2.4",
    "superagent": "^1.1.0"
  }
}




Add the changes and push it up


  > git add --all
  > git commit  -m "added engine to package.json"
  > git push heroku master








In code's current state I think it should fail, well at least fail talking to the database.  Mostly because there is no database.


I get








  > heroku addons




I need to add in the

Run this command to provision a Postgres hobby database.


  > heroku addons:add heroku-postgresql:hobby-dev







Getting Data into the Database



Where it says

Heroku Postgres databases are designed to be used with a Heroku app. However, they are accessible from anywhere and may be used from any application using standard Postgres clients.

Looks like the DB can be accessed from anywhere (something to consider when using Heroku).




Head over to https://postgres.heroku.com/databases and login you should be able to see your database.





Click on the DB




Now you can see your connection info.



Click here to show the username and password.








Connect to the DB via psql


In this case my
username = "pnbxuoysnzexft"
 password = "y9F1ZS_gLrGtrEaaEfTtq8cT6Q"
 url  = "ec2-107-22-161-155.compute-1.amazonaws.com"


(I am going to wipe this DB after writing this for those who are worried)

To make life easier I am going to make a password file see http://www.postgresql.org/docs/9.1/static/libpq-pgpass.html [6].   The psql command does not allow -p "myPassword" within the command, and that password is going to be pretty hard to type every time.


  > vi ~/.pgpass


And place the following in it.


ec2-107-22-161-155.compute-1.amazonaws.com:5432:dbgbuurpgrvmnc:pnbxuoysnzexft:y9F1ZS_gLrGtrEaaEfTtq8cT6Q


You then must change access to this file to 600


  > chmod 600 ~/.pgpass




Login


  > psql -U pnbxuoysnzexft -h ec2-107-22-161-155.compute-1.amazonaws.com dbgbuurpgrvmnc






Alternatively you could use this heroku command to make it simpler


  > heroku pg:psql --app glacial-anchorage-5446 HEROKU_POSTGRESQL_BLACK




The DB is empty…

Before I fill it up I found this piece of info https://devcenter.heroku.com/articles/heroku-postgresql#connection-permissions [7]

This shows that you cannot add any users to your Heroku database. You only get the default one.

… ok so my app_ro user is out…

Now I could just login and run the following commands to create the message table and populate it.




  > CREATE TABLE message (id serial, msg varchar(1024));
  > INSERT INTO message (msg) VALUES ('First Test Message');
  > INSERT INTO message (msg) VALUES ('Second Test Message');





But… I would rather dump my current database and move it up to the Heroku Postgres DB.






Move Database


To move the database run the following commands.  (There are better ways to do this with larger databases see https://devcenter.heroku.com/articles/heroku-postgres-import-export [8])

First dump the current database to an outfile


  > pg_dump -h localhost -U app_rw myapp  --no-acl --no-owner  -f dump_myapp.sql


Now create and populate the Heroku database with the following command.  (still making use of my ~/.pgpass file)


  > psql -U pnbxuoysnzexft -h ec2-107-22-161-155.compute-1.amazonaws.com dbgbuurpgrvmnc -f dump_myapp.sql





Don't forget to remove the dump file.



  > rm dump_myapp.sql



Login and check it


  > psql -U pnbxuoysnzexft -h ec2-107-22-161-155.compute-1.amazonaws.com dbgbuurpgrvmnc


And run the following command.


  > select * from message;




Wahoo worked!






Tweaking the app



I get…




Because my app is trying to log into the wrong database with the wrong credentials




Create a new config file config/production.json


  > vi config/production.json


And place the following in it.


{
    "db": {
        "host": "ec2-107-22-161-155.compute-1.amazonaws.com",
        "name": "dbgbuurpgrvmnc",
        "user": "pnbxuoysnzexft",
        "pass": "y9F1ZS_gLrGtrEaaEfTtq8cT6Q"
    }
}





On the Heroku dyno set the $NODE_ENV to production


  > heroku config:set NODE_ENV=production


Then to check it


  > heroku config


To test it log into bash


  > heroku run bash


Then run


  > echo $NODE_ENV







Exit to get out.



Now commit the changes in git and push to Heroku.


  > git add --all
  > git status
  > git commit  -m "fixed config"
  > git push heroku master


Now open the webapp, in my case




Wahoo it worked!

And with that I am done with this super long tutorial.








References

[1]        npm config module
                Accessed 4/2015
[2]        nodejs .gitignore file
                Accessed 4/2015
[3]        global .gitignore files.
                Accessed 4/2015
[4]        Deploying Node.js Apps on Heroku https://devcenter.heroku.com/articles/deploying-nodejs
                Accessed 4/2015
[5]        Connecting to Heroku Postgres Databases from Outside of Heroku
                Accessed 4/2015
[6]        The Password File
                Accessed 4/2015
[7]        Heroku Connection permissions
                Accessed 4/2015
[8]        Heroku DB import
                Accessed 4/2015





4 comments:

  1. Wow - this was a great walkthrough! Thank you so much for doing this! It really helps that you did absolutely everything from scratch and explained it all through. Thanks!

    ReplyDelete
  2. I want to thanks you for this wonderful tutorial. I tried many time to deploy a node server with postgres on a Heroku and each time it's failed ! But with your walk-through, everything went fine. I also learn new thing about unit testing so... I'm very grateful for your great work. Thank you again ! - A Happy Developer :D

    ReplyDelete
    Replies
    1. Thanks, I am glad to hear it was helpful to you. Keep working on unit testing! The industry always needs coders willing to do good unit testing :)

      Delete