I have some questions relating database

General
1

Hi, there
I am very new to node red and this is first message here
I want to build database then want to store some values inside database and then want to read database and compare with inject value then if it is same then debug true

can you please guide me how to do it and if there is an example it will be nice

2

First you need to decide what type of database. To do that, you will want to decide what kind of data you need to manage, how much of it and how rapidly it will update.

If you can tell us some of that, we should be able to steer you to some of the many options.

3

let me say between 1 to 100 strings want to store in database and then search one of them and if it is exist in side then debug true
could you please suggest what kind of database can use?

4

Is the set of strings fixed or does it get amended routinely? Do you need to be able to update it from node-red. If yes then where does the data come from.

So far what you have described sounds more like an array of strings rather than a database.

5

strings are fixed come from mqtt or write them manually
to make things simple let do make database contains values and do search if it is exist the debug true

6

Off the top of my head, it sounds like a simple SQLite Db would do fine. This is a single library, largely memory based db with file persistence. Great for relatively small datasets that are tabular in nature.

7

SQLite Db can install to ubuntu 16.04 VPS?

8

Yes, you only need the library. The appropriate node-red node will install it, no service required, not like MySQL.

9

so want to go step by step
could you please tell me steps how to install it on VPS and how to set username and password ?

10

Step 0: I assume your VPS is set up and secured? Running Ubuntu as you indicated.

Step 1: You need to follow the install instructions for Node-RED.

Step 2: Add the SQLite node node-red-node-sqlite - npm

11

> npm install node-red-node-sqlite
> npm WARN engine tar@4.4.6: wanted: {"node":">=4.5"} (current: {"node":"4.2.6","npm":"2.15.12"})
> -
> > sqlite3@4.0.2 install /root/node_modules/node-red-node-sqlite/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 ERR! UNCAUGHT EXCEPTION
> node-pre-gyp ERR! stack TypeError: this is not a typed array.
> node-pre-gyp ERR! stack at Function.from (native)
> node-pre-gyp ERR! stack at Object. (/root/node_modules/node-red-node-sqlite/node_modules/sqlite3/node_modules/node-pre-gyp/node_modules/tar/lib/parse.js:33:27)
> node-pre-gyp ERR! stack at Module._compile (module.js:410:26)
> node-pre-gyp ERR! stack at Object.Module._extensions..js (module.js:417:10)
> node-pre-gyp ERR! stack at Module.load (module.js:344:32)
> node-pre-gyp ERR! stack at Function.Module._load (module.js:301:12)
> node-pre-gyp ERR! stack at Module.require (module.js:354:17)
> node-pre-gyp ERR! stack at require (internal/module.js:12:17)
> node-pre-gyp ERR! stack at Object. (/root/node_modules/node-red-node-sqlite/node_modules/sqlite3/node_modules/node-pre-gyp/node_modules/tar/lib/list.js:10:16)
> node-pre-gyp ERR! stack at Module._compile (module.js:410:26)
> node-pre-gyp ERR! System Linux 2.6.32-042stab128.2
> node-pre-gyp ERR! command "/usr/bin/nodejs" "/root/node_modules/node-red-node-sqlite/node_modules/sqlite3/node_modules/.bin/node-pre-gyp" "install" "--fallback-to-build"
> node-pre-gyp ERR! cwd /root/node_modules/node-red-node-sqlite/node_modules/sqlite3
> node-pre-gyp ERR! node -v v4.2.6
> node-pre-gyp ERR! node-pre-gyp -v v0.10.3
> node-pre-gyp ERR! This is a bug in node-pre-gyp.
> node-pre-gyp ERR! Try to update node-pre-gyp and file an issue if it does not help:
> node-pre-gyp ERR! https://github.com/mapbox/node-pre-gyp/issues
> npm ERR! Linux 2.6.32-042stab128.2
> npm ERR! argv "/usr/bin/nodejs" "/usr/local/bin/npm" "install" "node-red-node-sqlite"
> npm ERR! node v4.2.6
> npm ERR! npm v2.15.12
> npm ERR! code ELIFECYCLE
**> **
> npm ERR! sqlite3@4.0.2 install: node-pre-gyp install --fallback-to-build
> npm ERR! Exit status 7
> npm ERR!
> npm ERR! Failed at the sqlite3@4.0.2 install script 'node-pre-gyp install --fallback-to-build'.
> npm ERR! This is most likely a problem with the sqlite3 package,
> npm ERR! not with npm itself.
> npm ERR! Tell the author that this fails on your system:
> npm ERR! node-pre-gyp install --fallback-to-build
> npm ERR! You can get information on how to open an issue for this project with:
> npm ERR! npm bugs sqlite3
> npm ERR! Or if that isn't available, you can get their info via:
> npm ERR!
> npm ERR! npm owner ls sqlite3
> npm ERR! There is likely additional logging output above.
**> **
> npm ERR! Please include the following file with any support request:
> npm ERR! /root/npm-debug.log
>

I use this command to install sqlite database

npm install node-red-node-sqlite

but when open node red I get this 

Flows stopped due to missing node types.

sqlitedb
sqlite
12

Before you install any nodes you should read the README file.
If you read that file for this node it contains a section on how to install. See https://flows.nodered.org/node/node-red-node-sqlite

What user are you logged in as? Is it root? If it is it is very bad practice and will probably cause you issues when you come to upgrade things later.

13

yes user is root I am using VPS

14

I get it is solved

cd ~/.node-red

npm i sqlite3 --build-from-source

npm install node-red-node-sqlite
16

now sqlite installed what is next step?

17

Before you can use it from node-red you need to understand a bit about sqlite.

Googe for
sqlite tutorial
and there are lots of hits.

18

Note also this warning line --^ node 4 is beyond end of life and not supported by security patches. But as you have it working then for now maybe lease it alone... but... at some point you will need to upgrade as several of our (non-core) nodes do NOT support it anymore.

1 Like
19

This is an absolute no-no! You should not even be able to connect to a VPS using root. Please read up at least a little on Internet security first, then rebuild your VPS and configure securely.

Only then should you consider continuing.

If you want to play with an insecure system - get a low-cost Raspberry Pi and keep it off the Internet.

Without wishing to seem obstructive, I'm afraid that I can't help you further until I know that you are not adding to the many security issues of the Internet.

20

I install mysql on vps and connect to node red then now want to search for some thing inside database then if find it want to send yes to debug and if not found want to send no

this code for search
msg.topic= "SELECT * FROM tab WHERE name LIKE '11'";
return msg;

21

Attach a debug node (displaying the complete msg object ) to the output of the sql node and see what it returns for the two cases. Then you should be able to figure out what you can test in a switch node to achieve the results you want.