4 ms·

My rant is not entirely appropriate in the context of Rails, but generally speaking, I cannot for the life of me understand why so many programmers take somethi

My rant is not entirely appropriate in the context of Rails, but generally speaking, I cannot for the life of me understand why so many programmers take something very simple - a SQL query - and decide the best way to deal with it is to hide the SQL in some kind of wrapper, thus “simplifying” it. Not once have I used one of these libraries where within hours you run across a non-arcane scenario that the wrapper can’t handle (just yesterday: Postgres type cast to insert an enum value).

pcarolan 7y ago
For 80% of use cases all you're doing is Person.find(id), Items.top(10), user.email, etc. ORMs are great for productivity, then eventually you run into a complex query where you want to join a bunch of tables, sort, filter, etc. In these cases, the best practice is to do this in SQL in my opinion. All ORMs give you the ability to get an object by executing raw sql. It's not an either or, it's a both and for general productivity.
specialp 7y ago
We have abandoned ORMs completely for a repository based approach. It is much more satisfying to write a function to extract/insert whatever you want into a database using the full expressiveness of their query language, and power of the database. The premise of having something abstract the DB is cool but with ORMs you lose expressiveness, and are now fighting the syntax and operation of it. The overhead is also large.
barbecue_sauce 7y ago
I think to many programmers, writing a bunch of separate SQL queries seems like it violates the DRY principle. If you start to address this by parameterizing the construction of query strings, it starts feeling like you're writing an ORM any way, so you might as well use one in the first place. Personally, I don't really have a preference. I think each approach has a place depending on the scope and intent of the project, the size of your team, and their familiarity with raw SQL.
shay_ker 7y ago
I think the general idea to be able to test bits and pieces of the logic. With an ORM like ActiveRecord, Sequel, or SQLAlchemy, you can split up a much larger query into multiple functions and then test each individual function. It makes it a lot easier to reason about future changes. I don't know if I agree with a "Query Object" - I don't instantiate objects that much these days, but it's nice to put that logic into a different file than, say, your model or controller.
JMTQp8lwXL 7y ago
Security. Security is the huge implementation detail as to why we aren't writing "SELECT * FROM" + unsanitized_unsafe_user_input. When the Junior on your team doesn't know about prepared statements, at least you can fall back on the ORM.
Tainnor 7y ago
I think this comment is tangential to the article. The author happens to use ActiveRecord (an ORM) in their query object. They might as well have used raw SQL, a simple query builder, or anything else. The more crucial point is that the data access code is isolated from the web related code (the controller) and that the interface relates to the domain, instead of to DB details. This is good practice in any moderately complex code base IMHO. I have seen horrible Rails codebases that just use database calls everywhere in the application, often even in templates. I think it's a good pattern, but in general (after having written quite some Ruby in the past), I still don't understand the aversion of many Rubyists to consider "architecture" as anything more than "the names of the folders of your Rails app", instead of thinking about layering, bounded contexts, etc. (As for ORMs, they definitely have drawbacks; but handwriting SQL leaves you vulnerable to other issues too, such as code injection, runtime syntax errors, lack of type safety (admittedly, not relevant in the context of Ruby), etc. These days I mostly just prefer a simple query builder with the option to drop to raw SQL when necessary.)
Daishiman 7y ago
SQL is simple for a certain class of use cases, but as a language it lacks a lot of things, or supports features that are difficult to use in practice: * It's much easier to manage and manipulate variables in the general-purpose language that constructs queries than in SQL * It is practically impossible to compose queries or manipulate their AST. SQL is not easy to compose; ORM languages make composability trivial * The above feature of ORMs make them amenable to the constructs most people use for manipulating queries with common web framework abstractions. In DRF, queries are separated by a base query, filtering by key, filtering by random acceptable criteria, pagination, etc. You just cannot separate the query components in SQL in a way that naturally maps to this abstraction. * Validating SQL syntax within an IDE that supports a host language is painful; validating chains of ORM events built in the host language is trivial. * Assigning behavior to resulting rows through objects in a ORM is, again, super trivial, while manipulating raw results in SQL to give them abstract behavior requires building your own intermediate layer. Why build something when it's already there and optimized for your use cases? * If you're building a library for common behavior that is database-agnostic, ORMs handle this well. You're not forced into learning the ins and outs of uncommon database engines.

