Database Insert/Update/Delete from JSON

[Apple]

Please consider a database query “users”:

[
    {
        "id": 1,
        "name": "George",
        "country": "NL"
    },
    {
        "id": 2,
        "name": "Teodor",
        "country": "BG"
    }
]

That’s a JSON representation of our rows. Through a series of steps, I modify the names:

[
    {
        "id": 1,
        "name": "George P.",
        "country": "NL"
    },
    {
        "id": 2,
        "name": "Teodor T.",
        "country": "BG"
    }
]

Now, I want to UPDATE the database with the new data.

Current solution involves:

Repeat users:
    Database update

This means, two database queries will be executed. Imagine we have 1000 rows, 1000 database queries would be executed.

But just a couple of queries could be enough to update this data, following my previous research of doing an INSERT/UPDATE with multiple values/rows in it:

Smarter Database Multi Insert

Continuing the discussion from Slow insert records into the database: Actually, I’ve just realized how slow is the Database Multi Insert in Wappler, even with transactions as mentioned in my previous post. I did the same test using a third-party SQL viewer, with the import feature, and it basically transforms the CSV into a bulk INSERT statement: INSERT (...) VALUES (...), (...), (...), (...), (... about 300) INSERT (...) VALUES (...), (...), (...), (...), (... about 300) // instead of Wapple…

This feature request is for the addition of new alternatives to Database Insert, Update and Delete steps that take as input the table name and the JSON representation of rows (as exampled above). This would be an alternative to the traditional database query dialog, and a faster one (in computer speed) due to the ability to automatically “compress” inserts/updates/deletes into a single (or comparably small) query (something similar to a database transaction)

Database insert/update/delete from JSON representation is already present in software like Directus and NocoDB, bring it to Wappler!

Edit: I remembered about the potential security risks - this should not be used when accepting the entire JSON as user input (e.g.: if the user changes the id in the JSON, then s(he) could potentially be changing another user’s record. To prevent these, check how other frameworks are implementing these checks/guards, such as Laravel:

Laravel - The PHP Framework For Web Artisans

Wappler could equally accept a “fillable” or “guarded” options to protect against mass-assignment misuse

[nickneustroev]

That would be amazing!