sql/no-sql support in one codebase

[mikicho]

This is not exactly a feature request, more than an open question for discussion.

In the last few years, I see ORMs that tries to support both SQL and no-sql engines.
The problem is IMO is that no-sql and SQL are radically different from each other (syntax, capabilities, features, workflow) and expose them through one unified API ends in tragedy (look on TypeORM... :). It's like to try to add Redis support.
In addition to the technical difficulties for implementing such a hydra. this complexity also prevents/make-it-really-difficult for you to add some specific unique features that exist on one engine and not on the others, I hate it when the ORM blocks me to use some special features in Postgres.

The only orm-like library who can do it that I'm familiar with is Prisma because they are generating the code on-the-fly and basically sort of has a dedicated codebase for each engine.

Why do you put a lot of effort to support Mongo? what's the added value here?
Maybe it's worth separating this into 2 codebase which extends the same core?

[B4nan]

Why do you put a lot of effort to support Mongo? what's the added value here?

Well, first of all, why do you think I am putting "a lot of effort" in it? There were very few changes related to mongo in last year or so.

The reason is simple, MikroORM was originally mongo only library (v1). So its actually the opposite - I spend a lot of time in adding first class support for SQL. And it is much more painful because of the various dialects (as well as differences among various versions of the dialect).

Maybe it's worth separating this into 2 codebase which extends the same core?

This is already implemented in v4: #475
Having it as dedicated repository does not make sense to me.

It's like to try to add Redis support.

We can add even redis support, we can support any storage driver that is capable of handling arrays (for collections). I was actually thinking about making an example about creating custom driver that would be using redis for demonstration purposes. You could even use JSON file as a backend.

[darkbasic]

I hate it when the ORM blocks me to use some special features in Postgres

I hate it as well, but the problem here is not the ORM but the query builder itself. Do you know of any query builder which allows you to make the most out of Postgres? Even Knex.js only supports a fraction of its functionality. That's why I manually write SQL queries when I need to.

[mikicho]

Well, first of all, why do you think I am putting "a lot of effort" in it? There were very few changes related to mongo in last year or so.

I didn't know that... most of the time it's the sql -> no-sql path.. :)

This is already implemented in v4: #475

Oh, looks great!
Would this separation let "higher" package to override completely the core behavior? How flexible it is?

We can add even redis support

Sure! the question is how much it affects the maintenance efforts and the manpower the project needs to fix bugs AND continue to insert features, new platform capabilities, and so on... I think some projects (not only ORMs.. but ORMs are very vulnerable to this..) "died" because of (IMO) wrong "financial"(manpower..) behavior

@darkbasic
Unfortunately, didn't find one... I thought about Slonik, but it's lack of out-of-the-box features such soft delete...

[B4nan]

Would this separation let "higher" package to override completely the core behavior? How flexible it is?

SQL drivers have their own EntityManager and EntityRepository implementation (that extends the core ones), same for mongo. Currently this is used to allow having QB and knex helper methods only on SQL flavours (on the other side, mongo has a shorthand for making aggregations there). It should be quite flexible, but we need to try :] All SQL drivers have common ancestor, but technically we could also have postgres flavour of EntityManager if we need to.

In generally, extensibility is one of the big goals in the long run. Instead of implementing (and therefore maintaining) everything in MikroORM, I'd rather add more ways to extend it so others can create what they need. That is also the reason why from the beginning of v2 (where sql support was introduced), we have support for custom drivers.

Sure! the question is how much it affects the maintenance efforts and the manpower the project needs

We can support redis without making any changes in the ORM, right now. At least I believe so, as it the end, it will be just another NoSQL driver.

Also I forgot to reply to this bit:

this complexity also prevents/make-it-really-difficult for you to add some specific unique features that exist on one engine and not on the others, I hate it when the ORM blocks me to use some special features in Postgres.

While you say that the problem is SQL vs NoSQL, in fact the problem is more about the differences among SQL dialects. You want to get most of postgres, but MySQL and mainly SQLite will be the drivers holding you from doing it.

Anyway, MikroORM does support mapping raw results to entities, you can always use raw SQL to do driver specific things.

I never really understand why people think that when there is some API missing in the ORM, it means they can't do it :] I know its not what you are saying here, I just see it pretty much in all the "to ORM or not" discussions all over the internet. It's such a wrong reason.

The main purpose of ORMs is to ease working with relationships, not to replace SQL. I also don't really understand people not learning SQL first, before using any abstraction on top of it...

Btw look at #527, a lot of nice things for v4 are already implemented.

[darkbasic]

Unfortunately, didn't find one... I thought about Slonik, but it's lack of out-of-the-box features such soft delete...

I do use Slonik with Mikro-ORM, but it's not really a query builder because you basically manually write queries.

Anyway, MikroORM does support mapping raw results to entities, you can always use raw SQL to do driver specific things.

And that's why Slonik couples so well with Mikro-ORM.

While you say that the problem is SQL vs NoSQL, in fact the problem is more about the differences among SQL dialects. You want to get most of postgres, but MySQL and mainly SQLite will be the drivers holding you from doing it.

Exactly.

[aaron-harvey]

I was actually thinking about making an example about creating custom driver that would be using redis for demonstration purposes. You could even use JSON file as a backend.

I would be interested in this - I have hopes of implementing couchbase support at some point this year.

[mikicho]

@B4nan Thanks for the detailed and insightful response! you clearly put some light on my main question so I won't continue this further to keep it clean for later readers :)

Btw look at #527, a lot of nice things for v4 are already implemented.

Exciting!

@darkbasic

you basically manually write queries.

And what's wrong with that? ;)

And that's why Slonik couples so well with Mikro-ORM.

Didn't think about that! great idea!

[dzcpy]

@aaron-harvey Are you still interested in implementing it?

[aaron-harvey]

@aaron-harvey Are you still interested in implementing it?

Sorry missed this - I'm still interested, but I have been unable to find the time as of yet.