Learn
Learn Node SQLite
Serial Queries

By default, the commands we issue to our database run in parallel. Every request we make gets sent to the database — which processes them all as quickly as it can, regardless of the order in which they got sent. This is usually a good thing because it means that we can get results faster, but in our case, we don’t want to try to INSERT data into a table that hasn’t been created yet. One way to avoid this issue is to write all of our code in nested callbacks, let’s take a look at how that might look:

db.run("DROP TABLE Dog", error => { db.run("CREATE TABLE Dog", error => { db.run("INSERT INTO Dog (breed, name, owner, fur_color, fur_length) VALUES ('Dachschund', 'Spike', 'Elizabeth', 'Brown', 'Short')", error => { } } }

As you can see, with this technique every command gets increasingly indented, which becomes a bit of an eyesore if we want to guarantee multiple things run chronologically. Another way of accomplishing this task is by using the db.serialize() method like so:

db.serialize(() => { db.run("DROP TABLE Dog"); db.run("CREATE TABLE Dog"); db.run("INSERT INTO Dog (breed, name, owner, fur_color, fur_length) VALUES ('Dachshund', 'Spike', 'Elizabeth', 'Brown', 'Short')"); });

In the previous example, we explicitly tell the database to:

  • First, remove the table Dog if it exists.
  • Second, create an empty table named Dog.
  • Third, insert a new row into the table. In exactly that order without running any command until the previous one completes.

Instructions

1.

Let’s un-nest your code to take advantage of db.serialize(). We’ll go step by step. First, open a call to db.serialize(). Put all of your nested db code inside of db.serialize()’s callback function.

2.

We start with a clean slate every time the code runs with a DROP TABLE IF EXISTS statement. . All your queries are currently inside the callback for this query. Close the callback function after the error checking and un-nest the db.each() method You can leave the contents of db.each() as they are for now. The db.each() query should be on the same level as your DROP TABLE query and will run serially after it.

3.

Move your command to CREATE the table Average into your db.serialize() method call right after dropping the table and before db.each(). It should be at the same level of nesting as db.each().

Leave your command to INSERT the rows into Average inside the second callback of db.each(), guaranteeing that the averages are calculated after your table is created.

4.

No more errors! After all your rows have been inserted with the averageTemperatureByYear.forEach() loop inside db.each(), create a new db.all() query to SELECT all rows from the Average table and printQueryResults() with the transformed data!

5.

We were able to add this information to the new table, congrats! Review the results logged to the console, do they make sense?

Folder Icon

Sign up to start coding

Already have an account?