search

Vincit / objection.js

An SQL-friendly ORM for Node.js
https://vincit.github.io/objection.js
MIT License
7.25k stars 637 forks source link

Poll - What would you like to see in the future versions of objection? #1302

Closed koskimas closed 2 years ago

koskimas commented 5 years ago

I'm starting to plan the 2.0 release and I'd like to hear the community's opinions what we should add to objection in 2.0 or later down the road.

I'd like to reiterate here that the goal of objection was never to become Eloquent, Django's ORM, Active Record or any other known ORM. Objection's goal is said pretty well at the top of the github README. Objection is a "query builder on steroids" that never get's in the way of SQL, but still attempts to provide tools for working with repetitive stuff and especially relations.

That said, there are many things those ORMs do better than objection and we should try to bring in the features that suit objection's design goal.

SO, I'd like you to post here what you miss from any other tool you've worked with and think objection should really have. Vote the features using :+1: and :-1:

Couple of things 2.0 will have:

https://github.com/Vincit/objection.js/projects/1

tonisostrat commented 5 years ago

I concede the following is quite niche so feel free to shoot it down :)

One thing I've always missed (and due to that I've implemented myself) is functionality similar to JPA's @Embeddable/@Embedded for logical grouping of related fields in a single table.

If this sounds like something you might want to include in objection I would be more than happy to help you brainstorm and figure out the constraints/logic.

koskimas commented 5 years ago

@tonisostrat I'll try not to shoot down anything or add my own opinions here too early to not affect the discussion.

heisian commented 5 years ago

This may be more on the knex-level, but being able to selectively run seeds would be nice.

Also maybe seeing tools like https://github.com/Vincit/knex-db-manager, https://github.com/Vincit/objection-db-errors, and https://github.com/Vincit/objection-find become a part of the core or have better visibility and code examples in the docs, to help folks write less boiler plate initially.

Perhaps those features could be toggled via feature flags by the developer(s).

zemccartney commented 5 years ago

+1 to @heisian 's note for at least https://github.com/Vincit/objection-db-errors (only b/c I'm not familiar with the other 2; thanks for sharing those links, good weekend reading :) ). My coworkers and I have started to default to pulling in that plugin on our projects, really appreciate the extra support on error handling. No opinion / suggestion (helpful or otherwise) on where to go with that, but lowering the barrier to entry for that utility somehow I think would be a really solid addition.

(ps Objection is ill! Thanks for all your work here (and for polling for feedback on v2; looking forward to it))

GreatSchool2 commented 5 years ago

Would a @column('columnName') property decorator be appropriate? You'd use it to decorate a property of a model and the default (or base class) columnNameMapper would consult this property when performing the mapping.

e.g.

class Singer extends Model {
    static table = 'singer';

    @column('pseudonym')
    name: string;
}

would yield code models with a name property whose value would be used to populate the pseudonym column when a Singer instance is insert()ed, for example.

fer22f commented 5 years ago

Currently, I work with custom up, down methods in Model classes that I query for all my models to generate a database from scratch, and maintaining the ordering of what should be created first by hand via a simple numbering system.

But I don't ask to implement the management of that at all; I would like however, a way to generate the ordering from relationship graphs, for example.

I also agree with the naming, and would like to see custom behavior for joinRelation also (filters like we have in eager).

venelin-mihaylov commented 5 years ago

I would like:

  1. An option to mix WhereInEagerAlgorithm and JoinEagerAlgorithm in a single query. Thank you for your efforts
maxnordlund commented 5 years ago

Support for for await (...) loops would be nice. Especially if it's backed by cursors. This could fallback to limit/offset, even though that doesn't have the same performance (but still ~constant memory pressure).

for await (let person of People.query()) {
  ...
}

You can kinda get this by using knex stream interface, as Readable streams support asyncIterator since v10.0.0. Though when I did this I had to use People.query().build().stream() which feels a bit clunky.

tonisostrat commented 5 years ago

Currently, I work with custom up, down methods in Model classes that I query for all my models to generate a database from scratch, and maintaining the ordering of what should be created first by hand via a simple numbering system.

But I don't ask to implement the management of that at all; I would like however, a way to generate the ordering from relationship graphs, for example.

I also agree with the naming, and would like to see custom behavior for joinRelation also (filters like we have in eager).

I don't understand.. this library is based on knex which already handles migrations/changelogs, why don't you utilize it?

