# I have some questions relating database

**URL:** <https://discourse.nodered.org/t/i-have-some-questions-relating-database/2323>\
**Category:** General\
**Created:** [15 August 2018 19:41 UTC](https://discourse.nodered.org/t/i-have-some-questions-relating-database/2323 "2018-08-15T19:41:35Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![eleckits](https://avatars.discourse-cdn.com/v4/letter/e/8dc957/32.png) [@eleckits](https://discourse.nodered.org/u/eleckits)\
**Post date:** [15 August 2018 19:41 UTC](https://discourse.nodered.org/t/i-have-some-questions-relating-database/2323/1 "2018-08-15T19:41:35Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [15 August 2018 20:56 UTC](https://discourse.nodered.org/t/i-have-some-questions-relating-database/2323/2 "2018-08-15T20:56:12Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![eleckits](https://avatars.discourse-cdn.com/v4/letter/e/8dc957/32.png) [@eleckits](https://discourse.nodered.org/u/eleckits)\
**Post date:** [15 August 2018 21:05 UTC](https://discourse.nodered.org/t/i-have-some-questions-relating-database/2323/3 "2018-08-15T21:05:33Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [15 August 2018 21:10 UTC](https://discourse.nodered.org/t/i-have-some-questions-relating-database/2323/4 "2018-08-15T21:10:35Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![eleckits](https://avatars.discourse-cdn.com/v4/letter/e/8dc957/32.png) [@eleckits](https://discourse.nodered.org/u/eleckits)\
**Post date:** [15 August 2018 21:13 UTC](https://discourse.nodered.org/t/i-have-some-questions-relating-database/2323/5 "2018-08-15T21:13:49Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [15 August 2018 21:20 UTC](https://discourse.nodered.org/t/i-have-some-questions-relating-database/2323/6 "2018-08-15T21:20:31Z")

</div>

> [@eleckits](#):
>
> 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?

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.

---

<div class="post-metadata">

**Author:** ![eleckits](https://avatars.discourse-cdn.com/v4/letter/e/8dc957/32.png) [@eleckits](https://discourse.nodered.org/u/eleckits)\
**Post date:** [15 August 2018 21:23 UTC](https://discourse.nodered.org/t/i-have-some-questions-relating-database/2323/7 "2018-08-15T21:23:52Z")

</div>

> [@TotallyInformation](#):
>
> 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.

SQLite Db can install to ubuntu 16.04 VPS?

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [15 August 2018 21:36 UTC](https://discourse.nodered.org/t/i-have-some-questions-relating-database/2323/8 "2018-08-15T21:36:44Z")

</div>

> [@eleckits](#):
>
> SQLite Db can install to ubuntu 16.04 VPS?

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

---

<div class="post-metadata">

**Author:** ![eleckits](https://avatars.discourse-cdn.com/v4/letter/e/8dc957/32.png) [@eleckits](https://discourse.nodered.org/u/eleckits)\
**Post date:** [15 August 2018 21:42 UTC](https://discourse.nodered.org/t/i-have-some-questions-relating-database/2323/9 "2018-08-15T21:42:55Z")

</div>

> [@TotallyInformation](#):
>
> Yes, you only need the library. The appropriate node-red node will install it, no service required, not like MySQL.

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 ?

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [15 August 2018 21:49 UTC](https://discourse.nodered.org/t/i-have-some-questions-relating-database/2323/10 "2018-08-15T21:49:03Z")

</div>

> [@eleckits](#):
>
> 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 ?

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](https://www.npmjs.com/package/node-red-node-sqlite)

---

<div class="post-metadata">

**Author:** ![eleckits](https://avatars.discourse-cdn.com/v4/letter/e/8dc957/32.png) [@eleckits](https://discourse.nodered.org/u/eleckits)\
**Post date:** [16 August 2018 14:22 UTC](https://discourse.nodered.org/t/i-have-some-questions-relating-database/2323/11 "2018-08-16T14:22:53Z")

</div>

**\> 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](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

```auto
npm install node-red-node-sqlite

but when open node red I get this 

Flows stopped due to missing node types.

sqlitedb
sqlite
```

---

<div class="post-metadata">

**Author:** ![ukmoose](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ukmoose/32/13_2.png) [@ukmoose](https://discourse.nodered.org/u/ukmoose)\
**Post date:** [16 August 2018 14:32 UTC](https://discourse.nodered.org/t/i-have-some-questions-relating-database/2323/12 "2018-08-16T14:32:05Z")

</div>

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](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.

---

<div class="post-metadata">

**Author:** ![eleckits](https://avatars.discourse-cdn.com/v4/letter/e/8dc957/32.png) [@eleckits](https://discourse.nodered.org/u/eleckits)\
**Post date:** [16 August 2018 14:40 UTC](https://discourse.nodered.org/t/i-have-some-questions-relating-database/2323/13 "2018-08-16T14:40:47Z")

</div>

> [@ukmoose](#):
>
> 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.

yes user is root I am using VPS

---

<div class="post-metadata">

**Author:** ![eleckits](https://avatars.discourse-cdn.com/v4/letter/e/8dc957/32.png) [@eleckits](https://discourse.nodered.org/u/eleckits)\
**Post date:** [16 August 2018 14:52 UTC](https://discourse.nodered.org/t/i-have-some-questions-relating-database/2323/14 "2018-08-16T14:52:07Z")

</div>

I get it is solved

cd ~/.node-red

npm i sqlite3 --build-from-source

```auto
npm install node-red-node-sqlite

```

---

<div class="post-metadata">

**Author:** ![eleckits](https://avatars.discourse-cdn.com/v4/letter/e/8dc957/32.png) [@eleckits](https://discourse.nodered.org/u/eleckits)\
**Post date:** [16 August 2018 15:00 UTC](https://discourse.nodered.org/t/i-have-some-questions-relating-database/2323/16 "2018-08-16T15:00:02Z")

</div>

> [@TotallyInformation](#):
>
> Step 2: Add the SQLite node [node-red-node-sqlite - npm](https://www.npmjs.com/package/node-red-node-sqlite)

now sqlite installed what is next step?

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [16 August 2018 15:04 UTC](https://discourse.nodered.org/t/i-have-some-questions-relating-database/2323/17 "2018-08-16T15:04:26Z")

</div>

> [@eleckits](#):
>
> now sqlite installed what is next step

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.

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [16 August 2018 15:58 UTC](https://discourse.nodered.org/t/i-have-some-questions-relating-database/2323/18 "2018-08-16T15:58:23Z")

</div>

> [@eleckits](#):
>
> npm WARN engine tar@4.4.6: wanted: {"node":"\>=4.5"} (current: {"node":"4.2.6","npm":"2.15.12"})

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.

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [17 August 2018 17:55 UTC](https://discourse.nodered.org/t/i-have-some-questions-relating-database/2323/19 "2018-08-17T17:55:23Z")

</div>

> [@eleckits](#):
>
> yes user is root I am using VPS

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.

---

<div class="post-metadata">

**Author:** ![eleckits](https://avatars.discourse-cdn.com/v4/letter/e/8dc957/32.png) [@eleckits](https://discourse.nodered.org/u/eleckits)\
**Post date:** [21 August 2018 21:41 UTC](https://discourse.nodered.org/t/i-have-some-questions-relating-database/2323/20 "2018-08-21T21:41:50Z")

</div>

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;

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [21 August 2018 23:02 UTC](https://discourse.nodered.org/t/i-have-some-questions-relating-database/2323/21 "2018-08-21T23:02:22Z")

</div>

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.

[Next page](https://discourse.nodered.org/t/i-have-some-questions-relating-database/2323.md?page=2)