help: intsert data and get id

[vatheara]

i try to do this but got syntax error

 const conversation_query = "INSERT INTO Conversations (id,userId, title)  VALUES (:id, :userId, :title); SELECT LAST_INSERT_ID();";
 const conversation_params = {id:'test3', userId:44, title:'test123'};
 const conversationId = await conn.execute(conversation_query, conversation_params)

is there anyway to get the id of the inserted data ?

[iheanyi]

Hey there. you actually have an extra, unnecessary query in there. You can just run INSERT INTO and then on the response, you can use the insertId attribute that's returned on the object for the last inserted ID. Hope this helps!

[iheanyi]

By the way, here's a link to the code in question: https://github.com/planetscale/database-js/blob/main/src/index.ts#L34

If you do const rows = conn.execute(...), you can then do rows.insertId to get the last inserted ID.

[vatheara]

Thanks for reply I also tried to use insertId but it's always return 0 as value which is incorrect because i generate id from uuid()

anyway in my case i'm using this to get the inserted id which is I don't know if it's a good practice or not but it's work for me :

        const id_query = "SELECT id FROM Conversations WHERE userId=:userId  ORDER BY createdAt DESC LIMIT 1";
        const id_params = {userId}
        const conversationId = await conn.transaction(async (conn) => {
          return await conn.execute(conversation_query, conversation_params).then(async() => {
             return (await conn.execute(select_query,select_params)).rows[0];
           })
         })

note: i'm using "@planetscale/database": "^1.10.0", and the id doesn't have default value

[iheanyi]

Hmmm, okay that's interesting. We'll look into insertId not working with UUIDs, do you know if the equivalent works with regular MySQL?

[iheanyi]

Okay, @vatheara , I found the problem. This is a MySQL level constraint. According to MySQL's documentation, LAST_INSERT_ID() is meant for returning the ID for a primary key that is an AUTO_INCREMENT integer. Therefore, it makes sense that it will always be 0 because there is no auto-incrementing primary key column. I hope this helps!

[mattrobenolt]

To add to this, part of the benefits of UUIDs as well in practice is that you can generate the ID client side, which it appears you're doing.

It appears you're doing like:

const params = {id: uuid(), ...}

This uuid() is be generated client side.

So you could either do like,

const userId = uuid();
const params = {id: userId};

Or just grab

const params = {id: uuid()};
...
const userId = params.id;

There's not particularly a reason to fetch this from the server. If the INSERT succeeded, you already have the ID you need.

And a last point, the syntax you're referring to, specifically RETURNING is PostgreSQL syntax, not MySQL.

[vatheara]

Appreciate it thanks you!

[EvanBoyle]

@iheanyi I see that insertId is singular. Is there any way to get all of the id fields for each row in a bulk insert?

[mattrobenolt]

That's how MySQL works, it'll return the last insert id when auto incrementing. But this guarantees they are sequential within a single batch, so if you wrote 5 rows, you know the range is {lastInsertId, lastInsertId-1, lastInsertId-2, lastInsertId-3, lastInsertId-4}