I’ve recently been exploring more of the features of Node-RED through the IBM Cloud and found how simple it is to create some really quite complicated applications by just connecting up a string of nodes into a flow. One thing that I noticed pretty quickly, however, is that there’s no mySQL node under storage which got me wondering as to how to connect and use ClearDB MySQL instance in my flows. Well, if you’re wondering the same, here’s how in a pretty simple set of steps (You can of course, also use pre-existing Node-RED and / or ClearDB MySQL databases if you have them).
- Create a Node-RED Starter service. I’m not going into details on this, save to say go to the IBM Cloud catalog, search for Node-RED, click the service icon and follow the instructions. Allow it to provision.
- Create a ClearDB Managed SQL Database. Again, I won’t go into details, just search for it in the IBM Cloud catalog, click the icon, decide on your payment plan (I chose the free CB5 plan because I’m just doing some testing) and click create.
- There’s no real need to connect the two but I’ve done so, just to show within the services themselves that the two interact.
- From the IBM Cloud dashboard, click on your ClearDB service and then click on the banner that reads ‘OPEN CLEARDB MANAGED MYSQL DATABASE DASHBOARD’.
- If you don’t already have MySQLWorkbench installed, click on the ‘How to Connect’ tab and then click on the MySQL Workbench link to install it. I found this easiest to work with.Then go to the tab marked ‘Community Edition’ (or ‘Dev and Production Edition’ if you are using a Paid-for version) and click the link, which is the name of your database. This will bring up some more tabs, the default one being ‘Performance’ with some nice dials on it.
- Click the System Information tab. Here, you will see your database name (in the blue banner, to the left of the colon and the words ‘System Information’), the hostname (ending in ‘.cleardb.net’ or simiar) and under ‘Access Credentials’ your Username and Password.
- Once it’s installed, start MySQL Workbench and then create a connection to your MySQL instance.
- On the MySQL Workbench welcome screen, click the plus (+) symbol next to mySQL Connections. Enter a Connection Name, which is meaningful to you and leave the connection method as ‘Standard (TCP/IP)’. In the Hostname field, enter the hostname as it appears in step 6, along with your username. Leave the Port number as is and enter your database name as displayed in step 6 as the Default Schema(everything to the left but not including the colon (:)). Add in the password as shown via step 6 too. Click ‘Test Connection’ to make sure everything is entered correctly and a connection can be made and then click ‘OK’ when the test is successful.
- Click the connection box which should now appear and connect to your mySQL database. Now create a table and insert some data into it using the MySQL Workbench tool. (hint: type ‘create table myNames (firstname varchar(10));’ and on a new line type ‘insert into myNames values ‘Trevor’;’ then on another new line type ‘commit;’. Next, press the execute icon, which looks like a lightning bolt, third in from the left. This should return three lines with green ticks in the Action Output pane).
- Checkpoint: We now have a Node-RED service and a ClearDB MySQL Managed database service provisioned. We have found the connection information that we need for the database (hostname, database name, username and password). We have installed MySQL Workbench, connected to the ClearDB service, created a table called myName and inserted one record into it. Brilliant. Time to head over into Node-RED!
- From the IBM Cloud dashboard, click on the URL for your Node-Red editor. If you have not used the editor before (i.e. you have just provisioned it) then you will first need to secure it and so on. Follow the screen prompts to create a username and password (leave everything else as it is) and start up your flow editor.
- Once in the flow editor, you will need to install the mySQL nodes. To do this, click the hamburger (the three horizontal lines) in the top right hand corner, to the right of the Deploy button. Then click ‘Manage Pallate’.
- Click the ‘Install’ tab and then type ‘mySQL into the Search Modules box. Three results will come back but we want the one called node-red-node-mysql. Click the ‘Install’ button next to it.
- Check for any dependencies that may be needed (there were none when I completed this) and finish the install, which will take a few seconds at most.
- Look at the Storage section – you should now see an orange mySQL node, which you can drag into the Flow window.
- To test, drag in an inject node, a function node, a mySQL node and a debug node, connecting them up left to right in that order.
- In the inject node, change the payload to ‘text’ and enter ‘select * from myNames’.
- In the function node, have this code: ‘msg.topic = msg.payload; return msg;’. The query has to be passed in msg.topic.
- Configure the mySQL node by editing the database field (click the pencil) and on the second page add in the host, user, password and database name as per step 6 / 7 above. The port and timezone can stay as is. Click Update and then done.
- You don’t need to configure the debug node. Just click ‘Deploy’.
- Assuming the deploy works, the mySQL db node will show ‘connected’. Check your connection details if it doesn’t.
- Click the Inject node to run the query and you will see in the debug window the output – click the small arrows to open the Array and Object and you’ll then see ‘firstname: Trevor’ as the JSON output from the query.
- You can re-write your query to an ‘INSERT’ statement in the inject node, click deploy and test again (you can see the output in mySQL Workbench or just run another select injection) but the result is that INSERT, SELECT and DELETE operations can now be issued through this node.
So there you have it. Obviously, this is the starting point of bigger applications so good luck and happy building!