Does Drizzle support MySQL's LAST_INSERT_ID function?
I'm wanting to return a newly created record in the db immediately after a POST. MySQL doesn't support
returning so it seems the alternative is a separate request in the same transaction that fetches the new record. It appears that the easiest way to do this is with MySQL's LAST_INSERT_ID function. Does Drizzle support this or does this have to be done with raw SQL?9 Replies
Answered my own question. For anyone else with the same question — the value returned from an insert includes the
insertId. Man I love Drizzle 🫠but if you use uuid as id then it return a number instead of uuid
I saw all you questions in thread, will answer all of them soon
But I will duplicate this one here, so maybe @PapaFinn is interested in it
MySQL supports returning
insertId only for auto_increment fields. Those can be only int/tinyint/bigint/smallint/mediumint. Any other types can't have a returned insertId.
It means, to achieve it you need to store uuid in code and then using it to do a select
Some references:
1. For example Prisma or some other orms are returning whole object after insert into MySQL. That is because those orms are ALWAYS doing second request to database(same as I explained you)
Why we think our approach is better? You have full visibility and flexibility on actions you apply to your database. You can decide either you need to make 1 request to the database or 2(when you want to get inserted data). Other orms just making 2 roundtrips for you always, that may be crucial for some applications performance
2. This is a restriction for MySQL and SQLite, as long as they can't do that themselves. For example PostgreSQL has RETURNING(in drizzle you write .returning()), that natively return whole inserted object without second select statementthank you does mariadb have it? and if yes then how to do it mariadb with drizzle, which Library you recommend
Seems like MaridDB has returning support https://mariadb.com/kb/en/insertreturning/
we don't have a support for MariaDB and all functionality for it. For now Drizzle is PostgreSQL, MySQL, SQLite
but we will have MariaDB support as well
I can't answer on all the alternatives for MariaDB so far, didn't investigate it
ok please just give the example of how we should do in mysql
- insert the row
- get the inserted row data
i could do generate the cuid or uuid in the application layer then insert the data and get the data using that cuid or uuid .
but when we will have support for auto generated default value for primary key using cuid or uuid etc, that time if we follow this approach it won't be possible because uuid will be auto generated.
hope you understand what i am trying to say
note: lets assume 2 db call is fine in this scenario. but i want to use auto generated uuid and get the auto generated id or full row
with autogenerated default approach(after we will add this functionality) I guess we will send you back this generated id from insert call, so you can do second select yourself
thats great. this is what i was looking for thank you