Best practice mssql node

General
1

I'm using data from the database to update multiple screens every 5 min from the database.
To do this, I need to run several queries in the same database.

What is the best practice, make a flow for every query with his own MSSQL node, like this example (the query is in the inject node):

image
image571Ă—214 8.22 KB

Or do I use function blocks where I place the query in and connect them to the MSSQL node:

image
image685Ă—238 20.7 KB

I haven't tried the second way so I don't know if this works.
I noticed that having multiple MSSQL nodes that are triggered at the same time causes problems.

Thanks for any ideas :slight_smile:
Filip Top

2

Typically, I would not do this in parallel - but even more-so not with the OLD, less capable, very buggy, potentially dangerously out-of-date MSSQL node.

Make sure you are using the in-support, up-to-date node-red-contrib-mssql-plus node

As for other recommendations, don't use {{var}} syntax in your queries - always use parameters (to avoid risk of SQL injections)

3

I concur with @Steve-Mcl that it is not a good I idea to trigger concurrent queries. What I always do is define a generic node-group which is reused across all flows via link-calls.
In that group I put some standard protection & error management, e.g., a rate limiter (ensuring a minimum "breather" between queries), error catching/handling, retries etc.

4

And... be sure to observe the best practices for query creation and execution... injection attacks for example can be a key issue using SQL. Never hurts to structure SQL queries so they are not obviously vulnerable.

5

Thank you all for the input, where some very good idea's.

I installed the newer node-red-contrib-mssql-plus and found it a very good idea to build a subflow for connecting to the database. This way I have only one place to be if something changes. But I don't really know how to optimize it, so I build something simple like this:

image
image772Ă—152 15.1 KB

Does this make sense or is it the most stupid idea?

6

when you put a node like this (in a subflow) each instance of the subflow will create a separate connection to the database. If this is azure, you can easily hit connection limits.

You are better off using link-call as pointed out by @omrid

example: Can Node Mssql running in many flow? - #5 by bakman2

7

Thank you Steve. It was not clear to me what the difference was between a subflow and a link call.
So I have this in the end:

image
image713Ă—370 38.8 KB

Two more questions:

  1. What is the best place to put the group? I have it now on my flow, but I have multiple flows. Do I make a flow only for this one, or can I still put it in a subflow? (or is it than creating multiple connections)
  2. The status (like here done), can I show it in the link call.

I know, a lot of questions but I try to get it right from the first time.
Thanks

8

You can put the link call group (with the db node) on a separate flow tab. It does not need to be on the same tab as the calling flow.

1 Like
9
  1. You can place the node-group anywhere you wish (even in its own tab) and call it from any flow tab in the editor. I recommend calling the entry point (link-in) of the group it with a dynamic address (msg.target), not by node Id, to protect against Id changes (import, copy/paste etc.).
  2. I am not familiar with a way to show status on a link-call. @Steve-Mcl ?
  3. I would move the split node outside of the group.
1 Like