bookshelf/bookshelf

A simple Node.js ORM for PostgreSQL, MySQL and SQLite3 built on top of Knex.js

bookshelf's Issues

`attach` with additional attributes

When I call attach I generally want to be able to set other attributes on the M2M relation.

A common example might be a timestamp.

Something like collection.belongsToMany(db.Models.Link).attach(link, {archived: false})

I'd also like to be able to set defaults and methods for the M2M relation.

class CollectionsLinks extends Bookshelf.Model
  defaults:
    archived: false

I found the following which seems to indicate you can't pass in a model and additional params - I'd have to create a hash myself.

      // If the item is an object, it's either a model
      // that we're looking to attach to this model, or
      // a hash of attributes to set in the relation.
      if (_.isObject(item)) {
        if (item instanceof Model) {
          data[relatedData.foreignKey] = item.id;
        } else {
          _.extend(data, item);
        }
      } else if (item) {
        data[relatedData.foreignKey] = item;
      }

Also the comments for belongsToMany say:

The joinTableName may be replaced with another object, will serve as the joining model.

I expected to be able to pass a Bookshelf.Model, whose defaults would be set on attach, as well as timestamps, etc.

Examples of using many to many relations with additional attrs on the join table would be helpful.

Option to require response on fetch

Shortcut for making an error condition (failed promise) when an empty response is returned by the fetch call...

model.fetch({require:true});
collection.fetch({require:true});

Possibly applies to eager loaded relations:

model.fetch({
   withRelated: ['recommendations.rec_rows', 'recommendations.caption_rows'],
   requiring: ['recommendations.rec_rows']
})

Meaning that if the model, recommendations or "rec_rows" is blank, the promise won't resolve... Any cases where this would be necessary?

Determining handling for empty response relations

Should these be loaded on the relations hash as empty Model/Collection instances?

findOrCreate

Would be good to have a findOrCreate method.

DB server disconnection is not handled

setInterval(function(){
  new user().fetch().then(function(rows){
    console.log(rows);
  }, function(e){
    console.log(e);
  })
}, 2000);

If I run the above code and then kill MySQL after few seconds then the process exits with Error: Connection lost: The server closed the connection. instead of handling in the promise. If I run the code after killing MySQL server then promise is resolved with error and does not kill the process.

Flag for bypassing timestamps

Mentioned in #53, there may be cases where you'd want to save the model without affecting the updated_at timestamp, perhaps a {timestamp: false} flag in the save options?

if (this.hasTimestamps && options.timestamp !== false) {
  _.extend(attrs, this.timestamp(options));
}

updates using UNIQUE (but not PRIMARY) key

Hi all,

We currently have a MySQL table whose rows include, e.g.:

• id
• uuid
• name

The uuid is a UUIDv4 that exists so that we have a unique, essentially unguessable identifier for an entity that can be used in URLs, without exposing the underlying primary key (id). We haven't been sure, though, of the best way to fetch and update models using that unique uuid, though. Altering idAttribute hasn't felt ideal, because then uuid becomes our primary key, which isn't what we want for joins. We're currently using

models.Entity
.forge()
.query()
.where({uuid: req.params.uuid})
.update(attributes)
.then(function() {
    // success
}, function(err) {
    // error
});

which works. But we ran into a side effect whereby, because we're dropping down to the query layer with the above, a hook we've defined in our model's initialize method isn't invoked:

this.on('updating', function(model, attributes, options) {
    model.set('updated_at', dateFormat(new Date(), 'yyyy-mm-dd HH:MM:ss'));
});

Are we better off implementing one or both of these in some other way perhaps? We did notice that the documentation for Bookshelf.Sync.update alludes to "a custom query", but we weren't sure if that might help us with both goals?

Many thanks!

Convert datetime to UTC while saving

Can we have this feature please? It's pretty common to store date and time in UTC.
Another request to convert BOOLEAN type to true, false in model get

Update if exists, insert if it doesn't

A feature I've really been missing is access to the INSERT ... ON DUPLICATE KEY UPDATE syntax. At the moment, there are 2 queries per save() but in some instances 1 query could replace the SELECT & UPDATE/INSERT operations.

I realise this means the object would not be populated without a further SELECT query, so this would exist more as a utility function. Possibly in Knex?

Defining data types, separate model files and sync with DB

1. How do I define data type and other properties in model? Coming from Sequelize:

   title: { type: Sequelize.STRING, allowNull: false},
   someUnique: {type: Sequelize.STRING, unique: true},
   identifier: { type: Sequelize.STRING, primaryKey: true}

