Feathers database adapter for Objection.js, an ORM based on KnexJS SQL query builder for Postgres, Redshift, MSSQL, MySQL, MariaDB, SQLite3, and Oracle. Forked from feathers-knex.
License: MIT License
after switching from Knex to Objection code that used to work throws a 'bad request'.
after debugging, the solution is whitelist: ['$ilike'] with small caps. Different from documentation.
objection is required in sources and in types of feathers-objection, yet objection is listed only in devDependencies.
Other ecosystem packages with similar situations:
mongoose: not used in sources but mongoose is in peerDependencies, types on the other hand are used but @types/mongoose is only in devDependencies.
mongodb: used in sources and in types, both mongodb and @types/mongodb are listed only in devDependencies.
knex: used in types, knex is only in devDependencies.
elasticsearch: used in types, @types/elasticsearch is only in devDependencies.
nedb: used in types, @types/nedb is only in devDependencies.
In this adapter, the model can be retrieved from the service. Allowing to have finer control with objection models.
const Todo = app.service.('/todos').model
transaction.start(Todo.knex())The graphUpsert offered by objection.js can also be used to insert data. It offers more flexibility when creating multiple records or relationships.
Example use-case:
const data = [{
id: 'movie-one',
name: 'Movie One',
}, {
id: 'movie-two',
name: 'Movie Two'
}];
A movie with the id movie-two already exists.
Create request:
app.service('movies').create(data)
Response if using insert graph:
Error: id provided has already been used
Response if using upsert graph:
[{
id: 'movie-one',
name: 'Movie One',
createdAt: '2018-09-10T15:03:54.929Z',
updatedAt: '2018-09-10T15:03:54.930Z'
}, {
id: 'movie-two',
name: 'Movie Two',
updatedAt: '2018-09-10T15:03:54.940Z'
}]
This is just one of the many use-cases as it can also be used for relationships.
Suggested API:
A new option called createUseUpsertGraph
Hi,
I've an interesting bug annoying me for quite a few hours now. I have my users model which has a phone jsonb field. I've defined my Schema as follows:
static get jsonSchema() {
return {
type: 'object',
required: ['role'],
properties: {
email: { type: ['string', 'null'] },
password: {type:['string', 'null']},
accountkitId: { type: ['string', 'null']},
phone: {
type: 'object',
required: [],
properties: {
number: {type: 'string'},
country_prefix: {type: 'string'},
national_number: {type: 'string'}
}
},
role: {type:'string', enum: ["rider", "driver", "employee"]},
inviterId: {type:'integer'},
cityId: {type:'integer'},
isBlocked: {type:'boolean', default: false},
isActive:{type:'boolean', default:false},
}
}
}
now, with the following query, I get an error:
let user = await UserService.find({
query: {
phone: {
number: '+919123456789'
},
$limit: 1
}
});
Error: select count(distinct(\"users\".\"id\")) as \"total\" from \"users\" where \"users\".\"phone\"#>'{number}' = $1 - invalid input syntax for type json"
If I omit the + before the query i.e. only use '919123456789' (which is actually present in my DB), it doesn't complain but doesn't give the correct result either. It just can't find the record.
let user = await UserService.find({
query: {
phone: {
number: '919123456789''
},
$limit: 1
}
});
Please help as to what could be causing this and what can I do so that it just works. Thanks.
"objection": "^1.6.9",
"feathers-objection": "^4.2.3",
Just rolled a Feathers app with Typescript and Objection. After npm run dev I get the following error:
Could not find a declaration file for module 'feathers-objection'.
Additionally I get the error Cannot find name 'Application' in the autogenerated user.class.ts
EDIT
Unlike feathers-knex, there's no types folder in node_modules/feathers-objection
It has been a while since I've worked on a project using feathers (I would love to in the future!) and unfortunately been MIA in maintaining this adapter. Huge thanks to @dekelev for extending and improving this adapter and covering more use cases I didn't imagine before.
Now I'm wondering if this repo can benefit by moving to the community maintained org https://github.com/feathersjs-ecosystem giving it more visibility and for people who might use it and also improve it further.
In my test project, I have two models. User (id, username, password) and Message (id, userId, text).
class Message extends Model {
static get tableName() {
return 'messages';
}
static get relationMappings() {
var User = require("./users");
return {
user: {
relation: Model.BelongsToOneRelation,
modelClass: User,
join: {
from: 'messages.userId',
to: 'users.id'
}
}
};
}
}
...
app.use('/messages', ObjectionService({
model: Message,
id: 'id',
paginate: {
default: 10,
max: 100
},
allowedEager: '[user]'
}))
If I query messages by userId /messages?userId=1 it works.
If I query messages by user.username /messages?$joinEager=user&user.username=Bob I get an error.
error: select count("id") as "total" from "messages" where "user"."username" = $1 - missing FROM-clause entry for table "user"
Now if I comment out this.objectify(countQuery, query) within the _find() function, the server responds properly including the total value.
{ "total": 11, "limit": 10, "skip": 0, "data": [ ... ] }
But I'm not sure if I'm breaking anything by removing that line. Am I'm doing something wrong?
After adding:
properties: {
an_array: {type: 'array'}
}```
I can no longer create new records from front-end client with regular: `create({an_array:["something"]})`.
The error is: `malformed array literal: "["something"]"`
It works when I delete the definition from model.js but then I can't search for `$contains`.
Since graph upsert and insert is a major and rather useful feature of objection.js, I think this module should also offer support for it.
I have it partially implemented in a privately maintained fork. I'll migrate the basic functionality over soon.
From what I can tell, the $noSelect needs to be a query param. However when including it as a queryParam, feathers throws the following error:
error: BadRequest: Invalid query parameter $noSelect
at new BadRequest (/Users/rjewing/code/personal/evolution/packages/api/node_modules/@feathersjs/adapter-commons/node_modules/@feathersjs/errors/lib/index.js:86:17)
at commons_1._.each (/Users/rjewing/code/personal/evolution/packages/api/node_modules/@feathersjs/adapter-commons/lib/filter-query.js:41:27)
at Object.keys.forEach.key (/Users/rjewing/code/personal/evolution/packages/api/node_modules/@feathersjs/adapter-commons/node_modules/@feathersjs/commons/lib/utils.js:15:45)
at Array.forEach (<anonymous>)
at Object.each (/Users/rjewing/code/personal/evolution/packages/api/node_modules/@feathersjs/adapter-commons/node_modules/@feathersjs/commons/lib/utils.js:15:30)
at cleanQuery (/Users/rjewing/code/personal/evolution/packages/api/node_modules/@feathersjs/adapter-commons/lib/filter-query.js:35:21)
at Object.filterQuery [as default] (/Users/rjewing/code/personal/evolution/packages/api/node_modules/@feathersjs/adapter-commons/lib/filter-query.js:87:20)
at Object.filterQuery (/Users/rjewing/code/personal/evolution/packages/api/node_modules/@feathersjs/adapter-commons/lib/service.js:45:46)
at Object._patch (/Users/rjewing/code/personal/evolution/packages/api/node_modules/feathers-objection/lib/index.js:513:14)
at callMethod (/Users/rjewing/code/personal/evolution/packages/api/node_modules/@feathersjs/adapter-commons/lib/service.js:12:22)
at Object.patch (/Users/rjewing/code/personal/evolution/packages/api/node_modules/@feathersjs/adapter-commons/lib/service.js:83:16)
at processHooks.call.then.hookObject (/Users/rjewing/code/personal/evolution/packages/api/node_modules/@feathersjs/feathers/lib/hooks/index.js:56:27)
at process.internalTickCallback (internal/process/next_tick.js:77:7)
Currently countDistinct is used for count queries:
feathers-objection/src/index.js
Line 341 in df2c7a4
Testing in postgres, this is significantly slower (10x) then just a count query and also prevents parallelization by the dbms.
I would think that the default should not use countDistinct as most the majority of the time, the id column is going to be a pk which ensures uniqueness.
Yesterday I opened a issue on objection.js (issue 1153) asking how to do a recursive eager with a additional relation object.
Basically I wanted to convert a existing expression ([user, replies.[user, replies.[user, replies.[user, replies.[user, replies.[user]]]]]]) into a prettier looking recursive expression (replies.^5) with each reply having a user relation attached.
They suggested using objection's object notation with a function to call itself recursively to create a nested replies object. Which worked well.
function nestedReplies(depth) {
if (depth === 0) return {}
return {
user: true,
replies: nestedReplies(depth - 1),
}
}
var allowedEager = nestedReplies(5);
Now previously I was using a named eager filter in my ugly expression ([user, replies(replyFilter).[user, replies(replyFilter).[user, replies(replyFilter).[user, replies(replyFilter).[user, replies(replyFilter).[user]]]]]]) which I'm having trouble porting to the objection nation.
Objection's parseRelationExpression() converted my ugly expression to this.
{
"user": true,
"replies": {
"$modify": [
"replyFilter"
],
"user": true,
"replies": {
"$modify": [
"replyFilter"
],
"user": true,
"replies": {
"$modify": [
"replyFilter"
],
"user": true,
"replies": {
"$modify": [
"replyFilter"
],
"user": true,
"replies": {
"$modify": [
"replyFilter"
],
"user": true
}
}
}
}
}
}
So I added a $modify:["replyFilter"] property to my nestedReplies()'s output so the object notation matched objection's parsed version of my ugly expresion.
Problem I'm having now is the $modify keys are getting stripped somewhere before feathers-objection calls q.eager(query.$eager, this.namedEagerFilters);.
I use a feathers hook to set my default query.$eager property.
function before(context) {
context.params.query = context.params.query || {};
context.params.query["$eager"] = context.params.query["$eager"] || allowedEager;
console.log("$eager: " + JSON.stringify( context.params.query["$eager"], null, "\t"));
...
}
Which prints to console
$eager: {
"user": true,
"replies": {
"$modify": [
"replyFilter"
],
"user": true,
"replies": {
"$modify": [
"replyFilter"
],
"user": true,
"replies": {
"$modify": [
"replyFilter"
],
"user": true,
"replies": {
"$modify": [
"replyFilter"
],
"user": true,
"replies": {
"$modify": [
"replyFilter"
],
"user": true,
"replies": {}
}
}
}
}
}
}
I added two debug messages to feathers-objection's createQuery.
console.log("<createQuery> params.query: " + JSON.stringify( params.query, null, "\t" ));
console.log("<createQuery> _filterQuery: " + JSON.stringify( _filterQuery, null, "\t" ));
Which prints out
<createQuery> params.query: {
"$eager": {
"user": true,
"replies": {
"$modify": [
"replyFilter"
],
"user": true,
"replies": {
"$modify": [
"replyFilter"
],
"user": true,
"replies": {
"$modify": [
"replyFilter"
],
"user": true,
"replies": {
"$modify": [
"replyFilter"
],
"user": true,
"replies": {
"$modify": [
"replyFilter"
],
"user": true,
"replies": {}
}
}
}
}
}
}
}
<createQuery> _filterQuery: {
"filters": {},
"query": {
"$eager": {
"user": true,
"replies": {
"user": true,
"replies": {
"user": true,
"replies": {
"user": true,
"replies": {
"user": true,
"replies": {
"user": true,
"replies": {}
}
}
}
}
}
}
}
}
I thought maybe it was @feathersjs/commons' filterQuery() function that was stripping the keys, but when I add debug messages to the function they never execute. So now I'm stuck. Any ideas on what might be removing the $modify keys from my eager object.
This isn't a huge priority. The string notation works fine for now. Just thought I'd bring it up in case it was fixable.
I have a jsonb column in my table. So far I haven't figured out how to filter by an attribute within the column.
Glancing at Objection's jsonQueries.js file, it looks like wrapping the column name in a ref() call will let you query the value.
If that's the case. Would it be possible to add an additional service option for column names to wrap ref around?
Hi, great job on this library, thanks for the effort.
I can't find my way around filtering a query using nested fields loaded through a relation mapping. Eager filtering will send the parent relation with nulls in the nested relation fields, when the idea should be to NOT include those parents in the results.
For example, if you have Post and Comment, with Comment having a BelongsToOne relation to Post, and you want to load comments only for posts that are published, you can't do something like
comments.find({ query: { $eager: '[post]', 'post.published': true })
It throws saying 'post.state' is not a column of 'comment' table.
Doing something like that with standard SQL or knex is trivial because joined fields are part of the result set and thus you can use them in your .where filters.
This is specially important when you add pagination to the mix, because you want to retrieve N results that really match the expected query, not filtering out in the client or in a post hook.
Any idea or workaround, or is this simply not possible?
Edit: in the original Objection library you can do it with joinRelation, see here:
Vincit/objection.js#541 (comment)
Objectionjs upsertGraph method requires ID to be present in the data objection, it should be auto-populated to the data object from the ID param in update method, i.e. service.update(id, data, params) when graph upsert is enabled.
It looks like although the code for composite keys is there in the src/index.js file, the initialisation of the adapter doesn't take into account the 'idColumn' property of the Objection model.
This has led me to failed queries all the time which seem inexplicable if one does not enable the 'debug' mode of the knex module to see the actual queries made.
Steps to reproduce the faulty behaviour:
I have created a small patch to remedy this issue. @mcchrish Can you please check and merge?
#40
I believe that if this is OK, it justifies releasing 3.0.2 asap, as it is impossible to work with tables that has ID columns with name other than the name 'id;
Hi
Thanks for your work. Can it be that patch method is not working as expected?
If I patch a service and omit a field it gets overwritten by null.
Hi,
I've the following relations:
class shifts extends Model {
static get tableName() {
return 'shifts';
}
static get jsonSchema() {
return {
type: 'object',
required: ['name'],
properties: {
name: { type: 'string' },
date: {type: 'date'}
}
};
}
static get relationMappings() {
const Slot = require('./slots.model')
return {
slots: {
relation: Model.HasManyRelation,
modelClass: Slot,
join: {
from: 'shifts.id',
to:'slots.shiftId'
}
}
}
}
$beforeInsert() {
this.createdAt = this.updatedAt = new Date().toISOString();
}
$beforeUpdate() {
this.updatedAt = new Date().toISOString();
}
}
class slots extends Model {
static get tableName() {
return 'slots';
}
static get jsonSchema() {
return {
type: 'object',
required: ['startAt', 'endAt', 'availableOn'],
properties: {
startAt: { type: 'time' },
endAt: {type: 'time'},
availableOn: {type: 'number'},
shiftId: {type:'number'}
}
};
}
static get relationMappings() {
const Shift = require('./shifts.model')
return {
shift: {
relation: Model.BelongsToOneRelation,
modelClass: Shift,
join: {
from: 'slots.shiftId',
to: 'shifts.id'
}
}
};
}
slotDisplayName() {
return (moment(this.startAt, "hh:mm:ss").format('h:mm a') + ' - ' + moment(this.endAt,"hh:mm:ss").format('h:mm a'));
}
static get virtualAttributes() {
return ['slotDisplayName'];
}
$beforeInsert() {
this.createdAt = this.updatedAt = new Date().toISOString();
}
$beforeUpdate() {
this.updatedAt = new Date().toISOString();
}
}
but when I do the following :
await this.app.service('slots').Model.query().eager('shift');
or
await this.app.service('shifts').Model.query().eager('slots');
I get the following errors:
"slots.relationMappings.shift: Cannot read property 'get' of undefined"
"shifts.relationMappings.slots: Cannot read property 'get' of undefined"
Any clue why this could be happening? I'm using feathers-objection and have whitelisted eager.
I opened an issue on the objections github page but sami asked me to open an issue here as my model looks ok and it could be a but in feathers-objection.
I trying to connect to Objections but with the example like this Model.knex(Knex(config)) but with this it's not working even when I use it to App.configure, Im getting the following error
fn.call(this);
^
TypeError: Cannot read property 'call' of undefinedset custom id in service options
const options = {
model: Model,
id : 'my_own_custom_id', // auto increment
paginate
};
do post request at that service
error: NotFound: No record found for id 'undefined'
It seems that when PATCHing related entities, allowedUpsert option won't work, and related entities will not be updated. With PUT method, allowedUpsert works and related entities are in fact updated.
Since feathers-objection uses createService method to instantiate Service class, is it possible to extend it and override some methods? All I can figure is that I can either create new Service class, or implement hooks. Neither seem good enough because it either means rewriting every method or duplicating code/queries.
Can you give us an example of working application with actual relations between models because I cannot get stuff like allowEager, eagerFilters to work like it's described in the documentation.
If I have a model that looks like:
Record {
id: string,
name: string
status: string
}
When running the following patch call:
app.services('records').patch(id, {status: 'old'}, {query: {status: 'new'}});
The db will be successfully updated, however a NotFound error is thrown. This appears to happen b/c the record is updated, and then directly fetched using the id & query params that were provided. However one of those query params has been updated as a result of the patch.
It seems like it would be better to track the PK's of the records that were patched & use that to fetch the records again, or to disable the query param for patchs. The current behavior is not very clear.
When querying a jsonb column by a key containing a period, Objection needs the key to have square brackets around it. .
Objection example of how it looks.
var results = await Item.query().where(ref('properties:[oai.iai.al]').castInt(), 80)
Now when querying via feather's service.find() it works.
var results = await app.service('items').find({
query: {
properties: {
"[oai.iai.al]": 80
}
}
});
But when wrapping the key with brackets in a querystring it fails.
var res = await fetch("http://127.0.0.1:8471/items?" + qs.stringify({
properties: {
"[oai.iai.al]": 80
}
}));
I'm thinking the express.urlencoded() middleware is parsing the brackets as a array. Feathers-Objection may need to check if the key contains periods and add the brackets itself.
Adding this to objectify() seems to help.
Object.keys(params || {}).forEach(key => {
let value = params[key];
// Wrap key with brackets if it contains a period.
if (parentKey && key.includes(".") && !key.match(/\[(.*)\]/)) {
key = "[" + key + "]";
}
if (_utils.default.isPlainObject(value)) {
return this.objectify(query, value, key, parentKey);
}
...
Hi
I'm not able to implement timestamps properly when extending your service class.
After updating an entity the createdAt field is null. Looks like this can only be changed from
within the service itself or completely overwriting your update method.
const { Service } = require('feathers-objection');
const uuid = require('uuidv4');
module.exports = class BaseService extends Service {
create(data, params) {
if(!Array.isArray(data)) data = [data];
for(let i = 0; i < data.length; i++) {
data[i].id = uuid();
data[i].createdAt = new Date().toISOString();
delete data[i].updatedAt;
}
if(data.length === 1) data = data[0];
return super.create(data, params);
}
update(id, data, params) {
data.updatedAt = new Date().toISOString();
delete data.createdAt;
return super.update(id, data, params);
}
patch(id, data, params) {
data.updatedAt = new Date().toISOString();
delete data.createdAt;
return super.patch(id, data, params);
}
}
eager's second argument allows eager filtering. See http://vincit.github.io/objection.js/#eager
I was looking at the documentation for $eager and $joinEager, and while viewing the Objection reference material, I see that the related eager() and joinEager() methods will be deprecated in Objection v3 in favor of withGraphFetched and withGraphJoined. Are there plans to change this API to reflect those deprecations, or will it be OK to continue using $eager and $joinEager?
I was having trouble filtering results by multiple relationship values. I opened a ticket on objection.js asking how to do it. While I wait on that I figured I'd try filtering using a jsonb column instead.
My postgresql table has a jsonb column with a array of strings. I'd like to filter results by checking if the array contains a string.
It works in Objection. Here's what the query looks like.
Article.query()
.where(ref("articles.jsonb_column:tags"), "?", "news")
.then(function(results) {
console.log("articles: " + JSON.stringify( results , null, "\t"));
});
In Feathers-Objection, I added ? to the OPERATORS_MAP list.
const OPERATORS_MAP = {
...
"?": '?'
}
The castText() part in objectify() causes postgres to throw an error.
select count(distinct("articles"."id")) as "total" from "articles" where CAST("articles"."jsonb_column"#>>'{tags}' AS text) 'news' $1 - operator does not exist: text ? unknown
Removing castText() when the operator is ? seems to fix it.
if (columnType === 'object' || columnType === 'array') {
if (operator == "?") {
return query.where((0, _objection.ref)(`${this.Model.tableName}.${methodKey || column}:${(methodKey ? column : key).replace(/\(/g, '[').replace(/\)/g, ']')}`), operator, value);
} else {
return query.where((0, _objection.ref)(`${this.Model.tableName}.${methodKey || column}:${(methodKey ? column : key).replace(/\(/g, '[').replace(/\)/g, ']')}`).castText(), operator, value);
}
}
After those two changes, this query works as expected.
app.service('articles').find({
query: {
$and: [
{
"jsonb_column": {
"tags": { "?": "news" }
}
},
{
"jsonb_column": {
"tags": { "?": "weather" }
}
},
]
}
}).then(function(results) {
console.log("articles: " + JSON.stringify( results , null, "\t"));
});
Here's a list of jsonb operators. Hopping you can add support for them. :)
Table 9.44. Additional jsonb Operators
In your final complete code example in the README, you import something from ../lib called ObjectionService. Does that refer to this module, or something else?
Point dependencies to the new registries that are being maintained e.g. feathers-query-filter and feathers-errors.
I have a parent table product and two relations: hasOne brand, hasMany attributes. I'm trying to filter products by brand name like this {{apiUrl}}/product?$eager=[brand, attributes]&$joinRelation=[brand]&$sort[brand.name]=1, but I'm receiving an error
"select distinct \"product\".* from \"product\" inner join \"brand\" on \"brand\".\"id\" = \"product\".\"brandId\" where \"product\".\"name\" = $1 order by \"brand\".\"name\" asc limit $2 - for SELECT DISTINCT, ORDER BY expressions must appear in select list"
due to this line https://github.com/feathersjs-ecosystem/feathers-objection/blob/master/src/index.js#L290 using distinct
I tried also using $joinEager, but I need pagination and the limits get applied to the relations. $limit: 10, 1 product, 1 brand, 10 attributes, only a single product ends up in the result.
By removing the distinct portion I get the results as intended.
distinct ?The current API supports the ability to define eager filters (named or otherwise) upfront when the service is created, however, there doesn't seem to be any way to create dynamic eager filters from the client. In my use-case, I would like to be able to do something like the following:
service.find({
query: {
$eager: {
someRelation: {
userId: 1,
}
}
}
});
// Or maybe some new operator like:
service.find({
query: {
$eager: 'someRelation'
$filterEager: {
someRelation: {
userId: 1,
}
}
}
});Where userId is a property on someRelation.
I would imagine this new API would also work with existing named eager filters:
service.find({
query: {
$eager: {
'someRelation(someNamedFilter)': {
userId: 1
}
}
}
});And could even support most of the query operators:
service.find({
query: {
$eager: {
someRelation: {
userId: {
$nin: [1, 2, 3]
}
}
}
}
});Having looked at the code, I can't think of why the above wouldn't be achievable given that the DSL already supports quite complex filtering on the root model. I'm no expert on Objection though, so I could obviously be missing something here!
Hi,
I've been using this for the past couple weeks and it's been working great, but I was wondering if this library supports mergeAllowEager? I didn't see it anywhere in the docs and I didn't see anything in the source code, but just wanted to check.
Thanks
Latest version introduced a error in a old project of mine.
error: TypeError: Cannot read property 'properties' of null
at Object.keys.forEach.key (/PROJECT_PATH/node_modules/feathers-objection/lib/index.js:202:40)
at Array.forEach ()
at Object.objectify (/PROJECT_PATH/node_modules/feathers-objection/lib/index.js:165:31)
at Object.createQuery (/PROJECT_PATH/node_modules/feathers-objection/lib/index.js:298:10)
at Object._find (/PROJECT_PATH/node_modules/feathers-objection/lib/index.js:316:40)
at Object._get (/PROJECT_PATH/node_modules/feathers-objection/lib/index.js:386:17)
at q.then.row (/PROJECT_PATH/node_modules/feathers-objection/lib/index.js:455:19)
at tryCatcher (/PROJECT_PATH/node_modules/bluebird/js/release/util.js:16:23)
at Promise._settlePromiseFromHandler (/PROJECT_PATH/node_modules/bluebird/js/release/promise.js:512:31)
at Promise._settlePromise (/PROJECT_PATH/node_modules/bluebird/js/release/promise.js:569:18)
at Promise._settlePromise0 (/PROJECT_PATH/node_modules/bluebird/js/release/promise.js:614:10)
at Promise._settlePromises (/PROJECT_PATH/node_modules/bluebird/js/release/promise.js:693:18)
at Async._drainQueue (/PROJECT_PATH/node_modules/bluebird/js/release/async.js:133:16)
at Async._drainQueues (/PROJECT_PATH/node_modules/bluebird/js/release/async.js:143:10)
at Immediate.Async.drainQueues (/PROJECT_PATH/node_modules/bluebird/js/release/async.js:17:14)
at runCallback (timers.js:810:20)
at tryOnImmediate (timers.js:768:5)
at processImmediate [as _immediateCallback] (timers.js:745:5)
I found changing line 202 from
const property = this.jsonSchema.properties[column] || methodKey && this.jsonSchema.properties[methodKey];
to
const property = this.jsonSchema && (this.jsonSchema.properties[column] || methodKey && this.jsonSchema.properties[methodKey]);
got rid of the error.
Virtual attributes aren't being added to results after updating Objection to 1.6.4+. I think this issue is related.
For example, trying to stringify Objection's results won't include virtual attributes.
Comment.query().then(function(results) {
console.log("comments " + JSON.stringify( results , null, "\t"));
})
I think Feathers-Objection is affected by this. Unsure if it's something F-O can fix or if it needs to be fixed on Objection's end. But reverting to [email protected] works for now.
Let's say I have User model and Address model defined like this:
// See https://vincit.github.io/objection.js/#models
// for more of what you can do here.
const { Model } = require('objection');
class users extends Model {
static get tableName() {
return 'users';
}
static relationMappings() {
const address = require('./address.model')();
return {
address: {
relation: Model.HasOneRelation,
modelClass: address,
join: {
from: 'users.address_id',
to: 'address.id'
}
}
};
}
static get jsonSchema() {
return {
type: 'object',
required: ['password', 'email', 'first_name', 'last_name'],
properties: {
email: { type: ['string'], minLength: 6 },
password: 'string',
first_name: { type: ['string'], minLength: 4 },
last_name: { type: ['string'], minLength: 4 },
}
};
}
$beforeInsert(ctx) {
this.createdAt = this.updatedAt = new Date();
}
$beforeUpdate() {
this.updatedAt = new Date();
}
}
module.exports = app => {
if (app) {
const db = app.get('knex');
db.schema.hasTable('users').then(exists => {
if (!exists) {
db.schema.createTable('users', table => {
table.increments('id');
table.integer('address_id').unsigned();
table.string('email').unique();
table.string('password');
table.string('first_name');
table.string('last_name');
table.timestamp('createdAt');
table.timestamp('updatedAt').nullable();
})
.then(() => console.log('Created users table')) // eslint-disable-line no-console
.catch(e => console.error('Error creating users table', e)); // eslint-disable-line no-console
}
})
.catch(e => console.error('Error creating users table', e)); // eslint-disable-line no-console
}
return users;
};
// See https://vincit.github.io/objection.js/#models
// for more of what you can do here.
const { Model } = require('objection');
class address extends Model {
static get tableName() {
return 'address';
}
static get jsonSchema() {
return {
type: 'object',
required: [],
properties: {
street: { type: ['string', 'null']},
house_number: { type: ['string', 'null']},
}
};
}
static get relationMappings() {
const user = require('./users.model')();
return {
users: {
relation: Model.HasManyRelation,
modelClass: user,
join: {
from: 'address.id',
to: 'users.address_id'
}
}
};
}
$beforeInsert() {
this.createdAt = this.updatedAt = new Date();
}
$beforeUpdate() {
this.updatedAt = new Date();
}
}
module.exports = app => {
if (app) {
const db = app.get('knex');
db.schema.hasTable('address').then(exists => {
if (!exists) {
db.schema.createTable('address', table => {
table.increments('id');
table.string('street');
table.string('house_number');
table.timestamp('createdAt');
table.timestamp('updatedAt').nullable();
})
.then(() => console.log('Created address table')) // eslint-disable-line no-console
.catch(e => console.error('Error creating address table', e)); // eslint-disable-line no-console
}
})
.catch(e => console.error('Error creating address table', e)); // eslint-disable-line no-console
}
return address;
};
How can I:
users.address_id field?get method?I only managed to do that by manually inserting address and then fetching id and inserting to users.address_id. 2) I can only assume that it's possible in after.get hooks but I again have to query for the already retrieved user and for address and appending it to context.result. This seems very trivial but I cannot find complete example either on feathers, objection or feathers-objection documentation.
DB: postgresql, version: latest (just upgraded).
Working SQL:
select * from products where segmentation ->> 'type' = 'WT';
feathers code:
app.service('products').find({
query: {
// id: 1008 - this works! so no other issues
segmentation: {type: "WT"} // returning error
}
});
not working with error: 'select count(distinct("products"."id")) as "total" from ' + '"products" where "segmentation" = $1 - invalid input syntax for ' + 'type json',
Other syntax like {type: '"WT"'} return 0 results. Do I do something wrong or there is an issue here?
Currently you can safely remove filterQuery and nothing will change.
First, it should exit here because filtered.query will be an object. I assume it is a typo and it should have been if (! utils.isPlainObject(query)), it would make sense that way.
But even after this, convertOperators is converting operators, but const key = operators[prop] ? operators[prop] : prop will always be prop itself. defaultOperators returns object where keys and values are equal, there is no mismatch or converting.
Am i missing something?
Most of the project setup was using feathers-cli.
> feathers g authentication
? What authentication providers do you want to use? Other PassportJS strategies not in this list can still be configured manually. Username + Password (Local)
? What is the name of the user (entity) service? users
force config/default.json
create src/authentication.js
force src/app.js// users.models.js
//
const {Model} = require('objection');
/* eslint-disable no-console */
// messages-model.js - A Objection.js/KnexJS
//
// See https://vincit.github.io/objection.js and http://knexjs.org/
// for more of what you can do here.
class Users extends Model {
static get tableName() {
return 'users';
}
static get jsonSchema() {
return {
type: 'object',
required: [
'email', 'password'
],
properties: {
id: {
type: 'integer'
},
email: {
type: 'string',
pattern: String.raw `^[^@\s]+@[^@\s]+\.[^@\s]+$`
},
password: {
type: 'string',
'minLength': 4
}
}
};
}
}
module.exports = function(app) {
const db = app.get('knexClient');
const tableName = 'users';
db.schema.hasTable(tableName).then(exists => {
if (!exists) {
db.schema.createTable(tableName, table => {
table.increments();
table.string('email').unique().notNullable();
table.string('password', 100).notNullable();
table.timestamps(true, true);
}).then(() => console.log(`Created ${tableName} table`)).catch(e => console.error(`Error creating ${tableName} table`, e));
}
});
return Users
};// users.services.js
//
// Initializes the `users` service on path `/users`
const objectionService = require('feathers-objection');
const createModel = require('../../models/users.model');
const hooks = require('./users.hooks');
module.exports = function(app) {
const Model = createModel(app);
const paginate = app.get('paginate');
const options = {
model: Model,
id: 'id',
paginate
};
// Initialize our service with any options it requires
app.use('/users', objectionService(options));
// Get our initialized service so that we can register hooks and filters
const service = app.service('users');
service.hooks(hooks);
};It took me a bit of time to get it working properly with this setup. Feathers out of the box is pretty intense when not using NeDB (which apparently makes everything instantly work, had to manually setup a lot of other stuff).
In the app I tried
app.service('authentication')
.create({strategy: 'local', email: '', password: ''})
.then(console.log);Which processed just fine. Which leaves me to believe possibly I have it setup a bit incorrecting using objection to find my query. Something like app.service('users').get(1).then(console.log); works properly.
Getting next error while Typescript compile:
[ERROR] 18:56:46 ⨯ Unable to compile TypeScript: src/services/users/users.class.ts(8,47): error TS2304: Cannot find name 'Application'.
Tested on 4.2.3 version.
I'm using $select to include a custom sql function in my comments query.
// Default $select in before hook.
context.params.query["$select"] = context.params.query["$select"] || [
'comments.*',
raw('comment_hotness(comments.score, comments.created_at) as hotness')
]
Which works well.
A problem I'm having is when I try to filter based on a relationship value, I get the ambiguous column name error.
// Default $joinRelation in before hook.
context.params.query["$joinRelation"] = context.params.query["$joinRelation"] || "[tags]";
A test query.
http://xxx/comments?tags.tag=abc
Error that's returned.
Error in 'comments' service method 'find' Forbidden: select distinct "comments".*, comment_hotness(comments.score, comments.created_at) as hotness, "id", "comments".* from "comments" inner join "comment_tags" as "tags" on "tags"."comment_id" = "comments"."id" where "tags"."tag" = $1 limit $2 - column reference "id" is ambiguous
In feathers-object index.js, adding the table's name to line 240 seems to fix it.
if (filters.$select) {
q = q.select(...filters.$select.concat(`${this.Model.tableName}.${this.id}`));
} // $eager for Objection eager queries
The new sql query.
select distinct "comments".*, comment_hotness(comments.score, comments.created_at) as hotness, "comments"."id", "comments".* from "comments" inner join "comment_tags" as "tags" on "tags"."comment_id" = "comments"."id"
The query then executes as expected.
Good evening, I'm trying to run a simple query using this library.
Good evening, I'm trying to run a simple query using this library
SELECT * FROM modules m LEFT JOIN students_modules sm ON (sm.idModule = m.id AND sm.idStudent = 1)
This is my modules.model.js
class modules extends Model {
static get tableName() {
return 'modules';
}
static get jsonSchema() {
return {
type: 'object',
required: ['id', 'description'],
properties: {
idPreModule: { type: 'integer' },
title: { type: 'string' },
description: { type: 'string' }
}
};
}
static get relationMappings() {
const Student = require('./student.model')();
return {
students: {
relation: Model.ManyToManyRelation,
modelClass: Student,
join: {
from: 'modules.id',
through: {
from: 'students_modules.idModule',
to: 'students_modules.idStudent'
},
to: 'student.idUtente'
}
}
};
}
}
Thanks for your help
The join relation in the following query is ignored. I'd assume that the same is true for $and.
{
$or:[
{ $joinRelation: 'child(exists)' },
{ success: true }
]
}
Hey @mcchrish,
I'd be interested in maintaining this project if you're interested as we're actively using this in a project.
With objection, you can refer to jsonb columns using a FieldExpression. You can wrap those in a ref to include them in any part of the query.
If would be great if the feathers adapter supported this. Especially with $sort, $pick, $select, etc
In _find, the $eager query parameter is added to count query.
error: error: select count("id") as "total" from "users" where "$eager" = $1 - column "$eager" does not exist
at Connection.parseE (C:\Users\xyz\feathers\backend\node_modules\pg\lib\connection.js:546:11)
at Connection.parseMessage (C:\Users\xyz\feathers\backend\node_modules\pg\lib\connection.js:371:19)
at Socket. (C:\Users\xyz\feathers\backend\node_modules\pg\lib\connection.js:114:22)
at emitOne (events.js:96:13)
at Socket.emit (events.js:188:7)
at readableAddChunk (_stream_readable.js:176:18)
at Socket.Readable.push (_stream_readable.js:134:10)
at TCP.onread (net.js:547:20)
I think that presence of query.$eager should be checked and removed if it exists?
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
Django
The Web framework for perfectionists with deadlines.
Laravel
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
Microsoft
Open source projects and samples from Microsoft.
Google
Google ❤️ Open Source for everyone.
Alibaba
Alibaba Open Source for everyone
D3
Data-Driven Documents codes.
Tencent
China tencent open source team.