CODEWITHSUNDEEP
mysqlnode.jsknex.js
sanjith kumar
sanjith kumar

Reputation: 355

Knex does not return insert Id

This is my table(CELLID) structure.

+---------+------------+------+-----+---------+-------+
| Field   | Type       | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| CELL_ID | int(11)    | NO   | PRI | NULL    |       |
| STATUS  | tinyint(4) | NO   |     | NULL    |       |
+---------+------------+------+-----+---------+-------+

And this is my code to insert into the table.

knex('CELLID').insert(insertObj)
    .then(function (result) {
      _log.info(reqContainer.uuid, "Successfully Added To CELLID||", result)
      // respond back to request
      _log.info(reqContainer.uuid, "Exiting CELLID_S");
      return resolve(result)  // respond back to request
    })
    .catch(function (err) {

      _log.error(reqContainer.uuid, "Failed Adding To CELLID ||", err)
      _log.error(reqContainer.uuid, "Exiting CELLID_S");
      // respond back to request
      return reject(Error("Failed Adding CELLID"));
    })

After a successful insert, the Id has to be returned.This does not happen in my case. I always get and Id of 0 on an insert.

I had tried by adding an extra column, auto-increment primary key ID(removing CELL_ID as PK).In this case, I get the ID(auto-increment value).

What am I missing here?

Thanks.

Upvotes: 9

Views: 33576

Answers (7)

Charlie Schliesser
Charlie Schliesser

Reputation: 8227

Not really different from other answers here, but to show a slightly simpler and async approach:

const [id] = await db('Foo').insert(data)
const foo = await db('Foo').where({ id })
res.json(foo)

Upvotes: 1

S.Peng
S.Peng

Reputation: 1

this is how i use it，（tips: Be sure to handle insert failures）

return knex(TABLE.CAPTCHA).insert({
    ...captcha,
    expire_time: createExpireTime()
}).then(ids => {
    return ids.length ? ids[0] : false;
});

Upvotes: 0

mixdev
mixdev

Reputation: 2844

You may need to post the insertObj too here because you have managed to create a column STATUS that forbids null AND default null.

To others who are wondering about returning(): The thing is, the particular feature 'returning' is not implemented in MySQL - reason why knex complains if you use returning(). But knex itself can return the ID of the inserted row if you write a callback.

knex.insert({
 name: 'Alice',
})
.into('person')
.then(function (id) {
  console.log(id) // [1001]
});

Upvotes: 0

Sahil Qureshi
Sahil Qureshi

Reputation: 11

Here is what I used

knex(tableName).insert(data).then(row => {return row[0]});

Upvotes: 1

israr
israr

Reputation: 352

To return the id of the record which is just inserted use the following syntax.

addCarMake: function(model, callback) {
    return db.insert(model).into('car_make').returning("make_id").then(function (id) {
        console.log("make_id====" + JSON.stringify(id));
        id2: Number = id;
        // callback(null,id);
        return db('car_make').where('make_id', id[0]).first();
        // return db.raw('select * from car_make where make_id=?',id)
    });
}

But the returning id is in array form , so always use id[0] to further works with it.

Upvotes: 2

Luke Bailey
Luke Bailey

Reputation: 51

MySQL doesn't support returning, so it's not available through Knex.

Here is a list of supported DB's from the Knex docs: http://knexjs.org/#Builder-returning

This is the only other method that I'm aware of to get the last inserted row: How to get the ID of INSERTed row in mysql?

Upvotes: 3

reidar13
reidar13

Reputation: 203

You have to pass second argument to the insert method, which specifies a column to retrieve a value from.

In your case it should look like this:

knex('CELLID').insert(insertObj, 'CELL_ID')
    .then(function (result) {
        // ...
    })

Upvotes: 17

Related Questions