bookshelf
A simple Node.js ORM for PostgreSQL, MySQL and SQLite3 built on top of Knex.js
Bookshelf.js - a JavaScript ORM for Node.js for PostgreSQL, MySQL, and SQLite3
I was searching high and low to find how to do basic counting (like SELECT COUNT(something) FROM table) with Bookshelf.js, but to no avail. Is there anything I'm missing? Or is it just used with a manual select query?
Thanks!
Source: (StackOverflow)
How do I insert an object like this into two tables Book and Page
var book = {
name: 'Hello',
author: 'World',
pages: [{
pagetitle: 'intro',
book: 8
}, {
pagetitle: 'chaptessr1',
book: 8
}]
};
Source: (StackOverflow)
I defined a Bookshelf model as
var Country = Bookshelf.Model.extend({
tableName: 'countries',
});
var Address = Bookshelf.Model.extend({
tableName: 'addresses',
country: function() {
return this.belongsTo(Country,'country_id');
},
});
Now I can fetch one of my models from the database
new Country({name:"Italy"}).fetch()
.then(function(country){
And create and Address
new Address({...}).save().then(function(address){
But I can't find, in the documentation, what method would help me to build the 'belongsTo' relationship. without manually setting the country_id attribute to the right one.
The only thing I see to build a relationship is the collection.create(object) method (http://bookshelfjs.org/#Collection-create) that is described as a convenience to create a model from an object,saving it, and adding it to the collection; I wouldn't know how to do the last part either.
Anyway, collection.create seems not to be available for model.related('collectionName') when collectionName refers to hasOne or belongsTo relationships since they do not reprensent collections.
Source: (StackOverflow)
Let's say we have a Join table vehicle_inspections and another join table inspection_actions, as well as basic tables for actions, vehicles, andinspections`.
Lets say I desire the following DB entries:
vehicles
----------------------------
id make
----------------------------
1 Toyota
actions
-------------------------------
id description
-------------------------------
2 Check Tire Pressue
inspections
-------------------------------
id location date
-------------------------------
3 New York tomorrow
vehicle_inspections
--------------------------------
vehicle_id inspection_id
--------------------------------
1 3
inspection_actions
--------------------------------
inspection_id action_id
--------------------------------
3 2
and the following bookshelf classes
inspection_actions.js
(function () {
'use strict';
var Repository = require('../repository');
module.exports = Repository.Model.extend({
tableName: 'inspection_actions',
});
})();
vehicle_inspections.js
(function () {
'use strict';
var Repository = require('../repository');
module.exports = Repository.Model.extend({
tableName = 'vehicle_inspections',
inspection: function () {
return this.belongsTo(require('inspection'));
},
fetchOrCreate: function(vehicleId, inspectionId, options) {
var self = this;
return self.query(function (qb) {
qb.where({
vehicle_id: vehicleId,
inspection_id: inspectionId
});
)}.fetch(options || {}).then(function (model) {
if (!model) {
model.save({
vehicle_id: vehicleId,
inspection_id: inspectionId
});
return model;
};
}
};
});
inspection.js
...
module.exports = Repository.Model.extend(_.extend({
tableName: 'inspections',
actions: function () {
return this.hasMany(require('./inspection-action'));
}
}));
And a route:
new VehicleInspection().fetchOrCreate(req.params.vehicle_id, req.params.inspection_id, {withRelated: ['inspection.actions']})
.then(function (vehicleInspection) {
var inspection = vehicleInspection.related('inspection');
console.log( inspection);
console.log(inspection.related(actions);
})
The inspection console log prints out the correct inspection, however, irrelevantly of what is in the database the second console.log prints out an empty result
{ length: 0,
models: [],
_byId: {},
...
targetIdAttribute: 'id',
foreignKey: undefined,
parentId: undefined,
parentTableName: 'tasks',
parentIdAttribute: 'id',
parentFk: undefined } }
This "bad" behaviour only occurs the first time a projectTasks entry is being created. What appears to be happening is that the inspection_action table is not being populated through the nested withRelated. How could I get this working nested create working?
Source: (StackOverflow)
How can i define hasMany Space -> Accounts relation?
var Space = Bookshelf.Model.extend({
tableName : 'spaces',
// Account variable does not exist :/
});
var Account = Bookshelf.Model.extend({
tableName : 'accounts',
spaceId : function() {
return this.belongsTo(Space);
},
});
What is the correct way to define this?
P.S. There is no tag for bookshelf js library: http://bookshelfjs.org/
Source: (StackOverflow)
I'm seeding my db from an array that looks like this, words and definitions are in a many to many relationship
var seeds = [
{
"word": "Click",
"definitions": ["Computer", "Mouse", "Tasto", "Pulsante", "Selezionare"]
}, {
"word": "Galoppo",
"definitions": ["Cavallo", "Andatura", "Trotto", "Ippica", "Passo"]
}, {
"word": "Raggio",
"definitions": ["Sole", "Bicicletta", "Diametro", "Luce", "Laser"]
}, {
.
.
.goes on for 1089 objects
this is what I tried
exports.seed = function (knex, Promise) {
var promises = seeds.map(function (seed) {
return knex('words').insert({
word: seed.word
}, 'id').then(function (word_id) {
var promises = seed.definitions.map(function (definition) {
return knex('definitions').insert({
definition: definition
}, 'id').catch(function (err) {
if (err.code === 1062)
return knex('definitions').select('id').where({
definition: definition
}).then(function (duplicate) {
return knex('definitions_words').insert({
definition_id: duplicate[0].id,
word_id: word_id
});
});
}).then(function (definition_id) {
return knex('definitions_words').insert({
definition_id: definition_id,
word_id: word_id
});
});
});
return Promise.all(promises);
});
});
return Promise.all(promises);
};
words are unique in my seeds but definitions may repeat, so I catch the duplication error and grab the id of the duplicate to put that in the junction table.
it seems to work fine, the junction table in fact ends up with 1089*5 rows (5445), but I get an error on the cli:
Error: Cannot add or update a child row: a foreign key constraint fails (`mytable`.`definitions_words`, CONSTRAINT `definitions_words_definition_id_foreign` FOREIGN KEY (`definition_id`) REFERENCES `definitions` (`id`))
Source: (StackOverflow)
I'm relatively new to Node and am working on a project using knex and bookshelf. I'm having a little bit of trouble unit testing my code and I'm not sure what I'm doing wrong.
Basically I have a model (called VorcuProduct) that looks like this:
var VorcuProduct = bs.Model.extend({
tableName: 'vorcu_products'
});
module.exports.VorcuProduct = VorcuProduct
And a function that saves a VorcuProduct if it does not exist on the DB. Quite simple. The function doing this looks like this:
function subscribeToUpdates(productInformation, callback) {
model.VorcuProduct
.where({product_id: productInformation.product_id, store_id: productInformation.store_id})
.fetch()
.then(function(existing_model) {
if (existing_model == undefined) {
new model.VorcuProduct(productInformation)
.save()
.then(function(new_model) { callback(null, new_model)})
.catch(callback);
} else {
callback(null, existing_model)
}
})
}
Which is the correct way to test this without hitting the DB? Do I need to mock fetch to return a model or undefined (depending on the test) and then do the same with save? Should I use rewire for this?
As you can see I'm a little bit lost, so any help will be appreciated.
Thanks!
Source: (StackOverflow)
I'm developing a node.js web service that looks like an auction. I'm using bookshelf.js for models and MySQL as DB.
Please consider the following db structure:
users:
id | login | password |
users_roles:
id | user_id | role_id |
roles:
id | name |
auctions:
id | user_id | rating_id |
rating:
id | speed | quality | communication |
Currently I have implemented User model as follows:
var Bookshelf = require('./bookshelf_model'),
Role = require("./role");
var User = Bookshelf.Model.extend({
tableName: 'users',
role: function () {
return this.belongsToMany(Role, 'users_roles', 'user_id', 'role_id');
}
});
module.exports = User;
I can get user details by
User.forge({id: 1}).fetch().then(
function(userDetails){
.......
}
);
This returns users' details from users table. To get user role I have to do yet another db call, like:
User.forge({id: 1}).role().fetch().then(
function(userRoleDetails){
.....
}
);
Is there any method to get all user info at once using bookshelf.js (or by using anythig else)? It would be great to get something like that at once:
user={
id:...,
login:...,
password:...,
role_id:....,
rating:/*avg rating from all user's auctions*/
}
Source: (StackOverflow)
I have a table set up to have timestamps and bookshelf configured to use them. Normally everything happens as it should and bookshelf takes care of the timestamps, but I have a case where I want to specify them, but when I try to do that the values are ignored and the current date is used.
I've tried to simplify my use case down to the essentials:
var Author = Bookshelf.Model.extend({
tableName: 'authors',
hasTimestamps: ['created_at', 'updated_at'],
bookAuthors: function(){
return this.hasMany(require('.book_authors'));
},
associateBookWithAuthor(bookId,timestamp) {
return self.related('bookAuthors').create({
book_id: bookId,
updated_at: timestamp, // ignored by bookshelf
created_at: timestamp // also ignored
})
}
}
I still want to keep the hasTimestamps configured for regular use cases. Is it possible to get Bookshelf to let me override the timestamps behavior?
Source: (StackOverflow)
I'm using Node.js with Bookshelf to setup an API. The JSON coming in the request looks like this
{
conversation: {
user_ids: [1, 2, 3, 4]
}
}
The conversation object looks like this:
var Conversation = storeManager.bookshelf.Model.extend({
tableName: 'conversations',
users: function() {
this.belongsToMany(User)
}
})
The user looks like this:
var User = storeManager.bookshelf.Model.extend({
tableName: 'users',
conversations: function() {
return this.belongsToMany(Conversation)
}
})
The database has a conversations and users table along with a conversations_users table that has a user_id and conversation_id field.
So I've been going through the docs and I'm struggling to figure out how I can create a conversation object that has a relationship with the existing users with the given IDs.
Here is what I have in my controller so far
ConversationController.prototype.create = function(req, res, next) {
// Create the conversation
Conversation.forge().set('users', req.body['conversation']['user_ids']).save().then(function(model) {
req.conversation = model
next()
}).catch(function(err) {
res.status(400).send(err)
})
}
Source: (StackOverflow)
I'm trying to use checkit with bookshelf and after adding the checkit rules, intentionally violating them, my promise#catch block doesn't seem to be properly catching the errors. (I could also be totally misunderstanding the use of catch here)
var validationRules = new Checkit({
email: 'required',
password: 'required'
});
var User = bookshelf.Model.extend({
tableName: 'users',
initialize: function() {
this.on('saving', this.validateSave);
},
validateSave: function() {
validationRules.run(this.attributes);
}
});
User.forge({}).save().then(function(validated) {
console.log('this shouldnt trigger');
}).catch(function(err) { // this doesnt seem to be working the way I expect
console.log(e.message);
});
When I create the empty user object, I'm getting the following unhandled error stacktrace, and also seeing a DB query being built (which may be a separate question for the bookshelf project and what happens when you hook into the 'saving' event)
Possibly unhandled Checkit Errors - email: The email is required; password: The password is required
at checkit/checkit.js:105:23
at tryCatch1 (bluebird/js/main/util.js:45:21)
at Promise._callHandler (bluebird/js/main/promise.js:597:13)
at Promise._settlePromiseFromHandler (bluebird/js/main/promise.js:607:18)
at Promise._settlePromiseAt (checkit/node_modules/bluebird/js/main/promise.js:769:18)
at Promise._settlePromises (checkit/node_modules/bluebird/js/main/promise.js:884:14)
at Async._drainQueue (checkit/node_modules/bluebird/js/main/async.js:98:12)
at Async._drainQueues (checkit/node_modules/bluebird/js/main/async.js:103:10)
at Async.drainQueues (checkit/node_modules/bluebird/js/main/async.js:37:14)
at process._tickCallback (node.js:415:13)
{ __cid: '__cid1',
method: 'insert',
options: undefined,
bindings: [],
sql: 'insert into `users` () values ()' }
ER_NO_DEFAULT_FOR_FIELD: Field 'email' doesn't have a default value
I have 2 questions about this:
- Since I have
debug: true turned on in my knex config, the block between the stacktrace and the ER_NO_DEFAULT_FOR_FIELD seems to be prepared SQL statements. Given that I introduced Checkit to catch validation errors on the Model level, why is SQL still being executed?
- Am I using the #catch block in the correct manner? And if so, why do I still get unhandled error stacktraces. (It looks as though the
e.message resulting from the #catch function is actually coming from MySQL rather than from Checkit) If not, what is the correct way to handle errors more gracefully here?
My main sources of information so far have been the bookshelf.js docs(http://bookshelfjs.org/), and the Checkit repo (https://github.com/tgriesser/checkit)
Source: (StackOverflow)
I have a User model and a Course model, and they have a many-to-many relationship, so basically users can join multiple courses and courses have many users (students). I have a join table and I'm trying to add additional information to the join table. Basically, users have a monthly quota of questions they can ask per course. So i've added a quota column to the User_Course join table. I'm having trouble accessing the quota column. Below is the relevant code to give an idea.
var User = DB.Model.extend({
Courses: function() {
return this.belongsToMany('Course', 'User_Course', 'userId', 'courseId');
},
});
var Course = DB.Model.extend({
Users: function() {
return this.belongsToMany('User', 'User_Course', 'courseId', 'userId');
}
})
And User_Course is my join table that has the additional quota column:
**User_Course:**
userId: int
courseId: int
quota: int
I want to be able to decrement my quota value. I'm able to update it using updatePivot, but I'm unable to simply get the value thats in quota. Here is the code I'm using to update the value:
var userPromise = new User.User({userId: userId}).fetch({withRelated: ['Courses']});
userPromise.then(function(_user) {
_user.related('enrolledCourses').updatePivot({courseId:courseId, quota:1}).then(function() {
return;
})
})
As you can see, I'm updating the quota value to 1 every time here. I'd like to programmatically determine what the current quota value is, and then decrement it.
Source: (StackOverflow)
I want to create an entry in a join table using bookshelf. I followed the instructions on the bookshelf website here. (Models instantiation with bookshelf and tables creation with knex).
Is there a way, upon creation of a new Book, to add values to the authors_books table WITHOUT creating an authors_books model/collection?
What should I add to this:
//Books references the Books collection that holds the Book models
var book = new Book({
title: title,
base_url: req.headers.origin
});
book.save().then(function(newBook) {
Books.add(newLink);
res.send(200, newBook);
});
Source: (StackOverflow)
BookshelfJS has the following example for using 'query':
model
.query({where: {other_id: '5'}, orWhere: {key: 'value'}})
.fetch()
.then(function(model) {
...
});
Is it okay to do the following:
var whereObj = {
'key1':'value1',
'key2':'value2'
};
model
.query({where: whereObj, orWhere: {key: 'value'}})
.fetch()
.then(function(model) {
...
});
Source: (StackOverflow)
I need a SQL query to find all duplicate groups. By duplicate, i mean any group that has the same members. So formally the problem can be written: Return all sets of channel_ids where in a given set containing m channels: channel i through m have contain the same members. I have a join table groups_members with columns group_id, user_id and a users table and a groups table. Thanks for any help.
Source: (StackOverflow)