fer22f commented 5 years ago

Currently, I work with custom up, down methods in Model classes that I query for all my models to generate a database from scratch, and maintaining the ordering of what should be created first by hand via a simple numbering system. But I don't ask to implement the management of that at all; I would like however, a way to generate the ordering from relationship graphs, for example. I also agree with the naming, and would like to see custom behavior for joinRelation also (filters like we have in eager).

I don't understand.. this library is based on knex which already handles migrations/changelogs, why don't you utilize it?

In a way, you're right. But I don't like how migrations separate the creation of tables from the Model object itself. It's perhaps a bit more cumbersome, but it's sure convenient!

Regardless, I should probably move to the knex migration way, I think the support for MS SQL is improved now, and it's way better for my own sanity. Thanks!

devinivy commented 5 years ago

I have a handful of thoughts. Given that objection is overall fairly stable and feature-complete, I think the focus should be on using a breaking change (i.e. v2.0) to tighten things up: focus on naming, making any notable alterations to queries, fixing API warts, work on related tooling. You know, things that are worth considering only when there isn't a ton of debt to pay off. It's my impression that there isn't!

I'll just list some ideas, and not so much propose specific solutions, although I'd be happy to talk about solutions if we want to peel-off some additional github issues.

koskimas commented 5 years ago

@devinivy Thanks! You have alot of great ideas there.

Passing arguments to modifiers

This will be fixed at least for the modify function's part. I'd like allow arguments for modifiers in relation expressions too. Once modify is fixed for named modifiers, you can at least do this:

class Pet extends Model {
  static get modifiers() {
    return {
      onlySpecies: (query, species) => query.where('species', species)
    }
  }
}

Person
  .query()
  .eager('[pets, children.pets(onlyDogs)]', {
    onlyDogs: query => query.modify('onlySpecies', 'dogs')
  })

That's still pretty verbose, but it allows you to "bind" arguments to existing modifiers. What do you think? Could that be enough? Another option would be a mechanism like this:

Person
  .query()
  .eager('[pets, children.pets(onlyDogs)]')
  .bindModifier('onlySpecies', 'onlyDogs', 'dogs')

but is that really better? It intruduces yet another concept.

We could also add helpers like these:

Person
  .query()
  .eager('[pets, children.pets(onlyDogs)]', {
    onlyDogs: bindModifier('onlySpecies', 'dogs')
  })

@devinivy Do you have any other ideas for this?

I've also thought about making modifiers query-wide, so that they could be used with joinRelation etc. So instead of this

Person
  .query()
  .eager('[pets, children.pets(onlyDogs)]', {
    onlyDogs: query => query.modify('onlySpecies', 'dogs')
  })

you'd say this:

Person
  .query()
  .eager('[pets, children.pets(onlyDogs)]'),
  .modifiers({
    onlyDogs: query => query.modify('onlySpecies', 'dogs')
  })

Relation modifiers coupled to eager algorithm

This is a tricky one. I also hate that this happens. That's one of the reasons why I'll deprecate the defaultEagerAlgorithm and eagerAlgorithm() proerties and methods. You need to explicitly use withGraphFetched or withGraphJoined to make it clearer that you are using a completely different algorithm.

Fixing this would again require objection to duplicate the knex API which I've been trying to avoid. We would need to wrap all where*, groupBy, join* etc. methods, parse the inputs and basically build an AST tree to be able to figure out what the user means when he/she uses column name in a query. Is it from a joined table? Is it from a parent query in a subquery situation? The MVP solution would be to wrap all where methods and automatically add ${tableRefFor(modelClass)}.column for each column that doesn't have an explicit table, but that would already require objection to duplicate and test the where API of knex.

If you have any good ideas, I'm all ears.

One thing we could do is add a Model.ref function that makes it cleaner to use tableRefFor. Model actually already has that, but it's not public yet. It allows you to do this:

class Person extends Model {
  static modifiers = {
    boys: query => {
      const ref = Person.ref
      query.where(ref('gender'), 'male').where(ref('age'), '<', 18)
    }
  }
}

Conflict between jsonb and join alias separator

This is another complete design flaw I'll be addressing. We can use -> as a separator for either the json references or the joinEager aliases. Which one would you prefer?

Consider standardizing validators to implement tableMetadata()