2. How do I separate each models in separate files? I was looking in the tests, all the models and collections, relations are defined in one file. For a large db, it's easier to manage if models are in separate files.

3. How do I database sync (like in sequelize) from my model?

implementing deleted_at

Hi Tim, all,

We're aspiring to implement soft deletions, whereby we set a field in our (MySQL) table (whose default value is otherwise NULL) to a DATETIME when it's "deleted." At the moment, we're doing so with

models.Entity
.forge()
.query({where: ['uuid', '=', req.params.uuid]})
.save({deleted_at: new Date()})
.then(function(model) {
    // success
}, function(err) {
    // failure
});

which results in SQL of:

{ command: 3,
  sql: 'update `submissions` set `deleted_at` = \'2013-08-23 01:57:11\', `updated_at` = \'2013-08-23 01:57:11\' where `uuid` = \'110ec58a-a0f2-4ac4-8393-c866d813b8d1\'' }

Is that likely the cleanest approach, though? Or might we be better off overriding destroy for all entities? Separately, it looks like, per Bookshelf.Model's default implementation of timestamp, there's no way to prevent updated_at from being updated in this scenario (which is a minor goal so that the final value of updated_at reflects the last time its actual data was modified, as opposed to its metadata)?

On the flip side, though, it was less clear to us how to go about tweaking fetch so that SELECTs include a WHERE deleted_at IS NULL. For instance, adding

this.on('fetching', function(model, columns, options) {
    model.query({where: ['deleted_at', null]});
});

got us most of the way there but actually yielded a query with WHERE deleted_at = NULL as opposed to WHERE deleted_at IS NULL. Is there perhaps some other way to achieve the latter, perhaps by dropping down into Knex?

Many thanks!

`pivot` documentation typo

By default, the pivot attributes are defined with the pivot_ prefix.

They are actually prefixed with _pivot_ with a leading underscore.

Model.toJSON with empty EagerRelation is ignored

