How to get last inserted ID of a raw query?

I’m trying to get the last inserted ID of this query:


mysql_sequelize.query(
    'INSERT IGNORE INTO steamer(steamer_id, prov_id, name, avatar) VALUES(UNHEX(REPLACE(UUID(),\'-\',\'\')), \'' + prov_id + '\', \'' + steamer_name + '\', \'' + steamer_avatar + '\')',
).then(function (data) {
    console.log('inserted STEAMER data---> ',data)  // not very helpful info below
});

But I get a pretty useless response back:

inserted STEAMER data--->  [ OkPacket {
    fieldCount: 0,
    affectedRows: 1,
    insertId: 0,
    serverStatus: 2,
    warningCount: 1,
    message: '',
    protocol41: true,
    changedRows: 0 },
  OkPacket {
    fieldCount: 0,
    affectedRows: 1,
    insertId: 0,
    serverStatus: 2,
    warningCount: 1,
    message: '',
    protocol41: true,
    changedRows: 0 } ]

I just want the affected row’s ID (it’s a binary) so I can throw it into a variable and use it for a subsequent query.

At this point, you may be saying “well give it a model” that looks like this maybe:

mysqlSteamer = mysql_sequelize.define('steamer', {
                    'steamer_id': {
                        primaryKey: true,
                        type: Sequelize.BLOB(16)
                    },
                    'prov_id': Sequelize.INTEGER(6),
                    'name': Sequelize.STRING(250),
                    'avatar': Sequelize.STRING
                }, {
                    tableName: 'steamer'
                }),

but I don’t think that’s right because I get this error in response:

TypeError: valueSets.map is not a function
    at Model.bulkBuild (/Users/sadpanda/git/my_repo/node_modules/sequelize/lib/model.js:1792:20)

Ideas?

Issue Analytics

State:
Created 8 years ago
Comments:5 (2 by maintainers)

Top GitHub Comments

2reactions

janmeiercommented, Feb 29, 2016

You can pass { type: sequelize.QueryTypes.INSERT } as the options argument - this will tell sequelize to return the insert it. Or you could use the insertId or the result you get back (in the case of your example it’s probably 0 because the insert is ignored

0reactions

amdpcommented, Apr 6, 2019

router.post(“/link”, (req, res) => { const rowData = { autoincrementId: req.body.id, name: req.body.name } yourModel.create(rowData) .then(row => { sequelize.query(‘SELECT @@IDENTITY’, {type: Sequelize.QueryTypes.SELECT}) .then(id => console.log(id); res.json({id: id[0][‘@@IDENTITY’] }) })); …