The Validator API is bad. This is a good idea, but overriding validators doesn't seem to be that common, so I'll probably leave this for later (3.0?) unless you'd like to take a stab at this problem @devinivy? You guys have probably written the most comprehensive custom validator in schwifty.

Make relation and unrelation more ergonomic

// Fetch
await Person.query().findById(1)
    .relatedQuery('pets').where('name', 'Fluffy');

// Unrelate
await Person.query().findById(1)
    .relatedQuery('pets').findById(42)
    .unrelate();

I agree with the title, but not with either of these examples. They would be confusing. The .relatedQuery('pets') in those calls completely changes the query. a where call before that affects the parent query, while where after it affects the "child" query. I'd rather just make creating model instances from just a relation property (primary key most of the time) easier. Something like this:

await Person.fromId(1).$relatedQuery('pets').where('name', 'Fluffy');
await Person.fromId(1).$relatedQuery('pets').findById(42).unrelate()

Consider folding objection-db-errors into core

Will be done. This was also brought up by @heisian

Focus on tooling and plugins

This is pretty vague. I'd like to see more plugins and 3rd party tools, but I don't know how to help there. The Validator interface is one thing, but I don't think that is the biggest limiting factor right now.

phormio commented 5 years ago

It's only a small feature, but how about a method called $loadRelatedUnlessLoaded, which is like Model#$loadRelated but doesn't load any data which is already in the object.

To see why this is useful, consider the following function.

async function f(user) {
  const u = user.$clone();
  await u.$loadRelatedUnlessLoaded('favouriteBook.author');
  // Do something with u.favouriteBook.author
}

I want to be able to pass a User object to f without having to care about which relations have been loaded. But if I call $loadRelated in f, then the application is doing unnecessary work if the data has already been loaded.

$loadRelatedUnlessLoaded may not be a good name; feel free to think of a better one.

koskimas commented 5 years ago

@phormio That's a good idea :+1: That has been requested before.

$loadRelated will be replaced by $fetchGraph in 2.0 (the old name will remain at least until 3.0). $fetchGraph will take an options object as the second argument. We can add an option for this. Something like

await u.$fetchGraph('favoriteBook.author', { reloadExisting: false })
devinivy commented 5 years ago

@koskimas thanks for the thoughtful response 🙏

Passing arguments to modifiers

I like the example you gave, and I think that's totally fine! I like the bindModifiers() helper too :) Not a huge fan of the query-wide eager modifiers but also not so hard against it, especially if the current interface remains in existence.

Relation modifiers coupled to eager algorithm

I agree, I think this is a really hard problem to solve and it's not easy to justify the effort. Going down that path would generate a lot of questions that I don't have answers to. I would be afraid of making the generated queries harder to control and more opaque. Making tableRefFor() more friendly sounds like it's low-hanging fruit, and would be a nice improvement. I would be happy to contribute a section to the docs that addresses this—I get the sense that a lot of people run into this issue then avoid it rather than solve it since tableRefFor() doesn't have a lot of visibility in the docs.

Conflict between jsonb and join alias separator

Would -> make a better separator for jsonb or joinEager aliases? Hmm... that's hard for me to say. I struggle with the idea of using that as a separator since it's so close in meaning to postgres's own jsonb operator. If I had to choose one, I think I would suggest using it for jsonb. Is ~> too weird? I don't have a full understanding of knex's limitations here, but another option might be to see what it would take to loosen those limitations in knex.

Consider standardizing validators to implement tableMetadata()

I'll look into it!

Make relation and unrelation more ergonomic

Fair! I don't think these two options are mutually exclusive, and the example you gave is totally straightforward / would be a nice improvement. At the same time it does feel a little "off" to me that in order to build some queries I need to create a model instance with some fields filled-in. It works, but it doesn't seem super objection-y (obviously this is just a matter of opinion—whatever you decide is "objection-y" by definition!) . I still think an API similar to what I proposed would be in the spirit of objection as it relates to query laziness and composability. Does it seem more reasonable if it's re-written in steps?

/* select * from users where type = 'musician' */
const musicians = User.query().where('type', 'musician');

/* select * from animals where
     type = 'dog' and
     "ownerId" in (select "ownerId" from users where type = 'musician') */
const musiciansDogs = musicians.relatedQuery('pets').where('type', 'dog');

/* update animals set "ownerId" = null where
     type = 'dog' and
     "ownerId" in (select "ownerId" from users where type = 'musician') */
