Could you please release the latest changes ?
I would like to depend on last commit 5bd3280 but not released yet.
I tried to npm i --save js-data/js-data-sql@5bd32803d9b84021a945cb17ba55ca1fba17f347 but there are not yet had a build so dist/js-data-sql.js is not up to date.

It would be useful if you could register your own query operator.

var adapter = new DSSqlAdapter({
  client: 'mysql',
  connection: {
    user: 'root'
  },
  queryOperators: {
    'search': (query, field, value) => {
      return query.whereRaw(`MATCH(${query.raw(field)}) AGAINST(? IN BOOLEAN MODE)`, value)
    },
    '|search': (query, field, value) => {
      return query.orWhereRaw(`MATCH(${query.raw(field)}) AGAINST(? IN BOOLEAN MODE)`, value)
    }
  }
}

They should maybe be executed before the built in operators to allow overriding.
https://github.com/js-data/js-data-sql/blob/0.11.9/src/index.js#L350

I'm proposing to add the search query operator to support full-text search

Comment.filter({
  where: {
    content: {
      'search': 'some search terms'
    }
  }
});

you could also specify multiple columns using a comma separated field (similar to how near queries do for lat/long columns)

Comment.filter({
  where: {
    'title,content': {
      'search': 'some search terms'
    }
  }
});

Currently knex has an open issue since 2014 to (generically) support full-text searches. In the interim, we can do as other have and use whereRaw and syntax based on the database type.

MySQL

I currently have been using MySQL (Google Cloud SQL) and plan to implement this first as it's pretty straight forward. Add a fulltext to the column(s) designed and then use match(column_name) against ('seach terms')

alter table `comment` add fulltext(`content`);
select * from comment where match(`content`) against ('some word')

-- or multiple columns
alter table `comment` add fulltext(`title`, `content`);
select * from comment where match(`title`, `content`) against ('some word')

Need to determine is we should always use boolean full-text searches

• https://blog.marceloaltmann.com/en-using-the-mysql-fulltext-index-search-pt-utilizando-mysql-fulltext/

Postgres

While I haven't looked into it much yet, Postgres appears a little more complicated and need to better understand tsvector. The following articles look like a good start.

• http://rachbelaid.com/postgres-full-text-search-is-good-enough/
• https://blog.lateral.io/2015/05/full-text-search-in-milliseconds-with-postgresql/
• http://shisaa.jp/postset/postgresql-full-text-search-part-1.html

Currently we have support for AND LIKE but not OR LIKE. The pipe before like (|like) makes it hard to read (when using a san-serif font), but it's what we do for the other operators to indicate whether to AND or OR the expression.

Need to add a test with regards to PR #53

I think for many people a big barrier to adopting js-data on the server side is a lack of transactions support. This is actually one of the main reasons I couldn't use backbone or any other existing ORM, and ended up writing my own data layer... again (sound familiar?). I would really love to solve this once and for all with js-data.

The big problem with transactions on node is having to pass the transaction to everything explicitly because there are no thread-locals. Does anyone have any opinion on using continuation-local-storage for this?

Otherwise, I can't see how to implement this without either making JSData.DS transaction-aware or creating a new store instance (and all the resource objects) for every request.

Maybe creating multiple stores is actually necessary in order to avoid transactions sharing objects in memory that should be different. But then we've lost most of our caching. Immutable objects would probably help a lot here.

It would be good to know if anyone has any plans or thoughts on how this should work.

Thanks.

Currently there is no way to group WHERE conditionals with the query syntax. This becomes a problem when using OR, for example:

Customer.findAll({
  where: {
    'customer.name': {'|ilike': '%foo'},
    'customer.code': {'|ilike': '%foo'},
    'customer.reseller_id': {'==': 25},
  }
});

which produces:

select "customer".* from "customer"
where
  "customer"."name" ilike '%foo%'
  or "customer"."customer_code" ilike '%foo%'
  and "customer"."reseller_id" = 25

but I need to be able to generate the following query:

select "customer".* from "customer"
where
  ("customer"."name" ilike '%foo%' or "customer"."customer_code" ilike '%foo%')
  and "customer"."reseller_id" = 25

One thought I had was to support passing an array of objects to where, and each object in the array would be wrapped in parenthesis (using knex's grouped chain)

Customer.findAll({
  where: [{
    'customer.name': {'|ilike': '%foo'},
    'customer.code': {'|ilike': '%foo'}
  }, {
    'customer.reseller_id': {'==': 25},
  }]
});

@jmdobry thoughts?

It would be very useful if you could apply a join and filter across the join . For example, with the following resources I want to find all the races at a specific address:

exports.Race = db.store.defineResource({
  name: 'race',
  relations: {
    belongsTo: {
      address: {
        localField: 'address',
        localKey: 'address_id'
      }
    },
  }
});

exports.Address = db.store.defineResource({
  name: 'address',
  relations: {
    hasMany: {
      race: {
        localField: 'races',
        foreignKey: 'address_id'
      }
    }
  }
});

Executing sqlAdapter.findAll(models.Race, { "address.city": "New York" }); would produce:

select * from race
join address ON race.address_id = address.id AND address.city = 'New York'

The equivalent knex query would be:

knex.select('*').from('race').join('address', function() {
  this.on('address.id', '=', 'race.address_id').on('address.city', '=', knex.raw('?', ['New York']))
})

I'm currently using a custom query with subquery selects, and have the need to filter on these extra fields in the query (ratings_average and ratings_count in my example). For these to work, they must be applied using HAVING instead of WHERE as the filtering must occur after the subqueries have executed (from my understanding).

  ...
  let filterQuery = db.sql.filterQuery(models.Event, this.query, options);

  var ratingsAverage = db.knex.raw('(select avg(rating) from review where event_id = event.id) ratings_average');
  var ratingsCount = db.knex.raw('(select count(rating) from review where event_id = event.id) ratings_count');
  filterQuery.select(ratingsAverage, ratingsCount);

  if (this.query.having) {
    for (var field in this.query.having) {
      for (var op in this.query.having[field]) {
        filterQuery.having(field, op, this.query.having[field][op]);
      }
    }
  }

  let entities = yield db.sql.findAll(models.Event, filterQuery, options);

My hacked code works for all the basic operators, but I would implement the same logic as the where is currently (handling OR'ing, NULLs, etc.)

Since this would increase the query API further, and is mostly specific to js-data-sql, I wanted to discuss if this is something we want to add.

Related side note: I'd like to come up with a way to define the ratings_average and ratings_count on the resource (kind of like a computed property, but they are retrieved as part of the query and not from a individual entities properties.

Since the SQL adapter uses Knex, is it possible to use it for Oracle databases?

if (params instanceof Object.getPrototypeOf(this.query.client).QueryBuilder) {

should spell

if (params instanceof Object.getPrototypeOf(this.query.client).queryBuilder) {

Refer to knex source: client.js#L61 for reference.
node -v yields v4.6.0 and npm list | grep knex yields ├─┬ [email protected].
npm list | grep js-data yields

├── [email protected]
├─┬ [email protected] (git://github.com/peteut/js-data-sql.git#c94819ffdfa09d293a6acaeab6a2db496c7b1fac)

I was not able to run CI, therefore I could not prepare a pull request yet.

Needs documented on http://www.js-data.io/docs/query-syntax

Version 0.11.9 provided the ability to perform a WHERE EXISTS query when referencing a hasMany property, but currently if you try to query deeper, it's applying the JOIN to the main SELECT and not the subselect within the WHERE EXISTS

/api/v1/races?where={"events.event_category.name":{"==":"5k"}}

Current

select `race`.* from `race`
inner join `event_category` on `event`.`event_category_id` = `event_category`.`id`
where exists (
  select `event`.* from `event` where `event_category` = '5k' and `event`.`race_id`=`race`.`id`
)
 - ER_BAD_FIELD_ERROR: Unknown column 'event_category' in 'where clause'

Should produce

select `race`.* from `race`
where exists (
  select `event`.* from `event`
  inner join `event_category` on `event`.`event_category_id` = `event_category`.`id`
  where `event_category` = '5k' and `event`.`race_id`=`race`.`id`
)

It looks to be an issue with closure within processRelationField. I believe the query will need to be passed in explicitly

Empty strings are converted to 0, so a latitude/longitude of ""/"" is set to the equator/prime meridian which makes for some interesting distances returned.

A simple NULLIF(column, '') should work

Need to create some tests for this change, which requires some new non-standard tables/relations to test against

new DSSqlAdapter() causes a global leak. It tries to call deepMixIn(this.defaults, options) but this.defaults is undefined at this point.

deepMixIn should assert that the first argument is an object, instead of mistakenly assigning all the properties onto the global object.

Thanks.

Regarding to this documentation LIKEI operator should be supported

http://www.js-data.io/docs/query-syntax

but it is not - at least for DSSqlAdapter (PostgreSQL)

Error is : ERR [Error: Operator not found]

Using this issue to track this adapters support of js-data/js-data#242

Currently all relations loaded via with are done so using a subsequent select * from relationTable where id in (...). For hasOne and belongsTo relations, loading these relatoins should be possible and more performant by adding a left join to the original query for each of these relation types and distributing the columns to the primary entity and each relation property based on the table name prefix of the column.

This was requested at http://stackoverflow.com/questions/36358836/js-data-sql-dssqladapter-create-inner-join

Currently I am using the following koa route to pass the query string parameters down to js-data-sql

router.get('/races', function*() {
  var options = {};
  if (this.query.with) {
    options.with = Array.isArray(this.query.with) ? this.query.with : [this.query.with];
    delete this.query.with;
  }
  if (this.query.where) {
    this.query.where = JSON.parse(unescape(this.query.where));
  }
  let races = yield db.sql.findAll(models.Race, this.query, options);

  this.body = races;
});

When calling GET /races?limit=3&offset=5, it appears that knex handles limit being passed in as a string (converts it to an int) but not offset.

{ method: 'select',
  options: {},
  bindings: [ 3, '5' ],
  sql: 'select * from `race` limit ? offset ?' }

  Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''5'' at line 1

While I could parse/convert the value in the route, it seemed reasonable to have js-data-sql to handle this simple case (especially with the interplay between js-data-http and js-data-sql). I also took a look at having knex handle this, but I had trouble finding where limit was doing the string/int conversion and felt it was a better fit to have js-data-sql to handle it (because of js-data-http).

Expect an imminent PR for this ;)

So I've spent the good part of this evening trying to get familiar with js-data within my backend (Node.js) and how it handles relations. After a few problems on my side (I had 2 belongsTo objects under relations for defineResource to name one), I think I've stumbled across an issue:

Until I set cacheResponse: true in JSData.DS's configuration I am unable to get any of my relation properties (belongsTo / hasMany) to be populated. I tried populating using

...
const Race = store.defineResource({
  name: 'race',
  relations: {
    belongsTo: {
      address: {
        localField: 'address',
        localKey: 'address_id'
      }
    },
  }
});

const Address = store.defineResource({
  name: 'address',
  relations: {
    hasMany: {
      race: {
        localField: 'races',
        foreignKey: 'address_id'
      }
    }
  }
});
...

let address = yield new store.Address.create(fixtures.address.race1);
let raceData = fixtures.race.race1;
raceData.address_id = address.id;
let race = yield new store.Race.create(raceData);
expect(race.address).to.exist; // AssertionError

let race2 = yield store.Race.loadRelations(race, ['address']);
expect(race2.address).to.exist; // AssertionError

If I set cacheResponse: true, the assertions succeed. Due to the issues explained on JSData on the Server, it seems like enabling cacheResponse: true would not be advisable.

On a semi-related note, the docs under Configuration do not indicate some defaults being different when ran under Node (like the "JSData on the Server" page does). It would be helpful if this documentation indicated the different default values based on the environment. I also wasn't sure if items like findHasMany, findBelongsTo, etc were set to false by default on Node, or if this was just recommended per the docs.

It doesn't appear that js-data-sql supports any kind of "between" query, whether WHERE age >= 30 AND age < 33 or WHERE age BETWEEN 30 and 32 I was looking to add support for this, but wasn't sure if any of these were already supported by the store or other adapters.

  it('should filter between values', function* () {
    var user1 = yield adapter.create(User, {name: 'John', age: 30});
    var user2 = yield adapter.create(User, {name: 'Sean', age: 33});

    // option 1
    var users = yield adapter.findAll(User, {age: [{'>=': 30}, {'<': 33}]});

    // option 2
    var users = yield adapter.findAll(User, [{age: {'>=': 30}}, {age: {'<': 33}}]);

    // option 3
    var users = yield adapter.findAll(User, {age: {'between': [30, 32]}});

    assert.equal(users.length, 1);
    assert.equal(users[0].age, 30);
  });

js-data:2.9.0
js-data-sql: 0.11.17 (using sqlite3 db)

After upgrading knex all test which include findAll-Queries fail with the following error:

  1) lib/media-manager importAlbum can import an album:
     TypeError: Expecting a function in instanceof check, but got undefined
      at DSSqlAdapter.filterQuery (node_modules/js-data-sql/dist/js-data-sql.js:415:70)
      at DSSqlAdapter.findAll (node_modules/js-data-sql/dist/js-data-sql.js:336:20)
      at node_modules/js-data/dist/js-data-debug.js:5319:46

Code-Example:

const covers = yield Cover.findAll()

My proposal is to remove all db client libraries from package.json and require user to install the requested one, and move js-data and knex as dependencies. I suggest this for the following reasons:

• It's rather odd (and eats up space) to have all the db client libraries installed when performing npm install js-data-sql.
• I'm using the mysql2 driver instead of mysql which means I have 2 db client libraries installed for MySQL.
• Knex requires you to install your desired library
• NPM3 will no longer install peer dependencies by default (which is the reason for moving js-data and knex as true dependencies

$ npm install --save js-data js-data-sql
npm WARN peerDependencies The peer dependency mariasql@>=0.1.21 included from js-data-sql will no
npm WARN peerDependencies longer be automatically installed to fulfill the peerDependency 
npm WARN peerDependencies in npm 3+. Your application will need to depend on it explicitly.
npm WARN peerDependencies The peer dependency mysql@>=2.5.5 included from js-data-sql will no
npm WARN peerDependencies longer be automatically installed to fulfill the peerDependency 
npm WARN peerDependencies in npm 3+. Your application will need to depend on it explicitly.
npm WARN peerDependencies The peer dependency pg@>=4.3.0 included from js-data-sql will no
npm WARN peerDependencies longer be automatically installed to fulfill the peerDependency 
npm WARN peerDependencies in npm 3+. Your application will need to depend on it explicitly.
npm WARN peerDependencies The peer dependency sqlite3@>=3.0.5 included from js-data-sql will no
npm WARN peerDependencies longer be automatically installed to fulfill the peerDependency 
npm WARN peerDependencies in npm 3+. Your application will need to depend on it explicitly.

When a resource uses a primary key that is non numeric (knex converts the string into 0 for the promise's resolution).

To help with this, if the id is being passed in with the attrs, then use that instead of the promise's results.

Also, in postgresql you need to pass the id column's name in with the insert to have the ids array be populated.

Need to configure Circle CI to test not just MySQL but also Postgres (and possibly others)

https://circleci.com/docs/configuration#services

Would you be opposed if I converted all the tests to use co-mocha? I was looking to submitting some pull requests and thought the specs were a little difficult to follow with all the Promise flow and thought it would help their readability greatly.

For example, here is the current create.spec.js using mocha and Promise then/catch

describe('DSSqlAdapter#create', function () {
  it('should create a user in a sql db', function () {
    var id;
    return adapter.create(User, {name: 'John'}).then(function (user) {
      id = user.id;
      assert.equal(user.name, 'John');
      assert.isDefined(user.id);
      return adapter.find(User, user.id);
    })
      .then(function (user) {
        assert.equal(user.name, 'John');
        assert.isDefined(user.id);
        assert.equalObjects(user, {id: id, name: 'John', age: null, profileId: null});
        return adapter.destroy(User, user.id);
      })
      .then(function (user) {
        assert.isFalse(!!user);
        return adapter.find(User, id);
      })
      .then(function () {
        throw new Error('Should not have reached here!');
      })
      .catch(function (err) {
        assert.equal(err.message, 'Not Found!');
      });
  });
});

and the same test using co-mocha with yielding promises

describe('DSSqlAdapter#create', function () {
  it('should create a user in a sql db', function* () {
    var createUser = yield adapter.create(User, {name: 'John'});
    var id = createUser.id;
    assert.equal(createUser.name, 'John');
    assert.isDefined(createUser.id);

    var findUser = yield adapter.find(User, createUser.id);
    assert.equal(findUser.name, 'John');
    assert.isDefined(findUser.id);
    assert.equalObjects(findUser, {id: id, name: 'John', age: null, profileId: null});

    var destoryUser = yield adapter.destroy(User, findUser.id);
    assert.isFalse(!!destoryUser);

    try {
      var findUser2 = yield adapter.find(User, id);
      throw new Error('Should not have reached here!');
    } catch(err) {
      assert.equal(err.message, 'Not Found!');
    }
  });
});

I'll finish and submit the change as a PR, but didn't want to make this kind of change without your consent :).

As for the PRs I was thinking about tackling

• Implement #18 (but if you can knock it out quicker, that would be awesome)
• Consolidate the relation handling inside find() and findAll() (basically extract it into a processRelations()
• I'd like to find a way use MySQL's spatial queries but still get the benefits of js-data-sql's relations (primarily with and putting the nested objects together).
  • One idea I was thinking about having findAll detect if the second parameter (current params) is an instance of a knex query builder and use that query to process instead of the one returned from filterQuery(). For example: sqlAdapter.findAll(Race, query, { with: 'address' })
  • Another idea was to just export the new processRelations() and use it directly. For example: sqlAdapter.processRelations(Race, yield sqlAdapter.query(...), { with: 'address' }) although I haven't thought on this API very much as to what would work best.

Just logging some notes to think about at this point

Retrieve a subset of properties

• asked by aaike on Slack#i-need-help

var users = yield Event.findAll({...}, {
  with: [
    // load all comments, what we currently support
    'comments',

    // load only comments with category_id = 1 (shorthand for '==')
    {comments: {category_id: 1}},

    // load only comments with category_id > 1
    {comments: {category_id: {'>': 1}}}
  ]
});

Support for adding calculated/aggregate properties

• discussed in Slack#js-data-sql

var events = yield Event.findAll({...}, {
  with: [
    // add `review_count` property to each event
    {review_count: {'review.rating': 'count'}},

    // add `review_average` property to each event
    {review_average: {'review.rating': 'average'}},
  ]
});

I've been working on upgrading all of my js-data stuff to v3 and I've run into an issue with querying through relations. I'm using js-data/js-data-http (both v3) on the front end and js-data/js-data-sql (both v3) on my API. I have two mappers:

const User = db.dataHealth.store.defineMapper({
  name: 'user',
  tableName: 'user',
  relations: {
    belongsTo: {
      reseller: {
        localField: 'reseller',
        foreignKey: 'reseller_id'
      },
      customer: {
        localField: 'customer',
        foreignKey: 'customer_id'
      }
    },
    hasMany: {
      login: {
        localField: 'logins',
        foreignKey: 'user_id'
      }
    }
  }
});

And

const Customer = db.dataHealth.store.defineMapper({
  name: 'customer',
  tableName: 'customer',
  relations: {
    hasMany: {
      user: {
        localField: 'user',
        foreignKey: 'customer_id'
      },
      account: {
        localField: 'accounts',
        foreignKey: 'customer_id'
      }
    },
    belongsTo: {
      reseller: {
        localField: 'reseller',
        foreignKey: 'reseller_id'
      },
      customer_type: {
        localField: 'customer_type',
        foreignKey: 'customer_type_id',
      }
    }
  }
});

In js-data v2, I was able to do queries like the following:

User.findAll({where: {user.customer.name: 'foo'}});

In js-data v3, queries like this produce the error

missing FROM-clause entry for table "customer"

I really need this functionality. Any suggestions? Thanks!