Integrating SQLite into Our Application

In the previous chapter, we took a look at SQLite and the functionality it can provide us in order to persist our data. We ended up learning a lot about SQLite but did not make any change to our existing application. In this chapter, we are going to get back down to our application core and enhance it to support data persistence with SQLite!

Integrating SQLite into our application requires some consideration to the following topics:

• How to interface our running SQLite instance with node.
• When to update the database with our data.
• When to read from the database. We will make use of our existing caching module to make this more efficient.

We also discussed the numerous possibilities that adding persistence was going to enable. We can't just let that go to waste. In this chapter, we will also be adding new features, such as the following...

Interfacing SQLite with node

Before we add any new features, we first have to get node and SQLite to play nice with each other.

Fortunately, there is a library to make our lives easier (https://www.npmjs.com/package/sqlite3). The way this package works is interesting enough for it to be worth mentioning. The node module itself comes with a running SQLite instance, which interfaces with the file containing all your data. This is in contrast to other database interfaces, which connect to a running database instance.

Running queries with node

In the previous chapter, we executed all our queries in a file called .sqlite.db. We will use this same file to demonstrate how to execute queries and receive their results inside our node process:

    /**
     * Import the SQLite library, and initialize the SQLite db
    instance
     * Mention the location of the ".sqlite.db" file that you 
    used in the previous chapter
     * We want the absolute path of the file, for better
      clarity, hence, the use of path.resolve
     * The location inside path.resolve, is the location of the 
     sqlite.db file relative to this one
    */
    const sqlite3 = require('sqlite3')
    const path = require('path')
    const db = new 
      sqlite3.Database(path.resolve('./.sqlite.db'))

   /**
     * The "serialize" method of the db instance makes sure
      that all...

Making our database module

Currently, our application requires the following features with respect to data persistence:

• Adding a new temperature to the database when it is recorded.
• Fetching historical temperature results so that our charts don't go blank. This can be rephrased as fetching the past 10 temperature results to initially populate our chart.
• Fetching temperatures for a particular date range.
• Fetching the average of temperatures in a date range.

In order to implement these features, we need to create a new module. In this module, each of the four points listed is implemented as separate methods of the singleton returned by the module.

Adding a new temperature to the database

In order to insert a value into...

Integrating the database module into our server application

Now that we have our database module ready, it's time to use the functionality we just created and update our existing modules to make use of this persistence.

Upgrading the sensor interface module

The functionality defined in get-cached-sensor-readings.js was defined to fetch readings from our sensor and return it via a callback function. Now that we have our database in place, we want to store the readings in addition to returning them:

    const getSensorReadings = require('./get-sensor-readings')

    /**
     * Import the database module that we created earlier
     */
    const databaseOperations = require('./database-
    operations&apos...

Adding new features - the ability to view readings from a custom time period

Adding persistence gives us a very small end result even though there is a lot going on under the hood. Now is the best time to make use of the power we have with SQLite to add more features to our application.

Adding the required APIs

Just like in the previous section, we will need to start by adding the APIs to get the readings and their average for a certain time period:

    app.get('/temperature/range', function (req, res) {
      /**
       * Here, the "start" and "end" datetimes for the range
     of readings are
       * expected to be received through the query parameters.
     This is spllied as part
       * of...

Summary

With this chapter, we finally gave our application a memory ability greater than that of a goldfish, and we finally have a place to permanently store all the readings that we record.

We started off by studying how to interface SQLite3 using node. This was core to our application since all the other application code would depend on it. After we figured out the basics, we managed to create a module that specialized in reading and writing to our database. We then went on to use this module to enhance the rest of our application and finally remove the annoying phenomenon of disappearing charts.

Finally, we moved on to adding two completely new features: showing a range of readings between dates provided by the user and showing the average of temperatures in this range.

It looks like we have covered all the parts of the web application stack that we discussed in Chapter 1,...