Learn how to use the sqlite3 node package to perform create, read, update, & delete operations on an SQLite database within JavaScript.

Learn Node SQLite

Throughout this lesson, we're going to access an SQLite database with temperature data for countries over the last 150 years. We're going to take this data, collect it per year in a JavaScript object, average it, and save it into a new SQL database!

To get these two worlds to communicate, we will be importing a package into our JavaScript code. This package will allow us to open the channels of communication with our SQLite database. Once we do that, we can start writing SQL directly in our JavaScript!

The first order of business is to import the module that will facilitate this connection. Recall that to import a module in JavaScript we can use `require()` like so:

```js
const sqlite3 = require('sqlite3');
```

The code above gives us a JavaScript object, called `sqlite3` that we can interact with via _methods_. The first method we're going to use on `sqlite3` is going to be the method that opens up a new database. In SQLite, a database corresponds to a single file, so the only argument required to open this database is the _path_ to the file that SQLite will use to save the database.

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

This code will create a new database file, in the current folder, named `db.sqlite`. Then we'll have a database to interact with!


Opening A Database

Not all SQL commands return rows, and in fact, some essential SQL commands do not. If we `INSERT` a row or `CREATE` a `TABLE` we will not receive a row in response. To perform SQL commands that do not return rows, we use `db.run()` to `run` the command. `db.run()` does not return a value, but, depending on the SQL command, it may attach properties to the `this` keyword within the scope of the callback. In some cases, like creating a table, `db.run()` will not modify `this`. In other cases, like when `INSERT`ing a row, a callback to `db.run()` will be able to access `this.lastID`, the ID of the last `INSERT`ed row.


Using db.run()

Wow, we were able to take data from a source, manipulate it for our needs, and save it separately. We've managed to set up a database, create tables, insert data, and modify it &mdash; all within JavaScript! We learned about how to ensure that JavaScript runs a set of commands chronologically so that we can avoid race conditions. We leveraged JavaScript methods fluently alongside our database queries in SQL. Now our knowledge of SQL and our understanding of JavaScript can work together to accomplish more than either could alone.

Good job!


Wrap Up

By default, the commands we issue to our database run in _parallel_. Every request we make gets sent to the database &mdash; 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:

```js
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:

```js                                                                                                                                    	 
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.


Serial Queries

`db.all()` is a useful tool to fetch all the data we have that meets certain criteria. But what if we only want to get a particular row? We could do something like this:

```js
db.all("SELECT * FROM Dog", (error, rows) => {
  printQueryResults(rows.find(row => row.id === 1));
});
```

In this example, we fetch all the rows from a database. Doing this populates a JavaScript variable, `rows`, that contains the results of our `SELECT` statement (all the rows from the database). We use JavaScript's `.find()` method to find the row with an ID of 1. Then print out that row.

With a tiny database, this might be OK, but it will be a considerable and unnecessary load if the database is large in any sense. Luckily, we have a different method that will fetch a single row from a database: `db.get()`. See it in action:

```js
db.get("SELECT * FROM Dog WHERE owner_name = 'Charlie'", (error, row) => {
  printQueryResults(row); 
});
```

Sometimes all we need to know is whether a record matching our query exists (for instance: the code above would answer the question "Does Charlie own a dog?" depending on whether or not `row` is `undefined`). Sometimes we know that there's only a single row because we are searching for a specific ID. And sometimes we only want an example of a row that would match our description. In the code above we would only print information about one dog. To accomplish this, we use `db.get()` instead of `db.all()`.

It's important to note that even if multiple rows match the query, `db.get()` will only return a single result. In the example above, if "Charlie" owns multiple dogs, the code provided will still only print information about one dog.


Retrieving A Single Row

While learning JavaScript, you likely learned about the powerful method `.forEach()` that allows us to process every element in an array separately. Now we will use a similar method that will enable us to process every row returned from a database query. 

