This is a log of every step I will take to create a 100%-JavaScript application back to front.
Hardware: Windows 7 64bit Home Edition ASUS laptop with Intel P6100 CPU @2.0GHz, 4GB RAM, 320GB HDD
Oracle VirtualBox 4.2.12
Ubuntu 12.04.2 – Long term support
Downloaded 32bit ISO image – laptop does not support 64bit virtualisation 🙁
Virtual machine: 1 CPU, 1GB RAM, 20GB HDD, with bridged network, boot CD first to use ISO image
Minimal install with OpenSSH server
Update repositories and upgrade base system
sudo apt-get update
sudo apt-get upgrade
sudo apt-get install python-software-properties
To use PostgreSQL Apt repository, follow: http://wiki.postgresql.org/wiki/Apt
Edit source list:
sudo vim /etc/apt/sources.list
and add append this line:
deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main
Save and exit.
Import repository key and update.
wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-9.2
sudo apt-get install libpq-dev
This will also install GCC and many related packages to compile code.
Install Node.js – refer to http://nodejs.org/
sudo apt-get install g++
sudo apt-get install make
sudo add-apt-repository ppa:chris-lea/node.js
sudo apt-get update
sudo apt-get install nodejs
Install git if you want to work with github or bitbucket etc.
sudo apt-get install git
I think my development environment is ready. Next step is to create an application.
Let’s prepare the database server. Create a database user and a database owned by that user.
sudo su postgres
createuser -P myuser
createdb -O myuser mydb
Make sure local connections are allowed for local users:
sudo vim /etc/postgresql/9.2/main/pg_hba.conf
psql -W --user=myuser --host=localhost mydb
And execute this code to create a table and save couple of records in our sample database.
create table tbl_test (
id int,
name text,
ts timestamptz,
info json,
primary key(id)
);
insert into tbl_test values (1, 'murat', now(), '{"a": "11", "b": "21"}');
insert into tbl_test values (2, 'yaman', now(), '{"a": "12", "b": "22"}');
Install Express for Node globally:
sudo npm install -g express
Clone my sample application if you want to.
git clone git@github.com:muratyaman/pgnode.git pgnode-app
cd pgnode-app
vim package.json
One of the dependencies is “pg” which is the PostgreSQL module for Node.js.
{
"name": "pgnode-app",
"description": "Sample app for Node.js and PostgreSQL",
"version": "0.0.1",
"private": true,
"scripts": {
"start": "node app.js"
},
"dependencies": {
"express": "3.2.6",
"jade": "*",
"pg": "*"
}
}
Install any dependencies.
npm install
Sample code: app.js
/**
* Module dependencies.
*/
var express = require('express')
, pg = require('pg').native
, routes = require('./routes')
, user = require('./routes/user')
//, test = require('./routes/test')
, http = require('http')
, path = require('path')
;
var app = express();
// all environments
app.set('port', process.env.PORT || 3000);
app.set('views', __dirname + '/views');
app.set('view engine', 'jade');
app.use(express.favicon());
app.use(express.logger('dev'));
app.use(express.bodyParser());
app.use(express.methodOverride());
app.use(app.router);
app.use(express.static(path.join(__dirname, 'public')));
// development only
if ('development' == app.get('env')) {
app.use(express.errorHandler());
}
//--
var dbUrl = "tcp://myuser:123456@localhost/mydb";
function disconnectAll() {
pg.end();
}
function testDate(onDone) {
pg.connect(dbUrl, function(err, client) {
client.query("SELECT NOW() as when", function(err, result) {
console.log("Row count: %d",result.rows.length); // 1
console.log("Current year: %d", result.rows[0].when.getFullYear());
onDone();
});
});
}
function testTable(onDone) {
pg.connect(dbUrl, function(err, client) {
client.query("CREATE TEMP TABLE reviews(id SERIAL, author VARCHAR(50), content TEXT)");
client.query("INSERT INTO reviews(author, content) VALUES($1, $2)",
["mad_reviewer", "I'd buy this any day of the week!!11"]);
client.query("INSERT INTO reviews(author, content) VALUES($1, $2)",
["calm_reviewer", "Yes, that was a pretty good product."]);
client.query("SELECT * FROM reviews", function(err, result) {
console.log("Row count: %d",result.rows.length); // 1
for (var i = 0; i < result.rows.length; i++) {
var row = result.rows[i];
console.log("id: " + row.id);
console.log("author: " + row.author);
console.log("content: " + row.content);
}
onDone();
});
});
}
//testDate(function() {
// testTable(disconnectAll)
//});
function processReqResSql(req, res, sql){
pg.connect(dbUrl, function(err, client) {
client.query(sql, function(err, result) {
var sr = new ServiceResult ('success');
sr.output = result.rows;
//for (i = 0; i < result.rows.length; i++) {
// sr.output[i] = JSON.parse(result.rows[i].data);
//}
res.send(sr);
});
});
}
function ServiceResult (status){
this.ts = new Date();
this.status = status;
this.output = new Array();
}
//--
app.get('/', routes.index);
app.get('/users', user.list);
app.get('/test', //test.list
function(req, res) {
var sql = 'select * from tbl_test';
processReqResSql(req, res, sql);
}
);
http.createServer(app).listen(app.get('port'), function(){
console.log('Express server listening on port ' + app.get('port'));
});
And run the (server) application.
node app
On your browser, request the test page:
http://yourserver:3000/test
Output should be:
{
"ts": "2013-06-22T19:54:43.413Z",
"status": "success",
"output": [
{
"id": 2,
"name": "yaman",
"ts": "2013-06-22T18:52:06.136Z",
"data": {
"a": "12",
"b": "22"
}
},
{
"id": 1,
"name": "murat",
"ts": "2013-06-22T18:51:58.254Z",
"data": {
"a": "11",
"b": "21"
}
}
]
}
You can see the output in JSON including the info field which uses JSON field type (added in PostgreSQL 9.2).
Lovely!
Next step is to create functions in PostgreSQL using PL/V8. This way I want to keep my server very lightweight and fast. I like my database server as the data gate keeper 🙂
Basically, almost every request will execute:
SELECT * FROM a_function_name_based_on_request (inputs_in_json_format);
And each function will output a row or many rows which will be served as part of the response.
https://github.com/muratyaman/pgnode
Next: Part 2