Insert, update and delete with return for MySQL

[Angelelz]

V0.26 introduced a lot of very nice features!
One very interesting is relational queries and insert, update and delete with return. Is there a plan to add MySQL to the list? Or is there any technical hurdle?

Thanks for such a good tool!

[Angelelz]

This was answered in tweet:
MySQL does not provide returning and you always have to make an extra select and we don't do anything implicitly. That's a little less DX, but in a long run it's a win for everybody

[205g0]

Out of curiosity, why would I want that? I need only the info if it was succesfully inserted, updated or deleted and the inserted id. I get all of that, e.g.:

{
    "fieldCount": 0,
    "affectedRows": 1, 
    "insertId": 66, 
    "serverStatus": 2,
    "warningCount": 1,
    "message": "",
    "protocol41": true,
    "changedRows": 0
}

And I still should have the object I've passed to Drizzle for all the other properties. If one wants to check if all objects were properly saved , a validation lib such as zod should be used before passing the data to Drizzle (hint: zod schemas or shapes can be derived from Drizzle!).

[Angelelz]

This reads better:

function insertUser(userData: InsertUser): SelectUser {
  return database.insert(users).values(...userData);
}

Than this:

async function insertUser(userData: InsertUser): SelectUser {
  const insertedUserData = await database.insert(users).values(...userData);
  return {
    ...userData,
    id: insertedUserData.insertId
  };
}

It's just a convenience thing, as many apis are expected to return the inserted data in the post request.
Also the first one doesn't even need to be an async function. Of course you'd still need to await it.

[205g0]

Yeah, but why does the scope which called insertUser needs ...userData back? It has latter itself since it must have passed it. you could just use that if you still need it.

as many apis are expected to return the inserted data

IDK, e.g. Mongo's insertOne doesn't give back the full object either, https://mongodb.github.io/node-mongodb-native/5.5/interfaces/InsertOneResult.html

[Angelelz]

It's a question about DX. It's the whole reason why we use an ORMs instead of straight up SQL.
It's a feature that exists for Postgres and SQLite but not for MySQL, my question was about that.

[205g0]

It's a question about DX.

Apologies, but I still don't get it. You have full access to that object, where is the DX better when accessing it through the return object than the input object? And, if it's DB-related why do you ask Drizzle anyway?

re sqlite: the returning clause is pretty limited to the point of being useless (check the docs), so it's just Postgres which offers this (and Postgres offers many things which should usually stay on app-level but that's Postgress)

[khuezy]

If you have fields on a table that have default values, you won't have those values in your input object... so it's very handy to have returning() as a convenience. I'm not sure why the mysql driver doesn't have returning (it can make a 2nd query using the insertId interally) - that would make for nicer DX than using you do the 2nd query yourself each time.

[Mohitraj27]

...userData,
    id: insertedUserData.insertId

This doesn't worked for me .. I Tried this it thrown me some error

{
"success": false,
"message": "Converting circular structure to JSON\n --> starting at object with constructor 'MySqlTable'\n | property 'id' -> object with constructor 'MySqlInt'\n --- property 'table' closes the circle",
"stack": "TypeError: Converting circular structure to JSON\n --> starting at object with constructor 'MySqlTable'\n | property 'id' -> object with constructor 'MySqlInt'\n --- property 'table' closes the circle\n at JSON.stringify ()\n
}