Drizzle ORM does not offer support for the Insert Returning feature

Question

Drizzle ORM does not offer support for the Insert Returning feature

I am facing a query while utilizing Drizzle ORM with MySQL.

At present, Drizzle ORM lacks an insert returning function for MySQL. Feel free to refer to this link for more information.

My platform registers users into the system and generates JWT tokens upon signup. As the JWT payload requires the user's id, it is crucial to retrieve the id of the recently added user.

In this scenario, how can I obtain the auto-incremented integer id for the newly inserted record?

mysql typescript sql-insert drizzle

Answer 1

Answer №1

Drizzle ORM lacks support for the returning function with MySQL, but it offers a workaround by utilizing the insertId property to obtain the auto-incremented ID.

For example:

const newUser = await db.insert(user).values({ name: "Jorge"})


const newWallet = await db.insert(wallet).values({ userId: newUser.insertId})

Answer 2

Drizzle ORM lacks support for the returning function with MySQL, but it offers a workaround by utilizing the insertId property to obtain the auto-incremented ID.

For example:

const newUser = await db.insert(user).values({ name: "Jorge"})


const newWallet = await db.insert(wallet).values({ userId: newUser.insertId})

Answer 3

Answer №2

Based on information found in the Drizzle Documentation

When using the insert method with Drizzle, you can retrieve inserted rows by calling the returning() function.

// Example of retrieving all inserted rows
await db.insert(users).values({ name: "Dan" }).returning();

// Limiting return to specific data
await db.insert(users).values({ name: "Partial Dan" }).returning({ insertedId: users.id });

To access a single value from the results, you could do the following:

const newUser = await db.insert(users).values({ name: "Partial Dan" }).returning({ insertedId: users.id });

return newUser[0];

NOTE: This feature is compatible with PostgreSQL and SQLite but NOT with MySQL. Apologies for any confusion caused.

Answer 4

Based on information found in the Drizzle Documentation

When using the insert method with Drizzle, you can retrieve inserted rows by calling the returning() function.

// Example of retrieving all inserted rows
await db.insert(users).values({ name: "Dan" }).returning();

// Limiting return to specific data
await db.insert(users).values({ name: "Partial Dan" }).returning({ insertedId: users.id });

To access a single value from the results, you could do the following:

const newUser = await db.insert(users).values({ name: "Partial Dan" }).returning({ insertedId: users.id });

return newUser[0];

NOTE: This feature is compatible with PostgreSQL and SQLite but NOT with MySQL. Apologies for any confusion caused.

Answer 5

Answer №3

To effectively handle the ResultSetHeader, it is recommended to parse it as follows:

await this.db.insert(users).values({"login": "xxxx"})

This operation will yield the insertId:

[
  ResultSetHeader {
    fieldCount: 0,
    affectedRows: 1,
    insertId: 33,
    info: '',
    serverStatus: 2,
    warningStatus: 0,
    changedRows: 0
  },
  undefined
]

Note: Starting from version 0.32.0, you have the option to utilize the $returningId() method for automatically receiving individual inserted IDs:

const result = await db.insert(users).values([{ name: 'John' }, { name: 'John1' }]).$returningId(); 
//    ^? { id: number }[]

Answer 6

To effectively handle the ResultSetHeader, it is recommended to parse it as follows:

await this.db.insert(users).values({"login": "xxxx"})

This operation will yield the insertId:

[
  ResultSetHeader {
    fieldCount: 0,
    affectedRows: 1,
    insertId: 33,
    info: '',
    serverStatus: 2,
    warningStatus: 0,
    changedRows: 0
  },
  undefined
]

Note: Starting from version 0.32.0, you have the option to utilize the $returningId() method for automatically receiving individual inserted IDs:

const result = await db.insert(users).values([{ name: 'John' }, { name: 'John1' }]).$returningId(); 
//    ^? { id: number }[]

Answer 7

Answer №4

When the Drizzle ORM lacks a built-in insert returning function for MySQL, there are alternative methods to obtain the auto-incremented ID of a newly added user. Here are a few common solutions:

