TBH I had same experience with all ORMs I have used sqlpp(c++), odb(c++), diesel(rust).

Python has lots of them, and only SQLAlchemy can be considered of high quality.

Not that Django's ORM, peewee or eloquent don't get the job done.

But they only cover 80% of the use cases. SQLA gets you to 95%, and you bridge the five remaining percents with raw sql injected in the proper places for those rare projects where the extra is important.

The cost of it is that it's a harder to use ORM, and a more verbose one. Which is why I generally use https://github.com/absent1706/sqlalchemy-mixins to make things easier.

What's more, ORM for languages like c++ or Rust have it extra hard as they are used from a low level tech while SQL is quite high level and easy to manipulate. It's hence attractive to use it directly.

If you use PHP, Ruby or Python, SQL doesn't seem that big of a gain anymore for the simple operations.

Now diesel is quite young, and the rust ecosystem has proven to be moving not only fast, but generally in a sane direction, so I wouldn't count it out just yet.

However, I've yet to find an even acceptable JS ORM. Not only do they fail to provide any decent operation beyond simple ones, but they have such poor introspection capabilities that they can't be used to create an ecosystem around them.

Which is, after all, __the main point of an ORM__. Avoiding to write SQL is definitively NOT the most important feature of an ORM. The critical one is having a common API you can build upon to create form generators, auth systems, data validation units, serializers, automatic UI, etc.

Without this, the case for using an ORM is way weaker.

http://blogs.tedneward.com/post/the-vietnam-of-computer-scie...

This match reminds me on the 2 sides of SQLAlchemy in a very good way :)

I never find a room to use ORM as it's just an unnecessary and ineffective layer.

I can understand peoples' disdain for ORMs (which tend to be large dependencies that are more frameworks than libraries), but I don't understand not having a query construction and composition layer, and usually the popular ORMs for a language have the most mature implementations of that. In Ruby, there is actually both ActiveRecord (ORM) and Arel (query composition). Nobody really uses Arel directly, though, but I think that's a shame.

  How are you constructing queries?

  given the questions "give me all posts owned by user
  X" and "give me all posts newer than timestamp Y",
  how do you ask for all posts owned by user X that are
  newer than timestamp Y?

Obviously, if your software needs to provide an arbitrary search capability, your needs will be different to mine and an ORM might make sense :)

I'm not talking about a search API, I'm talking about code units that ask myriad questions of the database in order to implement myriad business logic. If you don't have the problem of asking myriad questions of the database, fair enough, but that is a simpler database-connected-software use case than any I've come across (including the ones I thought were pretty simple). I'm very curious now what your service does :)

I’m sorry, but bind variables are Database 101. I have a hard time believing your use cases are particularly “myriad” given lack of the very basics.

We have plenty of complicated queries - when we've got complex SQL queries, we find it valuable to have pure SQL as you can paste it into a tool that lets you execute it on the database and view the results, the explain plans, and suchlike.

I'd be really concerned about the fragility of having that many disparate sources of truth about the structure of your data. Particularly when errors are only going to be discovered when the query actually runs.

You don't need to have an ORM, but at the same time, having no abstraction above "hardcode every query" is its own enormous set of problems.

Mostly query composition. I run in to this a lot. Say I need to write a less-than-simple query and reuse it with several different WHERE clauses.

Sometimes I think I just want migrations, not so much an ORM. I want to define views and stored procs in my repo and have them move to prod automatically, but I still want to write in straight SQL.

A lot of what I do is using some of the more esoteric features of PG (think domains, custom aggregate functions, etc), so the ability to run migrations as direct SQL is great.

But the other super critical thing (which was the first part of my original comment, and which nobody has really highlighted here) is a mature query construction library that knows how to properly protect against injection.