API File upload / download backed by MySQL + MQTT
Flow allows for uploading and downloading files, backed by MQTT and MySQL; it includes 3 API endpoints:
- POST /file which writes to MQTT topic file
- GET /file/:id which does basic validation and makes a call to the 3rd endpoint
- GET /binary/:id this endpoint is called by the above GET /file:id endpoint and is needed to return the data (images, PDFs, etc) in the correct format. This is where the database is queried and the data is returned.
Flow uses MySQL to store/query data https://flows.nodered.org/node/node-red-contrib-stackhero-mysql, writes a copy to disk /uploads and will create the MySQL table as necessary before performing inserts.
[{"id":"f40dad3e2042208d","type":"tab","label":"APIs","disabled":false,"info":"","env":[]},{"id":"e16b9e3fb7eaf6fe","type":"junction","z":"f40dad3e2042208d","x":89,"y":78,"wires":[[]]},{"id":"e4fd0340eb438fa9","type":"mqtt out","z":"f40dad3e2042208d","name":"","topic":"file","qos":"","retain":"","respTopic":"","contentType":"","userProps":"","correl":"","expiry":"","broker":"ea83830bf665d01e","x":530,"y":80,"wires":[]},{"id":"7aeadfd839a17b88","type":"mqtt in","z":"f40dad3e2042208d","name":"","topic":"file","qos":"2","datatype":"auto-detect","broker":"ea83830bf665d01e","nl":false,"rap":true,"rh":0,"inputs":0,"x":890,"y":80,"wires":[["29b7e0de10b61d92","7d3278b3d046456a","94e1371c4d75ae48"]]},{"id":"a594958877924b32","type":"Stackhero-MySQL","z":"f40dad3e2042208d","server":"8df4454477e1c3e3","name":"","x":1260,"y":180,"wires":[[]]},{"id":"d12d176404a4c921","type":"function","z":"f40dad3e2042208d","name":"prep payload","func":"var req = msg.req.files[0];\nvar filename = req.originalname;\nvar mimetype = req.mimetype;\nvar filesize = req.size;\nvar data = req.buffer;\n\nmsg.payload = {\n filename: filename,\n filesize: filesize,\n mimetype: mimetype,\n data: data\n}\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":270,"y":80,"wires":[["e4fd0340eb438fa9"]]},{"id":"34bc16f85ff1b762","type":"http in","z":"f40dad3e2042208d","name":"","url":"/file","method":"post","upload":true,"swaggerDoc":"","x":100,"y":80,"wires":[["3473e0502a83ae42","d12d176404a4c921"]]},{"id":"3e4b59e2633f6eeb","type":"file","z":"f40dad3e2042208d","name":"write file","filename":"filename","filenameType":"msg","appendNewline":false,"createDir":true,"overwriteFile":"true","encoding":"none","x":1160,"y":80,"wires":[[]]},{"id":"29b7e0de10b61d92","type":"function","z":"f40dad3e2042208d","name":"prep file","func":"\nvar data = Buffer.from(msg.payload.data);\n\nmsg.filename = \"uploads/\" + msg.payload.filename;\nmsg.payload = data;\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1020,"y":80,"wires":[["3e4b59e2633f6eeb"]]},{"id":"66ce439ab58a55e1","type":"http response","z":"f40dad3e2042208d","name":"","statusCode":"200","headers":{},"x":420,"y":120,"wires":[]},{"id":"3473e0502a83ae42","type":"template","z":"f40dad3e2042208d","name":"Upload","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"File upload","output":"str","x":260,"y":120,"wires":[["66ce439ab58a55e1"]]},{"id":"7d3278b3d046456a","type":"function","z":"f40dad3e2042208d","name":"create table if needed","func":"msg.topic = \"CREATE TABLE if not exists tests.file_upload ( \" +\n \"\tid INT auto_increment primary key not NULL, \" +\n \" `filename` varchar(255) NULL, \" +\n \" `filesize` varchar(100) NULL, \" +\n \" `mimetype` varchar(100) NULL, \" +\n \" `data` longblob NOT NULL, \" +\n \" `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP \" +\n \") \" +\n \"ENGINE = InnoDB \" +\n \"DEFAULT CHARSET = utf8mb4 \" +\n \"COLLATE = utf8mb4_general_ci;\";\n\nmsg.payload = {};\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1060,"y":140,"wires":[["6c5f09f18f4ae32d","a594958877924b32"]]},{"id":"4d432042c9f2173a","type":"function","z":"f40dad3e2042208d","name":"insert data","func":"var payload = msg.payload;\nvar file = Buffer.from(payload.data).toString('base64');\n\nmsg.topic = \"insert into file_upload (`filename`, `filesize`, `mimetype`, `data`) values (:filename, :filesize, :mimetype, :data)\";\nmsg.payload = {\n filename: payload.filename,\n filesize: payload.filesize,\n mimetype: payload.mimetype,\n data: file\n};\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1030,"y":240,"wires":[["baa5ae3c02b8d5e9","a594958877924b32"]]},{"id":"6c5f09f18f4ae32d","type":"debug","z":"f40dad3e2042208d","name":"SQL Table Debug","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1290,"y":140,"wires":[]},{"id":"baa5ae3c02b8d5e9","type":"debug","z":"f40dad3e2042208d","name":"SQL Insert Debug","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1290,"y":240,"wires":[]},{"id":"94e1371c4d75ae48","type":"delay","z":"f40dad3e2042208d","name":"","pauseType":"delay","timeout":"2","timeoutUnits":"milliseconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"allowrate":false,"outputs":1,"x":1030,"y":180,"wires":[["4d432042c9f2173a"]]},{"id":"21954579392b5f8c","type":"switch","z":"f40dad3e2042208d","name":"","property":"payload","propertyType":"msg","rules":[{"t":"nempty"},{"t":"else"}],"checkall":"false","repair":false,"outputs":2,"x":130,"y":520,"wires":[["c3d65775f2a664ac"],["48036e61bce02f90"]]},{"id":"fc37b5ff8177f613","type":"http response","z":"f40dad3e2042208d","name":"200","statusCode":"200","headers":{},"x":510,"y":540,"wires":[]},{"id":"48036e61bce02f90","type":"template","z":"f40dad3e2042208d","name":"404","field":"payload","fieldType":"msg","format":"json","syntax":"plain","template":"{\n \"message\": \"Not found\",\n \"status\": 404\n}","output":"json","x":130,"y":580,"wires":[["9eec1bec20546b98"]]},{"id":"9eec1bec20546b98","type":"http response","z":"f40dad3e2042208d","name":"http (404)","statusCode":"404","headers":{},"x":140,"y":620,"wires":[]},{"id":"ccc3784c9a1edbb5","type":"debug","z":"f40dad3e2042208d","name":"Result Debug","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":880,"y":540,"wires":[]},{"id":"22c16267951fcc4f","type":"function","z":"f40dad3e2042208d","name":"validation","func":"var params = msg.req.params;\n\nif (!isNaN(params.id)) {\n msg.payload = { id: params.id};\n} else {\n msg.payload = { };\n}\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":120,"y":480,"wires":[["21954579392b5f8c"]]},{"id":"c3d65775f2a664ac","type":"function","z":"f40dad3e2042208d","name":"findById","func":"var id = msg.payload.id;\n\nmsg.topic = \"select * from file_upload where id = :id;\";\nmsg.payload = { id: id };\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":320,"y":480,"wires":[["e000d15e4761ac76"]]},{"id":"e000d15e4761ac76","type":"Stackhero-MySQL","z":"f40dad3e2042208d","server":"8df4454477e1c3e3","name":"","x":480,"y":480,"wires":[["921d4df3aec4694b"]]},{"id":"921d4df3aec4694b","type":"function","z":"f40dad3e2042208d","name":"findById result","func":"msg.topic = \"\";\nvar result = msg.payload;\n\n\nif (Array.isArray(result) && result[0]) {\n var data = result[0];\n var file = Buffer.from(data.data.toString('utf8'), 'base64');\n\n msg.headers = {\n \"Content-Type\": data.mimetype,\n \"Content-Disposition\": 'attachment; filename=' + data.filename\n };\n msg.id = data.id;\n msg.payload = file;\n\n} else {\n msg.payload = {};\n}\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":660,"y":480,"wires":[["ccc3784c9a1edbb5","99670f9993a12b70"]]},{"id":"99670f9993a12b70","type":"switch","z":"f40dad3e2042208d","name":"","property":"payload","propertyType":"msg","rules":[{"t":"nempty"},{"t":"else"}],"checkall":"false","repair":false,"outputs":2,"x":330,"y":540,"wires":[["fc37b5ff8177f613"],["48036e61bce02f90"]]},{"id":"7b40deba5d4d46aa","type":"catch","z":"f40dad3e2042208d","name":"","scope":["e4fd0340eb438fa9"],"uncaught":false,"x":670,"y":620,"wires":[["ccc3784c9a1edbb5","3b1bb9d3cf2248d4"]]},{"id":"3b1bb9d3cf2248d4","type":"http response","z":"f40dad3e2042208d","name":"500","statusCode":"500","headers":{},"x":890,"y":620,"wires":[]},{"id":"5e0d37384ef4f7c9","type":"http in","z":"f40dad3e2042208d","name":"","url":"/binary/:id","method":"get","upload":false,"swaggerDoc":"","x":120,"y":420,"wires":[["22c16267951fcc4f"]],"info":"Find a file and return the base64 \ndecoded value from the database.\n\nThis is / should be called from /file/:id instead of directly.\n\n"},{"id":"d84998fbe1421a90","type":"http request","z":"f40dad3e2042208d","name":"","method":"GET","ret":"bin","paytoqs":"ignore","url":"http://localhost:1880/binary/{{{payload.id}}}","tls":"","persist":false,"proxy":"","insecureHTTPParser":false,"authType":"","senderr":false,"headers":[],"x":370,"y":200,"wires":[["56e752b4fcc6f10f"]]},{"id":"211bb7c309f50e7f","type":"http in","z":"f40dad3e2042208d","name":"","url":"/file/:id","method":"get","upload":false,"swaggerDoc":"","x":110,"y":200,"wires":[["82eeafa9f0de99e4"]]},{"id":"09865f9dbdc24a93","type":"switch","z":"f40dad3e2042208d","name":"","property":"payload","propertyType":"msg","rules":[{"t":"nempty"},{"t":"else"}],"checkall":"false","repair":false,"outputs":2,"x":130,"y":300,"wires":[["d84998fbe1421a90"],["74a9eadff7f587ee"]]},{"id":"74a9eadff7f587ee","type":"template","z":"f40dad3e2042208d","name":"404","field":"payload","fieldType":"msg","format":"json","syntax":"plain","template":"{\n \"message\": \"Not found\",\n \"status\": 404\n}","output":"json","x":290,"y":300,"wires":[["80ecd9d6bb840c72"]]},{"id":"80ecd9d6bb840c72","type":"http response","z":"f40dad3e2042208d","name":"http (404)","statusCode":"404","headers":{},"x":440,"y":300,"wires":[]},{"id":"82eeafa9f0de99e4","type":"function","z":"f40dad3e2042208d","name":"validation","func":"var params = msg.req.params;\n\nif (!isNaN(params.id)) {\n msg.payload = { id: params.id};\n} else {\n msg.payload = { };\n}\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":120,"y":260,"wires":[["09865f9dbdc24a93"]]},{"id":"fbef17ac744c56cb","type":"http response","z":"f40dad3e2042208d","name":"","statusCode":"200","headers":{},"x":720,"y":200,"wires":[]},{"id":"56e752b4fcc6f10f","type":"function","z":"f40dad3e2042208d","name":"Resp Headers","func":"var headers = msg.headers;\n\nmsg.headers = {};\n\nvar obj = {\n \"Content-Type\": headers['content-type'],\n \"Content-Disposition\": headers['content-disposition']\n};\n\nmsg.headers = obj;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":540,"y":200,"wires":[["fbef17ac744c56cb"]]},{"id":"ea83830bf665d01e","type":"mqtt-broker","name":"","broker":"mq","port":"1883","clientid":"","autoConnect":true,"usetls":false,"protocolVersion":"4","keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","birthMsg":{},"closeTopic":"","closeQos":"0","closePayload":"","closeMsg":{},"willTopic":"","willQos":"0","willPayload":"","willMsg":{},"userProps":"","sessionExpiry":""},{"id":"8df4454477e1c3e3","type":"Stackhero-MySQL-Server","name":"tests db","host":"db","port":"3306","tls":false,"database":"tests"}]
