[Swizec]

For me Django and ActiveRecord stand out as 2 good examples of what an ORM should be like. Both feel like they make the simple stuff super easy, the complex stuff figure outable, and the super hard stuff trivially possible with raw SQL and a decent mapping from that back to regular code.

Although over the years my code trends more and more towards `.rawSql` or whatever equivalent exists. Even for the simple stuff. It’s just so much easier than first thinking up my query then bending over backwards three times to make it fit into the ORM’s pet syntax.

Plus raw sql is so much easier to copypasta between different tools when you’re debugging.

And before you say “but sql injection!” – that’s what prepared statements/parametrized queries are for.

[djeiejejejej]

Same here, but IMO it is related to skill and experience.

Once you get sufficiently familiar with some paradigm the training wheels can come off.

“Raw” SQL is already an abstraction. Over time all the implicit magic will get on your nerves. Trying to shoehorn two completely different worlds into one abstraction is not worth it: you get to learn today’s untransferable funky ORM syntax and idiosyncrasies while losing sight of the actual skill that matters long term which is SQL itself.

I concede however that handling of SQL, the field names, the relations, is annoying. But it’s core to the problem you are probably solving (some form of CRUD). Plumbing is annoying but as a plumber I’d say get used to it instead of wishing to be dancer.

I notice this in other aspects of my work as well. When I switched away trom desktop environment to terminal I had the same feeling. It’s easier, less hassle, less wonky abstractions, more direct. Completely counter to what popular culture is telling me.

[KronisLV]

> I concede however that handling of SQL, the field names, the relations, is annoying. But it’s core to the problem you are probably solving (some form of CRUD). Plumbing is annoying but as a plumber I’d say get used to it instead of wishing to be dancer.

It feels more like outsourcing said plumbing to someone that has done a lot of it in the past and will in most cases save you time, even if they won’t do everything the way you’d prefer yourself.

Throw in a bit of codegen and reading your current schema (if using schema first approach) and you’re in a pretty nice spot to be, except for the times when ORMs will get confused with non trivial joins and relationships, but typically then you have an escape hatch to have the query be in raw SQL while still mapping the outputs to whatever objects you need.

To be clear, I still think that mapping read only entities/DTOs against specialized database views for non-trivial data selection makes sense a lot of time regardless of the stack (or even some in-database processing with stored procedures), but basic ORM mappings are useful a lot of time too.

[gniting]

> but typically then you have an escape hatch to have the query be in raw SQL while still mapping the outputs to whatever objects you need.

This is precisely why we introduced the "TypedSQL" feature in the Prisma ORM. For those who are interested in reading more on that: https://prisma.io/typedsql

[wsc981]

I've started working on a .NET project that uses EntityFramework and due to some of the magic, I prefer just using raw SQL as well. I've used other ORM solutions in the past as well and I am not a fan ...

But the team chose EF due to it supposedly being easier to integrate with whatever database the customer might be using and that seems like valid reasoning.

[neonsunset]

It's pretty straightforward, and LINQ method names map quite closely to SQL.

If you are not a fan of it however, you can use queries directly with '.FromSql(...)' retaining the convenience of object mapping. Interpolated queries use string interpolation API to convert them to parametrized queries that are injection-safe under the hood.

https://learn.microsoft.com/en-us/ef/core/querying/sql-queri...

[djeiejejejej]

Yes I agree. That’s a valid argument, but there are other ways as well like SQLKata and other query builders.

Depending on the complexity of the product I think writing standard SQL92 will get you far as well.

I don’t get how an ORM will handle Postgres’ CTEs and windowing functions. Those are pretty basic and extremely useful features to me, but those require dropping to raw SQL. Each vendor has those useful particularities that you immediately lose by going ORM.

So in practice you are already required to make your queries as bland as possible where GROUP BY and JOIN is about as complex as it will get. I’d say just do SQL92 work around the limitations - which is something you’ll have to do anyway, but now more directly - and all major vendors will work OOB.

[Izkata]

> I don’t get how an ORM will handle Postgres’ CTEs and windowing functions. Those are pretty basic and extremely useful features to me, but those require dropping to raw SQL. Each vendor has those useful particularities that you immediately lose by going ORM.

Django has supported windowing functions since 2.0, almost 7 years ago: https://docs.djangoproject.com/en/2.0/ref/models/expressions...

And people are experimenting with CTEs - this looks pretty natural to use, at least with the basic example, and it supports recursive CTEs even if the example looks clunky (though I think that's their choice of example rather than the actual recursive part making it look bad): https://dimagi.github.io/django-cte/ (repo: https://github.com/dimagi/django-cte)