Node-SQLite

Question 1

LEARN NODE SQLITE

Introduction

Answer 1

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 —CREATEingINSERTing andSELECTing, and then interacting with that data using the full force of JavaScript — 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.

Question 2

Opening A Database

Answer 2

Question 3

Retrieving All Rows

Answer 3

In the previous exercise we were able to import the ‘sqlite3’ library and use that to open our SQLite database — 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:
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.

Question 4

Retrieving A Single Row

Answer 4

we have a different method that will fetch a single row from a database: db.get(). See it in action:

Question 5

Using Placeholders

Answer 5

Question 6

Using db.run()

Answer 6

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 INSERTing a row, a callback to db.run() will be able to access this.lastID, the ID of the last INSERTed row.

Question 7

LEARN NODE SQLITE

Handling Errors Gracefully

Answer 7

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 — so wecatchthe error.

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

Question 8

Using db.each()

Answer 8

Question 9

Creating A New Table

Answer 9