const unrelateMusiciansFromDogs = musiciansDogs.unrelate();

// An alternative idea, just for fun.

const musicians = User.query().where('type', 'musician');
const musiciansDogs = User.relatedQuery('pets').per(musicians).where('type', 'dog');
const unrelateMusiciansFromDogs = musiciansDogs.unrelate();

Consider folding objection-db-errors into core

👍

Focus on tooling and plugins

Agree I was pretty vague there. I'm mostly trying to think of things that could fragment efforts to write generic tooling for objection, so that they can be squashed proactively in v2. Maybe there's not a lot in the way!

chalcedonyt commented 5 years ago

It's only a small feature, but how about a method called $loadRelatedUnlessLoaded, which is like Model#$loadRelated but doesn't load any data which is already in the object.

To see why this is useful, consider the following function.

async function f(user) {
  const u = user.$clone();
  await u.$loadRelatedUnlessLoaded('favouriteBook.author');
  // Do something with u.favouriteBook.author
}

I want to be able to pass a User object to f without having to care about which relations have been loaded. But if I call $loadRelated in f, then the application is doing unnecessary work if the data has already been loaded.

$loadRelatedUnlessLoaded may not be a good name; feel free to think of a better one.

This would be such a useful feature to have and reduce many unneeded queries!

koskimas commented 5 years ago

@caiquecastro That's a good idea. @phormio Could you create a separate issue about that feature? That's something we can add after 2.0 since it's not breaking.

phormio commented 5 years ago

@koskimas, I was actually quite happy with your own suggestion. However, I've opened a new issue asking for Model#$loadRelatedUnlessLoaded.

vladshcherbin commented 5 years ago

A small list I can think about to upvote some suggestions:

This is probably all I can think about, objection gives a joy using database and I'll recommend using it instead of other orms any day. Thank you for it ❤️

vladshcherbin commented 5 years ago

What about setting useLimitInFirst to true by default ? Docs say: Defaults to false for legacy reasons.

Any downsides/cons? 🤔

koskimas commented 5 years ago

@vladshcherbin That's a good idea! I'll add that to the 2.0 TODO list.

hirikarate commented 5 years ago

I am expecting model instance's ability to detect changes, and provides a save() method which internally calls patch() to write changed properties to database.

I have been using ObjectionJS for Anemic model and it's going well. Now I am implementing Domain-driven model following "Backed By a State Object" pattern suggested by Vaughn Vernon - author of popular DDD books. I am too lazy to switch to Sequelize ORM in one microservice while the rest are still using Objection.

koskimas commented 5 years ago

@hirikarate Sorry, but that will never be added to objection. If you want an ORM like that, objection is probably a very bad match for you.

AaronNGray commented 5 years ago

Hi, I am going to need the following for a project :- Objection / knex / PostgreSQL support for :-

That's my wish list ;)

dustinnewman commented 5 years ago

Remove Bluebird dependency in favor of native Promise! If you check the composition of objection, Bluebird is not a trivial contributor! https://bundlephobia.com/result?p=objection@1.6.9

AaronNGray commented 5 years ago

Moving to JavaScript native Promises is a must !

koskimas commented 5 years ago

@AaronNGray @dustinnewman98 Bluebird is already removed in the v2.0 branch and so is lodash.

leeallen337 commented 5 years ago

  1. I've been using Objection on small personal projects lately and it's been amazing. One thing I've looked for in the documentation and issues but I haven't been able to tell if it's possible or not is to pass a transaction to a hook?

    I use Sequelize daily at work (I personally enjoy Objection more 😉) and I've had a few cases where I created a transaction for doing an insert, I also had an afterCreate hook on the model that did some async action, if the async action in the afterCreate threw an error it would roll back the creation. This page on hooks under the "A Note About Transactions" has some of the relevant documentation.

    After looking at the Objection documentation I understand that I could make some custom query method and do the transaction and all the async stuff in there so that's an option too.

  2. Is there a way to turn off hooks? For example in Sequelize you can pass a { hooks: false } to not trigger the hooks attached to a model?

I understand that the goal of Objection is to be small and lightweight and not be everything to everyone. I also know that these questions are kind of small/niche so I completely get if these aren't on the roadmap for v2 or won't ever be on the roadmap. I thought I would ask just to see though. Thanks!

mattkahl commented 5 years ago

