Sqlite on Raspberry Pi Zero

Hey,

I'm trying to set up a small home automation server using a Raspberry Pi Zero W. I've got the code I want to use working on my laptop and trying to export it across to my raspberry pi. the 2 extra Nodes I need for my project are Dashboard and sqlite, dashboard installs and works just fine. However SQLite is a pig to install! it seems a lot a people have issues with this but I can't find a solution that works for me. Everytime I try to install it fails on:

pi@raspberrypi:~/.node-red $ npm install node-red-node-sqlite

sqlite3@4.0.6 install /home/pi/.node-red/node_modules/sqlite3
node-pre-gyp install --fallback-to-build

node-pre-gyp WARN Using request for node-pre-gyp https download
node-pre-gyp WARN Tried to download(403): https://mapbox-node-binary.s3.amazonaws.com/sqlite3/v4.0.6/node-v64-linux-arm.tar.gz
node-pre-gyp WARN Pre-built binaries not found for sqlite3@4.0.6 and node@10.15.1 (node-v64 ABI, glibc) (falling back to source compile with node-gyp)
make: Entering directory '/home/pi/.node-red/node_modules/sqlite3/build'
ACTION deps_sqlite3_gyp_action_before_build_target_unpack_sqlite_dep Release/obj/gen/sqlite-autoconf-3260000/sqlite3.c
TOUCH Release/obj.target/deps/action_before_build.stamp
CC(target) Release/obj.target/sqlite3/gen/sqlite-autoconf-3260000/sqlite3.o

Any help would be much appreciated

Thank you

Mark

Make sure you have installed sqlite on the pizero first do a
sudo apt-get update
then
sudo apt-get install sqlite3

Yup, I have done this and it didn't make any difference,

"sqlite3 is already the newest version (3.16.2-5+deb9u1)."

Node is V10.15.1
npm is V6.4.1
Node red is V0.19.4

What you've listed does not indicate a failure, it is just a warning to say that the library has to be compiled.

And how about trying the actual install instructions as per the readme ???

cd ~/.node-red
npm i --unsafe-perm node-red-node-sqlite

the --unsafe-perm is significant

I've been trying to get that to complete but it gets stuck on that command for hours, left if overnight and it still appeared to have no progress..

shouldn't take that long - but does take most of an hour I recall.

forgive my ignorance - I'm new to linux, I will try that again now....

On a Pi Zero? Not much power or memory there.

Still having issues, ran the install script as follows:

pi@raspberrypi:~/.node-red $ npm i --unsafe-perm node-red-node-sqlite

  • node-red-node-sqlite@0.3.6
    updated 1 package and audited 407 packages in 63.368s
    found 0 vulnerabilities

But when I start node-red I get the following warning:

26 Feb 13:58:41 - [warn] [node-red-node-sqlite/sqlite] Error: Cannot find module '/home/pi/.node-red/node_modules/sqlite3/lib/binding/node-v64-linux-arm/node_sqlite3.node'

Its not showing as installed on the pallete manager within Node-red

And it didn't do the compilation steps ? It should have done...
(unless it had succeeded before...) in which case stop and restart both Node-RED and the browser.

I've restarted both, still get the same warning on node-red-start, the node shows up in the pallete manager with a warning triangle against and the option to disable it, although the actual node is not on the tool bar to use?

26 Feb 16:16:06 - [warn] [node-red-node-sqlite/sqlite] Error: Cannot find module '/home/pi/.node-red/node_modules/sqlite3/lib/binding/node-v64-linux-arm/node_sqlite3.node'

I suggest uninstalling it and then re-installing it using the correct command. Perhaps it has got confused by failing half way through previous times.

Yes, I'm getting effectively the same message right now but I'm on Windows 10. Is this a Node.JS v10 issue?

24 Feb 22:43:52 - [warn] [node-red-node-sqlite/sqlite] Error: Cannot find module 'C:\src\nr\data\node_modules\sqlite3\lib\binding\node-v64-win32-x64\node_sqlite3.node' (line:4)
24 Feb 22:43:44 - [info] Node-RED version: v0.20.0-beta.5
24 Feb 22:43:44 - [info] Node.js  version: v10.15.1
24 Feb 22:43:44 - [info] Windows_NT 10.0.17763 x64 LE

I don't think it is a node 10 issue, I am using node 10.15.1 on Ubuntu with sqlite, without issues.

Just flashed a new SD card with stretch lite 2018-11-13 and stuck it into a Pi zero W
did an apt-get update/upgrade
sudo apt-get install sqlite3
installed NR using the script
ran npm i --unsafe-perm node-red-node-sqlite
started NR v0.19.6 node.js v10.15.1
created nodes to create a table, insert a row and retreive the row
It all works fine.

One thing. Originally I pulled an SD card from a pi3 and tried to use that and you can't because of the arm7 vrs arm6 issue (pizerow is arm6)

debug of the create/insert/select

2/26/2019, 8:48:10 PMnode: d2c14e4f.99ce3
CREATE TABLE dhtreadings(id INTEGER PRIMARY KEY AUTOINCREMENT, temperature NUMERIC, humidity NUMERIC, currentdate DATE, currenttime TIME, device TEXT) : msg : Object
{ _msgid: "798bb70b.76c888", topic: "CREATE TABLE dhtreadings(id IN…", payload: array[0] }
2/26/2019, 8:48:13 PMnode: d2c14e4f.99ce3
INSERT INTO dhtreadings(temperature, humidity, currentdate, currenttime, device) VALUES (22.4, 48, date('now'), time('now'), "manual") : msg : Object
{ _msgid: "7e4154e6.7c401c", topic: "INSERT INTO dhtreadings(temper…", payload: array[0] }
2/26/2019, 8:48:16 PMnode: d2c14e4f.99ce3
select * from dhtreadings : msg : Object
object
_msgid: "35444da1.a8cc32"
topic: "select * from dhtreadings"
payload: array[1]
0: object
id: 1
temperature: 22.4
humidity: 48
currentdate: "2019-02-27"
currenttime: "01:48:13"
device: "manual"

OK, so I have formatted my SD card, Burned a clean image of Raspbian stretch full (full as I had the img downloaded and ready to go),
I've run

and then

Node-red come pre-installed on Stretch full so I've just used

and then

I'm now running

just waiting for it to get past:

any idea how long this took on your pi zero?

Thanks

Mark

Even if it comes pre-installed, I would always suggest running the update script on the raspberry pi page in the docs yo ensure you have the latest recommended versions or node-red, nodejs, npm and all the pre-installed nodes.

The pre-installed version of Node.JS always used to be an old version and without npm which is a bit crazy as you are always going to need npm with Node. I personally never recommend installing npm separate to Node.JS as there have been times in the past when the latest version of npm didn't work well with the LTS version of Node.

As @ukmoose says, you should use Dave's script to get everything in working order. Not only will you get the right version of Node and npm, everything else gets set up as well.

One thing though, I'm not sure whether Dave's script includes build-essentials? I think you need that.

@pinkimark Mark, I don't know how long it took since I started it and went off and made dinner and came back sometime late and it as done. As @ukmoose and @TotallyInformation said, run the script (you can find it at https://nodered.org/docs/hardware/raspberrypi)

Another difference is that I installed stretch lite - not that that should matter