Using database nodes in most cases is related with two additional function nodes, one with SQL query input and second for parsing and routing result msg to different outputs.
So for four operations on database you need to add eight additional nodes what makes flow messy and hard to read.
.... so what would the function editor be used for? Can you give an example?
Would it be to set the query in the node itself? Or to do something with the result? It is not clear what you are proposing.
To get rid of additional function node for query I've created proposal to add mustache support to fixed statement editor: SQLite - Add support for mustache in fixed statement
To get rid of additional function node for result parsing and routing I propose here to add function editor - so for example I could route msg to different output based on "SELECT COUNT(*)" result (if ie. UserID not exists return 404 status code, else process further).
Can you provide an real flow that would benefit from the suggestion, so we can better understand.
That simply isn't the node-red way of designing a node. Each node has a well defined purpose and you use multiple nodes to create your flow.
The SQL node runs a query and returns a result - that is it's well defined purpose. That's it.
If the sqlite node had a built in code editor, then the expectation would be all database nodes have that built in. That means a lot of duplicated code and functionality - and also functionality that has to be maintained as new features are added to the code nodes. It is better to focus that functionality in the individual nodes that are designed for that purpose.
1 Like
I've did integration with two external APIs where flow is getting invoices in pdf from one side, parsing it (extract customer data, items and so on) and store in local db (SQLite), and then based on local db it uses second API to get (look for duplicates) and create documents in ERP (Purchase of Receipt and Purchase Invoice).
Whole integration uses database 14 times, so related operations only with db gets at sum 42 nodes (28 additional nodes).
We are using NR as
- ESB along with OpenAPI as spec
- tool for rapid solution (APIs, APIs+UI) development
- API backend
- integration framework
all with success.
I thought about what you said and think that product must evolve (as Church from Crusades to nowdays ;)) and meet market/use cases expectations to alive and expand.
In described example above to Colin I've pointed out number of additional nodes. When working with db:
- you must set the query
- upon the result you will route the msg
I meant an actual flow example where your suggestion would make a big difference.
Just a simple one, the minimum required to show the problem
Ah, I think I see where you are coming from, you have a database node for everywhere you use it, and for each one you need the nodes each side.
It's just about 20% of whole process.
[{"id":"2e385f94.13085","type":"inject","z":"4a79f0ec.1616b","name":"#3 Update GSX Invoice Table","topic":"","payload":"","payloadType":"date","repeat":"3600","crontab":"","once":false,"onceDelay":0.1,"x":190,"y":620,"wires":[["e44a18d2.787a98"]]},{"id":"e44a18d2.787a98","type":"function","z":"4a79f0ec.1616b","name":"#3 Set current date ","func":"let date = new Date();\nlet yy = String(date.getUTCFullYear()).substring(2,4);\nlet dd = String(date.getUTCDate()).length == 1 ? '0' + String(date.getUTCDate()) : String(date.getUTCDate());\nlet mm = String(date.getUTCMonth() + 1).length == 1 ? '0' + String(date.getUTCMonth() + 1) : String(date.getUTCMonth() + 1);\nlet dateFrom = `${mm}/${dd}/${yy}`;\n//msg.dateFrom = '08/23/18';//msg.payload;//'04/09/18';\nmsg.dateFrom = dateFrom;\nreturn msg;","outputs":1,"noerr":0,"x":470,"y":620,"wires":[["8964b952.ccaa58"]]},{"id":"8964b952.ccaa58","type":"http request","z":"4a79f0ec.1616b","name":"#3 GSX invoiceIDLookup","method":"GET","ret":"obj","url":"http://localhost:1880/gsx/invoiceIDLookup?fromDate={{{dateFrom}}}","tls":"","x":690,"y":620,"wires":[["a398920b.c24fa"]]},{"id":"a398920b.c24fa","type":"function","z":"4a79f0ec.1616b","name":"#3 Continue if available invoices","func":"var uniqEs6 = (arrArg) => {\n return arrArg.filter((elem, pos, arr) => {\n return arr.indexOf(elem) == pos;\n });\n}\n\nif (msg.statusCode == 500) {\n if (msg.payload.code == 'INV.LKP.001') {\n return [null, msg];\n } else {\n node.error(\"No Invoice(s) found for specified search criteria.\", msg);\n }\n} else {\n // remove duplicates - they happen\n let uniqInvoiceIds = uniqEs6(msg.payload);\n msg.payload = uniqInvoiceIds;\n // for tests\n //msg.payload = [uniqInvoiceIds[0]];\n //msg.payload = ['6921603818'];\n return [msg, null];\n}","outputs":2,"noerr":0,"x":950,"y":620,"wires":[["cdbd0371.f2953"],[]]},{"id":"cdbd0371.f2953","type":"split","z":"4a79f0ec.1616b","name":"#3 Split Invoice Ids","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":1190,"y":620,"wires":[["ed832201.d10c3"]]},{"id":"ed832201.d10c3","type":"function","z":"4a79f0ec.1616b","name":"#3 Invoice already exists?","func":"// Save gsx invoice details\nmsg.data = {};\nmsg.data.caseNumber = msg.payload;\n\n// Reset payload\nmsg.payload = {};\n\n// Query to execute on db\nmsg.topic = `\nSELECT Id\nFROM Invoices \nWHERE CaseNumber = ${msg.data.caseNumber};`;\n\nreturn msg;","outputs":1,"noerr":0,"x":1410,"y":620,"wires":[["d759fd4.72288"]]},{"id":"d759fd4.72288","type":"sqlite","z":"4a79f0ec.1616b","mydb":"d1671651.e4b308","sqlquery":"msg.topic","sql":"CREATE TABLE Invoices (\n Id INTEGER PRIMARY KEY,\n CaseNumber TEXT NOT NULL,\n OrderNumber TEXT NOT NULL,\n RepairNumber TEXT NOT NULL,\n SerialNumber TEXT NOT NULL,\n ImeiNumber TEXT NOT NULL,\n PurchaseOrderNumber TEXT NOT NULL,\n InvoiceDate TEXT NOT NULL,\n ShipTo TEXT NOT NULL,\n InvoiceType TEXT NOT NULL,\n TotalAmount TEXT NOT NULL,\n InvoiceAmount TEXT NOT NULL,\n Taxes TEXT NOT NULL,\n Currency TEXT NOT NULL,\n InvoiceData TEXT NOT NULL\n)\nWITHOUT ROWID;\n","name":"#3 Invoice already exists? (DB)","x":1670,"y":620,"wires":[["29ddf40e.638a2c"]]},{"id":"29ddf40e.638a2c","type":"function","z":"4a79f0ec.1616b","name":"Continue if not exists","func":"if (msg.payload.length === 0) {\n \n return [msg,null];\n} else {\n return [null,msg];\n}","outputs":2,"noerr":0,"x":1920,"y":620,"wires":[["1de9a800.9645c8"],[]]},{"id":"1de9a800.9645c8","type":"delay","z":"4a79f0ec.1616b","name":"#3 Limit 1msg/s","pauseType":"rate","timeout":"5","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":2120,"y":620,"wires":[["417770.146f889"]]},{"id":"417770.146f889","type":"http request","z":"4a79f0ec.1616b","name":"#3 GSX invoiceDetailsLookup","method":"GET","ret":"obj","url":"http://localhost:1880/gsx/invoiceDetailsLookup?invoiceId={{{data.caseNumber}}}","tls":"","x":2350,"y":620,"wires":[["69b004eb.0a68fc"]]},{"id":"69b004eb.0a68fc","type":"function","z":"4a79f0ec.1616b","name":"#3 Add invoice type if not exist","func":"msg.data = msg.payload;\nmsg.topic = `\n INSERT OR IGNORE INTO InvoiceTypes(Name) VALUES('${msg.data.invoiceType.trim()}');`\nreturn msg;\n\n","outputs":1,"noerr":0,"x":2630,"y":620,"wires":[["fedb4333.3704f"]]},{"id":"fedb4333.3704f","type":"sqlite","z":"4a79f0ec.1616b","mydb":"d1671651.e4b308","sqlquery":"msg.topic","sql":"CREATE TABLE Invoices (\n Id INTEGER PRIMARY KEY,\n CaseNumber TEXT NOT NULL,\n OrderNumber TEXT NOT NULL,\n RepairNumber TEXT NOT NULL,\n SerialNumber TEXT NOT NULL,\n ImeiNumber TEXT NOT NULL,\n PurchaseOrderNumber TEXT NOT NULL,\n InvoiceDate TEXT NOT NULL,\n ShipTo TEXT NOT NULL,\n InvoiceType TEXT NOT NULL,\n TotalAmount TEXT NOT NULL,\n InvoiceAmount TEXT NOT NULL,\n Taxes TEXT NOT NULL,\n Currency TEXT NOT NULL,\n InvoiceData TEXT NOT NULL\n)\nWITHOUT ROWID;\n","name":"#3 Add invoice type if not exist (DB)","x":2920,"y":620,"wires":[["e6bf2b5d.897128"]]},{"id":"e6bf2b5d.897128","type":"function","z":"4a79f0ec.1616b","name":"#3 Add currency if not exist","func":"msg.topic = `INSERT OR IGNORE INTO Currencies(Name) VALUES('${msg.data.currency}');`\nreturn msg;","outputs":1,"noerr":0,"x":3200,"y":620,"wires":[["805dc40.f17654"]]},{"id":"805dc40.f17654","type":"sqlite","z":"4a79f0ec.1616b","mydb":"d1671651.e4b308","sqlquery":"msg.topic","sql":"CREATE TABLE Invoices (\n Id INTEGER PRIMARY KEY,\n CaseNumber TEXT NOT NULL,\n OrderNumber TEXT NOT NULL,\n RepairNumber TEXT NOT NULL,\n SerialNumber TEXT NOT NULL,\n ImeiNumber TEXT NOT NULL,\n PurchaseOrderNumber TEXT NOT NULL,\n InvoiceDate TEXT NOT NULL,\n ShipTo TEXT NOT NULL,\n InvoiceType TEXT NOT NULL,\n TotalAmount TEXT NOT NULL,\n InvoiceAmount TEXT NOT NULL,\n Taxes TEXT NOT NULL,\n Currency TEXT NOT NULL,\n InvoiceData TEXT NOT NULL\n)\nWITHOUT ROWID;\n","name":"#3 Add currency if not exist (DB)","x":3470,"y":620,"wires":[["84748757.58b258"]]},{"id":"84748757.58b258","type":"function","z":"4a79f0ec.1616b","name":"#3 Add Invoice if not exists","func":"let data = msg.data;\nmsg.topic = `\nINSERT INTO Invoices \n(\n Id,\n CaseNumber,\n OrderNumber,\n RepairNumber,\n SerialNumber,\n ImeiNumber,\n PurchaseOrderNumber,\n InvoiceDate,\n ShipTo,\n InvoiceTypeId,\n TotalAmount,\n InvoiceAmount,\n Taxes,\n CurrencyId,\n InvoiceData,\n WarehouseId\n)\nVALUES\n(\n NULL,\n ${data.caseNumber.trim().length > 0 ? \"'\" + data.caseNumber + \"'\" : 'NULL'},\n ${data.orderNumber.trim().length > 0 ? \"'\" + data.orderNumber + \"'\": 'NULL'},\n ${data.repairNumber.trim().length > 0 ? \"'\" + data.repairNumber + \"'\": 'NULL'},\n ${data.serialNumber.trim().length > 0 ? \"'\" + data.serialNumber + \"'\" : 'NULL'},\n ${data.imeiNumber.trim().length > 0 ? \"'\" + data.imeiNumber + \"'\" : 'NULL'},\n ${data.purchaseOrderNumber.trim().length > 0 ? \"'\" + data.purchaseOrderNumber + \"'\" : 'NULL'},\n ${data.invoiceDate.trim().length > 0 ? \"'\" + data.invoiceDate + \"'\" : 'NULL'},\n ${data.shipTo.trim().length > 0 ? parseInt(data.shipTo) : 'NULL'},\n (SELECT Id FROM InvoiceTypes WHERE Name = '${data.invoiceType.trim()}'),\n ${data.totalAmount.trim().length > 0 ? \"'\" + data.totalAmount + \"'\" : 'NULL'},\n ${data.invoiceAmount.trim().length > 0 ? \"'\" + data.invoiceAmount + \"'\" : 'NULL'},\n ${data.taxes.trim().length > 0 ? \"'\" + data.taxes + \"'\" : 'NULL'},\n (SELECT Id FROM Currencies WHERE Name = '${data.currency}'),\n ${data.invoiceData.trim().length > 0 ? \"'\" + data.invoiceData + \"'\" : 'NULL'},\n (SELECT Id FROM Warehouses WHERE ShipTo = ${parseInt(data.shipTo)})\n)\n`\n\nif (msg.payload.length === 0) {\n return [msg, null];\n} else {\n return [null, msg];\n}","outputs":2,"noerr":0,"x":3740,"y":620,"wires":[["7384e631.138cd8"],[]]},{"id":"7384e631.138cd8","type":"sqlite","z":"4a79f0ec.1616b","mydb":"d1671651.e4b308","sqlquery":"msg.topic","sql":"CREATE TABLE Invoices (\n Id INTEGER PRIMARY KEY,\n CaseNumber TEXT NOT NULL,\n OrderNumber TEXT NOT NULL,\n RepairNumber TEXT NOT NULL,\n SerialNumber TEXT NOT NULL,\n ImeiNumber TEXT NOT NULL,\n PurchaseOrderNumber TEXT NOT NULL,\n InvoiceDate TEXT NOT NULL,\n ShipTo TEXT NOT NULL,\n InvoiceType TEXT NOT NULL,\n TotalAmount TEXT NOT NULL,\n InvoiceAmount TEXT NOT NULL,\n Taxes TEXT NOT NULL,\n Currency TEXT NOT NULL,\n InvoiceData TEXT NOT NULL\n)\nWITHOUT ROWID;\n","name":"#3 Add Invoice (GSX_DB)","x":3990,"y":620,"wires":[["a3d0e0f7.175f2"]]},{"id":"a3d0e0f7.175f2","type":"function","z":"4a79f0ec.1616b","name":"#3 Add Parts ","func":"let data = msg.data;\nlet insert;\nfor (var i = 0; i < data.partsInfo.length; i++) {\n let part = data.partsInfo[i];\n if (i === 0) {\n insert = `\n INSERT OR IGNORE INTO Parts SELECT\n NULL,\n '${data.partsInfo[i].partNumber}' AS Name\n `\n } else {\n insert = insert + ' ' + `\n UNION ALL SELECT\n NULL,\n '${data.partsInfo[i].partNumber}' AS Name \n `;\n }\n\n}\n\nmsg.topic = insert;\nreturn msg;","outputs":1,"noerr":0,"x":4190,"y":620,"wires":[["e18240aa.25852"]]},{"id":"e18240aa.25852","type":"sqlite","z":"4a79f0ec.1616b","mydb":"d1671651.e4b308","sqlquery":"msg.topic","sql":"CREATE TABLE Invoices (\n Id INTEGER PRIMARY KEY,\n CaseNumber TEXT NOT NULL,\n OrderNumber TEXT NOT NULL,\n RepairNumber TEXT NOT NULL,\n SerialNumber TEXT NOT NULL,\n ImeiNumber TEXT NOT NULL,\n PurchaseOrderNumber TEXT NOT NULL,\n InvoiceDate TEXT NOT NULL,\n ShipTo TEXT NOT NULL,\n InvoiceType TEXT NOT NULL,\n TotalAmount TEXT NOT NULL,\n InvoiceAmount TEXT NOT NULL,\n Taxes TEXT NOT NULL,\n Currency TEXT NOT NULL,\n InvoiceData TEXT NOT NULL\n)\nWITHOUT ROWID;\n","name":"#3 Add Parts (DB)","x":4370,"y":620,"wires":[["4ef9f05f.f546b"]]},{"id":"4ef9f05f.f546b","type":"link out","z":"4a79f0ec.1616b","name":"Jump to \"Parse PDF to Text\"","links":["66217944.3faf08"],"x":4495,"y":620,"wires":[]},{"id":"d1671651.e4b308","type":"sqlitedb","z":"","db":"/data/dbs/gsx.db"}]
What is 20% of the process?
Also if you are worried about efficiency (which won't be an issue in a flow that in fact) then change some of the function nodes to builtin nodes such as change and switch.
It's about 20% of flow.
What is about 20% of the flow?
Colin, about 20% of his nodes are related to handling the SQL queries.
@tmdoit of you want to do routing based on the output of the SQL node, use a Switch node rather than Function node. It doesn't reduce the number of nodes, but it uses the appropriate node for the task.
Well that sounds entirely reasonable to me, since that is mostly what the flow is about.
Mind you I can see an argument for allowing a query to be defined in the db node, analogous to specifying the url in the http request node either in the node or in the message, and with the ability to interpolate message data into the url.
But it is also interesting that even if that were available the first node in the flow that one might hope to eliminate - #3 Invoice already exists? - would still be necessary.
I would recommend using ISO date formats throughout. It translates better and avoids weird issues with system locales.
Personally, I wouldn't keep jumping in and out of function nodes - since you are using JS function nodes anyway, some of the ones like split really don't seem worth the effort. That would reduce the number of nodes.
You seem to me making REST calls back to Node-RED? I would probably use a sub-flow for that.
You have a very linear flow. It feels like you could refactor across all your flows and reduce visual complexity. But without seeing the whole thing it is very hard to say.
You might possibly also be able to simplify by keeping reference information in-memory but obviously that depends on the amount and complexity of data you have.
Thanks for advices, pasted part of flow is one of the first serious integrations which I'm aware of needs refactoring. That's not the point.
I'm using NR as integration platform, where in one flow I can use even 10 database nodes with "helper" nodes ("query" node and "router" node) it could get number of 30 (only db operations). When you do 10th integration, you're trying to get things simpler and more readable, one of action is to get rid of boilerplate which "helper" nodes are in this use case. When you get more experience, you just want to click to db node and see what goes inside and where goes outside than looking for it in other nodes, because this is the complete_functional context of db node operation, the same issue is with http input, http request nodes and similar.
Parameterised subflows could be very useful in such cases.
I was so motivated to use fixed statement with mustache that I missed prepared statement which do the job 
So with SQLite "additional query node" problem not exist.
