bookshelf js save() command not updating rows in postgresql db

Question

JS beginner trying to get a PostgreSQL DB talking to express.js through bookshelf.js.

github: https://github.com/duskyshelf/bookers-academy/blob/master/booker.js

var knex = require('knex')({
  client: 'pg',
  connection: "postgres://localhost/bookers"
});

var bookshelf = require('bookshelf')(knex);

var User = bookshelf.Model.extend({
  tableName: 'users'
});

var bob = new User({id: 2});
bob.save()

bookshelf.js seems unable to add any content to the db.

Current error message is: "Unhandled rejection CustomError: No Rows Updated'

flaviodesousa · Accepted Answer · 2018-12-25T20:41:05.567

44

When you create your model providing your own id, like in

var bob = new User({id: 2});

Bookshelf assumes it is an update operation, not an insertion. It sets the internal isNew attribute to false, and when save() is invoked, instead of INSERT INTO user(id, ...) VALUES (2, ...);, it executes UPDATE user ... WHERE id = 2;.

If there is no user with id = 2 the update will almost silently DO NOTHING.

To force an insert you must change the save() to:

bob.save(null, {method: 'insert'});

Bookshelf save() documentation describes this behavior.

edited Dec 25 '18 at 20:41

answered Jul 16 '15 at 09:08

flaviodesousa

7,255
4
28
34

1

this had me for a bit, i was using `{ insert: true }` – kkemple Jul 27 '15 at 18:19
1

I was providing my own id and it solved it for me as well since it was taking it as an update instead of an insert. Thanks! – John Nov 05 '20 at 15:35

score 0 · Answer 2 · answered Jul 14 '15 at 03:06

Did you create a User table using knex? One potential problem I can think of is that you do not have any logic that actually creates the table for your Postgres DB. Here is some sample code that will create a table in your database if it doesn't yet exist.

bookshelf.knex.schema.hasTable('User').then(function(exists) {
  if(!exists) {
    bookshelf.knex.schema.createTable('User'), function(user) {
      user.increments('id').primary();
      user.timestamps();
    }).then(function(table){
      console.log('Created Table:', table);
    });
  }
});

I tried your code, but I get the same error. I had a simpler version of that code originally, and I've also tried manually creating the table, all of which return the same error message. Could there be any errors in my PG setup? I kept everything default for simplicity. — David Upsdale, Jul 14 '15 at 08:37

score -1 · Answer 3 · answered Oct 31 '16 at 10:49

-1

new User({id: 2}).
 save().
 then((model) => {
  res.json({ success: true });
});

answered Oct 31 '16 at 10:49

n00b

59
3

Overwriting the `id` attribute (assuming it's a primary key) will cause Bookshelf to attempt an update instead of an insert. This will throw an error if there's no existing record with that `id`. – Lee Benson Nov 11 '16 at 20:36
sorry, I thought he was just trying to save data. I just tried to demonstrate to save data not updating. thanks for pointing it out otherwise it can misguide others. – n00b Nov 12 '16 at 05:04

score -5 · Answer 4 · answered Jul 16 '15 at 15:22

-5

No no! new User returns a PROMISE! You can't use it like that. Try

new User().save()

answered Jul 16 '15 at 15:22

uglycode

3,022
6
29
55

Not true. `new User` only instantiates the model in memory. Calling `save` *does* return a Promise. The OP's issue was specifying a primary key, which instructs Bookshelf to perform an update instead of an insert. – Lee Benson Nov 11 '16 at 20:34

bookshelf js save() command not updating rows in postgresql db

4 Answers4