```js
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`);
});
```

In the code above we `SELECT` all the Labrador dogs from our `Dog` database. We offer affirmation to each of the animals individually and then announce how many received this praise in sum. 

`db.each()` takes a query and a callback function that it performs on each row returned from the query. This is a useful technique for transforming or updating rows. This is also useful for memory management &mdash; we only have to look at one row at a time instead of trying to process every returned row at the same time. `db.each()` additionally takes an optional second callback function, which will be called when all of the queries are completed and processed.


Using db.each()

One of the most essential skills as a programmer is being able to identify and utilize the appropriate tool for a specified task. In the context of database management, this will mean using SQL to specify, store, update and retrieve data. In the context of web programming, this will mean writing JavaScript to automate, manipulate, and return relevant values &mdash; for presentation in a website or use in a backend script. What happens, then, when we need both? What if we want to retrieve data from a SQL database (using our database administration skills) and then manipulate and expose that data through JavaScript functions (using our web programming skills)?                                                 	 
                                                                                                                                         	 
In this lesson, we will learn how to manage an SQLite database from within JavaScript. We will see how to perform all the fundamental features of database management &mdash; `CREATE`ing `INSERT`ing and `SELECT`ing, and then interacting with that data using the full force of JavaScript &mdash; writing functions, wielding objects, and performing calculations. It's important to know that many of the results herein could be obtained purely through SQL or purely through JavaScript if need be. But something simple to perform (and read back) with one language might be very hard to write and understand in another.

In the workspace, there's code that opens a connection to an SQLite database. There's a function `getAverageTemperatureForYear()` that will take a `year` as an argument. The function retrieves the temperatures from that year and then calculates the year's average. We've called it with different years, illustrating the power of being able to power our SQL queries with JavaScript.


Introduction

In the previous exercise we were able to import the 'sqlite3' library and use that to open our SQLite database &mdash; so far so good! But we still haven't retrieved any information from it. Since we have access to our database as a JavaScript object, let's try running a query on it. Recall that a _query_ is a statement that speaks to a database and requests specific information from it. To execute a query and retrieve all rows returned, we use `db.all()`, like so:

```js
db.all("SELECT * FROM Dog WHERE breed='Corgi'", (error, rows) => {
  printQueryResults(rows);
});
```

In the previous example, we used the `db.all()` method to retrieve every dog of breed "Corgi" from a table named Dog and print them. 


Retrieving All Rows

No one's perfect. Code, like people, can make mistakes. This is OK! What's important is that we learn how to handle our difficulties while keeping our composure. Handling errors is an important part of the process when dealing with Node & SQL in particular. When our code throws an error, we should be able to handle it before it reaches our end users and incites panic and concern. We still want to know what happened, so that we can perform a suitable action based on the error that occurred &mdash; so we _catch_ the error.

For `db.run()`, `db.each()`, `db.get()`, and `db.all()`, the first argument to the callback will always be an `Error` object if an error occurred. If there is no error, this argument will be `null`. We can check if this error exists, and if it does exist, we can handle it.


Handling Errors Gracefully

Now we know how to retrieve data from a database when we know exactly what we're looking for. But we may not always know what values we will need to search for when writing our program. When we write a JavaScript function, we give the function _parameters_ that will have many different values when the function gets called. _Placeholders_ solve a similar problem in the world of SQL queries. Sometimes we'll want to search our database based on a user's submission. Or we might find ourselves wanting to perform a series of queries looping over some external data.

In those cases, we will have to use a placeholder. A placeholder is a part of our SQL query that we want to be _interpolated_ with a variable's contents. We want the value of the JavaScript variable to be placed within the SQL query. To do this properly, we'll need to pass a particular argument to our `db.run()` command that will tell it how to interpolate the query.

```js
const furLength1 = "short";
const furLength2 = "long";
const furColor1 = "brown";
const furColor2 = "grey";

const findDogByFur = (length, color) => {
  db.all(
    "SELECT * FROM Dog WHERE fur_length = $furLength AND fur_color = $furColor", 
    {
      $furLength: length,
      $furColor: color
    }, 
    (error, rows) => {
      printQueryResults(rows);
    }
  );
});

findDogByFur(furLength1, furColor1); // prints all dogs with short brown fur.
findDogByFur(furLength2, furColor1); // prints all dogs with long brown fur.
findDogByFur(furLength1, furColor2); // prints all dogs with short grey fur.
findDogByFur(furLength2, furColor2); // prints all dogs with long grey fur
```

As we can see in the example above, the power of placeholders is that we don't need to know precisely the data we're searching for at the time of writing our query. We can use these placeholders and then later, when we have values we want to find, we can plug them into the query. This is a highly effective tool that will allow us to harness our programming skills within our database queries.


Using Placeholders

So far we've managed to:

1. Query a database for weather records by location.
2. Reformat that data into a JavaScript object.
3. Manipulate that JavaScript object to find new, meaningful information.

That's pretty significant! Now it's time to take that useful information and add it to a table of our own. Since creating a table is another operation that does not return a row, we can use the same `db.run()` we used to `INSERT` rows. Let's see what happens when we `CREATE` a `TABLE` and `INSERT` our data into it. 

Notice there's a statement declaring "DROP TABLE IF EXISTS"  &mdash; this is because we want to make sure when we create our new table that we won't run into an error telling us the table already exists (from previous times running our code).
