display sqlite tables in node-red

knex.json

[{"id":"8484baf5.275938","type":"function","z":"5479c4bc.d53f9c","name":"sql exec","func":"var statements = msg.payload.split(\";\");\nflow.set('responses',[]);\nflow.set('statements',statements);\nflow.set('count',0);\nflow.set('exec', function(){\n    context.global.knex.raw(flow.get('statements')[flow.get('count')]).then(\n\t    function(resp){\n\t        var responses = flow.get('responses');\n\t        responses.push(resp);\n\t        flow.set('count',flow.get('count')+1);\n\t        if(flow.get('count')>=flow.get('statements').length){\n\t            msg.payload = JSON.stringify(responses);\n\t            msg.headers = {\n                     'Content-type' : 'application/json'\n                };\n\t            node.send(msg);\n\t        } else {\n\t            flow.get('exec')();\n\t        }\n\t    }\n    );\n} );\nflow.get('exec')();\nreturn null;","outputs":1,"noerr":0,"x":413,"y":106,"wires":[["3c0038e1.30eaa8","85edeaec.462b58","44d17862.358b98"]]},{"id":"4e5783cd.149e3c","type":"inject","z":"5479c4bc.d53f9c","name":"supplier","topic":"test","payload":"select rowid, * from supplier order by rowid limit 5 offset 0","payloadType":"str","repeat":"","crontab":"","once":false,"x":151,"y":106,"wires":[["8484baf5.275938"]]},{"id":"3c0038e1.30eaa8","type":"debug","z":"5479c4bc.d53f9c","name":"","active":false,"console":"false","complete":"false","x":844,"y":105,"wires":[]},{"id":"257c3872.0f6858","type":"http in","z":"5479c4bc.d53f9c","name":"sql webservice","url":"/sql","method":"get","swaggerDoc":"","x":106,"y":34,"wires":[["6acbe4f9.f0ad5c"]]},{"id":"6acbe4f9.f0ad5c","type":"function","z":"5479c4bc.d53f9c","name":"prepare sql","func":"if(msg.payload.sql!==undefined){\n    msg.topic=\"http\";\n    msg.payload=msg.payload.sql;\n    return msg;\n}\nreturn null;","outputs":1,"noerr":0,"x":292,"y":34,"wires":[["8484baf5.275938","25835abf.56b916"]]},{"id":"3485c5de.2001da","type":"http response","z":"5479c4bc.d53f9c","name":"","x":827,"y":32,"wires":[]},{"id":"25835abf.56b916","type":"debug","z":"5479c4bc.d53f9c","name":"","active":false,"console":"false","complete":"payload","x":474,"y":33,"wires":[]},{"id":"85edeaec.462b58","type":"function","z":"5479c4bc.d53f9c","name":"http?","func":"if(msg.topic===\"http\")\n    return msg;\nelse\n    return null;","outputs":1,"noerr":0,"x":683,"y":32,"wires":[["3485c5de.2001da"]]},{"id":"44d17862.358b98","type":"ui_template","z":"5479c4bc.d53f9c","group":"1e0e0f3c.a2d8b1","name":"Table View","order":2,"width":"0","height":"0","format":"<table>\n<tr>\n  <th ng-repeat=\"(key,value) in table[0]\">{{key}}</th>\n</tr>\n<tbody ng-repeat=\"row in table\">\n<tr ng-if=\"$even\">\n  <td ng-repeat=\"(key,value) in row\">{{value}}</td>\n</tr>\n<tr ng-if=\"$odd\">\n  <td style=\"background-color:#f1f1f1\" ng-repeat=\"(key,value) in row\">{{value}}</td>\n</tr>\n</tbody> \n</table>\n<style>\ntable, td  {\n  border: 1px solid grey;\n  border-collapse: collapse;\n  padding: 5px;\n}\n</style>\n<script>\n(function(scope) {\n    // debugger;\n    scope.table=[];\n    scope.$watch('msg', function (newValue, oldValue, scope) {\n            scope.table=JSON.parse(scope.msg.payload)[0];\n    });\n})(scope);    \n</script>","storeOutMessages":true,"fwdInMessages":true,"x":639,"y":174,"wires":[[]]},{"id":"a9407c55.7a262","type":"inject","z":"5479c4bc.d53f9c","name":"customer","topic":"test","payload":"select rowid, * from customer order by rowid limit 5 offset 0","payloadType":"str","repeat":"","crontab":"","once":false,"x":159,"y":176,"wires":[["8484baf5.275938"]]},{"id":"cac67e00.3e2e5","type":"ui_template","z":"5479c4bc.d53f9c","group":"1e0e0f3c.a2d8b1","name":"paging controller","order":3,"width":0,"height":0,"format":"<div paging page=\"page\" page-size=\"page_size\" total=\"total\" paging-action=\"paging(page, pageSize, total)\">\n</div> \n<script>\n(function(scope) {\n    scope.total=0;\n    scope.page_size=5;\n    scope.page=0;\n    scope.$watch('msg', function (newValue, oldValue, scope) {\n        // debugger;\n        if(scope.msg!==undefined){\n            scope.total=scope.msg.total;\n            scope.page_size=scope.msg.page_size;\n        }\n            \n    });\n    scope.paging=function(page,pageSize,total){\n        // debugger;\n        scope.msg.page=page;\n        scope.msg.page_size=pageSize;\n        scope.send(scope.msg);\n    };\n})(scope); \n</script>","storeOutMessages":true,"fwdInMessages":true,"x":679,"y":346,"wires":[["8ad1fca2.1d83c","55a11b3e.bf2a54"]]},{"id":"433e6055.23758","type":"function","z":"5479c4bc.d53f9c","name":"total, page_size","func":"msg.page_size=5;\nmsg.page=0;\nmsg.table=\"'\"+msg.payload+\"'\";\ncontext.global.knex.raw('select Count(*) as count from '+msg.table).then(\n\t    function(resp){\n\t        // console.log(resp[0].count);\n\t        msg.total=resp[0].count;\n\t        node.send(msg);\n\t    });\nreturn null;","outputs":1,"noerr":0,"x":458,"y":346,"wires":[["cac67e00.3e2e5"]]},{"id":"55a11b3e.bf2a54","type":"function","z":"5479c4bc.d53f9c","name":"sql page request","func":"msg.payload=\"select rowid, * from  \"+msg.table+\" order by rowid limit \"+msg.page_size+\" offset \"+msg.page_size*(msg.page-1);\nreturn msg;","outputs":1,"noerr":0,"x":418,"y":218,"wires":[["8484baf5.275938"]]},{"id":"109f19f5.b33ac6","type":"inject","z":"5479c4bc.d53f9c","name":"","topic":"","payload":"customer","payloadType":"str","repeat":"","crontab":"","once":false,"x":255,"y":345,"wires":[["433e6055.23758"]]},{"id":"8ad1fca2.1d83c","type":"debug","z":"5479c4bc.d53f9c","name":"","active":false,"console":"false","complete":"true","x":891,"y":345,"wires":[]},{"id":"2e25d478.6b392c","type":"ui_dropdown","z":"5479c4bc.d53f9c","name":"Table choice","label":"","group":"1e0e0f3c.a2d8b1","order":1,"width":"5","height":"1","passthru":true,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"","x":362,"y":448,"wires":[["433e6055.23758"]]},{"id":"2bd8bce4.3c6eb4","type":"function","z":"5479c4bc.d53f9c","name":"Database tables","func":"context.global.knex.raw('select * from sqlite_master').then(\n\t    function(resp){\n\t        msg.options=[];\n\t        for(var i=0; i<resp.length;i++){\n\t            if(resp[i].type==='table')\n\t                msg.options.push(resp[i].name);\n\t        }\n\t        msg.options=msg.options.sort();\n\t        msg.payload=msg.options[0];\n\t        node.send(msg);\n\t    });\nreturn null;","outputs":1,"noerr":0,"x":265,"y":528,"wires":[["2e25d478.6b392c","9040fa3d.ec5f18"]]},{"id":"46cc8faa.d1fe6","type":"inject","z":"5479c4bc.d53f9c","name":"once","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":true,"x":96,"y":530,"wires":[["2bd8bce4.3c6eb4"]]},{"id":"9040fa3d.ec5f18","type":"debug","z":"5479c4bc.d53f9c","name":"","active":false,"console":"false","complete":"true","x":526,"y":528,"wires":[]},{"id":"1e0e0f3c.a2d8b1","type":"ui_group","z":"","name":"Table Browser","tab":"dd66fffe.8e271","disp":true,"width":"26"},{"id":"dd66fffe.8e271","type":"ui_tab","z":"","name":"SQL","icon":"dashboard"}]

this is needed in settings.js (.node-red in user directory):

   functionGlobalContext: {
        // os:require('os'),
        // octalbonescript:require('octalbonescript'),
        // jfive:require("johnny-five"),
        // j5board:require("johnny-five").Board({repl:false})
		knex:require('knex')({
						client: 'sqlite3',
						connection: {
							filename: './Northwind.sqlite'
						},
						useNullAsDefault: true
					})
	},

And Northwind.sqlite is in the user directory...

...And copy the flow into node-red!

see there for full documentation there