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