DenoDB

The language appropriate tool for writing SQL is SQL. Anything else is a mess; even the ORM best case (generating a simple query from suitable metadata, without redundant source code) is both very complicated compared to just having the text of the SQL statement and very constraining for future evolution (e.g. when the query involves a new table, doing multiple queries and filtering data in the applications instead of using joins because it's the path of least resistance).

Writing, what, a couple dozen (at most) SQL statements that are custom designed for a single application? Not really a big deal. Sure, write your SQLite queries by hand. Fine. Whatever; I wouldn't even say it's inefficient if it's that few.

Writing ~20 SQL queries for each of 20 different data structures (including various JOINs and search options) to create a CRUD API? And maintaining them each individually? And needing to make changes to the query every time the clients need to make a slightly different search request?

When I can literally write the 20 different schemas in one file, type a build command, and get a fully featured GraphQL API with full JOIN and custom boolean logic search capability in a few seconds?

I'm saving myself probably 40,000 lines of code, counting all of the data wrangling and test cases that I'd need to create to provide the minimal functionality that I would need--and the GraphQL code wouldn't need to be modified to change the search or order-by options of a particular query. And lines of code you don't write are lines of code you don't need to maintain. It's a huge win. Not even close.

And that said, the above is based on a real project I'm currently involved with, and that project does have six custom GraphQL queries that I wrote by hand--most of which also leveraging the ORM to some degree.

In exactly three cases, I wrote raw SQL queries that the ORM didn't support directly.

So I'm going to say no, the black boxes don't need to work 100% of the time. For me they're working 99% of the time, and I write the last 1% by hand, which is just fine. If I had run across a terrible query in that one corner case, I'd probably not try to dig inside the black box; instead I'd create a custom query that does exactly what I want and expose it for that bit of functionality. Done. [1]

As to the ORM used by Duplicati? Crappy ORMs exist. That doesn't mean all ORMs are bad. I can see the exact queries (minus parameters) scroll by in my log that the ORM I'm using is writing out. My own custom SQL queries tend to be more lines of code in practice, and none of the queries that it has generated so far have been slow.

[1] Figuring out why a 100 line SQL statement is slow is generally not that hard: You put "EXPLAIN ANALYZE" in front of it, and it will point out where the time is going. That's a PostgreSQL feature, so you'd need to have a Postgres backend in order to use it. Good thing we're talking about using an ORM that could easily switch between SQLite and PostgreSQL! Optimize the schema and indices on PostgreSQL and then switch to SQLite for embedded work.

I've been doing this as my $DAYJOB for the last five years.

Your examples/predicted catastrophes are straw men that I've never seen in reality myself--though I've heard reports of Rails/ActiveRecord having issues similar to what you describe. Maybe that's the real problem you're worried about? RoR sucks. We can agree on that.

In the code I've been working with, though? An ORM can have annoying limitations, but doesn't suffer from weird magic. So in that case, write custom SQL to solve the exact issue the ORM doesn't support. Problem solved!

I'm confused by "use a query builder instead of an ORM," though. The ORMs I'm using are effectively query builders with knowledge of your object relationships. See for example Sequelize, TypeORM, or Prisma.io. Lighter query builders like knex.js don't seem to have enough information on the schema to give you type safety or auto-generated code (like FeathersJS or GraphQL resolver builders).

TypeORM and Prisma.io even allow you to use the Data Mapper pattern instead of the annoying ActiveRecord pattern. I suspect you're confusing the ActiveRecord pattern with "ORM". No "magical ORM layers" required.

Don't confuse Rails/ActiveRecord garbage for a modern ORM.

I have read through a bunch of your comments and it frankly doesn't even sound like you are doing database queries for an application: it sounds like you were tasked with creating a very general purpose API endpoint for application developers. You thereby to me don't even seem to be having a use case for an "ORM", so I am extremely confused? It seems like you wanted a tool that let you describe an intended API surface over some schema and have it automatically construct your backend for you... that sounds useful, and if that's some side effect of working with an ORM that is really cool I guess, but it isn't what an ORM is about. I would imaging then that the client is then using a GraphQL adapter to access your data.

In contrast, when all of us who despite ORMs are using that term, we are talking about a library or--worse--a framework designed to have the developer directly make queries using object-oriented data modeling. These layers then generate SQL that is pretty much guaranteed to be not just inefficient but ridiculously unscalable in ways that you run into even in simple software.

It sounds like in your architecture you would have no use for what I would call an ORM: at best, maybe the application developer that is consuming your API would in turn (I would argue, incorrectly) choose to use an ORM to access your GraphQL... but they are not generating SQL and you are not making queries. So I guess I just feel like you and we are talking about entirely unrelated use cases?

> The language appropriate tool for writing SQL is SQL. Anything else is a mess...

It sounds like you're pretty set on that opinion, and that's fine, but I suspect you just haven't run into the case where the raw SQL is far messier than using a query builder.

SQL strings are pretty inflexible. The big advantage of a query builder (note that I'm not saying "ORM") is that you can start with a simple base case and dynamically mutate the query to add clauses specific to the request you're handling. Maybe one user wants 10 results per page and another user wants 25, for example.

I have an example of how far you can take this at http://btubbs.com/postgres-search-with-facets-and-location-a.... I could not endorse trying to build the query interface in that post with raw SQL.