n8n workflow platform with InterSystems Cloud SQL

On the Latest GlobalSummit 2022, InterSystems Introduced Cloud SQL. So, you may have lightweight InterSystems IRIS with access to SQL only. Well, what if you would still need some Interoperability features in the cloud as well? There are various solutions on the market nowadays, which offer a bunch of integration adapters out of the box and can be extended with support from the community. Some time ago, I've implemented an adapter for the Node-RED project, which can be deployed manually everywhere you want. Now I would like to introduce a new integration with my recent discovery, n8n.io

n8n.io is a workflow automation platform, that supports over 200 different integrations out of the box and from a community, and now including InterSystems IRIS.

Let's Install it.

n8n is a NodeJS application, and can be installed directly on a host machine with npm 

$ npm install -g n8n
$ n8n s

Or it just with docker image. InterSystems IRIS node package requires debian, so, we need debian version of n8n

$ docker run -it --rm \
        --name n8n \
        -p 5678:5678 \
        -v ~/.n8n:/home/node/.n8n \
        n8nio/n8n:latest-debian

Now we can open http://localhost:5678/ in a browser. With the first start it will offer to register the owner of this instance, you may skip it.

So, it's the main window. Where you can use quite simply add new nodes in a workflow, all nodes accessible through the Plus buttons. By default only out-of-the box integrations available there, InterSystems IRIS integration is not in the list, yet. Let's install it. Settings -> Community Nodes -> Install, enter n8n-nodes-iris, check consent checkbox, and press Install again

Now this node is installed

And can be added to a workflow

InterSystems Cloud SQL

For the demo I will use Cloud SQL, but you may use any IRIS instance. n8n-nodes-iris does not support encrypted connection, yet. So, we would need to use unecrypted connection for this case.

I have Cloud SQL instance up and running, I have hostname, port, and Password which I set manually. Back to n8n instance, and add this server there. Credentials on the left panel, New, find IRIS here and Continue

You may create as many Credentials as you need, if say about IRIS, you would need it per any Server and Namespace, you would want to connect with. Not so much fields here, fill them and Save

Let's query

The idea of n8n nodes is quite simple. There are two types of nodes,

• triggers - can start the workflow, manually or by some event
• regular - just do some work, they get input from any previous node represented as JSON in UI, and may or may not return some other object after work. If node returns nothing workflow stops, after that node.

IRIS node is a regular one, and it may accept data from the previous node, but can't trigger any actions (but probably may get this functionality in the future),

Add new IRIS node, select previously created credentials from the list, or create a new one right from here. 

The only supported features is Execute Query and Insert, at the moment. And this can be extended in the future

• Execute Query, allows to execute any SQL query, as parameters placements allows to use $1, $2 and so on, values for parameters can be added too, and with help from n8n itself, it can just easily insert needed values from the input data.
• Insert, allows to insert data to the selected table. It shows the list of available tables, so, no need to retype it, as well as available columns for the selected table

For the demo, I decided to request reddit for a new topics and place them in IRIS SQL Table. To simplify the process, I've created table Sample.reddit with three fields subreddit, title and created.

I've added HTTP Request node, and configured it to request reddit's new feed in JSON format, and I can test the node with Execute node button, and the the real output right the way

It's a whole output as one, so, I need to split the posts here, with Items list node, 1 item in input, and 20 items in output

And finally it's time to insert data to SQL Table. When node executed it, executes the query as many as many items in input, and automatically places the data, as configured

And let's check whats in our table now form Cloud SQL UI

So, as you can see, even with simple version of IRIS, we still may get some integration, and possible to do more with this.

Check the video with demo of this project in action

https://www.youtube.com/embed/Bxr-ORkdzp4
[This is an embedded link, but you cannot view embedded content directly on the site because you have declined the cookies necessary to access it. To view embedded content, you would need to accept all cookies in your Cookies Settings]

Do not hesitate to vote for my application on the contest, and for sure you may support my work here