TL;DR; Maybe a way to generate jsonSchema from the state of a database table?

I've been using Objection for a couple of months now and have largely been pretty happy with it. My primary previous ORM experience has been with the Django ORM (written in Python).

One thing I've noticed that can be tough in a team setting is having a quick way to see all the properties of a Model. In something like Django, the Model is defined first (akin to Objection's optional jsonSchema) and then the migrations are derived from that; so you always have an up-to-date reference for what is available on a model. But with Objection, jsonSchema is optional and not intrinsically tied to the underlying data store, so if you don't try and keep it up-to-date (or don't define it at all) it's very hard to quickly tell what is available for a model without perusing every migration that involved the underlying database table.

I don't really know any options to resolve this other than "try to keep jsonSchema maintained for every model". I'd love to hear suggestions from others if there are any.

koskimas commented 5 years ago

@leeallen337 The transaction is already passed to the hooks as the transaction property of the context argument.

capaj commented 4 years ago

Postgre JSONB operators support. Like jsonb_set and such. Although this might be better addressed at knex level.

Better TS support for related queries when using the https://vincit.github.io/objection.js/api/types/#relationexpression-object-notation so that we don't have to always cast to the appropriate type when using TS strict mode.

elhigu commented 4 years ago

@capaj

Postgre JSONB operators support. Like jsonb_set and such. Although this might be better addressed at knex level.

jsonb_set is already handled by .update and FieldExpression or what kind of use-case you were thinking of? Last example in https://vincit.github.io/objection.js/api/query-builder/mutate-methods.html#update is actually using jsonb_set to update value inside json column and with FieldExpression you can refer / cast any attribute inside jsonb column.

capaj commented 4 years ago

@elhigu oh wow! I had no idea objection has this supported already! That is awesome.

sfabriece commented 4 years ago

@koskimas filtering relationJoins. Something like how knex joins do it.


knex.select('*').from('users').join('accounts', function() {
  this.on('accounts.id', '=', 'users.account_id').orOn('accounts.owner_id', '=', 'users.id')
})
Outputs:
select * from "users" inner join "accounts" on "accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."
johncmunson commented 4 years ago

I'd like to see SomeModel.query().insert() support bulk insert for DBs other than Postgres. Objection is built on top of knex, and by looking at one of my knex seed files it's very apparent that knex can handle bulk insert for other databases...

// I'm using SQLite, and knex handles this bulk insert just fine
exports.seed = knex => {
  return knex('todo').insert([
    {
      title: 'Pickup dry cleaning',
      complete: false,
      note: 'They close at 7pm',
      due_date: '2019-11-10 08:30:00',
      priority: 'low',
      category_id: 1
    },
    {
      title: 'Create meeting agenda',
      complete: false,
      note: 'See Jeff\'s email from 8/15',
      due_date: null,
      priority: 'medium',
      category_id: 2
    },
    {
      title: 'Finish math homework',
      complete: false,
      note: 'Only the even problems',
      due_date: '2019-12-15 10:15:00',
      priority: 'high',
      category_id: 3
    }
  ])
}
koskimas commented 4 years ago

@johncmunson There are a number of issues about that. postgres is the only db that returns the identifiers of all inserted rows (a requirement of objection) in case of a bulk insert. On all other databases we would need to run multiple separate inserts under the hood. The insert method should be kept as an equivalent of the SQL operation and it should not start multiple queries.

If we implemented bulk insert inside the insert method, for example the following example would fail:

const [user1, user2] = await User.query().insert(users)
// This would throw since `user2` doesn't have an id.
await user2.$loadRelated('roles')

You can already use insertGraph to do bulk inserts, but remember to use allowInsert if you insert user provided data directly.

We could add a separate insertArray method or something.

You can always do this (which is what insertGraph and inserArray would do)

await Promise.all([
    {
      title: 'Pickup dry cleaning',
      complete: false,
      note: 'They close at 7pm',
      due_date: '2019-11-10 08:30:00',
      priority: 'low',
      category_id: 1
    },
    {
      title: 'Create meeting agenda',
      complete: false,
      note: 'See Jeff\'s email from 8/15',
      due_date: null,
      priority: 'medium',
      category_id: 2
    },
    {
      title: 'Finish math homework',
      complete: false,
      note: 'Only the even problems',
      due_date: '2019-12-15 10:15:00',
      priority: 'high',
      category_id: 3
    }
  ].map(it => Todo.query().insert(it))
)
johncmunson commented 4 years ago

