4 ms·

I've come to the conclusion that ORMs are good for simple queries like User.find_by(email: "john@snow.com"), but once you get beyond that you are better off jus

I've come to the conclusion that ORMs are good for simple queries like User.find_by(email: "john@snow.com"), but once you get beyond that you are better off just writing sql.

tilyupo 2y ago
I agree, classic ORMs usually don't play well with complex queries. I think Qustar is closer to a query builder than ORM tbh. You can compose arbitrary queries using it.
- mst 2y ago
  People have often said of https://p3rl.org/DBIx::Class https://p3rl.org/DBIx::Class that's it's more a Relational to Object Mapper than an Object to Relational Mapper. We've (I was the original author, bias alert) always had a policy of "if you can't convince it to produce the exact same query that you'd've written by hand, that's either a bug or a missing feature." Some of said features do still remain missing, because of course they do, but the attitude is hugely important nevertheless. You're doing an awesome thing here, and ... I've been considering trying to write a better ROM for JS on and off for a while, and though I may still do so anyway, assuming my sieve-like brain doesn't forget about qustar first I think I should really talk to you about whether we can work together instead before I strike out on my own.
deleted 2y ago
[deleted]
hk__2 2y ago
> I've come to the conclusion that ORMs are good for simple queries like User.find_by(email: "john@snow.com"), but once you get beyond that you are better off just writing sql. When your queries become very complex having a good ORM like SQLAlchemy in Python is a life-saver.
notsylver 2y ago
It might be because I'm not used to SQL, but I've found the opposite. Writing a large query with lots of conditions (eg, if the user is signed in, hiding content they've blocked) is miserable without an ORM that can build the query and map the results. I don't like ORMs for lots of reasons but I find them a necessary evil. How do you deal with that in plain SQL, when a query can look completely different depending on the variables?
- eglintondust 2y ago
  A good query builder is more important than ORM imo. That's what I like about sqlalchemy. The query builder pretty much maps 1:1 to SQL and you can use it with or without the ORM mapping. Most of my projects have a mix of both along with plain SQL for some of the meatier queries.
- eglintondust 2y ago
  A good query builder is more important than ORM imo. That's what I like about sqlalchemy. The query builder pretty much maps 1:1 to SQL and you can use it with or without the ORM mapping. Most of my projects have a mix of both along with plain SQL for some of the meatier queries.
- evilduck 2y ago
  It's like a bell curve for me personally. The things I do with databases between the 25th and 75th percentiles of query complexity fit into an ORM or query builder tool nicely. These also tend to be the bulk of what I need. But those tools add more overhead to the effort of writing simple <25th percentile SQL (like small single-table queries), and then end up being wholly inadequate for the complexity above the 75th percentile, where the library often doesn't have good representation or documentation for the complex things a database can actually do for you in advanced use cases (leading you to do a less efficient similar operation in your programming language), or they try to be too generic to many databases and then aren't able to provide anything that might be specific to Postgres which leads you back to writing raw SQL again.

I've come to the conclusion that ORMs are good for simple queries like User.find_by(email: "john@snow.com"), but once you get beyond that you are better off just writing sql.

tilyupo 2y ago

I agree, classic ORMs usually don't play well with complex queries. I think Qustar is closer to a query builder than ORM tbh. You can compose arbitrary queries using it.

mst 2y ago
People have often said of https://p3rl.org/DBIx::Class https://p3rl.org/DBIx::Class that's it's more a Relational to Object Mapper than an Object to Relational Mapper. We've (I was the original author, bias alert) always had a policy of "if you can't convince it to produce the exact same query that you'd've written by hand, that's either a bug or a missing feature." Some of said features do still remain missing, because of course they do, but the attitude is hugely important nevertheless. You're doing an awesome thing here, and ... I've been considering trying to write a better ROM for JS on and off for a while, and though I may still do so anyway, assuming my sieve-like brain doesn't forget about qustar first I think I should really talk to you about whether we can work together instead before I strike out on my own.

deleted 2y ago

[deleted]

hk__2 2y ago

> I've come to the conclusion that ORMs are good for simple queries like User.find_by(email: "john@snow.com"), but once you get beyond that you are better off just writing sql. When your queries become very complex having a good ORM like SQLAlchemy in Python is a life-saver.

notsylver 2y ago

It might be because I'm not used to SQL, but I've found the opposite. Writing a large query with lots of conditions (eg, if the user is signed in, hiding content they've blocked) is miserable without an ORM that can build the query and map the results. I don't like ORMs for lots of reasons but I find them a necessary evil. How do you deal with that in plain SQL, when a query can look completely different depending on the variables?

eglintondust 2y ago
A good query builder is more important than ORM imo. That's what I like about sqlalchemy. The query builder pretty much maps 1:1 to SQL and you can use it with or without the ORM mapping. Most of my projects have a mix of both along with plain SQL for some of the meatier queries.
eglintondust 2y ago
A good query builder is more important than ORM imo. That's what I like about sqlalchemy. The query builder pretty much maps 1:1 to SQL and you can use it with or without the ORM mapping. Most of my projects have a mix of both along with plain SQL for some of the meatier queries.
evilduck 2y ago
It's like a bell curve for me personally. The things I do with databases between the 25th and 75th percentiles of query complexity fit into an ORM or query builder tool nicely. These also tend to be the bulk of what I need. But those tools add more overhead to the effort of writing simple <25th percentile SQL (like small single-table queries), and then end up being wholly inadequate for the complexity above the 75th percentile, where the library often doesn't have good representation or documentation for the complex things a database can actually do for you in advanced use cases (leading you to do a less efficient similar operation in your programming language), or they try to be too generic to many databases and then aren't able to provide anything that might be specific to Postgres which leads you back to writing raw SQL again.