# How to create table with SQLite

**URL:** <https://discourse.nodered.org/t/how-to-create-table-with-sqlite/77661>\
**Category:** General\
**Created:** [17 April 2023 14:02 UTC](https://discourse.nodered.org/t/how-to-create-table-with-sqlite/77661 "2023-04-17T14:02:39Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Nicklas](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/nicklas/32/74447_2.png) [@Nicklas](https://discourse.nodered.org/u/Nicklas)\
**Post date:** [17 April 2023 14:02 UTC](https://discourse.nodered.org/t/how-to-create-table-with-sqlite/77661/1 "2023-04-17T14:02:39Z")

</div>

Hello there,  
I'm relatively new to Node-Red, and I'm trying to create a table with values from SQLite, my problem is that the values are not in the table, my SQLite statement works fine but when it comes to the template node it just doesn't create a row. I'm pretty sure the problem is with the template node, but I don't know where, I hope someone can help me.

Edit:  
For the SQLite DB im using "node-red-node-sqlite"

template node:

```auto
<body>
  <table style="width:100%" id="myTable">
    <thead>
      <tr>
        <th>_dateUTC_</th>
        <th>_dateLocal_</th>
        <th>_timePeriod_</th>
        <th>LB.RS1.CN</th>
        <th>LB.RS2.CN</th>
        <th>LB.RS3.CN</th>
        <th>LB.HM40811.CN</th>
        <th>LB.HM40810.CN</th>
      </tr>
    </thead>
    <tbody>
      {{#each msg.payload}}
      <tr>
        <td>{{this._dateLocal_}}</td>
        <td>{{this._dateUTC_}}</td>
        <td>{{this._timePeriod_}}</td>
        <td>{{this["LB.RS1.CN"]}}</td>
        <td>{{this["LB.RS2.CN"]}}</td>
        <td>{{this["LB.RS3.CN"]}}</td>
        <td>{{this["LB.HM40811.CN"]}}</td>
        <td>{{this["LB.HM40810.CN"]}}</td>
      </tr>
      {{/each msg.payload}}
    </tbody>
  </table>
</body>

```

Template node configuration:

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

DB structure:  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/3/c336a9365096a3f5ab6f8f8c7eaf3e45ae30961f.png)

Flow:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/6/16c0ae5417d60d76660eb3f20ebd4d2fd3c12003.png)

```auto
[{"id":"6f98e7b14ffbc67d","type":"sqlite","z":"56fc7d74926adf91","mydb":"5ca7db8ffd6c58ac","sqlquery":"msg.topic","sql":"SELECT * FROM Allgemein;","name":"Counter DB","x":710,"y":1720,"wires":[["a10e02a83c543357"]]},{"id":"25bfb2b8105c07ff","type":"http in","z":"56fc7d74926adf91","name":"","url":"/table-data","method":"get","upload":false,"swaggerDoc":"","x":700,"y":1660,"wires":[["a10e02a83c543357"]]},{"id":"3aa0245fb8a7805f","type":"http response","z":"56fc7d74926adf91","name":"","statusCode":"","headers":{},"x":1150,"y":1660,"wires":[]},{"id":"a10e02a83c543357","type":"template","z":"56fc7d74926adf91","name":"try table","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"<body>\n <table style=\"width:100%\" id=\"myTable\">\n <thead>\n <tr>\n <th>_dateUTC_</th>\n <th>_dateLocal_</th>\n <th>_timePeriod_</th>\n <th>LB.RS1.CN</th>\n <th>LB.RS2.CN</th>\n <th>LB.RS3.CN</th>\n <th>LB.HM40811.CN</th>\n <th>LB.HM40810.CN</th>\n </tr>\n </thead>\n <tbody>\n {{#each msg.payload}}\n <tr>\n <td>{{this._dateLocal_}}</td>\n <td>{{this._dateUTC_}}</td>\n <td>{{this._timePeriod_}}</td>\n <td>{{this[\"LB.RS1.CN\"]}}</td>\n <td>{{this[\"LB.RS2.CN\"]}}</td>\n <td>{{this[\"LB.RS3.CN\"]}}</td>\n <td>{{this[\"LB.HM40811.CN\"]}}</td>\n <td>{{this[\"LB.HM40810.CN\"]}}</td>\n </tr>\n {{/each msg.payload}}\n </tbody>\n </table>\n</body>","output":"str","x":940,"y":1660,"wires":[["3aa0245fb8a7805f","be1dc158f4b5f12d"]]},{"id":"be1dc158f4b5f12d","type":"debug","z":"56fc7d74926adf91","name":"debug 1","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1160,"y":1720,"wires":[]},{"id":"31f1719a56a83c26","type":"inject","z":"56fc7d74926adf91","name":"","props":[{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":true,"onceDelay":0.1,"topic":"SELECT * FROM Allgemein","x":440,"y":1720,"wires":[["6f98e7b14ffbc67d"]]},{"id":"5ca7db8ffd6c58ac","type":"sqlitedb","db":"/table-data/sqlite","mode":"RO"}]

```