Gotcha, thanks for the great explanation and alternative solutions. To go along with that, I'm assuming that similar logic as described above is also why objection provides .delete() and .deleteById(), but .patch() and .update() do not have corresponding ById() methods? (although there does exist .patchAndFetchById() and .updateAndFetchById())

koskimas commented 4 years ago

patchById(id, obj) and updateById(id, obj) are missing because i think findById(id).patch(obj) is cleaner. I try to avoid methods that take multiple arguments. updateAndFetchById(id, obj) exists because it cannot be replaced by a single query. deleteById is also a bit redundant, but that's already there.

koskimas commented 4 years ago

Sorry, but I'm deleting comments that are not feature requests to keep this issue readable. Feel free to open separate issues.

fer22f commented 4 years ago

I have a few things now:

Why? Because drivers suck at this. Tedious/MSSQL doesn't have a solution for this. Postgres has a parsing solution, but no encoding solution. SQLite has nothing. Dealing with dates and decimal values is error-prone without some support for this. Typeorm has a transformer option, but supports poorly complex types like js-joda's (anything with a recursive object, really).

koskimas commented 4 years ago

Have a way to marshall types, in and out: I want to use js-joda, and big.js. It would be nice to be able to transform values before going to the database driver, and when it comes back (perhaps it could even be advanced to the point of issuing automatic cast(as string) mappings, but that would too good).

You can already do this. See the hook docs.

Filter inside relations. Now this plagues all my codebase. This is the reason I use joinEager, because I can where child values, and if there aren't any child values present, the parent value doesn't show up, this is awesome, but is not very versatile.

There is no way to do this with the normal eager/withGraphFetched. It's impossible. You need to use joinEager/withGraphJoined or subqueries. In addition to the examples behind the link, you can pass those relation subqueries virtually everywhere.

More support for the data mapper pattern.

Not going to happen :smile: All the issues listed here can easily be solved in other "objectiony" ways. Objection is not any other ORM, nor does it try to mimic them just because they are popular.

Support for nested transactions with soft transactions (maybe?), support for multiple transactions running at the same time with a connection pool. Drivers suck at this too. But this can be more easily circumvented (by having slower software).

Already supported. Simply use knex's way to nesting transactions or trx.raw to create a savepoint. I use them all the time.

Answers by @elhigu (an active knex maintainer)

Postgres has a parsing solution, but no encoding solution.

I just wanted to point out that pg driver actually does have encoding solution for incoming data that can be overridden. Most simple way to use it is to add function .toPostgres to your object for serialization. https://github.com/brianc/node-postgres/blob/master/lib/utils.js#L45

Anyways it is true that there is no common way for all databases to do that and it could probably be implemented as a objection.js plugin since there are already lifecycle methods where mappers could be called.

What we have currently is much more like ActiveRecord

“The active record pattern is an approach to accessing data in a database. A database table or view is wrapped into a class. Thus, an object instance is tied to a single row in the table. After creation of an object, a new row is added to the table upon save. Any object loaded gets its information from the database. When an object is updated the corresponding row in the table is also updated. The wrapper class implements accessor methods or properties for each column in the table or view.”

Objection is a bit like active record in a sense that class presents a table and instance a row, but objection does not create or update object back to database that way (and it has been design decision from the start to prevent having too much magic going on behind the scenes with caching and tracking changes in model attributes).

Support for nested transactions with soft transactions (maybe?), support for multiple transactions running at the same time with a connection pool.

Knex supports nested transactions through single connection and they have been implemented by using save points. So objection supports them too. Multiple transactions in same pool work also just fine.

We could get rid of calling .query() on the model itself, and having methods at all in the Model. What we have currently is much more like ActiveRecord, and when we need to use a transaction, we need to query(trx) (and never forget it).

If you don't bind base Model to any knex instance, then you can use models in a multitenant way. In my own projects I never use global binding to single knex instance, but always give knex to .query(knex) when doing queries. That way you can also easily decide if you want to choose to send query to some read replicas instead of master. That also prevents the errors where you forget to pass trx parameter to queries which should go to specific transaction (if you await just .query() it will throw an error).

fer22f commented 4 years ago

Ok, neat, thanks for the response!

