How does db.each() really work?

Frequently Asked Questions Node.js FAQ
1

Question

How does db.each() really work?

Answer

There is a length of abstraction in the .each() method from sqlite3, but it is not extremely complicated, although sometimes it makes it easier to work with a tool when we understand in depth what it does, so let us deconstruct the .each() method.

By the moment we call the function we have set up the connection to our database or created a new database and we have assigned it a variable name DB, now if we remember correctly db.each() takes two to three arguments, either query and a callback function or query, parameters, and callback function let’s say we have a simple queries, we just want to get all the people in the NYCphonedirectory table that have the name Jerry

db.each(`SELECT * FROM NYCphonedirectory WHERE name ='Jerry';`, () => {...} )

That query will be executed as SQL, and it will return an array of objects that have a name match to ‘Jerry’, once db.each() gets that response it will say:

responseArray.forEach(
  // here we will call the callback function for each individual object
 
)

for example, if our task is to serialize each Jerry and at the end give a final count this is the callback and how it would behave:

 let totalOfJerrys =   0;
 db.each(`SELECT * FROM NYCphonedirectory WHERE name ='Jerry';`, (err, row) => {
  totalOfJerrys += 1;
  console.log(` this is ${row.name} ${row.lastname} and he is the Jerry number ${totalOfJerrys}
} );

console.log('this are all the Jerrys: ' + totalOfJerrys);

This way for each person name Jerry, the callback will add one to the totalOfJerrys variable and I will console.log Jerry’s name and last name. As we can see, .each() calls the query to SQL, and loops through the returning array result, for each object in that array, .each() will be calling the callback to work with the row item every time.

2

Wouldn’t it be 3 or 4 arguments since db.each() can have two callback functions (ie. ‘query’, ‘parameter’, ‘callback’, ‘callback’)?

1 Like
3

I thought that too,
but the way @axelkaban is explaining it, I think he means:

db.each("SELECT * FROM Dog WHERE breed = 'Labrador'", 
  (error, row) => {
    // This gets called for every row our query returns
    console.log(`${row.name} is a good dog`);
  },
  (error, numberOfRows) => {
    // This gets called after each of our rows have been processed
    console.log(`There were ${numberOfRows} good dogs`);
});

Argument 1: "SELECT * FROM Dog WHERE breed = 'Labrador'"
Argument 2: (error, row) =>
Argument 3 is the optional, second callback: (error, numberOfRows) =>

Would make sense, I guess? :thinking:

4

I think he’s referring to a placeholders when he talked about parameters. if we consider the placeholders object as an argument then we can have 3 or 4 arguments in db.each:

  1. Query
  2. placeholders
  3. callback function
  4. second callback function