# The best way to implement device database

**URL:** <https://discourse.nodered.org/t/the-best-way-to-implement-device-database/7028>\
**Category:** General\
**Created:** [19 January 2019 20:05 UTC](https://discourse.nodered.org/t/the-best-way-to-implement-device-database/7028 "2019-01-19T20:05:32Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![aargollo](https://avatars.discourse-cdn.com/v4/letter/a/58f4c7/32.png) [@aargollo](https://discourse.nodered.org/u/aargollo)\
**Post date:** [19 January 2019 20:05 UTC](https://discourse.nodered.org/t/the-best-way-to-implement-device-database/7028/1 "2019-01-19T20:05:32Z")

</div>

Hi Guys, I´m very happy to post here!  
I working to building a IoT gateway, using Rpi3 and Zigbee serial interface. I Already have important parameters got from Zigbee end devices, like nwkshort address, IEEE address, EP, and ModelID, all those got from specific Zigbee messages and storage in the global variables.

To implement persistence, I believe that need to working with data base and after It I´ll working to implement another components, like to JSon API.

I would like to receive advise what the best data base to working, and If is possible I would like to receive links to study it?

Best Regards,  
Alex

---

<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:** [20 January 2019 09:15 UTC](https://discourse.nodered.org/t/the-best-way-to-implement-device-database/7028/2 "2019-01-20T09:15:57Z")

</div>

With v0.19 you can store global variables so they are available after restarts see [https://nodered.org/docs/user-guide/context](https://nodered.org/docs/user-guide/context)  
especially the section on context stores

---

<div class="post-metadata">

**Author:** ![aargollo](https://avatars.discourse-cdn.com/v4/letter/a/58f4c7/32.png) [@aargollo](https://discourse.nodered.org/u/aargollo)\
**Post date:** [20 January 2019 11:08 UTC](https://discourse.nodered.org/t/the-best-way-to-implement-device-database/7028/3 "2019-01-20T11:08:08Z")

</div>

Hi Ukmoose, I´m already working with global variable.  
My IoT gateway will have some devices that have dynamic association in two stages:

1st - attaching procedure, in this moment, gateway and devices will discovery the important parameter (ShortAdd, IEEEadd, EP, ModelID and etc.)  
2st - status stage, the gateway will send command to devices. For example if the exchange device is an actuator (like Light switch), the gateway, will use the parameters got in attaching stage, to send a command Command (ON or OFF).

The parameters will be stored in the database table or in the file for use in the status stage.  
Do you can advise the bast database to do it?

---

<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:** [20 January 2019 11:57 UTC](https://discourse.nodered.org/t/the-best-way-to-implement-device-database/7028/4 "2019-01-20T11:57:10Z")

</div>

I think that @ukmoose was pointing out that global vars can automatically be saved to persistent storage (a file for example) so all you need to do is to enable that and they will automatically be saved. There may therefore be no need for a database.

---

<div class="post-metadata">

**Author:** ![aargollo](https://avatars.discourse-cdn.com/v4/letter/a/58f4c7/32.png) [@aargollo](https://discourse.nodered.org/u/aargollo)\
**Post date:** [20 January 2019 14:39 UTC](https://discourse.nodered.org/t/the-best-way-to-implement-device-database/7028/5 "2019-01-20T14:39:57Z")

</div>

Hi @Colin and @ukmoose , tks!  
I have doubts how can I need to make a structured file to storage the parameters to use after it in the command structure, for example:

**Device File Example**  
node\_name, nwk\_shortAdd, nwk\_ieeeAdd, EP  
RWL020, 3340, 00178801021a83e8, 02

All those parameter above I'll available to send the command when necessary.

Please, they can share w/ me more information how can I structure the device file to persist the parameters?

Best Regards,  
Alex

---

<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:** [20 January 2019 14:58 UTC](https://discourse.nodered.org/t/the-best-way-to-implement-device-database/7028/6 "2019-01-20T14:58:45Z")

</div>

Why not save them in persistent context as suggested? Why do you want the extra work of saving a file yourself?  
If you really do want a file then convert the data to json and save a json file.

---

<div class="post-metadata">

**Author:** ![aargollo](https://avatars.discourse-cdn.com/v4/letter/a/58f4c7/32.png) [@aargollo](https://discourse.nodered.org/u/aargollo)\
**Post date:** [20 January 2019 15:54 UTC](https://discourse.nodered.org/t/the-best-way-to-implement-device-database/7028/7 "2019-01-20T15:54:16Z")

</div>

Colin,  
I build a parser to get parameters during 1st stage (attaching procedure) and than I didn´t construct a code structure to support multiples devices during attaching in the same time, for each attached device I have the same global variable. To get around this, I have in my mind before persist in file check if I have device storage in the file, to do it I´ll use the unique address (IEEE address). Before storage the my code need to have a structure to check if device have in the file and than if do not have include it.

Would you can share a best article to explain well how to build JSON structure?

---

<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:** [20 January 2019 16:10 UTC](https://discourse.nodered.org/t/the-best-way-to-implement-device-database/7028/8 "2019-01-20T16:10:21Z")

</div>

> [@aargollo](#):
>
> Would you can share a best article to explain well how to build JSON structure?

The Json node will convert a javascript object to a json string.  
An alternative would be an sqlite database.

---

<div class="post-metadata">

**Author:** ![aargollo](https://avatars.discourse-cdn.com/v4/letter/a/58f4c7/32.png) [@aargollo](https://discourse.nodered.org/u/aargollo)\
**Post date:** [20 January 2019 16:39 UTC](https://discourse.nodered.org/t/the-best-way-to-implement-device-database/7028/9 "2019-01-20T16:39:24Z")

</div>

Hi Guys, I have progress to working with JSON structure in a file, using JSON node and File Node as they can see in the 2st part of my code below :

```auto
	if (debug==1){
         let data={"ModelID":ModelID,"IEEEAdd":I3EAdd,"NetworkAdd":nwkaddr1+nwkaddr2,"EP1":ep1,"IEEEAddS":I3EAdds,"NetworkAdd1S":nwkaddrs1,"NetworkAdd2S":nwkaddrs2,"EP1S":ep1s};
         node.log(typeof data);
         msg.payload=JSON.stringify(data);
         return msg;
     }else{
											    
         let d = new Date();
         let t =d.getTime();
         payload={"Time":t,"ModelID":ModelID,"IEEEAdd":I3EAdd,"NetworkAdd":nwkaddr1+nwkaddr2,"EP1":ep1,"IEEEAddS":I3EAdds,"NetworkAdd1S":nwkaddrs1,"NetworkAdd2S":nwkaddrs2,"EP1S":ep1s};
         msg.payload=payload;
         //msg.filename='home/pi/HomeGateway/nwkFilestorage.txt'
        return msg;
    }

```

The next step: need to check before to write on file if Device exists, and than if exist the device must not be written to the file!

I'll post here my improvements!  
best regards,  
Alex

---

<div class="post-metadata">

**Author:** ![drmibell](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/drmibell/32/8424_2.png) [@drmibell](https://discourse.nodered.org/u/drmibell)\
**Post date:** [21 January 2019 03:18 UTC](https://discourse.nodered.org/t/the-best-way-to-implement-device-database/7028/10 "2019-01-21T03:18:49Z")

</div>

As @ukmoose and @Colin suggest, you probably don't really NEED a file. Where you may have caused yourself a problem is

> [@aargollo](#):
>
> I didn´t construct a code structure to support multiples devices

If each device is described by an object containing its parameters (properties), you can just create an array of those objects. Then it is easy to add or delete devices, search the array to see whether a particular device has already been attached, and so on. The array can be saved in a persistent global variable each time it is modified and retrieved when needed. No file needed.

---

<div class="post-metadata">

**Author:** ![aargollo](https://avatars.discourse-cdn.com/v4/letter/a/58f4c7/32.png) [@aargollo](https://discourse.nodered.org/u/aargollo)\
**Post date:** [21 January 2019 10:14 UTC](https://discourse.nodered.org/t/the-best-way-to-implement-device-database/7028/11 "2019-01-21T10:14:56Z")

</div>

Hi drmibell, thanks a lot.  
Thanks for your answer, I really want to do as you, Colin and ukmoose said!  
I can´t understanding how to do it without a file, my code got the parameter (properties) from each device in the same variable, during attaching procedure. I using different nodes as you can see bellow:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/c/c2861afeaff7faa19157531a78d61dbf0b24b7fe.png)

For example, you can see in part of the code bellow how can i got Nwk Short Address:

```auto
 if (msg.payload[9]!=2){
                  if (msg.payload[10]!=2) {
                              let nwkaddrs1 = msg.payload[9].toString(16);
                              let nwkaddrs2 = msg.payload[10].toString(16);
                              global.set('NWKAd1',nwkaddrs1);
                              global.set('NWKAd2',nwkaddrs2);

```

Please, do you can advise me how to add JSON objects in the memory without persist it in the file?

---

<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:** [21 January 2019 10:48 UTC](https://discourse.nodered.org/t/the-best-way-to-implement-device-database/7028/12 "2019-01-21T10:48:40Z")

</div>

Have you checked what version of Node-RED you are using?  
If it is v0.19.x have you edited your settings.js fie change contextStorage to localfilesystem?

(see my first reply [The best way to implement device database](https://discourse.nodered.org/t/the-best-way-to-implement-device-database/7028/2?u=ukmoose))

If you have have you tried running your flow (in order to write the global context ) and then restarting Node-RED and seeing if you can access it?

---

<div class="post-metadata">

**Author:** ![drmibell](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/drmibell/32/8424_2.png) [@drmibell](https://discourse.nodered.org/u/drmibell)\
**Post date:** [21 January 2019 15:51 UTC](https://discourse.nodered.org/t/the-best-way-to-implement-device-database/7028/13 "2019-01-21T15:51:40Z")

</div>

The code fragment and partial flow you have posted pretty much confirm my suspicion that you are struggling with a self-inflicted problem. If the incoming `msg.payload` contains all or most of the information related to the device being registered, then a single function node should extract those parameters and create an object describing the device. That same function can get the array of devices from context storage, check to see if the new device is already registered and so on. I can see why you might have wanted to break the process apart and check that you are extracting the parameters correctly, but now I think you need to put the pieces back together.

---

<div class="post-metadata">

**Author:** ![aargollo](https://avatars.discourse-cdn.com/v4/letter/a/58f4c7/32.png) [@aargollo](https://discourse.nodered.org/u/aargollo)\
**Post date:** [21 January 2019 16:10 UTC](https://discourse.nodered.org/t/the-best-way-to-implement-device-database/7028/14 "2019-01-21T16:10:18Z")

</div>

Hi @ukmoose sorry for that! I´ll study more about context!  
I wrote your answer to below divided in two parts.

a) My Node-Red is v0.19.4. and I´m not changed nothing in my settings.js, the default original settings is:  
//contextStorage: {  
// default: {  
// module:"localfilesystem"  
// },  
I need to change it to localfilesystem?  
b) I restarted my node-red and than I got success to access global variables.

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/4/4c64c87fc16a09e82614de4299ed54e9950e8814.png)  
My challenge is to study in global context and understanding how to handling multiples devices in global variables, without to persist it in file.

Best Regards,  
Alex

---

<div class="post-metadata">

**Author:** ![JayDickson](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/jaydickson/32/90592_2.png) [@JayDickson](https://discourse.nodered.org/u/JayDickson)\
**Post date:** [21 January 2019 16:16 UTC](https://discourse.nodered.org/t/the-best-way-to-implement-device-database/7028/15 "2019-01-21T16:16:12Z")

</div>

It looks like you're storing each piece of data as its own object in context. It may be of some benefit to encode it in a manner in in such that each node on your zigbee mesh gets its own object and each piece of data exists as a property of the object.

---

<div class="post-metadata">

**Author:** ![aargollo](https://avatars.discourse-cdn.com/v4/letter/a/58f4c7/32.png) [@aargollo](https://discourse.nodered.org/u/aargollo)\
**Post date:** [21 January 2019 16:21 UTC](https://discourse.nodered.org/t/the-best-way-to-implement-device-database/7028/16 "2019-01-21T16:21:20Z")

</div>

Hi @drmibell,  
Thanks.  
I have put the pieces back in the last node (JSON Parameters Persistence) in my flow, as you can see part of my code inside this node bellow:

```auto
        }else if (msg.payload[1]==129){
            
            switch (msg.payload[2]){
                 case 2:{
  
                        /* Análise do xxxx */
                        if (msg.sliced.length >=1){
                            let nwkaddrs1 = global.get('NWKAdS1');
                            let nwkaddrs2 = global.get('NWKAdS2');
                            let nwkaddr1 = global.get('NWKAd1');
                            let nwkaddr2 = global.get('NWKAd2');
                            let ep1s = global.get('EP1s');
                            let ep2s = global.get('EP2s');
                            let ep1 = global.get('EP1');
                            let ep2 = global.get('EP2');
                            let I3E1=global.get('IEEE1');
							let I3E2=global.get('IEEE2');
							let I3E3=global.get('IEEE3');
							let I3E4=global.get('IEEE4');
							let I3E5=global.get('IEEE5');
							let I3E6=global.get('IEEE6');
							let I3E7=global.get('IEEE7');
							let I3E8=global.get('IEEE8');
							let I3EAdd = +I3E1+I3E2+I3E3+I3E4+I3E5+I3E6+I3E7+I3E8;
							let I3Eb1=global.get('IEEEb1');
							let I3Eb2=global.get('IEEEb2');
							let I3Eb3=global.get('IEEEb3');
							let I3Eb4=global.get('IEEEb4');
							let I3Eb5=global.get('IEEEb5');
							let I3Eb6=global.get('IEEEb6');
							let I3Eb7=global.get('IEEEb7');
							let I3Eb8=global.get('IEEEb8');
							let I3EAdds = +I3Eb1+I3Eb2+I3Eb3+I3Eb4+I3Eb5+I3Eb6+I3Eb7+I3Eb8;
                            let ModelID = msg.sliced;
                            global.set('ZCLModelID',ModelID);
                            	if (debug==1){
                                                let data={"ModelID":ModelID,"IEEEAdd":I3EAdd,"NetworkAdd":nwkaddr1+nwkaddr2,"EP1":ep1,"IEEEAddS":I3EAdds,"NetworkAdd1S":nwkaddrs1,"NetworkAdd2S":nwkaddrs2,"EP1S":ep1s};
                                                node.log(typeof data);
                                                msg.payload=JSON.stringify(data);
                                                return msg;
											}else{
											    
											    let d = new Date();
											    let t =d.getTime();
											    payload={"Time":d,"ModelID":ModelID,"IEEEAdd":I3EAdd,"NetworkAdd":nwkaddr1+nwkaddr2,"EP1":ep1,"IEEEAddS":I3EAdds,"NetworkAdd1S":nwkaddrs1,"NetworkAdd2S":nwkaddrs2,"EP1S":ep1s};
                                                msg.payload=payload;
                                                //msg.filename='home/pi/HomeGateway/nwkFilestorage.txt'
                                                return msg;
											}
                            
                            
                        
                        } else{
                            msg.payload="ERROR: Network elements ";
                        }

```

My challenge is storage multiple devices, as object, using global context in memory using my code. I have in my mind that my JSON array, in memory, something like:

```auto
"{ModelID": **DEVICE1** ,"IEEEAdd":I3EAdd,"NetworkAdd":nwkaddr1+nwkaddr2,"EP1",ModelID": **DEVICE2** ,"IEEEAdd":I3EAdd,"NetworkAdd":nwkaddr1+nwkaddr2,"EP1,ModelID": **DEVICEn** ,"IEEEAdd":I3EAdd,"NetworkAdd":nwkaddr1+nwkaddr2,"EP1}

```

---

<div class="post-metadata">

**Author:** ![JayDickson](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/jaydickson/32/90592_2.png) [@JayDickson](https://discourse.nodered.org/u/JayDickson)\
**Post date:** [21 January 2019 17:16 UTC](https://discourse.nodered.org/t/the-best-way-to-implement-device-database/7028/17 "2019-01-21T17:16:52Z")

</div>

Here's something to play around with for getting stuff formatted into a context variable:

`[{"id":"84c463eb.44b728","type":"inject","z":"460ed46c.d01004","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":380,"y":260,"wires":[["c946e83b.64cec8"]]},{"id":"6842bf84.7986c8","type":"debug","z":"460ed46c.d01004","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":830,"y":260,"wires":[]},{"id":"c946e83b.64cec8","type":"change","z":"460ed46c.d01004","name":"","rules":[{"t":"move","p":"payload","pt":"msg","to":"payload.name","tot":"msg"},{"t":"set","p":"payload.prop1","pt":"msg","to":"property1","tot":"str"},{"t":"set","p":"payload.prop2","pt":"msg","to":"prop2","tot":"str"},{"t":"set","p":"payload.prop3","pt":"msg","to":"property 3","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":540,"y":260,"wires":[["ac15f9ab.5163e8"]]},{"id":"ac15f9ab.5163e8","type":"function","z":"460ed46c.d01004","name":"","func":"var unique = String(msg.payload.name);\nvar prop1 = msg.payload.prop1;\nvar prop2 = msg.payload.prop2;\nvar prop3 = msg.payload.prop3;\n\n\nvar properties = {\n \"prop1\":prop1, \n \"prop2\":prop2, \n \"prop3\":prop3,\n};\n\nnode.send({\n \"uniqueIdentifier\":unique,\n \"properties\":properties});\nflow.set(unique,\n {\"uniqueIdentifier\":unique,\n \"properties\":properties});","outputs":1,"noerr":0,"x":710,"y":260,"wires":[["6842bf84.7986c8"]]}]`

It will obviously need tailoring to your particular use case, but as it stands it can be made to generate a new variable every time a new unique identifier is encountered, but update the properties every time an already seen UID is seen again.

![16%20PM](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/9/976b780db1e47b774308eb81b8c7b541002a577c.png) ![04%20PM](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/5/5a71de8d4ba8a1dea473c63cdd6781e5cb5f8022.png)

Note, that it could be a hell of a lot more elegant. This was bashed together between taking service calls at the office.

---

<div class="post-metadata">

**Author:** ![aargollo](https://avatars.discourse-cdn.com/v4/letter/a/58f4c7/32.png) [@aargollo](https://discourse.nodered.org/u/aargollo)\
**Post date:** [22 January 2019 09:30 UTC](https://discourse.nodered.org/t/the-best-way-to-implement-device-database/7028/18 "2019-01-22T09:30:05Z")

</div>

Tks JayDikson,  
I starting to working with context variable, as you can see bellow:  
I have change my node to get IEEEaddr, that will be working as uniqueIdentifier.

**Part of my code to create the UniqueID:**

```auto
payload={"uniqueIdentifier":I3EAdd};
msg.payload=payload;
return msg;

```

**My change node config.:**  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/f/faf185f1f26acc1885f42c6b72c8294781bd540c.png)

**My debug log:**  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/1/11619913b9085846ab7376147cc5be04d6d38131.png)

But, when I changed the function before change node to construct UniqueID with IEEEaddr and dont get another parameters, as I did before, I cant see anymore the variables in Context Data.  
I believe that I need to change config parameters in settings.js. I´m right? Do you can help me?

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/9/9b46c02f02f43973014926793eafc9ff29d0e237.png)

Best Regards,  
Alex

---

<div class="post-metadata">

**Author:** ![JayDickson](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/jaydickson/32/90592_2.png) [@JayDickson](https://discourse.nodered.org/u/JayDickson)\
**Post date:** [22 January 2019 14:55 UTC](https://discourse.nodered.org/t/the-best-way-to-implement-device-database/7028/19 "2019-01-22T14:55:15Z")

</div>

```auto
payload={"uniqueIdentifier":I3EAdd};
msg.payload=payload;
return msg;

```

It looks like your entire payload is just an object with "uniqueIdentifier" as the key and whatever the variable "I3Add" is defined as. We need to see more of your function to be able to tell you much more. Also, can you expand that object in your context store so we can see the properties and any sub objects?

---

<div class="post-metadata">

**Author:** ![JayDickson](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/jaydickson/32/90592_2.png) [@JayDickson](https://discourse.nodered.org/u/JayDickson)\
**Post date:** [22 January 2019 17:16 UTC](https://discourse.nodered.org/t/the-best-way-to-implement-device-database/7028/20 "2019-01-22T17:16:07Z")

</div>

Here, I did some cleanup on that function. Hopefully this makes a little more sense as I was taking lots of unnecessary steps. (Including having a redundant unique identifier within the variable itself.) I've also tweaked it to use msg.topic as the variable identifier to be a little more in line with other conventions.

```
// *********************************************************
//this section defines what will be stored in the variable 
//"out". This is an object (defined with the curly brackets)
out=
{
    "properties":{
        "prop1":msg.payload.prop1, 
        "prop2":msg.payload.prop2, 
        "prop3":msg.payload.prop3,
    }
};
// *********************************************************

// *********************************************************
//here we send the entire message, preserving any inbound data
node.send(msg);
// *********************************************************

// *********************************************************
//this bit actually stores the data in the "out" object to a flow variable, ignoring the rest of the message
//the inbound msg.topic determines the flow variable name used
flow.set(String(msg.topic),out)
// *********************************************************
```

[Next page](https://discourse.nodered.org/t/the-best-way-to-implement-device-database/7028.md?page=2)