# Dashboard 2 CRUD node

**URL:** https://discourse.nodered.org/t/dashboard-2-crud-node/89081
**Category:** Dashboard
**Tags:** database, dashboard-2
**Created:** [29 June 2024 11:15 UTC](https://discourse.nodered.org/t/dashboard-2-crud-node/89081 "2024-06-29T11:15:45Z")
**Posts on this page:** 20
**Page:** 1

<div class="post-metadata">

### Author: ![Cyprien](https://avatars.discourse-cdn.com/v4/letter/c/a5b964/32.png) [@Cyprien](https://discourse.nodered.org/u/Cyprien)
#### Post date: [29 June 2024 11:15 UTC](https://discourse.nodered.org/t/dashboard-2-crud-node/89081/1 "2024-06-29T11:15:45Z")

</div>

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](https://flows.nodered.org/node/@flowfuse/node-red-dashboard)

- Add an SQLite database ([node-red-node-sqlite](https://flows.nodered.org/node/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](https://vuetifyjs.com/en/components/data-tables/basics/#crud-actions)). Initially, I attempted to do this by creating a new third-party widget ([Third-party Widgets](https://dashboard.flowfuse.com/contributing/widgets/third-party.html)), 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](https://nodered.org/docs/creating-nodes/subflow-modules))
- Manage foreign keys to display multiple tables

Feel free to test it out!

```json
[
    {
        "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": []
    }
]

```

---

<div class="post-metadata">

### Author: ![joepavitt](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/joepavitt/32/59722_2.png) [@joepavitt](https://discourse.nodered.org/u/joepavitt)
#### Post date: [29 June 2024 11:21 UTC](https://discourse.nodered.org/t/dashboard-2-crud-node/89081/2 "2024-06-29T11:21:15Z")

</div>

This is _excellent_, thanks for the contribution. @Steve-Mcl may be able to advise on surfacing the table config better in the subflow

---

<div class="post-metadata">

### Author: ![Cyprien](https://avatars.discourse-cdn.com/v4/letter/c/a5b964/32.png) [@Cyprien](https://discourse.nodered.org/u/Cyprien)
#### Post date: [29 June 2024 11:28 UTC](https://discourse.nodered.org/t/dashboard-2-crud-node/89081/3 "2024-06-29T11:28:50Z")

</div>

Thanks, Joe 🙂 Have you tried it?

For the tableName, I'm not sure it's the best solution to have an Environment Variable or to get the name of the table in the msg.payload. Also, I've set a global variable because the flow variable was not working.

---

<div class="post-metadata">

### Author: ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [29 June 2024 11:34 UTC](https://discourse.nodered.org/t/dashboard-2-crud-node/89081/4 "2024-06-29T11:34:26Z")

</div>

I am looking at the issue with "table name" field.

I suspect there is a bug in Node-RED 4.x - can you confirm 2 things please

1. Are you running Node-RED v4.0.0 or 4.0.1?
2. Do you see an error in the browsers console when you open a CRUD node instance
  1. Also when editing the subflow template and switching to "Preview" mode

---

<div class="post-metadata">

### Author: ![Cyprien](https://avatars.discourse-cdn.com/v4/letter/c/a5b964/32.png) [@Cyprien](https://discourse.nodered.org/u/Cyprien)
#### Post date: [29 June 2024 11:40 UTC](https://discourse.nodered.org/t/dashboard-2-crud-node/89081/5 "2024-06-29T11:40:21Z")

</div>

Thanks Steve !  
I'm running Node-RED 4.0.0, i will update and see if the bug is resolved...  
But, i think it's better get the table name from the msg.payload. As it, you can change the table at running time (With a dropdown for example).

---

<div class="post-metadata">

### Author: ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [29 June 2024 11:41 UTC](https://discourse.nodered.org/t/dashboard-2-crud-node/89081/6 "2024-06-29T11:41:54Z")

</div>

> [@Cyprien](#):
>
> I'm running Node-RED 4.0.0, i will update and see if the bug is resolved...

No, i believe the bug is still present in 4.0.1 - i just need you to confirm what I am seeing.

> [@Steve-Mcl](#):
>
> Do you see an error in the browsers console when you open a CRUD node instance
> 
> 1. Also when editing the subflow template and switching to "Preview" mode

---

<div class="post-metadata">

### Author: ![Cyprien](https://avatars.discourse-cdn.com/v4/letter/c/a5b964/32.png) [@Cyprien](https://discourse.nodered.org/u/Cyprien)
#### Post date: [29 June 2024 13:12 UTC](https://discourse.nodered.org/t/dashboard-2-crud-node/89081/7 "2024-06-29T13:12:10Z")

</div>

No, i don't see any error in my console...

---

<div class="post-metadata">

### Author: ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [29 June 2024 14:25 UTC](https://discourse.nodered.org/t/dashboard-2-crud-node/89081/8 "2024-06-29T14:25:56Z")

</div>

Yeah, no worries, it was a me thing (I did not have sqlite installed)

> [@Cyprien](#):
>
> Thanks, Joe 🙂 Have you tried it?

I have 🙂

Very nice generic CRUD manager for SQLite tables.

I have been through your code and made some adjustements. I will not mention everything but here are some of the things I touched:

1. All of your queries were 100% dynamic and susceptible to SQL Inject hacks. Using the node module [sqlstring](https://www.npmjs.com/package/sqlstring) I sanitize table names & column names and use prepared statement format (e.g. `UPDATE table SET a=?, b=? WHERE c=?`)
2. Your ui-template was using `props` where they should have been simple data values
3. Added a version number and help in the format of the [style guide](https://nodered.org/docs/creating-nodes/help-style-guide)
4. Allow `tableName` to be a simple string instead of only an env var
  1. Also, permit it to be set at runtime via `msg.tableName`

Otherwise - excellent demo of capabilities.  
I strongly recommend you add this to the [flow library](https://flows.nodered.org/) where you can maintain an online copy for everyone to benefit from

> [@Cyprien](#):
>
> For the tableName, I'm not sure it's the best solution to have an Environment Variable or to get the name of the table in the msg.payload

I have took the liberty of making it more flexible. If `msg.tableName` is passed in, it is stored inside the subflows context. That permits a default to be set via the subflow instance OR by sending a message.

Here is a demo using a drop down

![chrome_CmbA5CSohB](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/7/674f3c3222ec71ca0ac4489799c74d0c4ba55543.gif)

Here is what the built in help now looks like

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/4/c433552ce7e706e39f54d2cee25e8efdc744e3a7.png)

  

Below is the updated subflow and my demo flow.  
Feel totally free to discard/use/claim full ownership/whatever 🙂

````auto
[{"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":"1.0.0","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":"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-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 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 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":"8bb63501e512cea0","type":"junction","z":"c303567e3bd31179","x":80,"y":260,"wires":[["a224518bfb230ad0","8864a1534852e8bf"]]},{"id":"db32bad8827c60f7","type":"subflow:c303567e3bd31179","z":"dcc15970006a41ac","name":"","env":[{"name":"group","value":"5f592bffd34021f8","type":"conf-type"},{"name":"sqlite","value":"911a213596ee1bf7","type":"conf-type"},{"key":"DB2_SF_ORDER","value":"1","type":"str"}],"x":690,"y":680,"wires":[[]]},{"id":"0e3b50da602fba72","type":"inject","z":"dcc15970006a41ac","name":"","props":[{"p":"tableName","v":"table1","vt":"str"},{"p":"payload"}],"repeat":"","crontab":"","once":true,"onceDelay":0.1,"topic":"","payload":"table1","payloadType":"str","x":130,"y":680,"wires":[["8edc754ba4e9785d"]]},{"id":"8edc754ba4e9785d","type":"ui-dropdown","z":"dcc15970006a41ac","group":"5f592bffd34021f8","name":"","label":"Select a table:","tooltip":"","order":2,"width":"12","height":"1","passthru":true,"multiple":false,"chips":false,"clearable":false,"options":[{"label":"users","value":"table1","type":"str"},{"label":"roles","value":"table2","type":"str"}],"payload":"","topic":"topic","topicType":"msg","className":"","x":300,"y":680,"wires":[["431683103aa8f002"]]},{"id":"431683103aa8f002","type":"change","z":"dcc15970006a41ac","name":"","rules":[{"t":"set","p":"tableName","pt":"msg","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":510,"y":680,"wires":[["db32bad8827c60f7"]]},{"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":"22ea43815413e748","path":"/page8","icon":"home","layout":"grid","theme":"c2ff5ba1f92a0f0e","order":1,"className":"","visible":"true","disabled":"false"},{"id":"22ea43815413e748","type":"ui-base","name":"base","path":"/dashboard","includeClientData":true,"acceptsClientConfig":["ui-notification","ui-control"],"showPathInSidebar":false,"navigationStyle":"default","titleBarStyle":"default"},{"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"}}]

````

---

<div class="post-metadata">

### Author: ![Cyprien](https://avatars.discourse-cdn.com/v4/letter/c/a5b964/32.png) [@Cyprien](https://discourse.nodered.org/u/Cyprien)
#### Post date: [29 June 2024 15:01 UTC](https://discourse.nodered.org/t/dashboard-2-crud-node/89081/9 "2024-06-29T15:01:30Z")

</div>

Thank you so much for all your work Steve ! I'm not so good with JS / VueJS 🙂  
I will publish it on flow library (I've changed the version to 0.2 as it's not stable yet).  
I've added filtered capabilities, it could be good to make it optional.  
 ![output](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/5/c57d78791544e3c32f97c844843acbce83c8553c.gif)

````auto
[
    {
        "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": "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"
            ]
        ]
    }
]

````

---

<div class="post-metadata">

### Author: ![Cyprien](https://avatars.discourse-cdn.com/v4/letter/c/a5b964/32.png) [@Cyprien](https://discourse.nodered.org/u/Cyprien)
#### Post date: [29 June 2024 15:27 UTC](https://discourse.nodered.org/t/dashboard-2-crud-node/89081/10 "2024-06-29T15:27:00Z")

</div>

I've jus create the flow : [Dasboard-2-sqlite-crud : Create - Update - Delete the records of your Sqlite tables (flow) - Node-RED](https://flows.nodered.org/flow/ca8b64f1465f7246781315fdff6234c4)

---

<div class="post-metadata">

### Author: ![Cyprien](https://avatars.discourse-cdn.com/v4/letter/c/a5b964/32.png) [@Cyprien](https://discourse.nodered.org/u/Cyprien)
#### Post date: [30 June 2024 09:33 UTC](https://discourse.nodered.org/t/dashboard-2-crud-node/89081/11 "2024-06-30T09:33:04Z")

</div>

I would like to add extra info on each column (For example : header, size, color..). For this i have think about many solutions

1. A form like that, but i don't think it is possible with subflow  

2. Add possibility to add msg.configuration json message with column key and configuration

3. Add configuration in a special table of the database

I prefer the second solution, but you, what do you think ?

---

<div class="post-metadata">

### Author: ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [30 June 2024 09:57 UTC](https://discourse.nodered.org/t/dashboard-2-crud-node/89081/12 "2024-06-30T09:57:48Z")

</div>

> [@Cyprien](#):
>
> A form like that, but i don't think it is possible with subflow

No, there are limitations (and for good reason)

> [@Cyprien](#):
>
> Add possibility to add msg.configuration json message with column key and configuration

Possible, not too difficult.

Much like how the `msg.tableName` works. However, as you expand features you may wish to devise a better means of control whereby you can be specific about the purpose of msg.

Example:  
**Select table**

```auto
msg.topic = "table"
msg.payload = "table1"

```

**Apply config**

```auto
msg.topic = "config"
msg.payload = {
  columns: {
     "name": {
         "label": "User Name"
         "width": 200,
         "future-stuff-here": "etc"
     },
     "dob": {
         "label": "Date of Birth"
         "width": 120,
         "type": "datepicker"
     }
}

```

At a future time, you could even enhance this to accept multiple operations via a single message

```auto
msg.payload = {
   "config": {...},
   "table": "table2",
   "class": "finance-table",
}

```

There are a myriad of ways to do this, this is just of the top of my head.

> [@Cyprien](#):
>
> Add configuration in a special table of the database

Also possible (bit more difficult for users)

---

<div class="post-metadata">

### Author: ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)
#### Post date: [30 June 2024 11:21 UTC](https://discourse.nodered.org/t/dashboard-2-crud-node/89081/13 "2024-06-30T11:21:15Z")

</div>

Personally, I would use `msg.columns` rather than `msg.payload.columns`. Reserving `msg.payload` for the actual data payload.

This is on my backlog of work for the `uib-element` node which has a table output that needs the same enhancement.

---

<div class="post-metadata">

### Author: ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [30 June 2024 11:31 UTC](https://discourse.nodered.org/t/dashboard-2-crud-node/89081/14 "2024-06-30T11:31:55Z")

</div>

> [@TotallyInformation](#):
>
> Reserving `msg.payload` for the actual data payload

There is no payload applicable. The subflow reads all data from the sqlite database schema.

The reason I suggested using topic and payload was that it's the most well known and accessible properties of the msg. And since input into this node is irrelevant , using the popular topic and payload makes more sense. The topic forms a "control" method payload contains the associated data.

---

<div class="post-metadata">

### Author: ![Cyprien](https://avatars.discourse-cdn.com/v4/letter/c/a5b964/32.png) [@Cyprien](https://discourse.nodered.org/u/Cyprien)
#### Post date: [30 June 2024 14:23 UTC](https://discourse.nodered.org/t/dashboard-2-crud-node/89081/15 "2024-06-30T14:23:40Z")

</div>

> [@Steve-Mcl](#):
>
> At a future time, you could even enhance this to accept multiple operations via a single message
> 
> ```auto
> msg.payload = {
> "config": {...},
> "table": "table2",
> "class": "finance-table",
> }
> 
> ```

I think that is the best solution...  
Perhaps keep the table name separately to keep things simple to start.

---

<div class="post-metadata">

### Author: ![Cyprien](https://avatars.discourse-cdn.com/v4/letter/c/a5b964/32.png) [@Cyprien](https://discourse.nodered.org/u/Cyprien)
#### Post date: [2 July 2024 15:32 UTC](https://discourse.nodered.org/t/dashboard-2-crud-node/89081/16 "2024-07-02T15:32:41Z")

</div>

I've tried to share my node i've created from my subflow...  
I've created my npm package, but it doesn't appear in the palette. Do you have any ideas ?  
Links  
([dashboard-2-sqlite-crud - npm](https://www.npmjs.com/package/dashboard-2-sqlite-crud))

---

<div class="post-metadata">

### Author: ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [2 July 2024 17:39 UTC](https://discourse.nodered.org/t/dashboard-2-crud-node/89081/17 "2024-07-02T17:39:14Z")

</div>

Hi @Cyprien

Ideally, this would have been published as a scoped package - see **recommended naming convention** in [Introducing the Node Scorecard : Node-RED](https://nodered.org/blog/2022/01/31/introducing-scorecard)

Once done, you need to manually add it to the [flows library](https://flows.nodered.org/) then it appears in the node-red palette manager with the next 30 mins. See **Adding to [flows.nodered.org](http://flows.nodered.org)** here: [Packaging : Node-RED](https://nodered.org/docs/creating-nodes/packaging)

---

<div class="post-metadata">

### Author: ![rafasimple](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rafasimple/32/80215_2.png) [@rafasimple](https://discourse.nodered.org/u/rafasimple)
#### Post date: [2 July 2024 19:04 UTC](https://discourse.nodered.org/t/dashboard-2-crud-node/89081/18 "2024-07-02T19:04:07Z")

</div>

Hi Steve i published the problem in dashboard 2 crudsql

> [@Dasboard-2-sqlserverDatabase-crud](https://discourse.nodered.org/t/dasboard-2-sqlserverdatabase-crud/89188):
>
> Hi Guys im trying to replicate the crud of sqlite [https://flows.nodered.org/flow/ca8b64f1465f7246781315fdff6234c4](https://flows.nodered.org/flow/ca8b64f1465f7246781315fdff6234c4) but with sql server db, this is the payload that i receive in the log, , and this is my flow [flows.json](https://discourse.nodered.org/uploads/short-url/awNMZ8FOX1u7AVBtrHzfwf68YDB.json) (27.2 KB) i have problems, with the template,

---

<div class="post-metadata">

### Author: ![Cyprien](https://avatars.discourse-cdn.com/v4/letter/c/a5b964/32.png) [@Cyprien](https://discourse.nodered.org/u/Cyprien)
#### Post date: [2 July 2024 19:15 UTC](https://discourse.nodered.org/t/dashboard-2-crud-node/89081/19 "2024-07-02T19:15:49Z")

</div>

As i said to you @rafasimple, i think that the problem come from the pragma query that is not existing in sqlserver...

---

<div class="post-metadata">

### Author: ![rafasimple](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rafasimple/32/80215_2.png) [@rafasimple](https://discourse.nodered.org/u/rafasimple)
#### Post date: [2 July 2024 19:27 UTC](https://discourse.nodered.org/t/dashboard-2-crud-node/89081/20 "2024-07-02T19:27:25Z")

</div>

> [@Cyprien](#):
>
> i think that the problem come from the pragma query that is no

can you gave me a screenshot for your payload, after using the join node?

[Next page](https://discourse.nodered.org/t/dashboard-2-crud-node/89081.md?page=2)