Drizzle Team•11mo ago

Autogenerated default approach

How to return a default generated id like the isssue "Does Drizzle support MySQL's LAST_INSERT_ID function?" of Andrew Sherman: 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

10 Replies

Angelelz•11mo ago

You can inspect the response from your insert statement, I believe the field is called insertId

Kai RevonaOP•11mo ago

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

Angelelz•11mo ago

Mysql doesn't support returning the last inserted ID in any other type of data other than auto_increment cuid or uuid will not work If this is an important feature for you, mysql is probably not the right solution. Otherwise you'll need to run a select statement after the insert

Kai RevonaOP•11mo ago

But how to select the recently insert record

Angelelz•11mo ago

If you generated the id at the app level, you can use it to fetch the row in a select If it's autogenerated, you could use a createdAt column, and get the latest inserted row that way

Kai RevonaOP•11mo ago

How to get that Create the id in the app and add it to id of schema when insert, select by that id, right I don't know about how to use createdAt Please help me wth createdAt logic

Angelelz•11mo ago

You need a column with like this:

createdAt: timestamp('created_at').default(sql`CURRENT_TIMESTAMP`)

createdAt: timestamp('created_at').default(sql`CURRENT_TIMESTAMP`)

Kai RevonaOP•11mo ago

and What is SQL query

Angelelz•11mo ago

createdAt is just one way to do it. You can easily do this too:

const newUser = { name: 'Alice' };
await db.insert(user).values(newUser);
const insertedUser = await db.select(user).where(eq(user.name, 'Alice'))

const newUser = { name: 'Alice' };
await db.insert(user).values(newUser);
const insertedUser = await db.select(user).where(eq(user.name, 'Alice'))

I suggest following tutorials online and in Youtube, there is plenty of content for this type of basic operations.

Kai RevonaOP•11mo ago

Thanks

Gaming

Programming

Autogenerated default approach