node-postgres will not insert data, but doesn'

Question

I'm using the node-postgres module for node.js and I have a problem inserting data.

The function:

function addItems(listId, listItems, handle) {
    if (!listItems) {
        handle(listId);
        return;
    }
    var client = dbConnector.getClient(),
        important,
        dateArray,
        dateString,
        i,
        prepStatement;
    client.connect();
    for (i in listItems) {
        console.log(listItems[i]);
        dateArray = listItems[i].itemdate.split('-');
        dateString = dateArray[1] + '-' + dateArray[0] + '-' + dateArray[2];
        if (listItems[i].important) {
            important = 'true';
        } else {
            important = 'false';
        }
        prepStatement = {
            name: 'insert task',
            text: 'INSERT INTO listitem (todolist_id, name, deadline, description, important, done, created) VALUES ($1, $2, $3, $4, $5, $6, now()) RETURNING listitem_id',
            values: [ listId, listItems[i].itemname, dateString, listItems[i].itemdesc, important, listItems[i].done ]
        };
        var query = client.query(prepStatement);
        console.log("Adding item " + i);
        query.on('error', function(error) {
            console.log(error);
        });
        query.on('end', function(result) {
           console.log("Query ended");
           if (result) {
               console.log("Added listitem no " + result.rows[0].listitem_id);
           } 
        });
    }
    client.end();
    handle(listId);
}

No new data appears in the database. The query.on('error') and query.on('end') events are never fired. Come to think of it, I'm beginning to doubt if the query is even triggered (tho I can't see why it shouldn't).

The only log I get is:

{  itemname: 'Task 1',
   itemdate: '08-05-2012',
   important: 'on',
   itemdesc: 'A task',
   done: 'false' }
Adding item 0
{  itemname: 'Task 2',
   itemdate: '22-05-2012',
   important: 'on',
   itemdesc: 'Another one',
   done: 'false' }
Adding item 1

So how should I proceed in debugging this?

Answer 1

Your calling client.end() before your queries can execute. node-postgres is not going to throw a "not connected" error, because it is designed to queue queries until the connection is ready. https://github.com/brianc/node-postgres/wiki/Client#method-connect

Try this:

function addItems(listId, listItems, handle) {
    if (!listItems) {
        handle(listId);
        return;
    }
    var client = dbConnector.getClient(),
        important,
        dateArray,
        dateString,
        i, 
        prepStatement,
        queryCount = 0;
    client.connect();
    for (i in listItems) {
        console.log(listItems[i]);
        dateArray = listItems[i].itemdate.split('-');
        dateString = dateArray[1] + '-' + dateArray[0] + '-' + dateArray[2];
        if (listItems[i].important) {
            important = 'true';
        } else {
            important = 'false';
        }
        prepStatement = {
            name: 'insert task',
            text: 'INSERT INTO listitem (todolist_id, name, deadline, description, important, done, created) VALUES ($1, $2, $3, $4, $5, $6, now()) RETURNING listitem_id',
            values: [ listId, listItems[i].itemname, dateString, listItems[i].itemdesc, important, listItems[i].done ]
        };
        var query = client.query(prepStatement);
        queryCount++;
        console.log("Adding item " + i);
        query.on('error', function(error) {
            console.log(error);
        });
        query.on('end', function(result) {
           queryCount--;
           console.log("Query ended");
           if (result) {
               console.log("Added listitem no " + result.rows[0].listitem_id);
           } 
           if (queryCount === 0) {
             client.end();
             handle(listId);
           }
        });
    }
}

All the above does is keep track of the number of queries you've issued and when they have all ended, then calls client.end() and handle(listId);

This can be tedious and error prone, thus a few libraries exist to make asyc flow easier. My favorite is async, it works in the browser and in node. https://github.com/caolan/async

Using async, I would rewrite the code as:

function addItems(listId, listItems, handle) {
    if (!listItems) {
        handle(listId);
        return;
    }
    var client = dbConnector.getClient(),
        important,
        dateArray,
        dateString,
        i, 
        prepStatement;
    client.connect();

    async.forEach(
      listItems,
      // called for each listItems
      function(listItem, callback){
        console.log(listItem);
        dateArray = listItem.itemdate.split('-');
        dateString = dateArray[1] + '-' + dateArray[0] + '-' + dateArray[2];
        if (listItem.important) {
            important = 'true';
        } else {
            important = 'false';
        }
        prepStatement = {
            name: 'insert task',
            text: 'INSERT INTO listitem (todolist_id, name, deadline, description, important, done, created) VALUES ($1, $2, $3, $4, $5, $6, now()) RETURNING listitem_id',
            values: [ listId, listItem.itemname, dateString, listItem.itemdesc, important, listItem.done ]
        };
        var query = client.query(prepStatement);
        //console.log("Adding item " + i);
        query.on('error', function(error) {
            console.log(error);
            callback(error),
        });
        query.on('end', function(result) {
           console.log("Query ended");
           if (result) {
               console.log("Added listitem no " + result.rows[0].listitem_id);
           } 
           callback(null,result);
        });
      }, 
      // called after iterator function
      function(err) {
        if (err) return; // could use this as an err handler for all queries              
        client.end();
        handle(listId);
      }
    );
}; 

see also async.forEachSeries, but I don't think it's needed in this case because the node-postgres client is going to run the queries in series anyways.