I have already set up a basic webapp with a free "Hobby" Postgres database see http://www.whiteboardcoder.com/2015/04/your-second-heroku-node-app.html The "Hobby" Database can only contain 10K rows. You can pay Heroku $9/mo to up that to 10M rows, which is a very good way to go. They also offer some paid Postgres servers that do not have these limits.

But! What if you want to use your own Postgres Database? What if you want to use your own Amazon RDS Postgres Database? How do you do that? How do you hook up your app to use a remote Database?

I am going to try and cover that as best I can in this write up.

The code

Create the app locally.

> mkdir myapp

> cd myapp

> npm init

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 npm modules

For testing install

> npm install --save-dev mocha

> npm install --save-dev chai

> npm install --save-dev chai2-json-schema

> npm install --save-dev superagent

The rest, Express, config, and Postgres.

> npm install -S express

> npm install -S config

> npm install -S pg

Create app.js and server.js

> vi app.js

And place the following in 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);
        }
    });
});

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

Edit server.js

> vi server.js

And place the following in it.

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

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

Install Postgres and create the DB

Install Postgres, I am assuming you are on Ubuntu 14.04.

> sudo apt-get update

> sudo apt-get install postgresql

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

This script create a database called myapp with a table called message and populates that table with two messages. It also creates two users for that database. app_ro is a read only user and app_rw is a read write user. Each user has a password of "myPassword".

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"

OK local database is working!

Exit out of the Postgres user.

> exit

Set up tests

Create a few TDD tests. I already created these before in http://www.whiteboardcoder.com/2015/04/your-second-heroku-node-app.html.

Now I am just going to show you what I put in it.

First the database test db.test.js

> mkdir test

> vi test/db.test.js

And place the following in it.

Then create the app.test.js

> vi test/app.test.js

And place 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('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();
        });
    };
});

Create the config files

Create the default config file

> mkdir config

> vi config/default.json

And place the following in it

