Knex does not return insert Id

Question

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.

When 0 id is returned can you check what was actually inserted to DB? does it help if you change the name of the id column to `id`instead of `CELL_ID`? — Mikael Lepistö, Apr 18 '17 at 20:23
No, it does not matter if I change the name of the id column to `id` instead of `CELL_ID` .I guess we get the inserted `id` , only if our `id` is auto-generated(Auto-Increment). — sanjith kumar, Apr 20 '17 at 11:41
`.returning() is not supported by mysql and will not have any effect.` probably because of that — Mark Robson, Jun 20 '19 at 14:10
.returning() is not supported by sqlite3 and will not have any effect. — Gpak, Jul 19 '21 at 08:23

score 17 · Answer 1 · answered Jul 15 '17 at 15:25

17

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) {
        // ...
    })

answered Jul 15 '17 at 15:25

reidar13

203
2
6

does that work with mysql ? for a multi insert. I know that it's not supported in mysql. But in mysql we can use LAST_INSERTED_ID() But it can't give us all the id's. Prematurely we can think that we can deduce the rest of the ids by the last id. But isn't it possible that other rows are inserted too. Are the multi recurrent insertions queued and processed consequently. or They aren't. I think they aren't. And so we can't assume anything. & the only way maybe to get the id's is by re query them and we need to have a way to get them. Or by one insert at a time. & mysql can handle it. – Mohamed Allal Sep 05 '19 at 15:31

score 3 · Answer 2 · answered Aug 30 '17 at 20:27

3

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?

answered Aug 30 '17 at 20:27

Luke Bailey

51
5

`returning` for MySQL returns `id`. (Seems to only be `id` and nothing else). – Dan Oct 29 '18 at 19:13
@Dan I believe it would return `id` even without using `returning`, or at least that's how it works with our MySQL app, and we aren't using `returning`. But I'm also just assuming OP is using mysql based on the `mysql` tag – Luke Bailey Oct 31 '18 at 20:03
For multi updates (i cannot confirm for inserts) knex returns only the last `id` – ix.trc Feb 21 '23 at 08:44

score 2 · Answer 3 · edited Sep 20 '19 at 13:26

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.

score 0 · Answer 4 · answered Aug 11 '20 at 21:24

0

Here is what I used

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

answered Aug 11 '20 at 21:24

Sahil Qureshi

1
2

mixdev · Answer 5 · 2020-09-28T10:02:17.600

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]
});

score 0 · Answer 6 · answered Dec 12 '20 at 06:39

0

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;
});

answered Dec 12 '20 at 06:39

S.Peng

1
1

score 0 · Answer 7 · answered May 18 '23 at 22:28

0

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)

answered May 18 '23 at 22:28

Charlie Schliesser

7,851
4
46
76

Knex does not return insert Id

7 Answers7