localhost:1880/table-data:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/a/1a995389118bfe555218d1930e66fc109597a138.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:** [17 April 2023 14:11 UTC](https://discourse.nodered.org/t/how-to-create-table-with-sqlite/77661/2 "2023-04-17T14:11:30Z")

</div>

> [@Nicklas](#):
>
> e can help me.

Given data in `msg.payload`:

```auto
[{"value":"foo"},{"value":"bar"},{"value":"baz"}] 

```

your template would be:

```auto
<tbody>
  {{#payload}}
      <tr>
        <td>{{value}}</td>
      </tr>
  {{/payload}}
</tbody>

```

Try applying your field names to the template as shown ↑

More reading here: [mustache(5) - Logic-less templates.](http://mustache.github.io/mustache.5.html) (as linked in the built in help of the template node)

---

<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:** [17 April 2023 14:26 UTC](https://discourse.nodered.org/t/how-to-create-table-with-sqlite/77661/3 "2023-04-17T14:26:22Z")

</div>

Am I right in thinking that `<body> .. </body>` should not be present in the template?

---

<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:** [17 April 2023 14:30 UTC](https://discourse.nodered.org/t/how-to-create-table-with-sqlite/77661/4 "2023-04-17T14:30:32Z")

</div>

This particular example, the op is not using dashboard (has created an endpoint) and so needs to provide the whole kit and caboodle from html to /html

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [17 April 2023 14:31 UTC](https://discourse.nodered.org/t/how-to-create-table-with-sqlite/77661/5 "2023-04-17T14:31:31Z")

</div>

Further to Steve's points  
Mustache property names can not have periods in property names.  
I would change the sql query to rename the properties  
e.g.

```auto
SELECT 
  _dateLocal_, 
  _dateUTC_ ,
  _timePeriod_,
  LB.RS1.CN AS LBRS1CN,
  LB.RS2.CN AS LBRS2CN,
  LB.RS3.CN AS LBRS3CN,
  LB.HM40811.CN AS LBHM40811CN,
  LB.HM40810.CN AS LBHM40810CN
FROM 
  Allgemein

```

And the template would be

```auto

  <table style="width:100%" id="myTable">
    <thead>
      <tr>
        <th>_dateUTC_</th>
        <th>_dateLocal_</th>
        <th>_timePeriod_</th>
        <th>LB.RS1.CN</th>
        <th>LB.RS2.CN</th>
        <th>LB.RS3.CN</th>
        <th>LB.HM40811.CN</th>
        <th>LB.HM40810.CN</th>
      </tr>
    </thead>
    <tbody>
      {{#payload}}
      <tr>
        <td>{{_dateLocal_}}</td>
        <td>{{_dateUTC_}}</td>
        <td>{{_timePeriod_}}</td>
        <td>{{LBRS1CN}}</td>
        <td>{{LBRS2CN}}</td>
        <td>{{LBRS3CN}}</td>
        <td>{{LBHM40811CN}}</td>
        <td>{{LBHM40810CN}}</td>
      </tr>
      {{/payload}}
    </tbody>
  </table>

```

You can add body tags if you are not using ui-template node  
Finally the sql node needs to be between the http in and response node.  
@Colin I believe that is only ui-template.

---

<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:** [17 April 2023 15:51 UTC](https://discourse.nodered.org/t/how-to-create-table-with-sqlite/77661/6 "2023-04-17T15:51:22Z")

</div>

> [@Steve-Mcl](#):
>
> op is not using dashboard

True, I had missed that.

---

<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:** [1 May 2023 15:51 UTC](https://discourse.nodered.org/t/how-to-create-table-with-sqlite/77661/7 "2023-05-01T15:51:58Z")

</div>

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