Orientdb: Type-safe way of building queries

[maseev]

Hi, guys!

As I'm aware of, OrientDB lacks the ability to build type-safe queries with Java API. Almost every query that I've encountered in the documentation looks something like this:

List<Animal> result = db.query(
  new OSQLSynchQuery<Animal>("select * from Animal where ID = 10 and name like 'G%'"));

This example was taken from Object API documentation. Maybe this approach is ok for simple queries, but it quickly becomes a pain in the ass when you try to build something more complex.
There are three projects, that come to my mind, which provide such API: Morphia, JOOQ, Slick.

Instead of creating a complex query from a bunch of strings and variables, it could be done via something similar to morphia:

datastore.createQuery(UserLocation.class)
    .field("x").lessThan(5)
    .field("y").greaterThan(4)
    .field("z").greaterThan(10);

or JOOQ:

create.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count())
      .from(AUTHOR)
      .join(BOOK).on(AUTHOR.ID.equal(BOOK.AUTHOR_ID))
      .where(BOOK.LANGUAGE.eq("DE"))
      .and(BOOK.PUBLISHED.gt(date("2008-01-01")))
      .groupBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
      .having(count().gt(5))
      .orderBy(AUTHOR.LAST_NAME.asc().nullsFirst())
      .limit(2)
      .offset(1)

I think, having an API like this would be a great feature to develop truly type-safe queries. Because of compile-time checks, it would be impossible to pass a query to OrientDB at runtime that wouldn't work.

WDYT?

[smolinari]

Check this out.

https://github.com/Syncleus/Ferma

It works over the Tinkerpop/ Gremlin interface though.

I think Luigi also worked some on a Spring Data implementation too.

And for sure, any other type of OGM/ Query Builder would be a great addition too.

Scott

[tglman]

Hi,

Actualy long time (before join orientdb), i was working on something similar in my free time, here it is: https://github.com/objectquery/objectquery-orientdb is outdated, but probably not too hard to update, any volonteer is welocme :), how it work is acutually on the website ;)

There was as well someone else doing something like that, there were a couple of mail about it on the ML, but i couldn't find the mail again, here is a similar project i found on github https://github.com/raymanrt/orientqb.

Bye

[maseev]

That's all great, but these projects aren't very active, and they aren't part of the official OrientDB API. Having an API integrated into OrientDB directly would be much better because it would get official support from the OrientDB team.

[smolinari]

AFAIK, there aren't many, if any, databases that have built in query builder APIs for a particular language. That kind of thing has always been a userland responsibility.

Scott

[maseev]

If I'm not mistaken, Morphia is developed by a team who developed MongoDB. My point here is it would be great if such a library would be supported by the OrientDB team in order to be always up to date with the latest updates (and bug fixes, of course) in OrientDB database.

[smolinari]

From what I can tell, only two of the contributors from Morphia are from MongoDB. So, I'd say they have a helping hand in working on the code, but it isn't quite like what you think in terms of the code support.

Also, MongoDB is a good bit bigger a company than ODB is and can afford to offer support on external packages like Morphia.

My personal opinion is, ODB should concentrate fully on making the core database the highest quality it can be, which includes good performance. They need to get that right, before they can think of supporting external packages. If they get the core database right, then they can start spreading their wings to support endeavors like you suggest for sure. In fact, I would bet money it would happen automatically, once quality is very good! 😄

Scott

[luigidellaquila]

hi @maseev @smolinari

The comments Scott made are absolutely correct, this issue is currently out of the scope of OrientDB project.
We prefer to concentrate on core features and leave these components to third party. In the future, if we have enough resources to also invest on this, probably we will directly contribute to external projects like Ferma.

Thanks

Luigi