# SQLite node possible bug, how to workaround?

**URL:** <https://discourse.nodered.org/t/sqlite-node-possible-bug-how-to-workaround/10027>\
**Category:** General\
**Created:** [11 April 2019 14:49 UTC](https://discourse.nodered.org/t/sqlite-node-possible-bug-how-to-workaround/10027 "2019-04-11T14:49:56Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![swiftnesses](https://avatars.discourse-cdn.com/v4/letter/s/f17d59/32.png) [@swiftnesses](https://discourse.nodered.org/u/swiftnesses)\
**Post date:** [11 April 2019 14:49 UTC](https://discourse.nodered.org/t/sqlite-node-possible-bug-how-to-workaround/10027/1 "2019-04-11T14:49:56Z")

</div>

Hi Everyone,

I've spend the last couple of days getting to know the SQLite node and attempting to apply it to a use case I'm trying to solve. Sadly, it seems a possible bug is thwarting my efforts, please see:

> <https://github.com/node-red/node-red-nodes/issues/533>

Is there a way I can workaround this issue, I'm really keen to use the functionality of the prepared node (it's clean and allows me oversight of my query), perhaps it's even user error and not a bug!

Help would be appreciated.

Thanks 🙂

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [11 April 2019 14:56 UTC](https://discourse.nodered.org/t/sqlite-node-possible-bug-how-to-workaround/10027/2 "2019-04-11T14:56:04Z")

</div>

Have you tried converting the value you are inserting to a string before passing it to the node?

---

<div class="post-metadata">

**Author:** ![swiftnesses](https://avatars.discourse-cdn.com/v4/letter/s/f17d59/32.png) [@swiftnesses](https://discourse.nodered.org/u/swiftnesses)\
**Post date:** [11 April 2019 15:11 UTC](https://discourse.nodered.org/t/sqlite-node-possible-bug-how-to-workaround/10027/3 "2019-04-11T15:11:00Z")

</div>

Yes, I tried...

```auto
var chat_id = 1111111111111111
chat_id.toString()

```

No change sadly...

---

<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:** [11 April 2019 15:11 UTC](https://discourse.nodered.org/t/sqlite-node-possible-bug-how-to-workaround/10027/4 "2019-04-11T15:11:33Z")

</div>

It isn't appending .0 it has just made a guess at how many decimal places you might want. Use `toFixed()`.

---

<div class="post-metadata">

**Author:** ![swiftnesses](https://avatars.discourse-cdn.com/v4/letter/s/f17d59/32.png) [@swiftnesses](https://discourse.nodered.org/u/swiftnesses)\
**Post date:** [11 April 2019 15:12 UTC](https://discourse.nodered.org/t/sqlite-node-possible-bug-how-to-workaround/10027/5 "2019-04-11T15:12:34Z")

</div>

Hi Colin,

I assumed my understanding wasn't correct - thanks for clearing that up. I'll try your suggestion now.

Why do I not see this behaviour when running the same query via other tools?

---

<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:** [11 April 2019 15:13 UTC](https://discourse.nodered.org/t/sqlite-node-possible-bug-how-to-workaround/10027/6 "2019-04-11T15:13:54Z")

</div>

I guess that the node is using toString() to convert it. What other tool are you using that does something different? Note that if you use a command line client for example then you are passing it a string, not a number.

---

<div class="post-metadata">

**Author:** ![swiftnesses](https://avatars.discourse-cdn.com/v4/letter/s/f17d59/32.png) [@swiftnesses](https://discourse.nodered.org/u/swiftnesses)\
**Post date:** [11 April 2019 15:15 UTC](https://discourse.nodered.org/t/sqlite-node-possible-bug-how-to-workaround/10027/7 "2019-04-11T15:15:50Z")

</div>

I tried command line and some Mac SQLite applications to run the same query, never had the same issue, regardless, that's not proof there's an issue, merely an observation.

Further, it only happens with 'long' numbers, like the one in my example I posted to GitHub, I've not quite tracked down at what point it starts to add the '.0'

---

<div class="post-metadata">

**Author:** ![swiftnesses](https://avatars.discourse-cdn.com/v4/letter/s/f17d59/32.png) [@swiftnesses](https://discourse.nodered.org/u/swiftnesses)\
**Post date:** [11 April 2019 15:17 UTC](https://discourse.nodered.org/t/sqlite-node-possible-bug-how-to-workaround/10027/8 "2019-04-11T15:17:46Z")

</div>

I tried this and the prepared node:

```auto
var chatid = 1111111111111111
chatid.toFixed(0)
node.error(chatid)

msg.params = {
    $message_id:1111,
    $user_id:1111,
    $chat_id:chatid,
    $verification_status:1111
}

return msg

```

chat\_id shows in the the table as: 1.11111111111111e+15 ☹

---

<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:** [11 April 2019 15:18 UTC](https://discourse.nodered.org/t/sqlite-node-possible-bug-how-to-workaround/10027/9 "2019-04-11T15:18:18Z")

</div>

Note that storing it with a trailing .0 is not incorrect. It is a perfectly valid representation of the number. If you read it back out again and convert it to a number you will end up with the number you started with. Javascript does not have the concept of integers and floats, they are all just numbers.

---

<div class="post-metadata">

**Author:** ![swiftnesses](https://avatars.discourse-cdn.com/v4/letter/s/f17d59/32.png) [@swiftnesses](https://discourse.nodered.org/u/swiftnesses)\
**Post date:** [11 April 2019 15:21 UTC](https://discourse.nodered.org/t/sqlite-node-possible-bug-how-to-workaround/10027/10 "2019-04-11T15:21:01Z")

</div>

So, 111111111111 becomes 111111111111.0  
1111111111111111 becomes 1.11111111111111e+15

P.S. I'm not a coder, so feel free to slap me 🤣

---

<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:** [11 April 2019 15:24 UTC](https://discourse.nodered.org/t/sqlite-node-possible-bug-how-to-workaround/10027/11 "2019-04-11T15:24:11Z")

</div>

Are you seeing that with .toString(). I don't. This in a function node displays the numbers as they are entered.

```auto
x = 11111;
node.warn(x.toString())
x = 11111111;
node.warn(x.toString())
x = 11111111111;
node.warn(x.toString())
x = 111111111111;
node.warn(x.toString())
x = 11111111111111;
node.warn(x.toString())
x = 1111111111111111;
node.warn(x.toString())
return msg;

```

What version of nodejs are you using?  
`node -v`

---

<div class="post-metadata">

**Author:** ![swiftnesses](https://avatars.discourse-cdn.com/v4/letter/s/f17d59/32.png) [@swiftnesses](https://discourse.nodered.org/u/swiftnesses)\
**Post date:** [11 April 2019 15:26 UTC](https://discourse.nodered.org/t/sqlite-node-possible-bug-how-to-workaround/10027/12 "2019-04-11T15:26:55Z")

</div>

> [@Colin](#):
>
> x = 11111; node.warn(x.toString()) x = 11111111; node.warn(x.toString()) x = 11111111111; node.warn(x.toString()) x = 111111111111; node.warn(x.toString()) x = 11111111111111; node.warn(x.toString()) x = 1111111111111111; node.warn(x.toString()) return msg;

No, I don't see it with the function node and your example.

I'm running the latest docker which includes Node v8.15.1

---

<div class="post-metadata">

**Author:** ![swiftnesses](https://avatars.discourse-cdn.com/v4/letter/s/f17d59/32.png) [@swiftnesses](https://discourse.nodered.org/u/swiftnesses)\
**Post date:** [11 April 2019 15:29 UTC](https://discourse.nodered.org/t/sqlite-node-possible-bug-how-to-workaround/10027/13 "2019-04-11T15:29:16Z")

</div>

It's worth noting I only see the behaviour with the 'prepared' node, it works fine if I inject the query via the msg.topic or used the 'fixed' 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:** [11 April 2019 15:31 UTC](https://discourse.nodered.org/t/sqlite-node-possible-bug-how-to-workaround/10027/14 "2019-04-11T15:31:09Z")

</div>

You said you saw it with toString().

---

<div class="post-metadata">

**Author:** ![swiftnesses](https://avatars.discourse-cdn.com/v4/letter/s/f17d59/32.png) [@swiftnesses](https://discourse.nodered.org/u/swiftnesses)\
**Post date:** [11 April 2019 15:32 UTC](https://discourse.nodered.org/t/sqlite-node-possible-bug-how-to-workaround/10027/15 "2019-04-11T15:32:47Z")

</div>

Argh, only when used with the node, i.e. to build the msg.params...

To be clear, debug terminal never shows an issue, only the final value at the table is wrong.

I assume the issue occurs between the 'prepared' node receiving the msg.parms, and converting them, before it applies the query. Hence my assumption that it's an issue with the SQLite node somewhere (a pretty uneducated assumption, but it's all I have!).

---

<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:** [11 April 2019 15:35 UTC](https://discourse.nodered.org/t/sqlite-node-possible-bug-how-to-workaround/10027/16 "2019-04-11T15:35:22Z")

</div>

So if you put a function node to convert it with toString() and then feed that into the node it doesn't put in the db the string generated by toString()?

---

<div class="post-metadata">

**Author:** ![swiftnesses](https://avatars.discourse-cdn.com/v4/letter/s/f17d59/32.png) [@swiftnesses](https://discourse.nodered.org/u/swiftnesses)\
**Post date:** [11 April 2019 15:36 UTC](https://discourse.nodered.org/t/sqlite-node-possible-bug-how-to-workaround/10027/17 "2019-04-11T15:36:53Z")

</div>

Well, the msg.params is built with a function node, so I'm not sure I can do much more, I tried toFixed() and toString() there.

My example is in the GitHub issue, it's pretty simple if you want to try (I have a feeling it's a JS thing that I don't understand...).

---

<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:** [11 April 2019 15:39 UTC](https://discourse.nodered.org/t/sqlite-node-possible-bug-how-to-workaround/10027/18 "2019-04-11T15:39:36Z")

</div>

To save me getting the whole flow can you just post the function please? I presume you have put a debug on the output of that to see what it is showing.

---

<div class="post-metadata">

**Author:** ![hotNipi](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/hotnipi/32/383_2.png) [@hotNipi](https://discourse.nodered.org/u/hotNipi)\
**Post date:** [11 April 2019 15:40 UTC](https://discourse.nodered.org/t/sqlite-node-possible-bug-how-to-workaround/10027/19 "2019-04-11T15:40:53Z")

</div>

What is the reason, you are using a number for \_id?  
It will be much more safe if you generate unique string. Yes that may fail as random is random but this is rare and can be predicted.

Something like  
`var id = "chat_"+Math.random().toString(36).substr(2, 6)+"."+Math.random().toString(36).substr(3, 7);`

---

<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:** [11 April 2019 15:45 UTC](https://discourse.nodered.org/t/sqlite-node-possible-bug-how-to-workaround/10027/20 "2019-04-11T15:45:42Z")

</div>

Just looked back at your code and see

> [@swiftnesses](#):
>
> var chatid = 1111111111111111  
> chatid.toFixed(0)

The second line converts it to a string but doesn't save it. You need  
`chatid = chatid.toFixed(0)`

[Next page](https://discourse.nodered.org/t/sqlite-node-possible-bug-how-to-workaround/10027.md?page=2)