Sunday, October 23, 2016

nodejs postgres pg.client returning partial results in json

i had a very weird problem with a nodejs and postgre app i'm building:
i'm running a sql query with join in dbeaver:
select matches.match_date, t1.team_long, t1.team_short,, 
t1.logo, t2.team_long, t2.team_short,, t2.logo 
from teams t1, teams t2, matches 
and in dbeaver it is returning the expected result
match_date team_long team_short 
2016-11-22 Juventus JUVE 
id logo team_long team_short id logo
6 1juventus.png Everton EVE 7 1everton.png
which includes all expected columns.
but when run in my node app with pg client, i'm only receiving a portion of the columns in the resulting json:
in the above json, the data for Juventus is missing.
for some crazy reason, i thought.

here is the js code i'm using to read the data in node:
// SQL Query > Select Data
const query = client.query(' select matches.match_date, 
t1.team_long as host_team_long, t1.team_short 
as host_team_short, as host_id, t1.logo as host_logo, 
t2.team_long as guest_team_long, 
t2.team_short as guest_team_short, guest_id, 
t2.logo as guest_logo from teams t1, 
teams t2, matches where 

// Stream results back one row at a time
query.on('row', (row) => {
so, somewhere here hides the key to the weird behavior.
but what could it be???
is it something in the postgre db 'driver'?
or nodejs itself?

about an hour of guessing later, i turned to write a question about this problem on

copy/paste the sql query, copy paste the results...should i include the column names as well?
let's do it for readibility...

wait a second!

the column names for the same fields of different records are the same!
but, but, but - how will this project in the json results?

apparently you cannot have two properties with the same name lies the reason for my 'trimmed' results!

whatever json lib the pg client is using it doesn't care (perhaps rightly so, perhaps not) that we may be overwriting values in result of a sql query


quickly changing the sql query with custom result column names (using 'as') solved the problem! here are the correct and beautiful results:
{ match_date: '2016-10-22',
  host_team_long: 'Atl├ętico de Madrid',
  host_team_short: 'ATL',
  host_id: 3,
  host_logo: 'Atletico_Madrid.png',
  guest_team_long: 'Internazionale',
  guest_team_short: 'INT',
  guest_id: 8,
  guest_logo: 'Inter_Milan.png' }

on to the next problem!

Saturday, February 06, 2016

Let me tell you about Hapi - building a REST API engine for Nodejs

(i will be showing something a built with hapi - check it out here:

 i would admit again and again that i missed the javascript revolution, but i am trying to catch up!
first i had an idea for a small site where i could just drop some video links and be able to play videos off the site - is the result of this experiment.

the only thing that made it possible for me to create this site was the magnificent MeteorJS full stack framework.
at the time i started playing with Meteor i had barely an idea about fullstack, nodejs and what you can do with javascript (both server and client side).

well, Meteor opened my eyes to a wonderful new world indeed - i learned quite a lot, and my next web app will be created with it for sure.

but now i had an idea for a mobile app.
Meteor actually also works with mobile as well - thanks to integration with Cordova and this seems to work great, but i wanted something more flexible on the frontend/clientside and found out about Ionic - which seems like a great choice for hybrid cross platform apps - i was instantly sold.
yet for the backend i wanted to try something new - i wanted to play and get to know the mighty Nodejs.

i had heard about Express, which seems like the best engine for node, but i wanted something a little higher level then that i quickly found out about Sails.
Sailjs seemed a great project, very promissing and with a lot of features. but one thing started bothering me while i was reading the web about it - and that was that the project seemed on the decline in terms of support and progress.

so i kept looking around and then there was Hapi.

i played a bit for some example projects and then started creating my own stuff.
and this turned out extremely easy to do!
first you create your server:
const server = new Hapi.Server();
  port: process.env.PORT
and then, since we're building an API - start adding endpoints. as simple as:
    method: 'POST',
    path: '/dosomething',
we just said the server that we're exposing some functionality to react to HTTP POST requests at "/dosomething". let's continue with the "config" section
    config: {
      tags: ['api'],
      description: 'create user account with email and password',
i'm using swagger to document my api and you should too! read more about swagger and what else it offers for testing the api and much more
      auth: false,
"auth" defines how and if we want to limit access to this endpoint - to this resource. more on this later
      validate: {
        payload: {
          email: Joi.string().email().lowercase().required(),
          username: Joi.string().alphanum().min(3).max(30).optional(),
          password: Joi.string().required()
behold one of the beauties of hapi: it allows you in a declarative way to define validation of the expected input it receives from connected clients. this is done with the help of the excellent Joi validation package. so here for our POST request we say that the body should be a json conforming to a certain schema, with fields which Joi will later to ensure are valid email, username and password. and we can even declare which fields we require and which are optional!
      handler: function(request, reply) {
        // Create mongodb user object to save it into database
        var user = new UserModel(request.payload);

        // Call save methods to save data into database
        // and pass callback methods to handle error {
          if (error) {
            return reply(Boom.badRequest(' failed: ' + error.message));
          } else {
            return reply().code(201);
the handler is where we process the request and return response to the client. i have a few models created by the superb mongoose. my data is not that complex, so i just created a few mongodb collections. at some time i might go back to some relational magic with postgre. note also the line mentioning Boom. Boom is a quite handy package, perhaps especially for REST APIs where you communicate through HTTP verbs and notations and responding with meaningful HTTP codes is quite important. well, Boom helps you with that.

Let's get back to that "auth" thing.

there are different ways to handle security and nodejs and related technologies being based around web programming are well all into that. hapi also offers - or should i say, there are many authentication packages build for hapi, which help you select your approach and build a secure site. i my case i decided to use json web tokens with the hapi-auth-jwt2 package.

this is how it works:

server.register([require('hapi-auth-jwt2')], function(err) {
  server.auth.strategy('jwt', 'jwt', {
    key: process.env.JWT_SECRET,
    validateFunc: validate,
    verifyOptions: {
      ignoreExpiration: false

first we need to register the jwt2 package with the server and tell it that by default any endpoint is secured with jwt tokens. then for every route we need to secure we just add this in the config section:
    config: {
      auth: 'jwt',
and thats really it - from now on, if any access to the endpoint doesn't include a jwt token, access will be denied! if we want an endpoint or other resource to not be restricted, just put
auth: false 
as in an earlier example. of course you may provide a function that is ensuring the token complies with some additional logic, that's all up to you, for a serious app you'd likely have to do this, the function signature is
var validate = function(decoded, request, callback)

so, add more routes, read more, follow best practices and don't forget to start the server!
  server.start((err) => {

    if (err) {
      throw err;

    console.log('Server running at:',;

so this is basically it from me.
i'm quite new to hapijs, and not really familiar with many things/approaches etc, but one learns and has fun, so let the party continue

the github repo with the hapi api is at

hope that this may help someone!

Tuesday, January 05, 2016

emulator: ERROR: unknown skin name 'nexus_5'

while playing with ionic everything worked fine with the ios emulator, but i got this error when starting the android emulator:
emulator: ERROR: unknown skin name 'nexus_5'
all articles on stackoverflow advised to remove the default created avd image and create a new one. this is fine, but how do i find and run the avd manager on mac?
well there is a quick tip for this too:
go to where android is installed, for me it was
i cound't find anything named 'avd' there though, but it turned out the correct way to invoke this is
./android avd
now, the avd manager should run and you can create a new virtual device.
but why did we receive the error in the first place?
i my setup there was a default created device for nexus_5, so i decided to run it the same error. so the virtual device itself was bad? turned out an easy fix - avd itself tells you if there is a problem with the device and it was easy to just set the skin to a valid one - probably on osx the android sdk somehow doesn't provide the nexus_5 skin?

after this running
ionic emulate android
ran flawlessly and the sample app appeared.

i'm very excited about ionic, let's see if something will come out of this too. one point of bother for me is that i only yesterday found that that the current ionic is ionic framework 1, and there is a v2 in the works, to be released around Q3. so should i even start with v1, will it not be obsolete to invest in it, when v2 finally arrives?

just realised this is my first post for 2016 :) Happy New Year everyone!