Dasboard-2-sqlite-crud : Create - Update - Delete the records of your Sqlite tables
This flow contains an example of dasboard-2-sqlite-crud subflow that is still under heavy development.
You can see the topic on the forum about this subflow.
Dependencies
[{"id":"35cfe52e2d5716f2","type":"tab","label":"Flux 1","disabled":false,"info":"","env":[]},{"id":"c303567e3bd31179","type":"subflow","name":"CRUD","info":"Provides CRUD operations for a SQLITE database\r\n\r\n### Dependancies\r\n* node-red-node-sqlite\r\n* @flowfuse/node-red-dashboard\r\n\r\n### Inputs\r\n\r\n: *tableName* (string) : (optional) Override the set table name.\r\n\r\n### Details\r\n\r\nSimple place a CRUD node on your flow, select a database and a page group\r\nand enter the name of the table.\r\n\r\nAt runtime, the selected table can be adjusted bt sending a message\r\nwith a different table name e.g:\r\n\r\n```\r\nmsg.tableName = 'table2'\r\n```\r\n","category":"dashboard 2","in":[{"x":60,"y":60,"wires":[{"id":"814556f5c568a525"}]}],"out":[{"x":820,"y":440,"wires":[{"id":"bab0d5516e735f49","port":0}]}],"env":[{"name":"group","type":"ui-group","value":"","ui":{"icon":"font-awesome/fa-object-group","label":{"fr":"Group","en-US":"UI Group"},"type":"conf-types"}},{"name":"sqlite","type":"sqlitedb","value":"","ui":{"icon":"font-awesome/fa-database","label":{"fr":"sqlite DB","en-US":"Database"},"type":"conf-types"}},{"name":"tableName","type":"str","value":"table1","ui":{"icon":"font-awesome/fa-table","label":{"fr":"Table Name","en-US":"Table"},"type":"input","opts":{"types":["str","env"]}}}],"meta":{"module":"dasboard-2-sqlite-crud","type":"dasboard-2-sqlite-crud","version":"0.2","author":"Cyprien","desc":"A Subflow for CRUD operations on SQL tables","keywords":"sqlite dashboard-2.0","license":"MIT"},"color":"#3FADB5","inputLabels":["Table name"],"icon":"font-awesome/fa-database"},{"id":"8bb63501e512cea0","type":"junction","z":"c303567e3bd31179","x":80,"y":260,"wires":[["a224518bfb230ad0","8864a1534852e8bf"]]},{"id":"6e2149f9d3d66c37","type":"ui-base","name":"My Dashboard","path":"/dashboard","includeClientData":true,"acceptsClientConfig":["ui-notification","ui-control"],"showPathInSidebar":false,"showPageTitle":true,"navigationStyle":"default","titleBarStyle":"default"},{"id":"1c0f3d4725d7a082","type":"ui-theme","name":"Default Theme","colors":{"surface":"#ffffff","primary":"#0094CE","bgPage":"#eeeeee","groupBg":"#ffffff","groupOutline":"#cccccc"},"sizes":{"pagePadding":"12px","groupGap":"12px","groupBorderRadius":"4px","widgetGap":"12px"}},{"id":"cc40bb10b267f42c","type":"ui-page","name":"Page 1","ui":"6e2149f9d3d66c37","path":"/page1","icon":"home","layout":"grid","theme":"1c0f3d4725d7a082","order":1,"className":"","visible":"true","disabled":"false"},{"id":"f266d749d3f32880","type":"ui-group","name":"My Group","page":"cc40bb10b267f42c","width":6,"height":1,"order":1,"showTitle":true,"className":"","visible":true,"disabled":false},{"id":"c7c418f2f2495625","type":"sqlitedb","db":"nodered.sqlite","mode":"RWC"},{"id":"5f592bffd34021f8","type":"ui-group","name":"CRUD Test","page":"6685af11067a04cd","width":"12","height":"1","order":1,"showTitle":true,"className":"","visible":"true","disabled":"false"},{"id":"6685af11067a04cd","type":"ui-page","name":"Sqlite CRUD","ui":"6e2149f9d3d66c37","path":"/page8","icon":"home","layout":"grid","theme":"c2ff5ba1f92a0f0e","order":2,"className":"","visible":"true","disabled":"false"},{"id":"c2ff5ba1f92a0f0e","type":"ui-theme","name":"Default","colors":{"surface":"#ffffff","primary":"#0094ce","bgPage":"#eeeeee","groupBg":"#ffffff","groupOutline":"#cccccc"},"sizes":{"pagePadding":"12px","groupGap":"12px","groupBorderRadius":"4px","widgetGap":"12px"}},{"id":"0a27aaf6a3bdd877","type":"sqlite","z":"c303567e3bd31179","mydb":"${sqlite}","sqlquery":"msg.topic","sql":"","name":"","x":330,"y":280,"wires":[["8b2a6046199cac92"]]},{"id":"aa3e508a944f6f82","type":"sqlite","z":"c303567e3bd31179","mydb":"${sqlite}","sqlquery":"msg.topic","sql":"","name":"","x":330,"y":240,"wires":[["8b2a6046199cac92"]]},{"id":"4e68cbeba8f82f1a","type":"ui-template","z":"c303567e3bd31179","group":"${group}","page":"","ui":"","name":"Table","order":0,"width":0,"height":0,"head":"","format":"<template>\n <v-card flat>\n <template v-slot:text>\n <v-text-field \n v-model=\"search\" \n label=\"Search\" \n prepend-inner-icon=\"mdi-magnify\" \n variant=\"outlined\" \n hide-details\n single-line>\n </v-text-field>\n </template>\n <v-data-table :search=\"search\" :headers=\"headers\" :items=\"items\" :sort-by=\"[{ key: sortBy, order: 'asc' }]\">\n <template v-slot:top>\n <v-toolbar flat>\n <v-toolbar-title>{{ tableName || 'CRUD' }}</v-toolbar-title>\n <v-divider class=\"mx-4\" inset vertical></v-divider>\n <v-spacer></v-spacer>\n <v-dialog v-model=\"dialog\" max-width=\"500px\">\n <template v-slot:activator=\"{ props }\">\n <v-btn class=\"mb-2\" color=\"primary\" dark v-bind=\"props\">\n New Item\n </v-btn>\n </template>\n <v-card>\n <v-card-title>\n <span class=\"text-h5\">{{ formTitle }}</span>\n </v-card-title>\n\n <v-card-text>\n <v-container>\n <v-row>\n <v-col v-for=\"header in editableHeaders\" :key=\"header.key\" cols=\"12\" md=\"4\" sm=\"6\">\n <v-text-field v-model=\"editedItem[header.key]\" :label=\"header.title\">\n </v-text-field>\n </v-col>\n </v-row>\n </v-container>\n </v-card-text>\n\n <v-card-actions>\n <v-spacer></v-spacer>\n <v-btn color=\"blue-darken-1\" variant=\"text\" @click=\"close\">\n Cancel\n </v-btn>\n <v-btn color=\"blue-darken-1\" variant=\"text\" @click=\"save\">\n Save\n </v-btn>\n </v-card-actions>\n </v-card>\n </v-dialog>\n <v-dialog v-model=\"dialogDelete\" max-width=\"500px\">\n <v-card>\n <v-card-title class=\"text-h5\">Are you sure you want to delete this item?</v-card-title>\n <v-card-actions>\n <v-spacer></v-spacer>\n <v-btn color=\"blue-darken-1\" variant=\"text\" @click=\"closeDelete\">Cancel</v-btn>\n <v-btn color=\"blue-darken-1\" variant=\"text\" @click=\"deleteItemConfirm\">OK</v-btn>\n <v-spacer></v-spacer>\n </v-card-actions>\n </v-card>\n </v-dialog>\n </v-toolbar>\n </template>\n <template v-slot:item.actions=\"{ item }\">\n <v-icon class=\"me-2\" size=\"small\" @click=\"editItem(item)\">\n mdi-pencil\n </v-icon>\n <v-icon size=\"small\" @click=\"deleteItem(item)\">\n mdi-delete\n </v-icon>\n </template>\n <template v-slot:no-data>\n <v-btn color=\"primary\" @click=\"initialize\">\n Reset\n </v-btn>\n </template>\n </v-data-table>\n </v-card>\n</template>\n\n<script>\n export default {\n data() {\n return {\n search: '',\n dialog: false,\n dialogDelete: false,\n headers: [],\n editableHeaders: [],\n items: [],\n editedIndex: -1,\n editedItem: {},\n defaultItem: {},\n sortBy: '',\n pkColumn: null,\n tableName: '',\n tableInfo: [],\n initialItems: []\n }\n },\n computed: {\n formTitle() {\n return this.editedIndex === -1 ? 'New Item' : 'Edit Item'\n },\n },\n watch: {\n dialog(val) {\n val || this.close()\n },\n dialogDelete(val) {\n val || this.closeDelete()\n },\n // Is it better to use $socket ?\n msg: function () {\n console.log(this.msg.payload)\n this.initialize()\n },\n },\n created() {\n this.initialize()\n },\n methods: {\n initialize() {\n this.tableName = this.msg.tableName\n this.tableInfo = this.msg.payload.pragma\n this.items = this.msg.payload.select\n this.pkColumn = this.tableInfo.find(column => column.pk === 1)\n \n this.headers = this.tableInfo.map(column => ({\n title: column.name,\n key: column.name,\n sortable: true,\n isPk: column.pk === 1\n }))\n this.headers.push({ title: 'Actions', key: 'actions', sortable: false })\n\n this.editableHeaders = this.headers.filter(header => !header.isPk && header.key !== 'actions')\n\n this.defaultItem = this.tableInfo.reduce((acc, column) => {\n if (!column.pk) {\n acc[column.name] = null\n }\n return acc\n }, {})\n\n this.editedItem = { ...this.defaultItem }\n //this.items = [...this.initialItems]\n\n // Set the default sort column (first non-PK column)\n this.sortBy = this.headers.find(header => !header.isPk && header.key !== 'actions')?.key || ''\n },\n editItem(item) {\n this.editedIndex = this.items.indexOf(item)\n this.editedItem = { ...this.defaultItem }\n for (let key in this.defaultItem) {\n this.editedItem[key] = item[key]\n }\n this.dialog = true\n },\n deleteItem(item) {\n this.editedIndex = this.items.indexOf(item)\n this.editedItem = { ...item }\n this.dialogDelete = true\n },\n deleteItemConfirm() {\n console.log(this.editedItem)\n const payload = {\n topic: \"DELETE\",\n tableName: this.tableName,\n payload: {\n id: this.editedItem.id\n }\n };\n this.send(payload)\n this.items.splice(this.editedIndex, 1)\n\n this.closeDelete()\n },\n close() {\n this.dialog = false\n this.$nextTick(() => {\n this.editedItem = { ...this.defaultItem }\n this.editedIndex = -1\n })\n },\n closeDelete() {\n this.dialogDelete = false\n this.$nextTick(() => {\n this.editedItem = { ...this.defaultItem }\n this.editedIndex = -1\n })\n },\n // mounted() {\n // this.$socket.on('msg-input:' + this.id, function(msg) {\n // // do stuff with the message\n // console.log('message received: ' + msg.payload)\n // })\n // },\n save() {\n if (this.editedIndex > -1) {\n // Update existing item\n const updatedItem = { ...this.items[this.editedIndex] }\n for (let key in this.editedItem) {\n updatedItem[key] = this.editedItem[key]\n }\n Object.assign(this.items[this.editedIndex], updatedItem)\n const payload = {\n topic: \"UDPATE\",\n tableName: this.tableName,\n payload: {\n item: updatedItem\n }\n };\n this.send(payload)\n } else {\n // Create new item\n const newItem = { ...this.editedItem }\n if (this.pkColumn) {\n // Generate a temporary ID for the new item\n // In a real application, this would be handled by the backend\n newItem[this.pkColumn.name] = Math.max(0, ...this.items.map(item => item[this.pkColumn.name])) + 1\n }\n this.items.push(newItem)\n const payload = {\n topic: \"INSERT\",\n tableName: this.tableName,\n payload: {\n item: newItem\n }\n };\n this.send(payload)\n }\n this.close()\n },\n },\n}\n</script>","storeOutMessages":true,"passthru":true,"resendOnRefresh":true,"templateScope":"local","className":"","x":170,"y":440,"wires":[["183212e5b6cf7098"]]},{"id":"8b2a6046199cac92","type":"join","z":"c303567e3bd31179","name":"pragma + select","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"action","joiner":"\\n","joinerType":"str","useparts":true,"accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":530,"y":260,"wires":[["4e68cbeba8f82f1a"]]},{"id":"183212e5b6cf7098","type":"switch","z":"c303567e3bd31179","name":"","property":"topic","propertyType":"msg","rules":[{"t":"eq","v":"DELETE","vt":"str"},{"t":"eq","v":"UDPATE","vt":"str"},{"t":"eq","v":"INSERT","vt":"str"}],"checkall":"true","repair":false,"outputs":3,"x":330,"y":440,"wires":[["38376f96fc57c7b2"],["d1846549f2f97804"],["cb05e5ceb2903a29"]]},{"id":"cb05e5ceb2903a29","type":"function","z":"c303567e3bd31179","name":"INSERT","func":"node.warn({ payload: msg.payload });\nconst data = RED.util.cloneMessage(msg.payload.item);\nconst tableName = msg.tableName\n\n// Extract the ID\nconst id = data.id\ndelete data.id\n\n// Build columns, data placeholders and values array\nconst entries = Object.entries(data)\nconst columns = []\nconst values = []\nconst keys = Object.keys(data)\nfor (let index = 0; index < keys.length; index++) {\n const key = keys[index]\n columns.push(sqlstring.escape(keys[index])) // sanitize\n values.push(data[key])\n}\nconst columnsNames = columns.join(', ')\nconst placeHolders = values.map(() => '?').join(', ')\n\n// Construct query using sqlstring.format to avoid SQLi\nmsg.topic = sqlstring.format(`INSERT INTO ?? (${columnsNames}) \\n VALUES (${placeHolders})`, [tableName, ...values, id])\n\nreturn msg\n","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[{"var":"sqlstring","module":"sqlstring"}],"x":500,"y":480,"wires":[["bab0d5516e735f49"]]},{"id":"bab0d5516e735f49","type":"sqlite","z":"c303567e3bd31179","mydb":"${sqlite}","sqlquery":"msg.topic","sql":"","name":"","x":690,"y":440,"wires":[["db8543fe2425d54a"]]},{"id":"38376f96fc57c7b2","type":"function","z":"c303567e3bd31179","name":"DELETE","func":"const tableName = msg.tableName\nmsg.topic = sqlstring.format(`DELETE FROM ?? WHERE id = ?`, [tableName, msg.payload.id])\nreturn msg\n","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[{"var":"sqlstring","module":"sqlstring"}],"x":500,"y":400,"wires":[["bab0d5516e735f49"]]},{"id":"d1846549f2f97804","type":"function","z":"c303567e3bd31179","name":"UPDATE","func":"const data = RED.util.cloneMessage(msg.payload.item)\nconst tableName = msg.tableName\n\n// Extract the ID\nconst id = data.id\ndelete data.id\n\n// Build columns, data placeholders and values array\nconst entries = Object.entries(data)\nconst sets = []\nconst values = []\nconst keys = Object.keys(data)\nfor (let index = 0; index < keys.length; index++) {\n const key = keys[index]\n sets.push(`${sqlstring.escape(keys[index])} = ?`) // sanitize\n values.push(data[key])\n}\nconst setWhat = sets.join(', ')\n\n// Construct query\nmsg.topic = sqlstring.format(`UPDATE ?? SET ${setWhat} WHERE id = ?`, [tableName, ...values, id])\n\nreturn msg\n","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[{"var":"sqlstring","module":"sqlstring"}],"x":500,"y":440,"wires":[["bab0d5516e735f49"]]},{"id":"755df15324a9f017","type":"link in","z":"c303567e3bd31179","name":"link in 1","links":["db8543fe2425d54a"],"x":385,"y":140,"wires":[["5fc7b414cefb9a53"]]},{"id":"db8543fe2425d54a","type":"link out","z":"c303567e3bd31179","name":"link out 1","mode":"link","links":["755df15324a9f017"],"x":815,"y":400,"wires":[]},{"id":"814556f5c568a525","type":"switch","z":"c303567e3bd31179","name":"has msg.tableName?","property":"tableName","propertyType":"msg","rules":[{"t":"nempty"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":220,"y":40,"wires":[["0a6393ef22b912d7"],["420c95722bd1cbad"]]},{"id":"420c95722bd1cbad","type":"change","z":"c303567e3bd31179","name":"use env tableName","rules":[{"t":"set","p":"tableName","pt":"msg","to":"tableName","tot":"env"}],"action":"","property":"","from":"","to":"","reg":false,"x":230,"y":100,"wires":[["0a6393ef22b912d7"]]},{"id":"a224518bfb230ad0","type":"function","z":"c303567e3bd31179","name":"PRAGMA","func":"msg.topic = sqlstring.format('PRAGMA table_info(??)', [msg.tableName])\nmsg.action = 'pragma'\nreturn msg\n","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[{"var":"sqlstring","module":"sqlstring"}],"x":180,"y":240,"wires":[["aa3e508a944f6f82"]]},{"id":"8864a1534852e8bf","type":"function","z":"c303567e3bd31179","name":"SELECT","func":"\nmsg.topic = sqlstring.format('SELECT * FROM ?? LIMIT 100', [msg.tableName])\nmsg.action = 'select'\nreturn msg\n","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[{"var":"sqlstring","module":"sqlstring"}],"x":180,"y":280,"wires":[["0a27aaf6a3bdd877"]]},{"id":"5fc7b414cefb9a53","type":"change","z":"c303567e3bd31179","name":"","rules":[{"t":"set","p":"tableName","pt":"msg","to":"tableName","tot":"flow"}],"action":"","property":"","from":"","to":"","reg":false,"x":530,"y":100,"wires":[["8bb63501e512cea0"]]},{"id":"0a6393ef22b912d7","type":"change","z":"c303567e3bd31179","name":"","rules":[{"t":"set","p":"tableName","pt":"flow","to":"tableName","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":530,"y":40,"wires":[["5fc7b414cefb9a53"]]},{"id":"db32bad8827c60f7","type":"subflow:c303567e3bd31179","z":"35cfe52e2d5716f2","name":"","env":[{"name":"group","value":"5f592bffd34021f8","type":"conf-type"},{"name":"sqlite","value":"c7c418f2f2495625","type":"conf-type"},{"name":"tableName","value":"Test","type":"str"}],"x":830,"y":220,"wires":[[]]},{"id":"0e3b50da602fba72","type":"inject","z":"35cfe52e2d5716f2","name":"","props":[{"p":"tableName","v":"table1","vt":"str"},{"p":"payload"}],"repeat":"","crontab":"","once":true,"onceDelay":0.1,"topic":"","payload":"table1","payloadType":"str","x":270,"y":220,"wires":[["8edc754ba4e9785d"]]},{"id":"8edc754ba4e9785d","type":"ui-dropdown","z":"35cfe52e2d5716f2","group":"5f592bffd34021f8","name":"","label":"Select a table:","tooltip":"","order":2,"width":"12","height":"1","passthru":true,"multiple":false,"chips":false,"clearable":false,"options":[{"label":"Test","value":"Test","type":"str"},{"label":"Dessert","value":"Dessert","type":"str"}],"payload":"","topic":"topic","topicType":"msg","className":"","x":440,"y":220,"wires":[["431683103aa8f002"]]},{"id":"431683103aa8f002","type":"change","z":"35cfe52e2d5716f2","name":"","rules":[{"t":"set","p":"tableName","pt":"msg","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":650,"y":220,"wires":[["db32bad8827c60f7"]]}]