pcarolan 7y ago

For 80% of use cases all you're doing is Person.find(id), Items.top(10), user.email, etc. ORMs are great for productivity, then eventually you run into a complex query where you want to join a bunch of tables, sort, filter, etc. In these cases, the best practice is to do this in SQL in my opinion. All ORMs give you the ability to get an object by executing raw sql. It's not an either or, it's a both and for general productivity.

specialp 7y ago

We have abandoned ORMs completely for a repository based approach. It is much more satisfying to write a function to extract/insert whatever you want into a database using the full expressiveness of their query language, and power of the database. The premise of having something abstract the DB is cool but with ORMs you lose expressiveness, and are now fighting the syntax and operation of it. The overhead is also large.

barbecue_sauce 7y ago

I think to many programmers, writing a bunch of separate SQL queries seems like it violates the DRY principle. If you start to address this by parameterizing the construction of query strings, it starts feeling like you're writing an ORM any way, so you might as well use one in the first place. Personally, I don't really have a preference. I think each approach has a place depending on the scope and intent of the project, the size of your team, and their familiarity with raw SQL.

shay_ker 7y ago

I think the general idea to be able to test bits and pieces of the logic. With an ORM like ActiveRecord, Sequel, or SQLAlchemy, you can split up a much larger query into multiple functions and then test each individual function. It makes it a lot easier to reason about future changes. I don't know if I agree with a "Query Object" - I don't instantiate objects that much these days, but it's nice to put that logic into a different file than, say, your model or controller.

JMTQp8lwXL 7y ago

Security. Security is the huge implementation detail as to why we aren't writing "SELECT * FROM" + unsanitized_unsafe_user_input. When the Junior on your team doesn't know about prepared statements, at least you can fall back on the ORM.

Tainnor 7y ago

I think this comment is tangential to the article. The author happens to use ActiveRecord (an ORM) in their query object. They might as well have used raw SQL, a simple query builder, or anything else. The more crucial point is that the data access code is isolated from the web related code (the controller) and that the interface relates to the domain, instead of to DB details. This is good practice in any moderately complex code base IMHO. I have seen horrible Rails codebases that just use database calls everywhere in the application, often even in templates. I think it's a good pattern, but in general (after having written quite some Ruby in the past), I still don't understand the aversion of many Rubyists to consider "architecture" as anything more than "the names of the folders of your Rails app", instead of thinking about layering, bounded contexts, etc. (As for ORMs, they definitely have drawbacks; but handwriting SQL leaves you vulnerable to other issues too, such as code injection, runtime syntax errors, lack of type safety (admittedly, not relevant in the context of Ruby), etc. These days I mostly just prefer a simple query builder with the option to drop to raw SQL when necessary.)

Daishiman 7y ago

SQL is simple for a certain class of use cases, but as a language it lacks a lot of things, or supports features that are difficult to use in practice: * It's much easier to manage and manipulate variables in the general-purpose language that constructs queries than in SQL * It is practically impossible to compose queries or manipulate their AST. SQL is not easy to compose; ORM languages make composability trivial * The above feature of ORMs make them amenable to the constructs most people use for manipulating queries with common web framework abstractions. In DRF, queries are separated by a base query, filtering by key, filtering by random acceptable criteria, pagination, etc. You just cannot separate the query components in SQL in a way that naturally maps to this abstraction. * Validating SQL syntax within an IDE that supports a host language is painful; validating chains of ORM events built in the host language is trivial. * Assigning behavior to resulting rows through objects in a ORM is, again, super trivial, while manipulating raw results in SQL to give them abstract behavior requires building your own intermediate layer. Why build something when it's already there and optimized for your use cases? * If you're building a library for common behavior that is database-agnostic, ORMs handle this well. You're not forced into learning the ins and outs of uncommon database engines.