[bob1029]

ORM is usually a bad idea if you are trying to reduce the overall complexity of a solution. Only in the happiest of cases does an ORM solve all of your problems without creating a multitude of new ones.

My experience with ORMs is very similar to my experience with web frameworks. I view them both as a way to offload cognitive burden while you learn about other aspects of the problem space. Once you reach mastery in those other areas, you can begin to dispense with the frameworks and resume more ownership over these areas.

Surrendering a little bit of control up-front makes a lot of sense when you are trying to work through a difficult & new problem. I would definitely prefer the computer do some sub-optimal, blind-mapping of my objects until I could settle on a final schema. Managing a bunch of raw SQL queries while your relational model is still in flux is not something I would look forward to.

[lambdafourtwo]

People usually switch to ORMs because they want to use in-language primitives for their application of choice. It's jarring and inelegant to switch between SQL and javascript or something like that.

The problem with an ORM is that it's a high level abstraction On top of what is ALREADY a high level abstraction: SQL. ANd it's not even a one to one abstraction... they are very different and this actually adds more complexity when it comes to optimizing SQL.

You optimize SQL with hacks to get it to compile into an efficient query. With an ORM you have to hack the ORM in order to hack the sql in order for it to compile into an efficient query. It's nuts.

The fix for this problem is to not use an ORM. You want to use in language primitives? Make an abstraction that is one to one with SQL. A library that gives primitives that directly represent SQL language primitives. That's what we actually all want. We don't actually want an orm.

[hnfong]

> Make an abstraction that is one to one with SQL

Which SQL?

[lambdafourtwo]

The answer to your question is rather obvious. But if you really like ORMs then you likely are hoping your comment serves as some form of revelation to me and this blinds you to the answer.

Either way the answer is this:

All the popular versions of SQL. I mean what else could the answer be? Your question was rhetorical. Trying to expose a flaw.

See the sqlalchemy expression language in sqlalchemy core. It's already very similar to what I'm proposing. It exposes something that is one to one to ansi sql. Then the underlying implementation translates that expression language into different sql languages. This is the key: Underlying implementations of ORMs typically ALREADY handle all versions of SQL languages.

What I'm proposing is a realistic extension of that. Separate APIs for EVERY sql language. You already have separate implementations... thus separate APIs is not such a crazy unrealistic extension of that.

All sql languages have common language primitives, thus an API could have two sets of libraries. One for common primitives and the other for language specific primitives. But that common library can end up being a rabbit hole, so if it's possible to do cleanly... sure, but if not then just seperate APIs for every sql language is fine.

I mean does code really need to be portable across sql databases? I wrote ORM code for postgresql, do I suddenly need that code working with SQLlite? It might be a convenience, but it's not a huge requirement.