# Visualize Data from Database to Chart

**URL:** <https://discourse.nodered.org/t/visualize-data-from-database-to-chart/46018>\
**Category:** Dashboard\
**Created:** [21 May 2021 07:57 UTC](https://discourse.nodered.org/t/visualize-data-from-database-to-chart/46018 "2021-05-21T07:57:18Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![ChrisTs99](https://avatars.discourse-cdn.com/v4/letter/c/779978/32.png) [@ChrisTs99](https://discourse.nodered.org/u/ChrisTs99)\
**Post date:** [21 May 2021 07:57 UTC](https://discourse.nodered.org/t/visualize-data-from-database-to-chart/46018/1 "2021-05-21T07:57:19Z")

</div>

Hello,  
I am new to Node-Red, and I am facing a simple problem. Connection of my nodes is explained bellow. There is an inject node which is my matrix: "CREATE TABLE RANDOMNUM( TIMESTAMP INT PRIMARY KEY NOT NULL, VALUE INT NOT NULL, BOOL INT NOT NULL)". This node is connected to a "litedb" node and "litedb" is connected to a debug node. There is another inject node which is connected to the following function.

var randomNum = Math.round(Math.random()\*100);  
var largeBool = (randomNum \> 50?1:0);  
var newMsg = {  
"topic": "INSERT INTO RANDOMNUM VALUES ( "+msg.payload+","+randomNum+","+largeBool+")"}  
return newMsg;

This function is connected as well to the litedb node and what I want to do is to visualize the variable "newMsg" in a chart. I have tried different connections with chart but it none of them works.

Thanks for your time, appreciate your help !

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/d/fd1eeca5093088a6d3367ab33b3c37bc7d672df4.png)

---

<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 May 2021 09:14 UTC](https://discourse.nodered.org/t/visualize-data-from-database-to-chart/46018/3 "2021-05-21T09:14:41Z")

</div>

> [@ChrisTs99](#):
>
> This node is connected to a "litedb" node

By 'litedb' are you refering to sqlite??  
When explaining a issue, it is helpful (and time saving for anyone going to give you some of their time) to provide the name of the exact node you are using like: The flow is using node-red-node-sqlite.

What is the debug node showing when you insert a value? (please change the debug node to show the 'complete msg object')

---

<div class="post-metadata">

**Author:** ![ChrisTs99](https://avatars.discourse-cdn.com/v4/letter/c/779978/32.png) [@ChrisTs99](https://discourse.nodered.org/u/ChrisTs99)\
**Post date:** [21 May 2021 09:19 UTC](https://discourse.nodered.org/t/visualize-data-from-database-to-chart/46018/4 "2021-05-21T09:19:18Z")

</div>

Excuse me for my wrong formulation, indeed I am refering to sqlite. The debug node shows the values bellow.

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/8/485a21b53a00fbbdb578a3d2513d1e8095d23ef2.png)

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [21 May 2021 09:27 UTC](https://discourse.nodered.org/t/visualize-data-from-database-to-chart/46018/5 "2021-05-21T09:27:32Z")

</div>

INSERT doesn't SELECT data from the database.

Are you wanting to add the incoming value to the chart - or - SELECT data from the database to display many points on the chart in one go?

---

<div class="post-metadata">

**Author:** ![ChrisTs99](https://avatars.discourse-cdn.com/v4/letter/c/779978/32.png) [@ChrisTs99](https://discourse.nodered.org/u/ChrisTs99)\
**Post date:** [21 May 2021 09:29 UTC](https://discourse.nodered.org/t/visualize-data-from-database-to-chart/46018/6 "2021-05-21T09:29:31Z")

</div>

Display many points on the chart in one go. Thanks for your help!

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [21 May 2021 09:30 UTC](https://discourse.nodered.org/t/visualize-data-from-database-to-chart/46018/7 "2021-05-21T09:30:55Z")

</div>

> [@ChrisTs99](#):
>
> Display many points on the chart in one go

Then you need to do a SELECT against the database & then you need to format the returned rows of data to match the described format in the built-in help on the chart node.

NOTE: the SELECT should have a suitable WHERE / TOP / LIMIT clause to avoid sending more data to the chart than there are pixels (e.g. if you try to display 10000 points on a 400px chart - not only will it be pointless but it will make your browser crawl)

---

<div class="post-metadata">

**Author:** ![ChrisTs99](https://avatars.discourse-cdn.com/v4/letter/c/779978/32.png) [@ChrisTs99](https://discourse.nodered.org/u/ChrisTs99)\
**Post date:** [21 May 2021 09:34 UTC](https://discourse.nodered.org/t/visualize-data-from-database-to-chart/46018/8 "2021-05-21T09:34:14Z")

</div>

I was thinking about creating a new function which will have only selected values from the previous function, for example "randomNum" and "largeBool". I want these to values to be visualized in a chart but I don't know how to write the code for this. Its simple but I am confused.

Thanks again for your time.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [21 May 2021 09:37 UTC](https://discourse.nodered.org/t/visualize-data-from-database-to-chart/46018/9 "2021-05-21T09:37:17Z")

</div>

To add points to a chart at the same time as INSERTing to database, just send a msg with the topic and payload set.

e.g. your "write query" function you could return these values out of a 2nd output wired directly to the chart.

Share your function code.

---

<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 May 2021 09:51 UTC](https://discourse.nodered.org/t/visualize-data-from-database-to-chart/46018/10 "2021-05-21T09:51:51Z")

</div>

I recommend watching this playlist: [Node-RED Essentials](https://www.youtube.com/playlist?list=PLyNBB9VCLmo1hyO-4fIZ08gqFcXBkHy-6). The videos are done by the developers of node-red. They're nice & short and to the point. You will understand a whole lot more in about 1 hour. A small investment for a lot of gain.

There’s a great page in the docs that will explain how to use the debug panel to find the right path to any data item.

Pay particular attention to the part about the buttons that appear under your mouse pointer when you over hover a debug message property in the sidebar.

![BX00Cy7yHi](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/d/bd1f333a9e043b061b39917c328b0094d3e52d30.gif)

[https://nodered.org/docs/user-guide/messages](https://nodered.org/docs/user-guide/messages)

---

<div class="post-metadata">

**Author:** ![ChrisTs99](https://avatars.discourse-cdn.com/v4/letter/c/779978/32.png) [@ChrisTs99](https://discourse.nodered.org/u/ChrisTs99)\
**Post date:** [21 May 2021 10:33 UTC](https://discourse.nodered.org/t/visualize-data-from-database-to-chart/46018/11 "2021-05-21T10:33:42Z")

</div>

var randomNum = Math.round(Math.random()\*100);  
var largeBool = (randomNum \> 50?1:0);  
var newMsg = {  
"topic": "INSERT INTO RANDOMNUM VALUES ( "+msg.payload+","+randomNum+","+largeBool+")"}  
return newMsg;

my function code is this, but I am still confused. Thanks for you reply!

---

<div class="post-metadata">

**Author:** ![ChrisTs99](https://avatars.discourse-cdn.com/v4/letter/c/779978/32.png) [@ChrisTs99](https://discourse.nodered.org/u/ChrisTs99)\
**Post date:** [21 May 2021 10:34 UTC](https://discourse.nodered.org/t/visualize-data-from-database-to-chart/46018/12 "2021-05-21T10:34:29Z")

</div>

I will thank you!

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [21 May 2021 10:40 UTC](https://discourse.nodered.org/t/visualize-data-from-database-to-chart/46018/13 "2021-05-21T10:40:59Z")

</div>

Try this...  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/a/5a0cc0d8bcd1567a1a255366613541d0fcc576dc.png)

Demo function to import... (use CTRL+I)

```auto
[{"id":"19bd1cee.2a2653","type":"function","z":"553814a2.1248ec","name":"","func":"var randomNum = Math.round(Math.random() * 100);\nvar largeBool = (randomNum > 50 ? 1 : 0);\nvar newMsg = {\n \"topic\": \"INSERT INTO RANDOMNUM VALUES ( \" + msg.payload + \",\" + randomNum + \",\" + largeBool + \")\"\n}\n\nvar msg2 = {\n topic: \"number\",\n payload: randomNum\n}\nvar msg3 = {\n topic: \"bool\",\n payload: largeBool\n}\n\nreturn [newMsg, msg2, msg3];\n\n","outputs":3,"noerr":0,"initialize":"","finalize":"","libs":[],"x":930,"y":1580,"wires":[["697d7863.8f00a8"],["a5b75a41.540018"],["c167501b.98c19"]]}]

```

The function code (with comments to help you understand)...

```auto
var randomNum = Math.round(Math.random() * 100);
var largeBool = (randomNum > 50 ? 1 : 0);
var newMsg = {
    "topic": "INSERT INTO RANDOMNUM VALUES ( " + msg.payload + "," + randomNum + "," + largeBool + ")"
}

//create a msg with topic and payload set - for the number
var msg2 = {
    topic: "number",
    payload: randomNum
}

//create a msg with topic and payload set - for the bool
var msg3 = {
    topic: "bool",
    payload: largeBool
}

//return the 3 messages (array element 0 goes to output 1, element 1 goes to output 2 etc)
return [newMsg, msg2, msg3];

```

---

<div class="post-metadata">

**Author:** ![ChrisTs99](https://avatars.discourse-cdn.com/v4/letter/c/779978/32.png) [@ChrisTs99](https://discourse.nodered.org/u/ChrisTs99)\
**Post date:** [21 May 2021 12:10 UTC](https://discourse.nodered.org/t/visualize-data-from-database-to-chart/46018/14 "2021-05-21T12:10:11Z")

</div>

Thanks for your help! It was that simple, I am new to Node-Red and I guess I need to watch the tutorial above... Appreciate it buddy!

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [20 June 2021 12:10 UTC](https://discourse.nodered.org/t/visualize-data-from-database-to-chart/46018/15 "2021-06-20T12:10:19Z")

</div>

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.