Node.js and Postgres

2015-08-10

This tutorial shows how to create a simple web server with a tiny REST “API” and a PostgreSQL database. I asume that you have installed the node.js platform and npm.

I’m going to use the node-postgres client to connect to the PostgreSQL database. The installation is easily done with npm.

npm install pg

The table I want to query is very simple.

So I’m going to create a server listen on port 8081 that write a response if a GET request is received. The query to the backend database is in a separate function that connects to the PostgreSQL database and prints the result to the response stream as JSON.

// requires
var pg = require("pg")
var http = require("http")

var port = 8081;
var host = '127.0.0.1';
var conString = "pg://postgres:postgres@pikodat.com:5432/postgres";


http.createServer(function (req, res) {
    if (req.method == 'GET') {
        returnUserList(req, res);
    }
}).listen(port, host);


/**
 * Print all users from db as JSON String
 * @param req Request
 * @param res Response
 */
var returnUserList = function (req, res) {
    // create connection to the db
    var client = new pg.Client(conString);
    client.connect();

    // get last and firstname from table "user"
    var query = client.query("SELECT lastname, firstname FROM user");
    query.on("row", function (row, result) {
        result.addRow(row);
    });
    // write to response as json string
    query.on("end", function (result) {
        res.writeHead(200, {
            'Content-Type': 'text/plain'
        });
        res.write(JSON.stringify(result.rows) + "\n");
        res.end();
    });
}

That’s what I called a microservice ;). Just remember to put in some error handling!