[dpedu]

I'm totally on board with the idea that ORMs create a variety of inefficiencies, pain points, and make it really easy to create bad queries or querying strategies. But I use them anyways because the convenience of mapping a row to a code object makes writing programs feel fast and simple. And if you know how ORMs can cause problems and how to watch out for them, you can still get a lot of mileage out of them.

That being said, what's the closest alternative that satisfies this - "mapping rows to a code object" - that doesn't suffer the same problems as an ORM? A middle ground between an ORM (like SQLAlchemy, for example) and "your rows are returned as a key/value dictionary where the column names are keys" type approach like Python's DB-API's DictCursor or PHP's mysqli_fetch_assoc. Is there a middle ground here?

[to11mtm]

The happiest middle ground I've found in .NET has been LinqToDb.

It's more of a Micro ORM, -but- has a Linq DSL, as well as DSLs for lots of DB bits. CTEs, Window functions, Bulk copy, 'treat this in memory collection as an input rowset', certain DB Specific bits... and if you need some special sauce to deal with brownfield jank [0] it's very easy to wire-up custom SQL bits into your queries via attributes if needed.

If you use method syntax rather than linq query syntax, you will have minimal surprises with the SQL generated. Typically if it does generate something I didn't expect, I dig in and what it did was indeed both correct and better than what I was trying to do anyway.

[0] - Fun nasty case I ran into on a brownfield project; 'If this number has a decimal point, it is a direct percentage rate. If the number does NOT have a decimal point, it is the FK to a lookup table that has the percentage rate'

[bsuvc]

In .NET, I think Dapper comes closest to what you are describing. It does the object mapping, but you still write the queries as SQL.

https://github.com/DapperLib/Dapper

[taylorlunt]

Yes, there is a middle ground. Elixir's Ecto does this well.

Database rows map to structs. But it doesn't try to figure out how to mutate the data for you to keep the struct in sync with the database. All mutations are explicit using changesets (which can also be used for other non-database purposes, like validating user input for an API.)

There is no implicit preloading of data. You have to explicitly preload.

Data is never fetched implicitly. You have to call Repo.all or Repo.one or something.

It has a query DSL that's a thin wrapper over SQL. It's well-designed and I've never had a problem with it.

[cultofmetatron]

> Yes, there is a middle ground. Elixir's Ecto does this well.

Ecto is by far the closest thing to a perfect pattern for abstracting over sql that I've ever seen. I WISH other languages would create similar libraries. Its the biggest thing keeping me coming back to elixir for any kind of database project. it just makes sql so ergonomic.

[sarchertech]

As someone who works with an 8 year old 500k LOC elixir codebase with millions of users, I hate Ecto so much. The thing I hate most is that there are 2 different syntaxes.

But a close second is that it encourages composition in situations where duplication is the right choice. Having your sql query spread across 7 files makes tracking down bugs and performance issues (and fixing them) incredibly difficult.

[to11mtm]

I mean FWIW Scala has Slick (but then you're dealing with the rest of Scala ecosystem...) and there's Linq2Db on .NET side. Having dealt with all 3 on some level (Slick the least) I would say they are fairly similar in intent (i.e. differences are primarily due to language idioms.)

[ezst]

I'm not sure Slick is still maintained. Recently I depended on https://github.com/com-lihaoyi/scalasql#simpletable-variant-... for small CRUD stuff and liked it quite a bit. These days there's a whole boulevard through the Scala ecosystem that doesn't involve typelevel pedantry and obscure DSLs just for the love of hieroglyphics.

[win311fwg]

No, there is no middle ground. You can either maintain relations throughout the full application or you can transform them into application-native structures, the latter of which is ORM.

The article seems to be confusing ORM with query builders. Query builders are where you might avoid writing SQL. ORM is a data transformation technique.

[jillesvangurp]

> the convenience of mapping a row to a code object makes writing programs feel fast and simple.

Even when you had to do this manually, it was a very minor effort. A one time thing. These days of course any half decent LLM will produce this code without much fanfare. The argument just melts away.

Otherwise, ORMs just layer abstractions on abstractions. You end up with these weird half implied joins resulting in absolutely terrible actual joins happening. Unless you actually understand what you are doing, in which case you could be hammering out those joins manually. And of course the underlying SQL is usually a bit richer than this one size fits all nonsense ORMs do in order to work across sqlite, mysql, postgresql, etc. and pretend that it's all the same.

Another issue with ORMs is the object impedance mismatch where a junior wannabe coder thinks it's all just objects and classes and you end up with these gazillions of completely pointless tables that then necessitate a huge amount of joins. Often the right amount of tables is a lot smaller.

Also, if you aren't querying on it, does it really need its own column? I end up using my databases as document stores quite often. Gets you the best of both worlds. You get to query on nice indexed columns and then you deserialize the big blob of json or whatever into your rich object structure. Simple CRUD for objects shouldn't require a whole lot of engineering. It's only when every little object needs its own little table that shit gets complicated. And another benefit is that this usually results in more stable table structures that don't need a whole lot of database migrations. Getting rid of those removes a lot of needless faff from day to day deployments.

[nodamage]

> Even when you had to do this manually, it was a very minor effort. A one time thing.

Maybe if you’re fetching data from a single table… once you start joining across multiple tables and need deduplicate your result rows it gets pretty annoying to do it by hand though.