vincit / objection.js Goto Github PK

Currently when right hand operator of method is casted (postgresql cannot resolve type automatically, because it is jsonb field) to correct type, the type is decided from right hand operand type.

Type extraction should be extended to handle case where operator is IN or NOT IN and right hand argument is an array. In that case type to which left hand operand is casted should be resolved from first element of right hand side array.

In documentation in the site several times are mentioned:

Note that on postgresql you can just chain returning('') to the normal insert method to get the same result without an additional query.

But according to postgresql documentation it should be passed '*' instead ''. In this case it works as expected.

User.relationMappings = {
    business: {
        relation: Model.OneToOneRelation,
        modelClass: Business,
        join: { from: 'business.user_id', to: 'user.id' }
    }
};

Is there any way to do something like this currently, where the foreign key is on the other model?

After patch operation, the full model is not returned. It's missing id attribute:

Model.query()
.patch(newObj)
.where('id', modelId)
.then(function(newModel) {
    return newModel.toJSON();
})
.then(function(json) {
    // At this point json.id === undefined ?
});

I have three tables: people, roles, person_roles. I have defined many-to-many relationships on each of the models for people (person) and roles (role). As a test, I issue the following query:

models.Person
  .query()
  .eager('roles')
  .where({id: 1})
  .then(function (people) {
    console.log(people[0]);
    knex.destroy();
  })
  .catch(function (err) {
    console.error(err);
    knex.destroy();
  });

The result of which is the details from the people table for that person, but with a roles attribute set to []. It looks to me like the issue is happening on line 94 of ManyToManyRelation.js. The result of that line is an object with one property. The name of that property is "undefined". But the next access of that object is:

owner[self.name] = relatedByOwnerId[owner[self.ownerProp]] || [];

Nothing in the bracket resolves to "undefined", so the result is [].

Would it be a good idea to actually write own extended schema, which could be more consistent about nullables and required etc. fields?

Is there any reasonable alternatives? Here was one article to consider: http://gilesbowkett.blogspot.fi/2015/01/why-panda-strike-wrote-fastest-json.html

If we could extend e.g. https://github.com/pandastrike/jsck to support our favor of syntax?

Its missing from some files e.g. MoronRelationExpression.js. Maybe add jshint to be part of tests.

Is there no pass through for the stream implementation?

Currently only case where we are able to do something like

where ("jsonCol"->'a')::jsonb = ("jsonCo"->.'b')::jsonb

is with equality, subset and superset operators. This case was straight forward to implement, because all those operands works nicely with jsonb types.

To be able to use all other operators we would need to tell explicitly casting how jsonb fields are interpreted for given operator.

e.g.

