Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is there a way to access `last_inserted_id` in TypeORM?

Specifically, is there way to access the last_inserted_id in a TypeORM transaction? i.e.:

 try {
        // transaction
        await getManager().transaction(async (trManager): Promise<any> => {

            const company = new Company();
            const savedCompany = await trManager.save(company);
            const companyId = savedCompany.lastInsertedId;

            // ..............more saves..............//

            // await trManager.save(otherEntityUsingCompanyId);

        });
    } catch (err) {
        console.error("err: ", err);         
    }

I've looked through the docs thoroughly (admittedly, perhaps not thoroughly enough if i've missed something) and haven't seen anything. The closest documentation I've found that looks similar is:

const userId = manager.getId(user); // userId === 1

This seems like a common enough use case that I'm assuming I missed something, which is why I've hesitated to file an issue. Any help would be appreciated. Thanks!

like image 247
drlff Avatar asked Nov 21 '17 19:11

drlff


People also ask

Does typeorm modify the passed object after inserting into DB?

A) Appears that TypeORM will modify the passed object after inserting into DB. Additionally it will add/fill all properties that was populated by default column values. B) Extended answer of Willow-Yang.

Is there a way to get the last ID of a database?

Rather than use With blocks you can also create object references and use those, and as long as you use the same database reference, @@Identity will return the last ID. Note that CurrentDb returns a different dao.database reference every time it is called.

How to get the correct user ID after a database update?

If you use "SELECT @@Identity" on the connection or database object against which you ran your update, then you are guaranteed to get the correct ID. Consider this DAO code . .


2 Answers

NOTE: Please note that I have not used TypeORM since roughly the time of original answer, so there may be better ways to do this now.

Figured it out. Use returning method...

const inserts = await getConnection()
            .createQueryBuilder()
            .insert()
            .into(Company)
            .values([
                { Name: "abcdef", Address: "456 denny lane" }, 
                { Name: "ghijkil", Address: "899 ihop lane" }
            ])
            .returning("INSERTED.*")
            .printSql()
            .execute();

// `inserts` holds array of inserted objects
like image 159
drlff Avatar answered Dec 20 '22 09:12

drlff


OUTPUT or RETURNING clause only supported by Microsoft SQL Server or PostgreSQL databases.

For MySql, you can get the last_insert_id from the result. it looks like the following.

InsertResult {
  identifiers: [ { id: 1 } ],
  generatedMaps:
   [ { id: 1,
       creationTime: 2019-09-03T10:09:03.000Z,
       lastUpdate: 2019-09-03T10:09:03.000Z } ],
  raw:
   OkPacket {
     fieldCount: 0,
     affectedRows: 1,
     insertId: 1,
     serverStatus: 2,
     warningCount: 0,
     message: '',
     protocol41: true,
     changedRows: 0 } }
like image 44
Willow Yang Avatar answered Dec 20 '22 11:12

Willow Yang