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 opening http://localhost:3000/config
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
|
Now open http://localhost:3000/config
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
I found this page https://devcenter.heroku.com/articles/connecting-to-heroku-postgres-databases-from-outside-of-heroku
[5]
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
Accessed 4/2015
[5] Connecting to Heroku Postgres Databases from Outside of Heroku
https://devcenter.heroku.com/articles/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
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!
ReplyDeleteI'm glad you found it helpful :)
DeleteI 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
ReplyDeleteThanks, 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