RESTful API on IBM i with simple JWT authentication
RESTful API with simple JWT auth using node-red-contrib-db2-for-i
Expose your Db2 for i data on IBM i with REST services with Node-RED. Simple CRUD flow based on node-red-contrib-db2-for-i node, JWT token based authentication and access control node-red-contrib-users.
In this example, authenticated users are read-only and admin users can update/delete.
You'll find the database DDL used in this example (CUSTCHURN) in the 'IBM i and AI' github repo
[{"id":"5987e279.601cb4","type":"tab","label":"REST API Easy - Simple JWT Auth","disabled":false,"info":""},{"id":"5457c04d893a5e18","type":"group","z":"5987e279.601cb4","style":{"stroke":"#999999","stroke-opacity":"1","fill":"none","fill-opacity":"1","label":true,"label-position":"nw","color":"#a4a4a4"},"nodes":["e77e23f5a17c1e89","4e8189b18d073861","c486454d576250ea"],"x":700.4500122070312,"y":242.45001220703125,"w":367,"h":119},{"id":"a055e2e363521974","type":"group","z":"5987e279.601cb4","style":{"stroke":"#999999","stroke-opacity":"1","fill":"none","fill-opacity":"1","label":true,"label-position":"nw","color":"#a4a4a4"},"nodes":["7ea00af6.b6f6e4","f6f378b8.bd0518","15cf42c5.91475d","eb9a804a.bee328"],"x":720.5,"y":374,"w":345.50006103515625,"h":151},{"id":"37a641b5.b351b6","type":"http in","z":"5987e279.601cb4","name":"","url":"/customers","method":"get","upload":false,"swaggerDoc":"","x":95,"y":280,"wires":[["0b081e8e0bdade3d"]]},{"id":"7ea00af6.b6f6e4","type":"change","z":"5987e279.601cb4","g":"a055e2e363521974","name":"Set Headers","rules":[{"t":"set","p":"headers","pt":"msg","to":"{}","tot":"json"},{"t":"set","p":"headers.content-type","pt":"msg","to":"application/json","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":847,"y":483,"wires":[["f6f378b8.bd0518"]]},{"id":"f6f378b8.bd0518","type":"http response","z":"5987e279.601cb4","g":"a055e2e363521974","name":"","statusCode":"","headers":{},"x":990.0000610351562,"y":484,"wires":[]},{"id":"15cf42c5.91475d","type":"DB2 for i","z":"5987e279.601cb4","g":"a055e2e363521974","mydb":"79587fdd.4cf6","name":"","arraymode":true,"x":806.5,"y":415,"wires":[["7ea00af6.b6f6e4","eb9a804a.bee328"]]},{"id":"1f1dce82.081059","type":"function","z":"5987e279.601cb4","name":"SQL Query SELECT*","func":"msg.payload=\"select * from churn.custchurn3\" \n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":497.5,"y":274,"wires":[["0c2dbb2fbb04a80a"]]},{"id":"eb9a804a.bee328","type":"debug","z":"5987e279.601cb4","g":"a055e2e363521974","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":967.5,"y":415,"wires":[]},{"id":"3ab64a44.67f76e","type":"comment","z":"5987e279.601cb4","name":"API : list customers","info":"","x":103.5,"y":228,"wires":[]},{"id":"841fb465.148cd","type":"function","z":"5987e279.601cb4","name":"SQL Query","func":"msg.payload=\"select * from churn.custchurn3 where customerid='\"+ \nmsg.req.params.id+\"'\"\n//\"9237-HQITU\";\nreturn msg;","outputs":1,"noerr":0,"x":480,"y":455,"wires":[["74e7915c01f13f43"]]},{"id":"176f8c75.40020c","type":"http in","z":"5987e279.601cb4","name":"","url":"/customer/:id","method":"get","upload":false,"swaggerDoc":"","x":100,"y":461,"wires":[["94f1ee28de7e0dad"]]},{"id":"7e7dc103.f8437","type":"comment","z":"5987e279.601cb4","name":"API : get customer by ID","info":"","x":115,"y":414,"wires":[]},{"id":"fd158618.e08788","type":"http in","z":"5987e279.601cb4","name":"","url":"/customer/:id","method":"put","upload":false,"swaggerDoc":"","x":101,"y":565,"wires":[["1bb0b851dc7b31f8"]]},{"id":"89495af9.c2232","type":"comment","z":"5987e279.601cb4","name":"API : update customer by ID - admins only","info":"","x":165,"y":521,"wires":[]},{"id":"b5f8f49f.1159c8","type":"function","z":"5987e279.601cb4","name":"SQL Query UPDATE","func":"msg.payload=\"update churn.custchurn3\" +\n\" set \"+\n\"GENDER='\"+msg.req.body.GENDER +\"',\"+\n\"SENIORCITIZEN='\" + msg.req.body.SENIORCITIZEN + \"',\"+\n\"PARTNER='\" + msg.req.body.PARTNER + \"',\" +\n\"DEPENDENTS='\" + msg.req.body.DEPENDENTS + \"',\" +\n\"TENURE='\" + msg.req.body.TENURE + \"' \" +\n\"where customerid='\"+ \nmsg.req.params.id+\"';\";\n\n//curl -X PUT -H 'Content-Type: application/json' -i 'http://10.7.19.71:1880/customers/0019-EFAEP' --data '{\"CUSTOMERID\":\"0019-EFAEP\",\"GENDER\":\"Female\",\"SENIORCITIZEN\":\"0\",\"PARTNER\":\"No \",\"DEPENDENTS\":\"No \",\"TENURE\":\"72\",\"PHONESERVICE\":\"Yes\",\"MULTIPLELINES\":\"Yes \",\"INTERNETSERVICE\":\"Fiber optic\",\"ONLINESECURITY\":\"Yes \",\"ONLINEBACKUP\":\"Yes \",\"DEVICEPROTECTION\":\"Yes \",\"TECHSUPPORT\":\"No \",\"STREAMINGTV\":\"Yes \",\"STREAMINGMOVIES\":\"No \",\"CONTRACT\":\"Two year \",\"PAPERLESSBILLING\":\"Yes\",\"PAYMENTMETHOD\":\"Bank transfer (automatic)\",\"MONTHLYCHARGES\":\"101.30\",\"TOTALCHARGES\":\"7261.25\",\"CHURN\":\"No \"}'\n//\"GENDER=\t'Female'\n//\"SENIORCITIZEN '0'\n//PARTNER\t\"No \"\n//DEPENDENTS\t\"No \"\n//TENURE\t\"72\"\n//PHONESERVICE\t\"Yes\"\n//MULTIPLELINES\t\"Yes \"\n//INTERNETSERVICE\t\"Fiber optic\"\n//ONLINESECURITY\t\"Yes \"\n//ONLINEBACKUP\t\"Yes \"\n//DEVICEPROTECTION\t\"Yes \"\n//TECHSUPPORT\t\"No \"\n//STREAMINGTV\t\"Yes \"\n//STREAMINGMOVIES\t\"No \"\n//CONTRACT\t\"Two year \"\n//PAPERLESSBILLING\t\"Yes\"\n//PAYMENTMETHOD\t\"Bank transfer (automatic)\"\n//MONTHLYCHARGES\t\"101.30\"\n//TOTALCHARGES\t\"7261.25\"\n//CHURN\t\"No \"\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":723,"y":558.9999694824219,"wires":[["ed06644fd8bdbf5c"]]},{"id":"0b081e8e0bdade3d","type":"users_isloggedin","z":"5987e279.601cb4","name":"","enableCustomHandler":true,"outputs":2,"x":286.45001220703125,"y":280.45001220703125,"wires":[["1f1dce82.081059"],["f2196d7196431459"]]},{"id":"e77e23f5a17c1e89","type":"template","z":"5987e279.601cb4","g":"5457c04d893a5e18","name":"custom login page","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"<html lang=\"en\">\n<head>\n <meta charset=\"utf-8\">\n <meta name=\"viewport\" content=\"width=device-width, initial-scale=1.0\">\n <meta name=\"apple-mobile-web-app-capable\" content=\"yes\">\n <meta name=\"mobile-web-app-capable\" content=\"yes\">\n <style>\n * {\n box-sizing: border-box;\n}\n\nhtml {\n height: 100%;\n}\n\nbody {\n margin: 0;\n height: 100%;\n font-family: -apple-system, BlinkMacSystemFont, \"Segoe UI\", Roboto, Helvetica, Arial, sans-serif, \"Apple Color Emoji\", \"Segoe UI Emoji\", \"Segoe UI Symbol\";\n font-size: 15px;\n background: #000;\n}\n\n.form-group {\n margin-bottom: 15px;\n}\n\n.form-group input {\n width: 100%;\n border-radius: 2px;\n\n display: block;\n width: 100%;\n height: 34px;\n padding: 6px 12px;\n font-size: 14px;\n line-height: 1.42857143;\n color: #555;\n outline: 0;\n border: 1px solid #ddd;\n}\n\n.login-wrapper > a, button {\n text-decoration: none;\n cursor: pointer;\n background-color: #00979C;\n border: 1px solid #008d92;\n color: #f3f5f6;\n transition: color 0.2s, background-color 0.2s;\n\n text-align: center;\n padding: 12px 60px;\n font-size: 1.2rem;\n display: inline-block;\n margin-bottom: 1rem;\n border-radius: 2px;\n background-color: #00979C;\n font-weight: bold;\n text-transform: uppercase;\n}\n\n.login-wrapper > a:hover, .login-wrapper > a:active,\nbutton:hover, button:active {\n background-color: #00b5bb;\n color: #f3f5f6;\n border-color: #00abb0;\n}\n\n.login-wrapper {\n position: absolute;\n padding: 15px;\n margin: 0 auto;\n width: 400px;\n color: #EFF0F1;\n text-align: center;\n left: calc(50% - 200px);\n top: calc(50% - 180px);\n}\n\n.response {\n margin-top: 10px;\n padding: 15px;\n color: #fff;\n border-radius: 2px;\n}\n\n.response.success {\n background-color: #51b385;\n border-color: #63bb92;\n}\n\n.response.error {\n background-color: #c11532;\n border-color: #c11532;\n}\n\n@media (max-width: 768px) {\n .login-wrapper {\n width: 100%;\n left: 0;\n top: 100px;\n padding: 15px 30px;\n }\n}\n </style>\n <title>Node-RED Node Users</title>\n</head>\n<body>\n\n <div class=\"login-wrapper\">\n \n <h1>Custom Users Login</h1>\n <form id=\"login-form\">\n <div class=\"form-group\">\n <input type=\"text\" name=\"username\" placeholder=\"Username\"/> \n </div>\n <div class=\"form-group\">\n <input type=\"password\" name=\"password\" placeholder=\"Password\"/> \n </div>\n <button type=\"submit\">Login</button>\n <div class=\"response\"></div>\n </form>\n\n </div>\n \n <script src=\"http://code.jquery.com/jquery-3.3.1.min.js\"></script>\n <script>\n var responseTimer;\n\n function showResponse(message, type) {\n $(\".response\").text(message);\n $(\".response\").removeClass(\"success\").removeClass(\"error\").addClass(type).show();\n\n clearTimeout(responseTimer);\n responseTimer = setTimeout(function () {\n $(\".response\").fadeOut();\n }, 4000);\n }\n\n function getParameterByName(name) {\n var match = RegExp('[?&]' + name + '=([^&]*)').exec(window.location.search);\n return match && decodeURIComponent(match[1].replace(/\\+/g, ' '));\n }\n\n $(\"#login-form\").submit(function (e) {\n e.preventDefault();\n var username = $(this).find(\"input[name=username]\").val();\n var password = $(this).find(\"input[name=password]\").val();\n var cred = {\n username: username,\n password: password\n };\n $.post('/users', cred).done(function () {\n showResponse(\"Login success! Redirecting...\", \"success\");\n setTimeout(function () {\n window.location = '/users'; \n }, 2000); \n }).fail(function (xhr) {\n var msg = \"\";\n switch(xhr.status) {\n case 0:\n msg = \"Failed to connect with server.\";\n break;\n case 401: \n msg = \"Unauthorized: username and password not found\";\n break;\n default: \n msg = \"Server error: oops.. something went wrong...\";\n }\n showResponse(msg, \"error\");\n });\n });\n </script>\n\n</body>\n</html>","x":816.4500122070312,"y":283.45001220703125,"wires":[["4e8189b18d073861"]]},{"id":"4e8189b18d073861","type":"http response","z":"5987e279.601cb4","g":"5457c04d893a5e18","name":"","statusCode":"","headers":{},"x":981.4500122070312,"y":283.45001220703125,"wires":[]},{"id":"c486454d576250ea","type":"comment","z":"5987e279.601cb4","g":"5457c04d893a5e18","name":"User unauthorized, show login page","info":"","x":901.4500122070312,"y":320.45001220703125,"wires":[]},{"id":"1bb0b851dc7b31f8","type":"users_isloggedin","z":"5987e279.601cb4","name":"","enableCustomHandler":true,"outputs":2,"x":292.45001220703125,"y":564.6500244140625,"wires":[["d0def427550b29dc"],["b408be0a95c66f7b"]]},{"id":"fd2d552d0acd3184","type":"link in","z":"5987e279.601cb4","name":"link in 1","links":["f2196d7196431459","b408be0a95c66f7b","44eb5284d4847e24","10c726e524ad0652","d3ff5b1650333f08","f381dfabc376c6e9"],"x":696.9000244140625,"y":283.1499938964844,"wires":[["e77e23f5a17c1e89"]]},{"id":"f2196d7196431459","type":"link out","z":"5987e279.601cb4","name":"link out 1","mode":"link","links":["fd2d552d0acd3184"],"x":414.9000244140625,"y":322.3499755859375,"wires":[]},{"id":"b408be0a95c66f7b","type":"link out","z":"5987e279.601cb4","name":"link out 2","mode":"link","links":["fd2d552d0acd3184"],"x":407.45001220703125,"y":604.6500244140625,"wires":[]},{"id":"94f1ee28de7e0dad","type":"users_isloggedin","z":"5987e279.601cb4","name":"","enableCustomHandler":true,"outputs":2,"x":289.45001220703125,"y":461.6499938964844,"wires":[["841fb465.148cd"],["44eb5284d4847e24"]]},{"id":"44eb5284d4847e24","type":"link out","z":"5987e279.601cb4","name":"link out 3","mode":"link","links":["fd2d552d0acd3184"],"x":407.45001220703125,"y":500.6499938964844,"wires":[]},{"id":"fab9b720898f5cd4","type":"switch","z":"5987e279.601cb4","name":"check user scope","property":"payload.user.scope","propertyType":"msg","rules":[{"t":"eq","v":"CustomerAdmin","vt":"str"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":512.4500122070312,"y":697.75,"wires":[["e47c9baafd36681a"],["d3ff5b1650333f08"]]},{"id":"b6b93e96b584a918","type":"comment","z":"5987e279.601cb4","name":"API : delete customer by ID - admins only","info":"","x":165,"y":650.75,"wires":[]},{"id":"29b6877dbdc3077f","type":"http in","z":"5987e279.601cb4","name":"","url":"/customer/:id","method":"delete","upload":false,"swaggerDoc":"","x":118.44999694824219,"y":705.75,"wires":[[]]},{"id":"e47c9baafd36681a","type":"function","z":"5987e279.601cb4","name":"SQL Query DELETE","func":"msg.payload=\"Delete from churn.custchurn3\" +\n\"where customerid='\"+ \nmsg.req.params.id+\"';\";\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":745.4500122070312,"y":690.75,"wires":[["d532241e1927d9c3"]]},{"id":"32a5bf37d4dea026","type":"users_isloggedin","z":"5987e279.601cb4","name":"","enableCustomHandler":true,"outputs":2,"x":311.45001220703125,"y":704.75,"wires":[["fab9b720898f5cd4"],["10c726e524ad0652"]]},{"id":"10c726e524ad0652","type":"link out","z":"5987e279.601cb4","name":"link out 4","mode":"link","links":["fd2d552d0acd3184"],"x":412.45001220703125,"y":744.75,"wires":[]},{"id":"d3ff5b1650333f08","type":"link out","z":"5987e279.601cb4","name":"link out 5","mode":"link","links":["fd2d552d0acd3184"],"x":626.4500122070312,"y":742.75,"wires":[]},{"id":"d0def427550b29dc","type":"switch","z":"5987e279.601cb4","name":"check user scope","property":"payload.user.scope","propertyType":"msg","rules":[{"t":"eq","v":"customerAdmin","vt":"str"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":495.45001220703125,"y":565.75,"wires":[["b5f8f49f.1159c8"],["f381dfabc376c6e9"]]},{"id":"f381dfabc376c6e9","type":"link out","z":"5987e279.601cb4","name":"link out 6","mode":"link","links":["fd2d552d0acd3184"],"x":671.4500122070312,"y":629.75,"wires":[]},{"id":"0c2dbb2fbb04a80a","type":"link out","z":"5987e279.601cb4","name":"link out 7","mode":"link","links":["e7a5957ded28aed7"],"x":629.9000244140625,"y":273.3499755859375,"wires":[]},{"id":"e7a5957ded28aed7","type":"link in","z":"5987e279.601cb4","name":"link in 2","links":["0c2dbb2fbb04a80a","74e7915c01f13f43","ed06644fd8bdbf5c","d532241e1927d9c3"],"x":711.9000244140625,"y":414.1500244140625,"wires":[["15cf42c5.91475d"]]},{"id":"74e7915c01f13f43","type":"link out","z":"5987e279.601cb4","name":"link out 8","mode":"link","links":["e7a5957ded28aed7"],"x":581.4500122070312,"y":454.75,"wires":[]},{"id":"ed06644fd8bdbf5c","type":"link out","z":"5987e279.601cb4","name":"link out 9","mode":"link","links":["e7a5957ded28aed7"],"x":852.4500122070312,"y":558.7499694824219,"wires":[]},{"id":"d532241e1927d9c3","type":"link out","z":"5987e279.601cb4","name":"link out 10","mode":"link","links":["e7a5957ded28aed7"],"x":889.4500122070312,"y":690.75,"wires":[]},{"id":"79587fdd.4cf6","type":"DB2 for i Config","cnnname":"local","db":"*LOCAL","keepalive":true}]
