how to get record values without knowing column names in sqlite3 node.js

Question

Consider the following code of sqlite3 nodejs

db.each("SELECT rowid AS id, item FROM SampleTable", function(err, row) {
  return console.log(row);
});

This will print the following in console

{ id: 1, item: 'Item #0' }
{ id: 2, item: 'Item #1' }
{ id: 3, item: 'Item #2' }
{ id: 4, item: 'Item #3' }
{ id: 5, item: 'Item #4' }
{ id: 6, item: 'Item #5' }
{ id: 7, item: 'Item #6' }
{ id: 8, item: 'Item #7' }
{ id: 9, item: 'Item #8' }
{ id: 10, item: 'Item #9' }

I want to get the record values(1 2 3 ... & Item #0, Item #1...) without using column name ie without using row.id and row.item. My actual project is very dynamic in nature and deals with different tables in mutliple DBs. Hence it is not possible to know the column names.

I found the following in node-sqlite3 wiki https://github.com/mapbox/node-sqlite3/wiki/API

It is impossible to access them by column index; the only supported way is by column name.

I would like to know whether there is any work around for getting record values without using column names. The closest I could get to is to the following. loop and get key/value pair for JSON array using jQuery

Answer 1

You can use for/in to get column names (or row object properties).

Using a function to access property by index:

//col is a 0-based column index
function getRowColumn(row, col) {
     for (p in row) {
         if (col==0) return row[p];
         --col;
     }
}

So you can use:

... getRowColumn(row, 0) ...
... getRowColumn(row, 1) ...
... getRowColumn(row, 2) ...

On recent browsers, this solution can be used to access row data by column index: row[Object.keys(columnIndex)]

Flattening your object into an array, may be achieved also using for:

 function rowArray(row) {
     var a=[];
     for(var m in row) a.push(row[m]);
     return a;
 }

So you can use:

db.each("SELECT rowid AS id, item FROM SampleTable", function(err, row) {
     return console.log(rowArray(row));
});