{

"db": {

"host": "localhost",

"port": 5432,

"name": "myapp",

"user": "app_ro",

"pass": "myPassword"

}

Run the test via npm

> npm test

Test the app

Run the app locally

> npm start

And open http://localhost:3000/db

Looks good! The message is from the database.

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 [1] https://github.com/github/gitignore/tree/master/Global [2]

#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 the Heroku App

I am using the free version of Heroku so I can only have one app going at a time. I am going to list my current app and delete it.

> heroku apps

My current app is called glacial-anchorage-5446.

Remove this app, 'destroy' it muhahaha

> heroku apps:destroy --app glacial-anchorage-5446

Run the following to create an app

> heroku create

It created app at https://whispering-river-2611.herokuapp.com/

If I open https://whispering-river-2611.herokuapp.com/db

I get

A placeholder app

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

open

https://whispering-river-2611.herokuapp.com/db

It's not working because I need a database.

Run the following to check your addons

> heroku addons

Run this command to provision a Postgres hobby database.

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

Getting Data into the Database

The DB is empty, I need to fill it up with data.

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

Click on your Database

Grab your database information. And to make life simpler place it in ~/.pgpass file.

For example for me I can run the following command.

> echo "ec2-107-22-161-155.compute-1.amazonaws.com:5432:d2s7i164kc4lq0:vnzccunmgkafqb:WThddGe7JIDrWfeEUwYESmoABC" >> ~/.pgpass

It's in the form "hostname:port:database_name:username:password"

You then must change access to this file to 600

> chmod 600 ~/.pgpass

> psql -U vnzccunmgkafqb -h ec2-107-22-161-155.compute-1.amazonaws.com d2s7i164kc4lq0

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 vnzccunmgkafqb -h ec2-107-22-161-155.compute-1.amazonaws.com d2s7i164kc4lq0 -f dump_myapp.sql

Don't forget to remove the dump file.

> rm dump_myapp.sql

> psql -U vnzccunmgkafqb -h ec2-107-22-161-155.compute-1.amazonaws.com d2s7i164kc4lq0

And run the following command.

> select * from message;

Fix the config

The Database at Heroku is no correct, but the config file, that contains the db connection info is not.

Create the production config file

> vi config/production.json

And place the following in it.

{

"db": {

"host": "ec2-107-22-161-155.compute-1.amazonaws.com",

"name": "d2s7i164kc4lq0",

"user": "vnzccunmgkafqb",

"pass": "WThddGe7JIDrWfeEUwYESmoABC"

}

Commit the changes and push them up

> git add --all

> git status

> git commit -m "Added config"

> git push heroku master

On the Heroku dyno set the $NODE_ENV to production (config uses this)

> heroku config:set NODE_ENV=production

Then to check it

> heroku config

Now open https://whispering-river-2611.herokuapp.com/db

It's working

Why did I do all that? Well, I think now that I have an app working in Heroku and talking to it's database I think it may only be a matter of changing the config file config/application.json to point the RDS database I am about to make.

Before RDS

I have a Postgres database running locally at home… What if I just open up a port and route it to this box? Can I get Heroku to use my database running at home? I am guessing so, let me try it out.

You need to expose your server to the internet. In my case route port 5432 from my router to this virtual machine.

After I got this set up I tried to login to Postgres remotely. (I changed the IP address here to protect the innocent, in fact I just found out 203.0.113.0 - 203.0.113.253 are example IP addresses and don't exist J much like example.com)

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

Ooops it got rejected… I need to tweak my /etc/postgresql/9.3/main/postgresql.conf file to allow access from outside the machine.

> sudo vi /etc/postgresql/9.3/main/postgresql.conf

Update listen_addresses to the following

listen_addresses = '*'

I also need to edit /etc/postgresql/9.3/main/ pg_hba.conf file to allow access from outside the machine.

> sudo vi /etc/postgresql/9.3/main/pg_hba.conf

Append the following line to the end of this document (this allows connections from anywhere)

host all all 0.0.0.0/0 md5

Restart the Postgres server.

> sudo service postgresql stop

> sudo service postgresql start

OK now I should be able to log in.

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

Finally worked from outside my system.

Heroku talking to my local DB

Now can I get my app on Heroku talking to my database at home?

Well first I should update the message where id=1 so it's easy to tell.

> psql -h 203.0.113.220 -d myapp -U app_rw -c "update message set msg='from remote database' where id=1; "

Edit config/production.json

> vi config/production.json

{

"db": {

"host": "203.0.113.220",

"name": "myapp",

"user": "app_ro",

"pass": "myPassword"

}

Push it up to Heroku

> git add --all

> git commit -m "Talking to Home Postgres server"

> git push heroku master

Moment of truth open up https://whispering-river-2611.herokuapp.com/db ….

Nice, it worked. OK if that worked once I get the RDS up and running this should be a no brainer.

Before I move on though, I am closing access to my local Postgres server, putting pg_hba and postgresql.conf back to the way they were and stop port forwarding to this machine.

Create the RDS

I am not going to go into how to set up an AWS account in this post. I am assuming you already have one set up.

Select the region you want to create the RDS in.

Click on the Pull down in the upper right and select
"US East (N. Virginia).

Why? Because all the amazon Heroku servers are run out of there.

Click on Instances

Click Launch DB Instance

Choose PostgreSQL and click Select.

Select No for Multizone and click Next Step. (You can always make it a MZ later.

Select your Options

I chose…

· DB Engine 9.3.6

· db.t2.micro (The smallest cheapest server $0.018/hr ~ $14/mo)

· Mutli-Zone No

· Storage Type Magnetic

Settings

· Named it testing

· Master user = postgres

· Master password = myPassword** (don't worry I am wiping this RDS after I write this up)

Click Next Step

The defaults on this one are good. I have no VPC security groups so I may have to create one after this?

I am leaving the Database Name blank (I don't want to manually create it later).

All the rest looks good. Click Launch DB Instance

It's being created. Click On View Your DB Instance.

It's still 'creating' give it a few minutes.

OK now it's available.

There is my endpoint

testing.chkcoozrprm6.us-east-1.rds.amazonaws.com:5432

But what about my permissions?

Hover over i and this pop up come up.

Looks like it gave inbound access to my current IP address (Which I covered up). And access to no one else.

Nice, not bad for not setting up a security group. Let me see if it works.

From my local box let me try to login to this database.

> psql -h testing.chkcoozrprm6.us-east-1.rds.amazonaws.com -U postgres

That worked I am connected!

\q to get out

> \q

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 FROM Amazon RDS');

INSERT INTO message (msg) VALUES ('Second Test Message FROM Amazon RDS');

Run the following command to run the script.

> psql -h testing.chkcoozrprm6.us-east-1.rds.amazonaws.com -U postgres -d postgres -a -f script.sql

See if I can log in as the app_ro user.

> psql -h testing.chkcoozrprm6.us-east-1.rds.amazonaws.com -U app_ro -d myapp

OK its working!

Remove the sql file

> rm script.sql

Update config

Update the config/production file.

> vi config/production.json

Update it to.

{

"db": {

"host": "testing.chkcoozrprm6.us-east-1.rds.amazonaws.com",

"name": "myapp",

"user": "app_ro",

"pass": "myPassword"

}

Test it locally by setting the Environment variable NODE_ENV to production

> export NODE_ENV=production

Now run the program

> npm start

And open up http://localhost:3000/db

Wahoo it's working J

Heroku push

Commit the changes in git and push it up to Heroku

> git status

> git add --all

> git commit -m "Updated to talk to RDS"

> git push heroku master

Open https://whispering-river-2611.herokuapp.com/db

I should get an error, as I am pretty sure Heroku cannot access my RDS.

Yep.

OK I need to update my Security group for my Database.

Security Group

Click on Security Groups

Click on VPC page. (The database was put in a VPC network)

Click on Security Groups and then on Create Security Group

Name it. Then select the VPC the RDS is a part of. (In my case its easy I have no other VPCs so I just choose the one, if you are unsure you can check the RDS instance and it will list the VPC it is in)

Click Yes, Create.