147
I wish writing SQL queries was more popular than ORMs (lemmy.ml)
submitted 1 year ago by cyclohexane@lemmy.ml to c/programming@programming.dev
63 comments fedilink hide all child comments

Some backend libraries let you write SQL queries as they are and deliver them to the database. They still handle making the connection, pooling, etc.

ORMs introduce a different API for making SQL queries, with the aim to make it easier. But I find them always subpar to SQL, and often times they miss advanced features (and sometimes not even those advanced).

It also means every time I use a ORM, I have to learn this ORM's API.

SQL is already a high level language abstracting inner workings of the database. So I find the promise of ease of use not to beat SQL. And I don't like abstracting an already high level abstraction.

Alright, I admit, there are a few advantages:

  • if I don't know SQL and don't plan on learning it, it is easier to learn a ORM
  • if I want better out of the box syntax highlighting (as SQL queries may be interpreted as pure strings)
  • if I want to use structures similar to my programming language (classes, functions, etc).

But ultimately I find these benefits far outweighed by the benefits of pure sql.

you are viewing a single comment's thread
view the rest of the comments
[-] Von_Broheim@programming.dev 26 points 1 year ago* (last edited 1 year ago)

Yeah, that's great, until you need to conditionally compose a query. Suddenly your pre baked queries are not enough. So you either:

  • create your own shitty ORM based on string concatenation
  • create your own shitty ORM
  • or use a well supported ORM, those almost always support query composition and native queries

You write like it's ORM vs native. ORMs let you write native queries and execute them while also doing all the tedious work for you such as:

  • mapping results to model objects
  • SQL injection safety
  • query composition
  • connection builders
  • transaction management

So if you love native queries write native queries in an ORM which will do all the tedious shit for you.

[-] kogasa@programming.dev 4 points 1 year ago

Composable querying/pushdown is nice but transaction management is huge. It's not an easy task to correctly implement a way to share transactions between methods and between repository classes. But the alternative is, your transactions are limited to individual methods (or you don't use them, and you risk leaving your database in an inconsistent state without manual cleanup).

[-] lemmyvore@feddit.nl 1 points 1 year ago

mapping results to model objects

I agree. If you have a relational database and an object-oriented programming language you're going to have to map data one way or another.

That being said, using object-oriented doesn't necessarily mean the data abstraction needs to be objects too. Python is object-oriented yet Pandas is a very popular relational abstraction for it.

SQL injection safety

Parameterized queries are native to the database engine. They're going to be available regardless what you use on the client side.

(Well, if the database implements them... having flashbacks to back when MySQL didn't, and it taught a couple of generations of programmers extremely bad "sanitization" practices.)

query composition

Check out the active record pattern. It's a thin layer over SQL that lets you put together a query programatically (and nothing more).

connection builders

This is very database specific and many ORMs don't do a great job of it. If anything this is a con for ORMs not a pro.

transaction management

Again, very hit and miss. Each database has particular quirks and you need to know so much about them to use transactions effectively that it negates any insulation that the ORM provides.

this post was submitted on 08 Sep 2023
147 points (93.5% liked)

Programming

17314 readers
151 users here now

Welcome to the main community in programming.dev! Feel free to post anything relating to programming here!

Cross posting is strongly encouraged in the instance. If you feel your post or another person's post makes sense in another community cross post into it.

Hope you enjoy the instance!

Rules

Rules

  • Follow the programming.dev instance rules
  • Keep content related to programming in some way
  • If you're posting long videos try to add in some form of tldr for those who don't want to watch videos

Wormhole

Follow the wormhole through a path of communities !webdev@programming.dev

founded 2 years ago
MODERATORS
snowe@programming.dev
Ategon@programming.dev
MaungaHikoi@lemmy.nz