FAQ: Learn Node SQLite - Serial Queries

The main thing is that db.serialize will run the database queries in order but that is not enough in this case since other code is included below db.each. The code below db.each needs to run AFTER db.each is 100% complete and the data from the TemperatureData table has been added to the temperaturesByYear object. The db.each method therefore needs to have two callback-functions. The second db.each callback-function will wrap the remainder of the code and runs after the db.each is complete. I added some comments below:

//Empty object 
const temperaturesByYear = {};

db.serialize(() => { 
  
//db.serialize works good here to drop table, create table in order
db.run('DROP TABLE IF EXISTS Average', error => {
    if (error) {
      throw error;
    }
  })
db.run('CREATE TABLE Average (id INTEGER PRIMARY KEY, year INTEGER NOT NULL, temperature REAL NOT NULL)', logNodeError);
  
db.each('SELECT * FROM TemperatureData',
   //First call-back adds data-rows to the temperaturesByYear object
   (error, row) => {
      if (error) {
        throw error;
      }
      addClimateRowToObject(row, temperaturesByYear);
    }, 
    //Second db.each call-back wraps remaining code and will run when db.each is complete
   //This ensures that temperaturesByYear object has all data
   error => {
      if (error) {
        throw error;
      }
   //Below an array is made based on the data in temperaturesByYear object by helper function   
   const averageTemperatureByYear = calculateAverages(temperaturesByYear);
   //The array is iterated through to add data to the Average table   
   averageTemperatureByYear.forEach(row => {
        db.run('INSERT INTO Average (year, temperature) VALUES ($year, $temp)', {
          $year: row.year,
          $temp: row.temperature
        }, err => {
          if (err) {
            console.log(err);
          }
        });
      });
    db.all('SELECT * FROM Average',
    (error, row) => {
      printQueryResults(row)
    })
    });
  });

I was curious to find out why it is not working to place the db.all outside of the db.each statement and added some console.logs. It turns out, that db.serialize waits for the first and second callback of db.each to execute all statements. All the nested calls to db.run are fired off. Since those seem to be async, db.serialize moves on to db.all() not waiting until the rows actually have been written to the database. Therefore db.all will receive an empty database.

const {
  calculateAverages,
  addClimateRowToObject,
  logNodeError,
  printQueryResults
} = require('./utils');
const sqlite = require('sqlite3');

const db = new sqlite.Database('./db.sqlite');

const temperaturesByYear = {};

// start by wrapping all the code below in a serialize method
db.serialize(() => {
  db.run('DROP TABLE IF EXISTS Average', error => {
    if (error) {
      throw error;
    }
  })
  db.run('CREATE TABLE Average (id INTEGER PRIMARY KEY, year INTEGER NOT NULL, temperature REAL NOT NULL)', logNodeError);
  db.each('SELECT * FROM TemperatureData',
    (error, row) => {
      if (error) {
        throw error;
      }
      console.log(`received row from TempData`)
      addClimateRowToObject(row, temperaturesByYear);
    },
    error => {
      if (error) {
        throw error;
      }
      console.log(`starting callback 2`);
      const averageTemperatureByYear = calculateAverages(temperaturesByYear);
      averageTemperatureByYear.forEach(row => {
        console.log(`will insert row to Average table`)
        db.run('INSERT INTO Average (year, temperature) VALUES ($year, $temp)', {
          $year: row.year,
          $temp: row.temperature
        }, err => {
          if (err) {
            console.log(err);
          }
          console.log(`added row ${row.year} to Average table`)
        });
      });
    });

  db.all('SELECT * FROM Average',
    (error, row) => {
      console.log(`getting all rows from Average`);
      printQueryResults(row);
    })
});

But funny enough it turns out, that even when correctly nesting the db.all into db.each, there are still some rows that get added to the Average table AFTER db.all has run. And therefore these rows are still missing in the result of db.all. Now what?

just added some logs to the solution code. run it and check the console

const {
  calculateAverages,
  addClimateRowToObject,
  logNodeError,
  printQueryResults
} = require('./utils');
const sqlite = require('sqlite3');

const db = new sqlite.Database('./db.sqlite');

const temperaturesByYear = {};

// start by wrapping all the code below in a serialize method
db.serialize(() => {
  db.run('DROP TABLE IF EXISTS Average', error => {
    if (error) {
      throw error;
    }
  })
  db.run('CREATE TABLE Average (id INTEGER PRIMARY KEY, year INTEGER NOT NULL, temperature REAL NOT NULL)', logNodeError);
  db.each('SELECT * FROM TemperatureData',
    (error, row) => {
      if (error) {
        throw error;
      }
      addClimateRowToObject(row, temperaturesByYear);
    },
    error => {
      if (error) {
        throw error;
      }
      console.log(`starting callback 2`);
      const averageTemperatureByYear = calculateAverages(temperaturesByYear);
      averageTemperatureByYear.forEach(row => {
        db.run('INSERT INTO Average (year, temperature) VALUES ($year, $temp)', {
          $year: row.year,
          $temp: row.temperature
        }, err => {
          if (err) {
            console.log(err);
          }
          console.log(`added row ${row.year} to the Average table`)
        });
      });
      db.all('SELECT * FROM Average',
        (error, row) => {
          console.log(`getting all rows from Average`);
          printQueryResults(row);
        })
    });
});

the solution to the forementioned problem with not all INSERT statements executing before the Average table is read by db.all, is to nest a second db.serialize into the 2 callback to db.each. This ensures that all INSERT statements are execute before we read the Average table with db.all.

/* eslint-disable no-console */
const sqlite = require('sqlite3');
const {
  calculateAverages,
  addClimateRowToObject,
  logNodeError,
  printQueryResults,
} = require('./utils');

const db = new sqlite.Database('./db.sqlite');

const temperaturesByYear = {};
let averageTemperatureByYear;

// start by wrapping all the code below in a serialize method

db.serialize(() => {
  db.run('DROP TABLE IF EXISTS Average', (error) => {
    if (error) {
      throw error;
    }
  });

  db.run('CREATE TABLE Average (id INTEGER PRIMARY KEY, year INTEGER NOT NULL, temperature REAL NOT NULL)', logNodeError);

  db.each('SELECT * FROM TemperatureData',
    // callback 1
    (error, row) => {
      if (error) { throw error; }
      console.log('adding climaterow to object');
      addClimateRowToObject(row, temperaturesByYear);
    },
    // callback 2
    (error) => {
      if (error) { throw error; }
      console.log('starting callback 2');
      averageTemperatureByYear = calculateAverages(temperaturesByYear);
      console.log('finished calculating averages');

      // serialize all following db statements
      db.serialize(() => {
        averageTemperatureByYear.forEach((row) => {
          db.run('INSERT INTO Average (year, temperature) VALUES ($year, $temp)', {
            $year: row.year,
            $temp: row.temperature,
          }, (err) => {
            if (err) { console.log(err); }
            console.log(`added ${row.year} to the table`);
          });
        });

        console.log('will select * from Average');
        db.all('SELECT * FROM Average',
          (err, rows) => {
            if (err) throw err;
            console.log('getting all rows from Average');
            printQueryResults(rows);
          });
      });
    });
});