Utilize the LAST_INSERT_ID() function: After inserting the new user record into the database, you can use the LAST_INSERT_ID() function in MySQL to retrieve the auto-incremented ID of the last inserted record within the current session.

For example:

// Assuming you have a MySQL database connection or query execution method named "executeQuery" const result = await executeQuery(

INSERT INTO users (username, email, password)  VALUES ('new_user', '<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="bfd1dac8e0caccdacdffdac7ded2cfd3da91dcd0d2">[email protected]</a>', 'hashed_password'); SELECT LAST_INSERT_ID() as id;

);

const newUserId = result[0][0].id; // Utilize the "newUserId" in your JWT payload or wherever needed.

Answer 8

When the Drizzle ORM lacks a built-in insert returning function for MySQL, there are alternative methods to obtain the auto-incremented ID of a newly added user. Here are a few common solutions:

Utilize the LAST_INSERT_ID() function: After inserting the new user record into the database, you can use the LAST_INSERT_ID() function in MySQL to retrieve the auto-incremented ID of the last inserted record within the current session.

For example:

// Assuming you have a MySQL database connection or query execution method named "executeQuery" const result = await executeQuery(

INSERT INTO users (username, email, password)  VALUES ('new_user', '<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="bfd1dac8e0caccdacdffdac7ded2cfd3da91dcd0d2">[email protected]</a>', 'hashed_password'); SELECT LAST_INSERT_ID() as id;

);

const newUserId = result[0][0].id; // Utilize the "newUserId" in your JWT payload or wherever needed.

Answer 9

Answer №5

It appears that the provided solution may not be accurate; here is an alternative way to retrieve the inserted ID in Drizzle version 0.30.7.

const updatedUser = await db.insert(schema.users).values({ email: email, username: username, password: password })
return updatedUser[0].insertId;

Answer 10

It appears that the provided solution may not be accurate; here is an alternative way to retrieve the inserted ID in Drizzle version 0.30.7.

const updatedUser = await db.insert(schema.users).values({ email: email, username: username, password: password })
return updatedUser[0].insertId;

Answer 11

Answer №6

Currently utilizing drizzle version 0.30.10 in conjunction with MySql, which includes functionality for retrieving the insertId from the returned object.

const newUser = await db.insert(schema.users).values({ email: email, username: username, password: password })
return newUser[0].insertId;

Answer 12

Currently utilizing drizzle version 0.30.10 in conjunction with MySql, which includes functionality for retrieving the insertId from the returned object.

const newUser = await db.insert(schema.users).values({ email: email, username: username, password: password })
return newUser[0].insertId;

Drizzle ORM does not offer support for the Insert Returning feature

Answer №1

Answer №2

Answer №3

Answer №4

Answer №5

Answer №6

Similar questions

What could be causing the Angular/TypeScript model that I imported to fail to properly map to my service within my Angular application?

Angular2 app fails to update after emitting an event

Unable to launch MySql service due to issues with ibdata1 file recovery

Encountering an error when trying to add a new row due to a trigger in

Unlocking the Secrets of Printing MySQL Data

Iterate through every entry in a table and conduct calculations involving every other entry

Sequelize: There is no association between Model and Model2

What is the method for utilizing SHOW CREATE TABLE within a subquery?

Example of Using Bluebird in a Chain of Catch and Then Functions

In what situations might a finally block fail to execute?

How can one access DOM elements (getting and setting values) that are nested within an *ngFor loop?

The call in TypeScript React does not match any overload

Organize results from MySql using php

Is there a workaround for the React useContext issue in Typescript aside from using <Partial>?

What is the best approach to accessing and reading a JSON file within an Angular 12 application?

The Cloudflare KV namespace remains unbound

Using a conditional statement to generate objects within a TypeScript loop

Adding URL path in Angular 7's .ts file

Choose to either delete the record if it exists or keep it by performing just one

Is it possible to execute a script from a different directory?