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
PapaFinn
PapaFinn14mo ago
Answered my own question. For anyone else with the same question — the value returned from an insert includes the insertId. Man I love Drizzle 🫠
Md Jahidul Islam milon
but if you use uuid as id then it return a number instead of uuid
Andrii Sherman
Andrii Sherman13mo ago
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 statement
Md Jahidul Islam milon
thank you does mariadb have it? and if yes then how to do it mariadb with drizzle, which Library you recommend
Andrii Sherman
Andrii Sherman13mo ago
Seems like MaridDB has returning support https://mariadb.com/kb/en/insertreturning/
MariaDB KnowledgeBase
INSERT...RETURNING
Returns a resultset of the inserted rows.
Andrii Sherman
Andrii Sherman13mo ago
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
Md Jahidul Islam milon
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
Andrii Sherman
Andrii Sherman13mo ago
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
Md Jahidul Islam milon
thats great. this is what i was looking for thank you