Hello,
I am very pleased to share with you a subflow that enables CRUD operations using Dashboard 2 and SQLite.
Getting Started:
-
Add a Dashboard 2 group
-
Add an SQLite database (node-red-node-sqlite)
-
Add a table / Columns in the database with another tool
-
Import the subflow
-
Configure the subflow (The name of the table is not working now...)
-
Add an inject node and specify the name of the table in msg.payload
-
Start the dashboard and inject the name of the table
-
The table columns are created based on the columns in the specified table
-
You can update records
-
You can delete records
-
You can add records
-
The subflow output show SQL Queries
Why a subflow ?
I started from the Vuetify example (CRUD Actions). Initially, I attempted to do this by creating a new third-party widget (Third-party Widgets), but found it more suitable to develop a subflow instead.
Known Issues:
- I am unable to retrieve the table name from the Subflow configuration variable, so I use a global variable instead (if you can help me with this problem...)
- Better field type management is needed
Possible Improvements:
- Allow for configuration of each column (Title, color, etc.)
- Add filters to search database
- Add support for MySQL/PostgreSQL (I love PostgreSQL!)
- Package the Subflow as a module (Creating Subflow Modules)
- Manage foreign keys to display multiple tables
Feel free to test it out!
[
{
"id": "c303567e3bd31179",
"type": "subflow",
"name": "CRUD",
"info": "",
"category": "dashboard 2",
"in": [
{
"x": 60,
"y": 60,
"wires": [
{
"id": "e97f75d0134f7456"
}
]
}
],
"out": [
{
"x": 800,
"y": 280,
"wires": [
{
"id": "bab0d5516e735f49",
"port": 0
}
]
}
],
"env": [
{
"name": "group",
"type": "ui-group",
"value": "",
"ui": {
"icon": "font-awesome/fa-object-group",
"label": {
"fr": "Group"
},
"type": "conf-types"
}
},
{
"name": "sqlite",
"type": "sqlitedb",
"value": "",
"ui": {
"icon": "font-awesome/fa-database",
"label": {
"fr": "sqlite DB"
},
"type": "conf-types"
}
},
{
"name": "tableName",
"type": "env",
"value": "",
"ui": {
"label": {
"fr": "Table Name"
},
"type": "input",
"opts": {
"types": [
"env"
]
}
}
}
],
"meta": {},
"color": "#3FADB5",
"inputLabels": [
"Table name"
],
"icon": "font-awesome/fa-database"
},
{
"id": "0ba01e84a6c7c311",
"type": "junction",
"z": "c303567e3bd31179",
"x": 340,
"y": 80,
"wires": [
[
"2a1b4455eeac53fa",
"60bf13ec8c368a1b"
]
]
},
{
"id": "0a27aaf6a3bdd877",
"type": "sqlite",
"z": "c303567e3bd31179",
"mydb": "${sqlite}",
"sqlquery": "msg.topic",
"sql": "",
"name": "",
"x": 590,
"y": 100,
"wires": [
[
"8b2a6046199cac92"
]
]
},
{
"id": "2a1b4455eeac53fa",
"type": "change",
"z": "c303567e3bd31179",
"name": "SELECT",
"rules": [
{
"t": "set",
"p": "topic",
"pt": "msg",
"to": "\"SELECT * FROM \" & $globalContext(\"tableName\") & \" LIMIT 100\"",
"tot": "jsonata"
},
{
"t": "set",
"p": "action",
"pt": "msg",
"to": "select",
"tot": "str"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 440,
"y": 100,
"wires": [
[
"0a27aaf6a3bdd877"
]
]
},
{
"id": "60bf13ec8c368a1b",
"type": "change",
"z": "c303567e3bd31179",
"name": "PRAGMA",
"rules": [
{
"t": "set",
"p": "topic",
"pt": "msg",
"to": "\"PRAGMA table_info(\" & $globalContext(\"tableName\") & \")\"",
"tot": "jsonata"
},
{
"t": "set",
"p": "action",
"pt": "msg",
"to": "pragma",
"tot": "str"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 440,
"y": 60,
"wires": [
[
"aa3e508a944f6f82"
]
]
},
{
"id": "aa3e508a944f6f82",
"type": "sqlite",
"z": "c303567e3bd31179",
"mydb": "${sqlite}",
"sqlquery": "msg.topic",
"sql": "",
"name": "",
"x": 590,
"y": 60,
"wires": [
[
"8b2a6046199cac92"
]
]
},
{
"id": "4e68cbeba8f82f1a",
"type": "ui-template",
"z": "c303567e3bd31179",
"group": "${group}",
"name": "Table",
"order": 0,
"width": 0,
"height": 0,
"head": "",
"format": "<template>\n <v-data-table :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</template>\n\n<script>\n export default {\n props: {\n tableName: {\n type: String,\n required: true\n },\n tableInfo: {\n type: Array,\n required: true\n },\n initialItems: {\n type: Array,\n default: () => []\n }\n },\n data() {\n return {\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 }\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.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 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 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 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": 280,
"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",
"accumulate": false,
"timeout": "",
"count": "2",
"reduceRight": false,
"reduceExp": "",
"reduceInit": "",
"reduceInitType": "",
"reduceFixup": "",
"x": 790,
"y": 80,
"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": 310,
"y": 280,
"wires": [
[
"38376f96fc57c7b2"
],
[
"d1846549f2f97804"
],
[
"cb05e5ceb2903a29"
]
]
},
{
"id": "e97f75d0134f7456",
"type": "change",
"z": "c303567e3bd31179",
"name": "",
"rules": [
{
"t": "set",
"p": "tableName",
"pt": "global",
"to": "payload",
"tot": "msg"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 200,
"y": 60,
"wires": [
[
"0ba01e84a6c7c311"
]
]
},
{
"id": "cb05e5ceb2903a29",
"type": "function",
"z": "c303567e3bd31179",
"name": "INSERT",
"func": "const data = msg.payload.item\nconst tableName = global.get('tableName')\n\n// Exclude ID keys if present\nconst columns = Object.keys(data).filter(key => key !== 'id');\n\n// Prepare values\nconst values = columns.map(column => {\n const value = data[column];\n if (typeof value === 'string') {\n // remove '\n return `'${value.replace(/'/g, \"''\")}'`;\n }\n return value;\n});\n\n// Construct query\nmsg.topic = `\n INSERT INTO ${tableName} (${columns.join(', ')}) \n VALUES (${values.join(', ')})\n`\n\nreturn msg;",
"outputs": 1,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 480,
"y": 320,
"wires": [
[
"bab0d5516e735f49"
]
]
},
{
"id": "bab0d5516e735f49",
"type": "sqlite",
"z": "c303567e3bd31179",
"mydb": "${sqlite}",
"sqlquery": "msg.topic",
"sql": "",
"name": "",
"x": 670,
"y": 280,
"wires": [
[
"db8543fe2425d54a"
]
]
},
{
"id": "38376f96fc57c7b2",
"type": "function",
"z": "c303567e3bd31179",
"name": "DELETE",
"func": "const tableName = global.get('tableName')\nmsg.topic = `\n DELETE FROM ${tableName} \n WHERE id = ${msg.payload.id}\n`\nreturn msg;",
"outputs": 1,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 480,
"y": 240,
"wires": [
[
"bab0d5516e735f49"
]
]
},
{
"id": "d1846549f2f97804",
"type": "function",
"z": "c303567e3bd31179",
"name": "UPDATE",
"func": "const data = msg.payload.item;\nconst tableName = global.get('tableName');\n\n// Extract the ID and other columns\nconst id = data.id;\nconst columns = Object.keys(data).filter(key => key !== 'id');\n\n// Prepare SET clause\nconst setClause = columns.map(column => {\n const value = data[column];\n if (typeof value === 'string') {\n // Escape single quotes\n return `${column} = '${value.replace(/'/g, \"''\")}'`;\n }\n return `${column} = ${value}`;\n}).join(', ');\n\n// Construct query\nmsg.topic = `\n UPDATE ${tableName} \n SET ${setClause} \n WHERE id = ${id}\n`;\n\nreturn msg;",
"outputs": 1,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 480,
"y": 280,
"wires": [
[
"bab0d5516e735f49"
]
]
},
{
"id": "755df15324a9f017",
"type": "link in",
"z": "c303567e3bd31179",
"name": "link in 1",
"links": [
"db8543fe2425d54a"
],
"x": 285,
"y": 100,
"wires": [
[
"0ba01e84a6c7c311"
]
]
},
{
"id": "db8543fe2425d54a",
"type": "link out",
"z": "c303567e3bd31179",
"name": "link out 1",
"mode": "link",
"links": [
"755df15324a9f017"
],
"x": 805,
"y": 240,
"wires": []
}
]
Have you tried it?
