SQLite - Add support for mustache in fixed statement

Core Development Feature Requests
1

Like in MSSQL node, with this solution there is no need to use additional node for query gluing.

2

One of the concepts of having nodes is that each node should do a defined job and do it well. Conversely all nodes shouldn't try to cram in every possible way of doing things.

As we already have the template node that handles mustache well then the simple and more readable solution is to use that before the sqlite node...

3

Just change an abstraction level and it still will do :slight_smile:
Database node as whole to be functional needs:

  • query
  • query execution
  • result based route (could be one or more outputs)

A lot of my use cases, this granular concept makes flow messy:

  • functional node (query definition) gets name like db node (but point is just that there are two nodes to achieve one operation)

and hard to read:

  • moreover a lot of ifs (function nodes) based on sql query result - ie. if (SELECT COUNT(*) == 0) then output 1 else output 2

I've pasted example here:

4

Let's not have the same discussion to two separate topics.

1 Like
5

I think this is actually two threads - one re mustache on input and one re some processing on exit.

Assuming this one is still about the input side... I have to ask why support mustache in the fixed statement - when we already support passing in parameters to a prepared statement ? This is MUCH safer from an sql injection point of view.

(see https://github.com/mapbox/node-sqlite3/wiki/API#databaserunsql-param--callback for format of the msg.params )

1 Like
6

I know this solution from MSSQL node, which I remember handy.
The point is to have editor in db node. Before you asked, I thought about it, how to resolve problem and how to do it with prepared statement and today I needed MSSQL node but looking for other because previous has some bugs and found this one https://github.com/atsage/node-red-contrib-mssql-ps. This is (almost) exactly what I'm talking about. I've asked author why not define type parameters in msg.params (why mess with node UI UX). I didn't see the code, and not even check if this works, but from UX view, this is it.
Just add switch node (indeed is more proper here than function node) in to it and we are at home - one node to rule them three :wink:

7

So is this node not basically the same UI as the sqlite prepared statement mode ?

8

Haha, yes it is. Thanks for your time, I have no idea why ... sometimes you just have to grab the handle and open the door - sometimes I just forget :wink:

9

OK - No problem :slight_smile:
image

3 Likes
10

Probably my favourite :slight_smile: