Giter Site home page Giter Site logo

bookshelf / bookshelf Goto Github PK

View Code? Open in Web Editor NEW
6.3K 96.0 568.0 7.11 MB

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

Home Page: http://bookshelfjs.org

License: MIT License

JavaScript 99.84% Shell 0.16%
bookshelf javascript database mysql postgresql sqlite nodejs orm

bookshelf's Introduction

bookshelf.js

NPM Version Build Status Dependency Status devDependency Status

Bookshelf is a JavaScript ORM for Node.js, built on the Knex SQL query builder. It features both Promise-based and traditional callback interfaces, transaction support, eager/nested-eager relation loading, polymorphic associations, and support for one-to-one, one-to-many, and many-to-many relations.

It is designed to work with PostgreSQL, MySQL, and SQLite3.

Website and documentation. The project is hosted on GitHub, and has a comprehensive test suite.

Introduction

Bookshelf aims to provide a simple library for common tasks when querying databases in JavaScript, and forming relations between these objects, taking a lot of ideas from the Data Mapper Pattern.

With a concise, literate codebase, Bookshelf is simple to read, understand, and extend. It doesn't force you to use any specific validation scheme, and provides flexible, efficient relation/nested-relation loading and first-class transaction support.

It's a lean object-relational mapper, allowing you to drop down to the raw Knex interface whenever you need a custom query that doesn't quite fit with the stock conventions.

Installation

You'll need to install a copy of Knex, and either mysql, pg, or sqlite3 from npm.

$ npm install knex
$ npm install bookshelf

# Then add one of the following:
$ npm install pg
$ npm install mysql
$ npm install sqlite3

The Bookshelf library is initialized by passing an initialized Knex client instance. The Knex documentation provides a number of examples for different databases.

// Setting up the database connection
const knex = require('knex')({
  client: 'mysql',
  connection: {
    host     : '127.0.0.1',
    user     : 'your_database_user',
    password : 'your_database_password',
    database : 'myapp_test',
    charset  : 'utf8'
  }
})
const bookshelf = require('bookshelf')(knex)

// Defining models
const User = bookshelf.model('User', {
  tableName: 'users'
})

This initialization should likely only ever happen once in your application. As it creates a connection pool for the current database, you should use the bookshelf instance returned throughout your library. You'll need to store this instance created by the initialize somewhere in the application so you can reference it. A common pattern to follow is to initialize the client in a module so you can easily reference it later:

// In a file named, e.g. bookshelf.js
const knex = require('knex')(dbConfig)
module.exports = require('bookshelf')(knex)

// elsewhere, to use the bookshelf client:
const bookshelf = require('./bookshelf')

const Post = bookshelf.model('Post', {
  // ...
})

Examples

Here is an example to get you started:

const knex = require('knex')({
  client: 'mysql',
  connection: process.env.MYSQL_DATABASE_CONNECTION
})
const bookshelf = require('bookshelf')(knex)

const User = bookshelf.model('User', {
  tableName: 'users',
  posts() {
    return this.hasMany(Posts)
  }
})

const Post = bookshelf.model('Post', {
  tableName: 'posts',
  tags() {
    return this.belongsToMany(Tag)
  }
})

const Tag = bookshelf.model('Tag', {
  tableName: 'tags'
})

new User({id: 1}).fetch({withRelated: ['posts.tags']}).then((user) => {
  console.log(user.related('posts').toJSON())
}).catch((error) => {
  console.error(error)
})

Official Plugins

  • Virtuals: Define virtual properties on your model to compute new values.
  • Case Converter: Handles the conversion between the database's snake_cased and a model's camelCased properties automatically.
  • Processor: Allows defining custom processor functions that handle transformation of values whenever they are .set() on a model.

Community plugins

  • bookshelf-cascade-delete - Cascade delete related models on destroy.
  • bookshelf-json-columns - Parse and stringify JSON columns on save and fetch instead of manually define hooks for each model (PostgreSQL and SQLite).
  • bookshelf-mask - Similar to the functionality of the {@link Model#visible} attribute but supporting multiple scopes, masking models and collections using the json-mask API.
  • bookshelf-schema - A plugin for handling fields, relations, scopes and more.
  • bookshelf-signals - A plugin that translates Bookshelf events to a central hub.
  • bookshelf-paranoia - Protect your database from data loss by soft deleting your rows.
  • bookshelf-uuid - Automatically generates UUIDs for your models.
  • bookshelf-modelbase - An alternative to extend Model, adding timestamps, attribute validation and some native CRUD methods.
  • bookshelf-advanced-serialization - A more powerful visibility plugin, supporting serializing models and collections according to access permissions, application context, and after ensuring relations have been loaded.
  • bookshelf-plugin-mode - Plugin inspired by the functionality of the {@link Model#visible} attribute, allowing to specify different modes with corresponding visible/hidden fields of model.
  • bookshelf-secure-password - A plugin for easily securing passwords using bcrypt.
  • bookshelf-bcrypt - Another plugin for automatic password hashing for your bookshelf models using bcrypt.
  • bookshelf-bcrypt.js - Fork of bookshelf-bcrypt using bcryptjs, using less dependencies.
  • bookshelf-default-select - Enables default column selection for models. Inspired by the functionality of the {@link Model#visible} attribute, but operates on the database level.
  • bookshelf-ez-fetch - Convenient fetching methods which allow for compact filtering, relation selection and error handling.
  • bookshelf-manager - Model & Collection manager to make it easy to create & save deep, nested JSON structures from API requests.
  • bookshelf-spotparse - A plugin that makes formatting, parsing and finding models easier.
  • bookshelf-update - Simple Bookshelf plugin that allows simple patching of models and skips updating if no values have changed.

Support

Have questions about the library? Come join us in the #bookshelf freenode IRC channel for support on knex.js and bookshelf.js, or post an issue on Stack Overflow.

Contributing

If you want to contribute to Bookshelf you'll usually want to report an issue or submit a pull-request. For this purpose the online repository is available on GitHub.

For further help setting up your local development environment or learning how you can contribute to Bookshelf you should read the Contributing document available on GitHub.

F.A.Q.

Can I use standard node.js style callbacks?

Yes, you can call .asCallback(function(err, resp) { on any database operation method and use the standard (err, result) style callback interface if you prefer.

My relations don't seem to be loading, what's up?

Make sure to check that the type is correct for the initial parameters passed to the initial model being fetched. For example new Model({id: '1'}).load([relations...]) will not return the same as new Model({id: 1}).load([relations...]) - notice that the id is a string in one case and a number in the other. This can be a common mistake if retrieving the id from a url parameter.

This is only an issue if you're eager loading data with load without first fetching the original model. new Model({id: '1'}).fetch({withRelated: [relations...]}) should work just fine.

My process won't exit after my script is finished, why?

The issue here is that Knex, the database abstraction layer used by Bookshelf, uses connection pooling and thus keeps the database connection open. If you want your process to exit after your script has finished, you will have to call .destroy(cb) on the knex property of your Bookshelf instance or on the Knex instance passed during initialization. More information about connection pooling can be found over at the Knex docs.

How do I debug?

If you pass debug: true in the options object to your knex initialize call, you can see all of the query calls being made. You can also pass that same option to all methods that access the database, like model.fetch() or model.destroy(). Examples:

// Turning on debug mode for all queries
const knex = require('knex')({
  debug: true,
  client: 'mysql',
  connection: process.env.MYSQL_DATABASE_CONNECTION
})
const bookshelf = require('bookshelf')(knex)

// Debugging a single query
new User({id: 1}).fetch({debug: true, withRelated: ['posts.tags']}).then(user => {
  // ...
})

Sometimes you need to dive a bit further into the various calls and see what all is going on behind the scenes. You can use node-inspector, which allows you to debug code with debugger statements like you would in the browser.

Bookshelf uses its own copy of the bluebird Promise library. You can read up here for more on debugging Promises.

Adding the following block at the start of your application code will catch any errors not otherwise caught in the normal Promise chain handlers, which is very helpful in debugging:

process.stderr.on('data', (data) => {
  console.log(data)
})

How do I run the test suite?

See the CONTRIBUTING document on GitHub.

Can I use Bookshelf outside of Node.js?

While it primarily targets Node.js, all dependencies are browser compatible, and it could be adapted to work with other javascript environments supporting a sqlite3 database, by providing a custom Knex adapter. No such adapter exists though.

Which open-source projects are using Bookshelf?

We found the following projects using Bookshelf, but there can be more:

  • Ghost (A blogging platform) uses bookshelf. [Link]
  • Soapee (Soap Making Community and Resources) uses bookshelf. [Link]
  • NodeZA (Node.js social platform for developers in South Africa) uses bookshelf. [Link]
  • Sunday Cook (A social cooking event platform) uses bookshelf. [Link]
  • FlyptoX (Open-source Node.js cryptocurrency exchange) uses bookshelf. [Link]
  • And of course, everything on here use bookshelf too.

bookshelf's People

Contributors

6utt3rfly avatar adriano-di-giovanni avatar alechirsch avatar alliejanoch avatar alsonkemp avatar anyong avatar bendrucker avatar chamini2 avatar chentsulin avatar cspotcode avatar ebramanti avatar eerenyuan avatar gaarf avatar greenkeeper[bot] avatar johanneslumpe avatar jordaaash avatar kibertoad avatar kirrg001 avatar mastermatt avatar mrhwick avatar muddydixon avatar osher avatar playrom avatar rhys-vdw avatar ricardograca avatar tgriesser avatar tkrotoff avatar vellotis avatar wbyoung avatar znarkus avatar

Stargazers

 avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar

Watchers

 avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar

bookshelf's Issues

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.

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

`pivot` documentation typo

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

They are actually prefixed with _pivot_ with a leading underscore.

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?

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

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:

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).

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

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?

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

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.

`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).

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!

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.

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

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'

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.

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

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

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!

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?

`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.

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.

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!

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!

`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.

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!

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

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.

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 !

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?

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?

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.

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?

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?

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

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    ๐Ÿ–– Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. ๐Ÿ“Š๐Ÿ“ˆ๐ŸŽ‰

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google โค๏ธ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.