var Blog = Bookshelf.Model.extend({
  tableName: 'blog'

  posts: function() {
    return this.belongsToMany(Post, 'blog_post');
});

var Post = Bookshelf.Model.extend({
  tableName: 'post'
});

If the blog has posts, you'll get back:

{
  id: 1,
  name: 'My blog',
  posts: [
    { ... },
    { ... }
  ]
}

If the blog does not have posts, you'll get back:

{
  id: 1,
  name: 'My blog'
}

I expect to at least see publishers: [] in the JSON.

Support Node callbacks for fetch, load

It appears calling fetch().exec is not possible like it is in Knex.

Can we have this option?

Errors when tableName isn't defined on model or collection

Document this.relatedData

Bookshelf.Initialize hangs on MySQL 5.6.13

Hi,
I'm not completely sure this belongs here, maybe it should be posted at the mysql hub, but here goes anyway.
The following code causes a hang in my node.js app.
I do see two connections appearing on my MySQL instance.

Bookshelf.Initialize({
client : 'mysql',
connection : {
host : 'localhost',
port : '3306',
user : 'root',
password : 'root',
database : 'usermanagement',
charset : 'utf8'
}
});

`attach` documentation incorrect

Attaches one or more ids from a foreign table to the current table, in a belongsToMany relationship. Creates & saves a new model and attaches the model with the related model.

From relations.js:

it('provides "attach" for creating or attaching records', function(ok) {

        var admin1 = new Admin({username: 'syncable', password: 'test'});
        var admin2 = new Admin({username: 'syncable', password: 'test'});

        When.all([admin1.save(), admin2.save()])
          .then(function() {
            return When.all([
              new Site({id: 1}).admins().attach([admin1, admin2]),
              new Site({id: 2}).admins().attach(admin2)
            ]);
          })

Can take model objects too.

Helper to get single result from a M2M relation

A common query would be to get a single result from a M2M.

It becomes something like:

@belongsToMany(db.Models.Collection).query().where(id: id).limit(1).select().then (models) ->
  models[0] if models.length

In Sequelize I could write `User.getCollection(where: {id: id}).done ...) which would return a single result.

Having a single method which returns on object or null rather than an array. Maybe more for the Knex lib?

@belongsToMany(db.Models.Collection).query().where(id: id).single()

You could go even shorter too.

Also, a get raw json as the result when what I would like is a Collection of Models.

On a side note, I'm finding a lot of common queries are quite verbose in Bookshelf. I think finder method would help with this.

At present I've got a BaseModel class with lots of finder methods. I love the extensibility and clean Backbone-style codebase + documentation compared with Sequelize, but simple common operations are still a little verbose. I'm very conscious though of the desire to keep the lib quite small without the kitchen-sink, like Backbone.

Do i have to have multiple connections to the database when using transactions?

To use transactions, do i have to connect to db (init orm, load models, etc) in each request, or it is enough to do one connection at application start?

When app is serving many requests using one mysql connection, can transactions cross due to node async nature?

Passing data to view...

I'm trying to pass a collection to a view, here's my code:

var Book = Bookshelf.Model.extend({
    tableName: 'books'
});

var Books = Bookshelf.Model.extend({
    model: Book
});

app.get('/books', function(req, res){
    new Books().fetch().then(function(collection) {
        res.render('books', { books: collection });
    });
});

I learned node using a different orm and they had an example, but the same method didn't work here!

Question regarding loading relations and performance

Each related models (withRelated) are loaded using select statement instead of join. Isn't it cost performance? Something like

SELECT * FROM sitesmeta
left join site on 
site.id = sitesmeta.id
and site.id =1

Another question, if I want to build complex query (apart from simple collection fetch()), is query builder (using Knex) the preferred interface to use?

Clarify parse, toJSON, (format, translate, etc.)

Figure out the semantics of type conversion, formatting, and default value assignment for values when they are coming from and going to the client and coming from/going into the database.

Provide option to specify whether timestamps are underscored or camelCased

this.timestamp hardcodes underscores. When overriding parse and format with camelize and underscorize as suggested in the docs, they remain as underscores.

There should be an option whether to use camel or underscore.

    // Sets the timestamps before saving the model.
    timestamp: function(options) {
      var d = new Date();
      var vals = {};
      vals.updated_at = d;
      if (this.isNew(options)) vals.created_at = d;
      return vals;
    },

Workaround:

Monkey-patch timestamp.

Bookshelf.Model::timestamp = (options) ->
  d = new Date()
  vals = {}
  vals.updatedAt = d
  vals.createdAt = d  if @isNew(options)
  vals

detach() with no parameter doesn't work

Hi,

In the doc of detach() function :
If no parameters are specified, we assume we will detach all related associations.
This behaviour doesn't work at all, no query executed...

Here is a little patch to correct this, on line 918 of bookshelf.js add these lines :

      // If no paramater, delete all
      if(_.isEmpty(ids) && method == 'delete'){
        pending.push(this._processPivot(method, null, options));
      }

`http://bookshelfjs.org/#Collection-parse` menu item doesn't exist in docs

Relations with foreign key = 0

I was trying to figure out why my belongsTo relationships kept erroring out. Turns out, JS's type coercion has bitten me on the ass yet again:

https://github.com/tgriesser/bookshelf/blob/5320db5fed30f069cd2d9c110c2ab636e6e4745b/bookshelf.js#L930

When fkValue is 0, as was the case with one of my table rows, the expression !relatedData.fkValue is true and causes an error to be thrown. I'd submit a patch but I'm not a 100% sure if fkValue is ever null, or only undefined.

Sync, Model methods differences?

Model has query, save, fetch, destroy methods and Sync has similar methods (first, select, insert, update, del) as well. What's the difference between two or they can be used interchangeably?

To query I could use Model.query().where() or I can use sync.select([options])

findAndCountAll and Incrementing certain values of an instance

(from Sequelize) Really need findAndCountAll for paging. Get total records count with given query and get result with limit and offset. Any way to do it right now?

new Project.findAndCountAll({where: ["title LIKE 'foo%'"], offset: 10, limit: 
  2}).then(function(result) {
  console.log(result.count); //total records with given query
  console.log(result.rows); //result with offset and limit
});

Incrementing a column value without running into concurrency issues. Like:

new User.increment('versionNumber', 1).then(/* ... */) //Increment existing value by 1

Support client-side id generation

I am using client-side generated uuids.

Currently isNew checks whether id exists. This prevents an insert being sent if you pass in a generated uuid.

I think Spine supports this, might be interesting to see how they do it.

Creating new instances without "new" keyword

To provide more chaining flexibility:

require('../item').Item({
  // options
}).fetch().then(function () {
  // handler
})

Roadmap for 0.3.0

Definite:

• Update to Knex 0.2.0 syntax
• Fix for #35, create from collection not maintaining constraints
• Fix for #40, re-enabling {patch: true} on save.
• Document this.relatedData
• collection.fetchOne for fetching an individual model on a collection

Stretch / Features for 0.3.x:

• hasOne / hasMany through (#29)
• Bare-bones validation support with checkit.js (#39)
• Typecasting with castit.js

Use proper database credentials in example

The example shows using "root" and empty password as connection settings. This seems to be giving people the wrong idea. Could this maybe be changed to "your_db_user" and a random string (best case: linked to an online password generator) as the password?

Destroying instances and related models?

Should eager destruction mirroring eager loading be possible?

Support standard ORM finders

I've found it a little verbose doing simple queries.

Having common finder class methods is quite common in ORMs.

E.g. In Sequelize:

User.find(active: true).done (e, r) ->

Also common in ActiveRecord, Mongoose, etc.

The same in Bookshelf is:

coll = new UserCollection
coll.query().where(active: true).select().exec (e, r) ->

What do you think about finder class methods on the Model and Collection classes?

withRelated doesn't work ?

Hi !

I don't know if it's a bug or if I do wrong, but I don't make my code work with withRelated option on fetch() function.

Here is my MySQL database :

CREATE TABLE IF NOT EXISTS `users` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(64) NOT NULL,
  `password` varchar(64) NOT NULL,
  `email` varchar(255) NOT NULL,
  `created` int(11) NOT NULL,
  `updated` int(11) NOT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `roles` (
  `rid` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `access` longtext NOT NULL,
  PRIMARY KEY (`rid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `users_roles` (
  `uid` int(11) NOT NULL,
  `rid` int(11) NOT NULL,
  PRIMARY KEY (`uid`,`rid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I have one row in 'users' : {uid: 1, username: 'root', ...}
One row in 'roles' : {rid: 1, name: 'admin', ...}
And one row in users_roles : {uid: 1, rid: 1}

Here is my models definitions :

var User = Bookshelf.Model.extend({
    tableName: 'users',
    idAttribute: 'uid',
    roles: function() {
        return this.belongsToMany(Role, 'users_roles', 'rid', 'uid');
    },
});

var Role = Bookshelf.Model.extend({
    tableName: 'roles',
    idAttribute: 'rid',
    users: function(){
        return this.belongsToMany(User, 'users_roles', 'uid', 'rid');
    }
});

If i do that, it works :

new User({uid: 1})
    .roles()
    .fetch()
    .then(function(result){
        console.log(result.models[0].attributes);
    });

But the code below dont load my roles on my user :

new User({uid: 1})
    .fetch({withRelated: ['roles']})
    .then(function(result){
        console.log(result.related('roles').toJSON());
    });

If I watch the debug log of Bookshelf, 'bindings' var is undefined :

{ sql: 'select `roles`.*, `users_roles`.`uid` as `_pivot_uid`, `users_roles`.`rid` as `_pivot_rid` from `roles` inner join `users_roles` on `roles`.`rid` = `users_roles`.`rid` where `users_roles`.`uid` in (?)',
  bindings: [ undefined ],
  __cid: '__cid3' }

Where is my mistake ?

Thanks a lot !

Cannot get transaction working

I'm trying to get the Transaction working following the example from http://bookshelfjs.org/#Bookshelf-Transaction

However, it is not working. When I do

  new Library({name: 'Old Books'})
    .save({transacting: t})

The model gets a transacting property with t value and query generated has a column transacting there by failing the query. Either I'm doing something wrong or the example is not correct. I've tried to look for a test case but it is not there. Can you please add a test case? Your test cases have been very helpful in learning.

Also, many methods take additional [options], what are these possible options?

Thanks!

Seem to be having an issue with belongsTo when fetching related from a collection.

CREATE TABLE foo(id AS INT);
CREATE TABLE bar(id AS INT, foo_id AS INT);

var Foo = Bookshelf.Model.extend();
var Bar = Bookshelf.Model.extend({
    foo: function() {
        this.belongsTo(Foo);
    }});

(new Bar()).fetch({withRelated:['foo']})

results in:

SELECT "foo".* FROM "foo" WHERE "id" in ($1)

but the id being passed is bar's id, not foo's id. If I change bookshelf:572

this.query('whereIn', relation.foreignKey, _.pluck(resp, relation.parentIdAttr));

to:

this.query('whereIn', relation.foreignKey, _.pluck(resp, relation.otherKey));

then I get foo's id, which is what I would expect.

Renaming parse to not conflict with client models

Possibly rename to format?

Create from related collection doesn't maintain model constraints

Need to do something along these lines (with tests):

var data = {}
if (this.relatedData) {
  data[this.relatedData.foreginKey] = this.relatedData.fkValue;
}
return model.save(data, options).then(function() {

fetch doesn't return null if query returns no results

Returns a promise, which will resolve with the fetched Model, or null if the model isn't fetched.

    // Fetch a model based on the currently set attributes,
    // returning a model to the callback, along with any options.
    // Returns a deferred promise through the Bookshelf.sync.
    // If `{require: true}` is set as an option, the fetch is considered
    // a failure if the model comes up blank.
    fetch: function(options) {
      var model = this;
      options || (options = {});
      return this.sync(options)
        .first()
        .then(function(resp) {
          if (resp && resp.length > 0) {
            model.set(model.parse(resp[0], options), _.extend({silent: true}, options))._reset();
            if (!options.withRelated) return resp;
            return new EagerRelation(model, resp)
              .fetch(options)
              .then(function() { return resp; });
          } else {
            if (options.require) return when.reject(new Error('EmptyResponse'));
            model.clear({silent: true})._reset();
            return {};
          }
        })
        .then(function(resp) {
          model.trigger('fetched', model, resp, options);
          console.log ('hello')
          return model;
        });
    },

It returns model with empty attributes.

I'm trying to write a findOrCreate method, so returning null would be handy.

Dirty checking of properties

Hi Tim, I have a question regarding possibility to update only changed properties, is something similar implemented or I should use Knex for this? Please see my example. Thank you.

new UserTreed({id : id, userId: userId})
      .fetch()
      .then(function(userTreed) {
        if (userTreed) {

          //CHANGING ONLY state attribute
          userTreed.set("state", "commited");

          userTreed.save().then(function(){
            //ALL PROPERTIES WERE USED TO UPDATE ENTITY
            res.end('commited');
          })
        }else {
          res.end('no such entity')
        }

      }, function(err) {
        res.end(err);
      })

`format` should be called for `select`, `delete`, and `first` queries

From the docs an example of format is to be able to underscorize camel cased attrs before working with the db.

However, format is only run on insert and update. Format should be run for all sync methods.

Use Case

I use camel cased variables all through my code base, but I prefer to use underscores in my database because with Postgres, identifiers with capitalizations must be quoted which is irritating when sending raw queries to the db (e.g. when using psql on the command line).

Interface for dynamic eager relation constraints

Probably something along the lines of:

model.fetch({
  withRelated: ['items', 'people', {'places': function(qb) {
     qb.where({zipcode: zip});
  }]
}).then(...

Duplicate documention for `isNew()`

Is it possible to get a collection of Bookshelf Models

Hi Tim, I am wondering if it is somehow possible to load more models of the same table in a performant way. I know Bookshelf model is related to a database row

I am having similar scenario

tbl Post
tbl User,
tbl PostComment
-user_id
-post_id

Is it somehow possible to fetch all post comments with Bookshelf and having users and posts attached?

Collection and forge/save

I'm trying to use Collection.forge([...]).save() and am getting an error saying that the object has no method 'save'

Looking for multiple results.

There is a quick way to use the sql WHERE clause using a model. However it appears the collections do not support where clauses.

Is there a direct way to get a list of results from a query with a where clause or do I need to go all the way down to the Knex protocol.

For example, I am searching for a bunch of posts their authors and commentors from the same date (2013-05-26 as it is stored in my database), I would also like to have the eager loading done also. This code only returns the first result. However there could be many.

new Post({submitDate: '2013-05-26'}).fetch({withRelated:['author.name','comments.author']})
.then(...)

As a side note. To load a sqlite database the format below works, not that on the website.

var SqliteDB = Bookshelf.Initialize('sqlitedb', {
  client: 'sqlite',
  connection: {
    filename : './testdb'
  }
});

Model Validation

Yo, I am coming over from Sequelize ORM and for the most part migration has been pretty easy. However, while I was working with the Bookshelf ORM I noticed a few things about validation that have me confused about how it should be implemented.

First there is this comment in the source for the save method:

// Sets and saves the hash of model attributes, triggering
// a "creating" or "updating" event on the model, as well as a "saving" event,
// to bind listeners for any necessary validation, logging, etc.
// If an error is thrown during these events, the model will not be saved.

Then there is this comment above the _validate method.

// Validation can be complicated, and is better handled
// on its own and not mixed in with database logic.

So I can't tell if the plan is just to completely omit model validation from this ORM or not? Sequelize uses node-validator https://github.com/chriso/node-validator and I began to attempt implementing the missing validation methods that were omitted/stripped from the backbone inheritance, but stopped because I don't know if it's something Bookshelf is even interested in.

Can anyone tell me the plan for validation and Bookshelf or advise the best way to implement validation against my model if there is no intention to support it?

Thanks!

Sample application and model structure for large application?

Bookself looks very promising! Love the documentation. I'm evaluating Sequalize for one of my projects. Maybe we can have a wiki on how is it different from other ORM? Also a sample app would be nice focusing model structure (model per file), organization etc? How would one go about using the models in other parts of the application (RESTful API design, for example).