There is no way to do this with the normal eager/withGraphFetched. It's impossible. You need to use joinEager/withGraphJoined or subqueries. In addition to the examples behind the link, you can pass those relation subqueries virtually everywhere.

Yea, this is a bit sad, but these subqueries do seem very nice, I could rewrite my stuff to use EXISTS instead of joining, I think this could probably work.

You can already do this. See the hook docs.

Awesome. Maybe collect up a recipe for it? But I will definitely explore this a little more :)

Not going to happen 😄 All the issues listed here can easily be solved in other "objectiony" ways. Objection is not any other ORM, nor does it try to mimic them just because they are popular.

If you don't bind base Model to any knex instance, then you can use models in a multitenant way. In my own projects I never use global binding to single knex instance, but always give knex to .query(knex) when doing queries. That way you can also easily decide if you want to choose to send query to some read replicas instead of master. That also prevents the errors where you forget to pass trx parameter to queries which should go to specific transaction (if you await just .query() it will throw an error).

Ok, this seems like a good solution to the problem. You should always pass a knex object, it doesn't come with a query builder built-in.

I just wanted to point out that pg driver actually does have encoding solution for incoming data that can be overridden. Most simple way to use it is to add function .toPostgres to your object for serialization. https://github.com/brianc/node-postgres/blob/master/lib/utils.js#L45

My fault, I currently use this and mispoke. Although I think changing .prototype of library objects is not a good idea overall (but I do it), and doesn't work across modules (it's a bit of a weird global coupling).

fer22f commented 4 years ago

You can already do this. See the hook docs.

I can do this with models, but not with where, I suppose. But it's a good start. One example:

      .where(
        ref("updateInstant").castTo("date"),
        myLocalDate.toString()
      )
kedarmoghe commented 4 years ago
asergey87 commented 4 years ago

I would like to see in next version of Objection is conditinal clauses like in Laravel Eloquent https://laravel.com/docs/7.x/queries#conditional-clauses

This feature will avoid to use if/switch expressions in light queries, like:

const searchBooks = (year, searchTerm) => Books
.query()
.when(searchTerm.length, qb => qb.where('title', 'like', `%${searchTerm}%`)
.when(year !== undefined, qb => qb.where('year', '>=', year)
.orderBy('id', 'DESC')
.page(0, 100)
ameinhardt commented 4 years ago

@asergey87 : you could already use a custom QueryBuilder that adds conditions like:

export default class ExtendedQueryBuilder extends QueryBuilder {
  when(test, fnif, fnelse) {
    return test ? fnif(this) : (fnelse ? fnelse(this) : this);
  }

  whenClient(name, ...args) {
    const not = name[0] === '!';
    if(not) {
      name = name.slice(1);
    }
    return this.if(!!(not ^ (this.knex().client.config.client === name)), ...args);
  }
})
marziply commented 4 years ago

It would be nice to have the option to leverage the ES6 named export syntax, now that Node 14 supports ESM without the experimental flag.

This:

import { Model, QueryBuilder } from 'objection'

is much nicer than:

import objection from 'objection'

const { Model, QueryBuilder } = objection
vvo commented 4 years ago

Hey just chiming in, I saw some talks about unrelate and wanted to give this insight too:

Maybe there are challenges in making this more symmetrical, if so we could explain it in the doc if that's something we can't change.

Thanks!

vladshcherbin commented 4 years ago

@vvo I second this, would be nice if unrelate could work as relate in a more natural way, wondering if there are any limitation to this.

While working with m2m relations without graphs I'd be happy to have sync function where you pass relation ids and it would insert/delete current relations according to passed ones. However, this's a topic of a separate feature request.

koskimas commented 4 years ago

@vvo Unrelate follows the same logic as delete and it is in fact a delete in case of many to many relationships. You can do stuff like this:

user.$relatedQuery('teams').unrelate().where('teams.members', '<', 10)

or use whatever SQL query you want. You wouldn't be able to do that if we changed the API to take an id.

We could add a unrelateById method?

@vladshcherbin You can already do that using two queries.

await user.$relatedQuery('teams').unrelate()
await user.$relatedQuery('teams').relate(newIds)

but off course that will be slow if each user can have thousands of teams.

upsertGraph is exactly what you want.

await User.query().upsertGraph(
  {
    id: user.id,
    teams: newIds.map(id => ({ id })),
  },
  { relate: true, unrelate: true }
)