Smart Cities - Storing sensor data in a Database in Node-RED

Setting up SQLite on a Raspberry Pi

• Open the Terminal
• Enter the command sudo apt-get update to update software libraries
• Enter the command sudo apt-get install sqlite3 to install the SQLite3 database.

• It takes approximately one minute for the installation to complete.

• Check to see what version of SQLite is installed with the command sqlite3 --version
• This command will also prove that SQLite is correctly installed.

Installing Database Browser software

• In the Terminal enter the command sudo apt-get install sqlitebrowser

• This will take approximately 30 seconds to install.

• Once the software is installed it will appear in the Raspberry Pi menu under Programming.
• The name of the program is DB Browser for SQLite
• This software gives complete access to the database to view and edit data.

Updating Node-RED

• Node-RED needs to upgraded to a more recent version (version 14). The default version on the Raspberry Pi is version 10.
• Go to the URL https://nodered.org/docs/getting-started/raspberrypi
• You can search for this web page by entering the search node-RED upgrade Raspberry Pi
• This page explains how to perform the update.

• Enter the command bash <(curl -sL https://raw.githubusercontent.com/node-red/linux-installers/master/deb/update-nodejs-and-nodered) --help
• Just copy and paste the command from the web page.
• Use the right mouse button on the mouse to copy and paste or use the drop down menus on the computer screen.
• Short cut commands may not work correctly for copy and paste.
• Make sure to add --help at the end. Note that there are two (-) minus symbols.

• This instruction will output some more information on how to upgrade to a newer version of Node-RED.
• We need to upgrade to version 14.
• Copy the same command as before and add --14 to the end of the statement. Note that there are two (-) minus symbols before the 14.
• Answer y (yes) to any questions.

• The installation will take five minutes to complete.
• During the installation a progress table showing green ticks will appear.
• All items need a green tick.
• If there are any red crosses the installation has failed.

• Screen capture showing successful installation of Node-RED version 14.

Installing SQLite plugins for Node-RED

• Two plugins are required in Node-RED to use SQLite.
• Start Node-RED on the Raspberry Pi.

• The Terminal will open up automatically and start Node-RED using the command node-red-start
• Be sure to stop Node-RED when you are finished using the Terminal command node-red-stop

• Open up the default web browser.
• In the Search bar enter http://localhost:1880
• This will open up Node-RED in the Browser.

• Click on the Hamburger symbol and select Manage Pallet

• In the User Setting window click on the Install tab
• Enter SQLite
• Install the following plugins
  • sqlite-plugin-red
  • node-red-node-sqlite

• When prompted select Install
• Installation will take up to five minutes to install both plugins.

• If the installation is successful you will see two additions.
  • Clicking on the drop down arrow on the far right will show SQLite Tab as an extra selection.
  • Searching for SQLite will reveal a new node named sqlite

Creating a new SQLite database from Node-RED

• Click on the Sqlite tab.

• To create a new database click on the plus (+) symbol to the right of Select a database

• Enter the path and the name of the database.
• On this computer the path is /home/pi and the file name is botanica-park-lake.db
• Click on the red Add button to create the database file.

• You can verify that the database has been created using the File Manager