[mst]

Quite possibly the best for everything up to moderately complex.

You'd want to enter sqlalchemy (python), DBIx::Class (perl) and arguably Sequel (ruby) into the competition for 'best' though (I'm sticking to dynamic languages here) - exposed power and ability to drop to SQL for only parts of a query vary substantially between options.

[winrid]

On a related note I've been keeping an eye out for a really good ORM in a typed language that is good with relationships without tons of boilerplate, I guess it's hard to do. Hibernate has way too many annotations for my liking, and I'm not into the code generation of JOOQ. I bet you could build it in Nim pretty easily with its compile time stuff.

Diesel might get there in a few years... :)

I know people are gonna judge the whole ORM thing, but writing raw SQL is nice for performance but not for getting something out quickly, or prototyping, when you have lots of relationships.

[mst]

I stand by "an ORM should work as just a query generator and let you skip any object layer when you want to", "an ORM should be able to let you feed the object layer data from your own query" and "an ORM should let you mix and match programmatic query generation and literals to as granular an extent as possible" being table stakes if you have developers who actually like SQL.

There are a bunch of things I hate about the DBIx::Class stack (most of which were my poor decisions to begin with) but it gets those right. Example:

    $schema->resultset('Foo')
           ->where({ bar => $bar })
           ->update({
             baz => \'CASE quux WHEN 0 THEN baz - 1 ELSE baz + 1 END'
           });

would generate (the \'..' means 'reference to a scalar' in perl)

    UPDATE foo
    SET baz = CASE quux WHEN 0 THEN baz - 1 ELSE baz + 1 END
    WHERE bar => ?

and duplicate the relevant SQL generation guts across to a statically typed language wouldn't be horrible ... but every time I start trying to figure out how to get useful per-select-list types for query returns I end up tying myself in knots (easy enough for a simple-CRUD oriented ORM but once you're looking at expressions in SELECT that are query-specific it starts getting gnarly).

I think you may be right about Nim though and thank you for reminding me that I really need to get back to playing with that :)

(obligatory perl disclaimer - if you don't like perl, that's entirely fair, but in that case please steal the good parts of my/the rest of the DBIx::Class/SQL::Abstract team's ideas into your language of choice because developers who don't like perl deserve nice things too)

[winrid]

Oh, for sure on the SQL part. I do like being able to auto generate join tables and such, though!