Improvements to Query Builder and Lucid

assertchris commented 6 years ago

Brief history

Lucid is fantastic, but it still has a few rough edges that can trouble folks new to the framework. One of the big drivers or adoption is people coming from other languages and finding AdonisJS in the void of well-structured JS frameworks. Yet, migrating from those similar frameworks and AR implementations is jarring because of how queries are sometimes formed and often-times deserialised...

What problem does it solve?

We could look at making queries easier to write, with:

Starting queries directly on the modal class: Customer.where(...) vs Customer.query().where(...)
Adding more variations of SQL syntax to the query builder: Customer.where(...).orWhere(...) vs Customer.whereRaw(...)
Adding dynamic wheres: Customer.whereName(...) vs Customer.where('name', ...)

We could also look at making extraction easier, with:

Returning the same data type no matter how the data is fetched: Customer.find(...) === Customer.query().where(...).first() === Customer.all().first(). This requires a kind of collection data structure which maps closely to the query builder resolution methods (like fetch and all and first).
Making serialisers less of a developer-facing thing. They should still be there, but they should require a different kind of exposure than just being the default for a fetch call.

Proposal

Spend some time thinking about improvements to Query Builder and Lucid syntax and semantics.

Relevant links

https://laravel.com/docs/5.6/eloquent http://fractal.thephpleague.com https://laravel.com/docs/5.6/eloquent-serialization

Are you willing to work on it?

The planning, sure. The implementation may be beyond my abilities...

diego3g commented 6 years ago

One good think from eloquent that i miss on Adonis is passing objects to where to automatically create grouped wheres:

const Users = await User
  .query()
  .where({ role: 'admin', another: 'field' })
  .orWhere({ field: 'value', justonemore: 'another' })
  .fetch()

Also, i miss an updateOrCreate method to perform an INSERT OR UPDATE at database.

Another feature could be adding the possibility to pass objects to findBy:

const User = await User.findBy({ email: 'example@test.com', 'role': 'admin' });

The last, but not less important is adding one field to the model that holds if the entity was recently created or not, i mean, in cases where you use findOrCreate, you need to know if it was created or just selected from database.

ghost commented 6 years ago

A nice feature to the queryBuilder it wuld be support for JSON functions in MySQL or MariaDB; for example Laravel supports MySQL json operations

in mariaDB, here some examples about it are:

Specific value

SELECT id, name, JSON_VALUE(attributes, '$.data.salary') AS info FROM users;

or

an entire object

SELECT id. name, JSON_QUERY(attributes, '$.data') AS info FROM users;

or

a more beautiful object

SELECT id, name, JSON_DETAILED(attributes, '$data') AS info FROM users;

Laravel support

https://laravel.com/docs/5.6/queries#updating-json-columns

MariaDB JSON functions

https://mariadb.com/kb/en/library/json-functions/

In this way for example, only is a proposal

const posts = await Post.query().where('idPost', 1).update('data.age', 33)

PazzaVlad commented 6 years ago

Yeah, JSON function would be nice. SQLite also support it.

https://www.sqlite.org/json1.html

mourad-ghafiri commented 6 years ago

The whole world converges to NoSQL solutions and MongoDB starts to support transactions... improving Lucid to support Mongodb and GraphQL will help a lot.

there are third party libs for adonis mongodb that are great... but something official will be even better.

Just a suggestion :)

AdonisJs is awesome <3

RomainLanz commented 6 years ago

The whole world converges to NoSQL solutions and MongoDB

That's not true. Only devs that follow the trend without learning are using NoSQL as a primary database solution.

Also, nothing keeps you from using MongoDB in your Adonis application from day one.

Further lectures...

Concerning GraphQL, the next major version of Adonis will provide a first class support to serve a GraphQL endpoint.

I have done some works here but I'm not satisfied with the way resolvers are written. So going to improve that and provide an easy way to avoid common GraphQL issues, like the N+1 problem.

mourad-ghafiri commented 6 years ago

@RomainLanz pros and cons are facts... I have used both databases Paradigms... should say that both of them are great solutions... so there is no logical reason to ignore NoSQL over SQL DBs :)

The Trend is what create IT economy :) and a Tool is just a Tool if it is not following the trend it will be useless.

AdonisJs accepts TypeScript (non-standard) to extend the Market Segment :)

Anyway that an endless debate. thank you for your effort on adonisJs :)

PazzaVlad commented 6 years ago

Agree with @RomainLanz. Plus almost all modern SQL DB has first class json support, that simplify working with nested data (which is main selling point for noSQL).

adonisjs / rfcs