select * from "ModelJson" where ("ModelJson"."jsonObject"#>'{a}')::text LIKE ("ModelJson"."jsonObject"#>'{b}')::text;

Would require that we actually have to give type casting for both sides of operand. That is due to nature of jsonb fields where PostgreSQL just doesn't know how types should be casted, like the case is with normal columns.

One way to support type casting could be to extend filed expression a bit more to allow to tell to what type reference should be casted e.g.

.whereJson("ModelJson.jsonObject:a.0<numeric>", ">", "ModelJson.jsonObject:b.0<numeric>")

would yield

select * from "ModelJson" where ("ModelJson"."jsonObject"#>>'{a,0}')::numeric > ("ModelJson"."jsonObject"#>>'{b,0}')::numeric;

notice that when type casting is given, field value is extracted with #>> operator instead of #> to be able to the given casting.

Example current:

 Person
  .query()
  .where('age', '>', 40)
  .andWhere('age', '<', 60)
  .andWhere('firstName', 'Jennifer')
  .orderBy('lastName')
  .then(function (middleAgedJennifers) {
    console.log('The last name of the first middle aged Jennifer is');
    console.log(middleAgedJennifers[0].lastName);
  });

Example style ES6 Arrow :

Person
  .query()
  .where(x => x.age > 40)
  .andWhere(x => x.age < 60)
  .andWhere(x => x.firstName == 'Jennifer')
  .orderBy(x => x.lastName)
  .then(function (middleAgedJennifers) {
    console.log('The last name of the first middle aged Jennifer is');
    console.log(middleAgedJennifers[0].lastName);
  });

this syntax is wonderful.

Enchanment: Current implementation does not allow orring nor notting.

I have a table, entities which has a primary key of a generated UUID. I'd like for my services to not have to worry about this PK generation when creating a new Entity. Therefore, I added a beforeInsert method to generate the UUID and add it to the model. This much works, but I can't seem to find a way to return the generated UUID to the invoking service.

According to the documentation, the identifier should be returned with the insert():

NOTE: The return value of the insert query only contains the properties given to the insert method plus the identifier.

Unfortunately, the returned identifier is 0:

{ method: 'insert',
  options: {},
  bindings: [ '7ffecfde-ac66-49a9-a3f8-06be0f3fa61e', 'COMPANY' ],
  sql: 'insert into `entities` (`entity_id`, `entity_type`) values (?, ?)' }
THIS: {"entityType":"COMPANY","entityId":0}

Even if I use insertAndFetch(), I still cannot retrieve the generated UUID, because the select lookup fails when it tries to lookup the entity with a PK of 0.

service.js:

...
return Entity
    .bindTransaction(transaction)
    .query()
    .insert({entityType: 'COMPANY'});
...

entity.js:

...
Entity.tableName = 'entities';
Entity.idColumn = 'entity_id';

Entity.prototype.$beforeInsert = function (context) {
    this.entityId = uuid.v4();
};

Entity.prototype.$afterInsert = function (context) {
    console.log('THIS: ' + JSON.stringify(this));
};

Entity.jsonSchema = {
    type: 'object',
    required: ['entityType'],

    properties: {
        entityId: {type: 'string', minLength: 36, maxLength: 36},
        entityType: {type: 'string', minLength: 1, maxLength: 10}
    }
};
...

entity table (MySQL):

CREATE TABLE `entities` (
  `entity_id` varchar(36) NOT NULL,
  `entity_type` varchar(10) NOT NULL,
  PRIMARY KEY (`entity_id`),
  KEY `fk_entities_entity_type1_idx` (`entity_type`),
  CONSTRAINT `entities_ibfk_1` FOREIGN KEY (`entity_type`) REFERENCES `entity_types` (`entity_type`)
) ENGINE=InnoDB AUTO_INCREMENT=186 DEFAULT CHARSET=utf8;

Hello!
I try to implement read-modify pattern via row level locking mechanism. Something like this:

SELECT ... FOR UPDATE;
UPDATE ... COMMIT;

And cannot imagine how I can realise it in Objection. With pure knex I can use transaction method plus forUpdate.
Could you suggest any workaround for implementation this query?

Maybe it should open http://vincit.github.io/moron.js/MoronModel.html or some real index page.

Also would be nice to add link to github from header bar to get easily back to sources.

This question has come up few times, I wrote here some of my thoughts people who know better may correct me where I'm wrong:

_pi: So what does objection.js do differently than bookshelf.js which is also based on knex?

khoker: I've been partial to Bookshelf for recent projects (like Objection, also built on Knex). I see a lot of similarities, but I think I might be missing the obvious "wow that looks so much easier" vibe.
Anyone happen to see something that is annoying in Bookshelf that Objection might prove a solid alternative for?

One big difference is that Objection.js is not built following Backbone's Model / Collections style, which I personally don't find really nice to play with. My biased feeling is that Objection.js has a nicer and cleaner syntax. One does not have to ponder when to use model and when collections and all map, find, etc. operations can be done with lodash or with methods that bluebird offers for promises. Self updating collection idea works better in frontend world. Also Objection.js avoid using events for passing information what is happening unlike Backbone models.

Objection.js syntax to describe model properties, validation and relations is declarative.

Schema for validating DB input data uses standard JSON schema.

Objection.js eagering syntax is more permissive with recursive eagering (I suppose bookshelf does not have it).

'Objection.js' APIs has also been coded in a way to help IDE's autocompletion to work, so if you are write modelInstance.$query(). system knows that hey you got QueryBuilder as a return value and gives you a list of available methods and their documentation. Objection.js avoids using syntax where one writes .allMightyFilter({ dogsName : { gt: 1} }) which is basically impossible to make work with autocomplete.

EDIT 6.1.2017:

To sum up some additional features, that has been implemented during last 1.5 years.

• Development has been active and supported by Vincit (https://www.vincit.fi/en/) (however direction of development is community driven)
• Now in addition to eager fetching data through relations one can also insert and upsert nested model hierarchy to database with single query-command.
• Table / Column JSON field references were promoted to first class citizens so after #270 they can be passed to all APIs and references has also support for declaring additional casting / as for them ref('table.column:jsonattribute.wat[0].etc').castNumber().as('etc') or ref('id') which would have required knex.raw('??', ['id']) earlier.
• Support for postgresql JSONB operations including jsonb_set generation when patching single values inside jsonb column
• Eager fetching data through relations can be done with join or multiple selects (performance depends a lot case by case)
• Typings for typescript
• Improvements to Model's lifetime hooks and additional query context for passing some data for example session to multiple queries done by nested inserts or other more complex operations http://vincit.github.io/objection.js/#context

I seem to understand that one should use knex to connect to the database, but then how do we use knex in relation to moron.js?

I must be a moron, but I'd like simple easy-to-follow instructions for setting up a connection to the database and using it with moron.js before showing how to use the models.

I have question about validating a password before the user is inserted.

I have the following insert:

// Insert request
User.query()
    .insert({name: 'Jon', 'email: [email protected]', password: '123'})
    .then(function (user) {
        // Validations passed and user is added
    })
    .catch(function(error){
       // Validations did not pass
    });

// Validation Schema
User.jsonSchema = {
    type: 'object',
    required: ['name','email', 'password', 'salt'],
    properties: {
        id: {type: 'integer'},
        name: {type: 'string', minLength: 2, maxLength: 255},
        email: {type: 'string', minLength: 3, maxLength: 255},
        password: {type: 'string', minLength: 6, maxLength: 255},
        salt: {type: 'string', minLength: 3, maxLength: 255},
    }
};

I would like to be able to validate the password and add a salt but ONLY on an insert.
I can not seem to figure if jsonScheme has the ability to pass a function to the properties.password
or not. If not I need to detect when I am in $beforeValidate if I am in an insert state or an update state because the password will be an option on update, ie not change it if it is empty.

Is $beforeInsert just a callback or can it cause it to reject the promise or throw an error that will be caught in the promise? If so I can probably do the password validations and the adding of the salt in there manually.

Thanks!

Thanks for a great tool.

I have my models up and working successfully, except for the withSchema method. A query like this:
User.query().withSchema('shared') throws a TypeError withSchema is not a function.
This works fine if I use knex directly (eg. knex('users').select().withSchema('shared'))

Any thoughts?

Hello,

I'm trying trigger a separate insert (based off the model) whenever an update occurs on a model. Obviously, I need this to be atomic, so I'm looking for a way to bind a transaction to the update and the insert.

Ideally, I'd create the transaction in the $beforeUpdate function and bind it to the current object as well as the additional insert object. However, that seems quite difficult to accomplish with the way bindTransaction works. So, alternatively, I'd like to bind a transaction to the update outside of the model class, and be able to retrieve that transaction object from within the $beforeUpdate function so I can create the insert statement with a bound transaction.

Of course, I realize that I could create the insert outside of the model (in the service in the context of the transaction) but I want to abstract this insert away from the service developer. Any information you can provide to help me accomplish an additional atomic action within the model is appreciated.

Thanks,
Jeff

I have this query now in a controller:

User.query()
        .where('email', req.body.email)
        .first()
        .then(function(email){
            if(email){
                throw {data: {email: 'Already exists.'}};
            }
            return User.query().insert(req.body);
        })
        .then(function (user) {
            res.send({ token: user.token() });
        })
        .catch(function(error){
            console.log('error', error);
            return res.status(422).send(error.data || error);
        });

I would like to have this instead:

User.query()
            .insert(req.body)
            .then(function (user) {
                res.send({ token: user.token() });
            }).catch(function(error){
                return res.status(422).send(error);
            });

and then in $beforeInsert have the email checking query:

self.$beforeInsert = function(){

            if(!validatePassword(self.password)){
                // THIS THROW WORKS!
                throw {data: {password: 'String is too short (' + self.password.length + ' chars), minimum 8'}};
            }

            User.query()
            .where('email', self.email)
            .first()
            .then(function(user){
                console.log(user);
                if(user){
                    // THIS THROW CAUSES BLUEBIRD UNHANDLED REJECTION ERROR 
                    throw {data: {email: 'Already exists.'}};
                }

                // Create salt and hash password
                self.salt = new Buffer(crypto.randomBytes(16).toString('base64'), 'base64');
                self.password = hashPassword(self.password);
            })
            .catch(function(error){
                console.log(error);
                throw {data: {error: error}};
            });
        };

The first throw in $beforeInsert works. However the second throw, the one in a now "nested" promise causes an unhandled rejection error, most likely due to the fact that the promise in self.$beforeInsert does not return iteself back to the then method that handles the first insert query.

Is there a way to get a promise made in $beforeInsert to return back to the initial insert promise and have the errors caught at the parent level so bluebird is not complaining about unhandled rejection errors?

Thanks - Jon

E.g. https://travis-ci.org/fiddur/some-comments/jobs/82571193

When downgrading to 0.2.6 it works as it should. I haven't debugged more than that yet, coming up later…

So what I'm trying to achieve is to map a ManyToOne relationship only on the One side and I was wondering if (how) would it work? I have these models and they are fine for fetching data, however I am unsure how to about inserting a new review.

class Review extends Model {
  static get tableName() {
    return 'review';
  } 
  static get relationMappings() {
    return {
      movie: {
        relation: Model.OneToOneRelation,
        modelClass: __dirname + '/Movie',
        join: {
          from: 'review.movie',
          to: 'movie.id'
        }
      }
    }
  }
}

class Movie extends Model {
  static get tableName() {
    return 'movie';
  }
}

My first idea was to simply try

var review = {
  'message': 'foo',
  'movie': 1
};

Review.query().insert(review).then(...);

Where 1 is an ID for a movie. This told me that movie is supposed to be json. I went through the docs and tried changing 1 to {'#id': 1}, but then validation failed (I omitted it from the models but obviously they have properties) complaining for missing fields.

From the docs I saw the way it's supposed to work is something like

Movie
  .query()
  .where('id', 1)
  .first()
  .then(function (movie) {
    return movie.$relatedQuery('reviews').insert({message: 'foo'});
  });

But this would require me to write the mapping on the movie side as well and do a select first. I'd prefer to just do the insert and trust the id, then catch the error if the foreign key is wrong. Is there any way to do that?

As knex.raw, knex.fn can be very useful (think myModel.fn.now).

Thanks for the great component!

How I should handle situation where I use compound primary key? For example I try to insert new record and I get error 'Failed to create model: ... - column "id" does not exists'.
I tried to override idColumn and specify something as [ 'col1', 'col2' ].

dumpSql method is broken in 0.2.2, 0.2.3 and 0.2.4. For find queries like

somePerson.$relatedQuery('someManyToManyRelation').dumpSql();

it only returns something like

select * from "table"

The same query example + link to field expression syntax could be added also to readme.md

One has to:

• uncomment implementations of query builder methods
• istanbul report skipping annotations from QueryBuilder
• remove descript.skip from json-queries.js integration tests

QueryBuilder returning() method works slightly different to knex, so some objection specific description could be added to API docs.

Json query API could be even easier to use if all knex query builder methods can take in Json field expressions in addition to plain knex column references. In that case instead of knex implementation our json query versions would be called.

Somewhat related to #18

you have estimated SQL Server ?

Thanks !

Trying to run the es7 examples according to instructions:

~/dev/moron/examples/express-es7 ❯❯❯ npm start

> [email protected] start /Users/marv/dev/moron/examples/express-es7
> node_modules/babel/bin/babel-node --stage 0 app

sh: node_modules/babel/bin/babel-node: No such file or directory

npm ERR! Darwin 14.4.0
npm ERR! argv "node" "/usr/local/bin/npm" "start"
npm ERR! node v0.12.0
npm ERR! npm  v2.5.1
npm ERR! code ELIFECYCLE
npm ERR! [email protected] start: `node_modules/babel/bin/babel-node --stage 0 app`
npm ERR! Exit status 127

Dunno if this is due to Babel version changes or something, but simply adding ".js" to the command seemed to fix it:

iff --git a/examples/express-es7/package.json b/examples/express-es7/package.json
index 799c7cd..fe8c0f1 100644
--- a/examples/express-es7/package.json
+++ b/examples/express-es7/package.json
@@ -4,7 +4,7 @@
   "description": "Moron.js express ES7 example",
   "main": "app.js",
   "scripts": {
-    "start": "node_modules/babel/bin/babel-node --stage 0 app"
+    "start": "node_modules/babel/bin/babel-node.js --stage 0 app"
   },
   "author": "Sami Koskimäki",
   "license": "MIT",

Are you planning to support polymorphic associations?

Here's an example of it in another ORM.
Another one.

I haven't delved into moron's source code but perhaps an API like this would work:

// Schema for a comment model which relates polymorphically 
// to several tables through the commentable and commentableId attributes.
Comment.jsonSchema = {
    type: 'object',

    properties: {
        id: {type: 'integer'},
        text: {type: 'string'},
        commentable: {type: 'string'},
        commentableId: {type: 'integer'},
        authorId: {type: 'integer'},
        createdAt: {type: 'string', format: 'date-time'},
        updatedAt: {type: 'string', format: 'date-time'}
    }
};

Comment.relationMappings = {
    // A comment can be related to a topic.
    topic: {
        relation: Model.OneToOneRelation,
        modelClass: __dirname + '/topic',
        join: {
            from: 'Comment.commentableId',
            to: ['Comment.commentable', 'id']
        }

    // A comment can also be related to a user's profile.
    profile: {
        relation: Model.OneToOneRelation,
        modelClass: __dirname + '/profile',
        join: {
            from: 'Comment.commentableId',
            to: ['Comment.commentable', 'id']
        }
    }
};

Or maybe something like this:

    topic: {
        ...
        join: {
           from: 'Comment.commentableId',
           morph: {table: 'Comment.commentable', attribute: 'id'},
        }

For each table I have createdAt and updatedAt and it s redondant to set it in each beforeCreate/Update It is possible to add a static option to add it automatically ex:

class Foo extends Model {

    static $createdAt = 'tableName'
    static $UpdatedAt = 'tableName'

}

Model.Promise or something ?
This is to avoid having to require a (possibly different) promise lib - granted, it is questionnable request.

I have a model that defines a $beforeUpdate method:

Person.prototype.$beforeUpdate = function beforeUpdate() {
  this.email = `${this.email}.updated`;
  this.updatedAt = new Date();
};

If I log the Person instance prior to $beforeUpdate then email and updatedAt are at their original values. Once $beforeUpdate is invoked, and I log the same instance of Person, the fields have been modified accordingly. However, if I query my database directly, those two fields contain the original values they had prior to $beforeUpdate being invoked.

I am performing my update like so:

Person.query()
    .update(person)
    .where({id: person.id})
    .then(function (p) {
      console.log(`Updated person: ${p.username}`);
      return Promise.resolve(p);
    });

This is happening with Objection version 0.2.4.

Hi! first, thanks for this great orm is it possible to have a rollback transaction for unit test or something similar. Exemple I use mocha for my unit test, inside each describe in the before hook I want begin a transaction and rollback in after hook. I don t know how I can do it simply now.

I'm having an issue with precedence of SQL statements. I have something like this:

  Resource
    .query()
    .first()
    .where('id', id)
    .whereJsonSupersetOf('data:permissions', [{ actor: user.id, action: action }])
    .orWhereJsonSupersetOf('data:permissions', [{ actor: user2.id, action: action }])

Which emits SQL like this:

select * from "Resource" where "folderId" = ? and ( "data"#>'{permissions}' )::jsonb @> ?::jsonb or ( "data"#>'{permissions}' )::jsonb @> ?::jsonb

But that's not quite correct... I really need parenthesis around the JSON permission checks. In other words, I'd like to see the ultimate SQL look like this:

select * from "Resource" where "folderId" = ? and ( ( "data"#>'{permissions}' )::jsonb @> ?::jsonb or ( "data"#>'{permissions}' )::jsonb @> ?::jsonb )

Is there a way to force precedence / add parentheses?

Thanks!
Scott

Hello there.

We have relational data that we would like to be able to represent in the same way on both the server and client. The reason we want it to work in the client is that our app works offline (with a custom sync solution). It would be absolutely amazing if this library was tested again the cordova sqlite implementation and the related websql browser equivalent. Would you ever consider this kind of thing as a goal of this project or is this kind of thing out of scope?

Thanks for this great ORM. I was wondering if you planned on creating an es6 example project. I'm trying to get this lib working with Node 4.x / es6, but simply inheriting from Model isn't working, e.g.:

'use strict';
const Model = require('objection').Model;
class MyModel extends Model {
  get tableName() {
    return 'someTable';
  }
  // etc
}

In Model this.knex() returns null. Here's the specific method in Model that's angry:

Model.knexQuery = function () {
  return this.knex().table(this.tableName);
};

Do you have any plan to support computed fields in models?

Since OINAORM (Objection is not an ORM) it would be cleaner to just not
try to return an instance after a patch or update call - it doesn't really make sense
as soon as we're updating multiple records.
This is related to #21

What would be the most appropriate method to place code which updates createdAt and updatedAt columns ?

Thanks for the new version. While withSchema works on solo queries, is there a way to pass it through to subsequent eager joins?

Eg. User.query().withSchema('1').eager('tasks') correctly uses schema 1 when selecting all users, but the second query to get the tasks doesn't have it. I realise that automatically using the same schema might not always be desired though, if you're doing some sort of cross-schema query. Is there a way to set it easily?

With Babel we can use these features today. Would it be possible to use moron.js using ES6 classes and async functions instead of promises?

The name is very insulting. I highly recommend changing it ASAP.

Hello, I am searching a way to do this with objection I know how to do this with knex but I can t do the same with objection, I have try this:

Tags.query()
        .eager('[invoiceLines]')
        .where({key: 'collectId', "invoiceLines.payerId": '2'})

select * from tags t

left join invoiceLines_tags it on it.tagId = t.id
left join invoiceLines i on it.invoiceLineId = i.id

where t.key = "collectId"
    and i.payerId = 2

Thanks

Hi,

As documented on knexjs.org, there is an alias of nodeify as asCallback.
Is it possible to restore that alias in the promises returned by Objection ?

When an array type is defined for a property in the jsonSchema an update results in a malformed query.

Running the following

class Movie extends Model {

  static get tableName() {
    return 'movie';
  }

  static get jsonSchema() {
    return {
      type: 'object',
      properties: {
        tags: {type: 'array'}
      }
    }
  }
}

    Movie.query().patch({'tags': ['test']}).where('id', 1);

results in:

{ [error: update "movie" set "tags" = $1 where "id" = $2 - malformed array literal: "["test"]"]
  name: 'error',
  length: 144,
  severity: 'ERROR',
  code: '22P02',
  detail: '"[" must introduce explicitly-specified array dimensions.',
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'arrayfuncs.c',
  line: '242',
  routine: 'array_in' }

  error: update "movie" set "tags" = $1 where "id" = $2 - malformed array literal: "["test"]"
      at Connection.parseE (/.../node_modules/pg/lib/connection.js:539:11)
      at Connection.parseMessage (/.../node_modules/pg/lib/connection.js:366:17)
      at Socket.<anonymous> (/.../node_modules/pg/lib/connection.js:105:22)
      at emitOne (events.js:77:13)
      at Socket.emit (events.js:169:7)
      at readableAddChunk (_stream_readable.js:146:16)
      at Socket.Readable.push (_stream_readable.js:110:10)
      at TCP.onread (net.js:523:20)

Without the jsonSchema the query works as expected, I am assuming some sort of transformation or escaping happens that turns ["test"] into "["test"]" due to it being an array.

Leaving objection out completely and running the update via knex works as expected as well.

First of all, thanks for a great library!

I wonder whether there is a way to automatically convert all camelCase Javascript fields to snake-case database columns. I'd like to apply this trick to all models, rather than to every individual model.

Is that possible?