Why use SQL collectors? Arel v. Sequel v. T-sql

Question

I am trying to understand the benefits of building SQL using the object-oriented DSL constructor and parameterizing a raw SQL string. After researching / implementing the same query in three ways, I notice that raw SQL is by far the easiest to read. This begs the question: "why jump through a hoop?" Why not just declare and use raw SQL?

Here is what I came up with:

First, I assume that this makes SQL more portable, since it can then be used by any database with an adapter. It's probably big, right? However, isn't most T-SQL easy to understand for most databases?

Secondly, it provides a query object that can be reused - as a basis for other queries, a chain with names, etc.

What is the main return on investment you realize by creating your own SQL instead of declarations?

def instances_of_sql(ttype_id) #raw sql ttype_id = get(ttype_id).try(:id) ti = get('tmdm:type-instance') inst = get('tmdm:instance') type = get('tmdm:type') self.class.send :sanitize_sql, [%{ SELECT t.* FROM associations a JOIN roles type ON type.association_id = a.id AND type.ttype_id = ? JOIN roles inst ON inst.association_id = a.id AND inst.ttype_id = ? JOIN topics t ON t.id = inst.topic_id WHERE a.topic_map_id IN (?) AND a.ttype_id = ? AND type.topic_id = ? }, type.id, inst.id, self.ids, ti.id, ttype_id] end def instances_of_sql(ttype_id) #sequel ttype_id = get(ttype_id).try(:id) ti = get('tmdm:type-instance') ir = get('tmdm:instance') tr = get('tmdm:type') DB.from(:associations.as(:a)). join(:roles.as(:tr), :tr__association_id => :a__id, :tr__ttype_id => tr[:id]). join(:roles.as(:ir), :ir__association_id => :a__id, :ir__ttype_id => ir[:id]). join(:topics.as(:t), :t__id => :ir__topic_id). where(:a__topic_map_id => self.ids). where(:a__ttype_id => ti[:id]). where(:tr__topic_id => ttype_id). select(:t.*).sql end def instances_of_sql(ttype_id) #arel ttype_id = get(ttype_id).try(:id) ti = get('tmdm:type-instance') inst = get('tmdm:instance') type = get('tmdm:type') #tables t = Topic.arel_table a = Association.arel_table tr = Role.arel_table ir = tr.alias a. join(tr).on(tr[:association_id].eq(a[:id]),tr[:ttype_id].eq(type[:id])). join(ir).on(ir[:association_id].eq(a[:id]),ir[:ttype_id].eq(inst[:id])). join(t).on(t[:id].eq(ir[:topic_id])). where(a[:topic_map_id].in(self.ids)). where(a[:ttype_id].eq(ti[:id])). where(tr[:topic_id].eq(ttype_id)). project('topics.*').to_sql end 

I fully appreciate these areas and see how their chains can be useful. I am not worried about accessing the relevant records using the model. I'm just talking about creating a complex query.

Answer 1

The link that @Kyle Heironimus gave Nick Cullen’s thoughts about Arel had the following line:

You will notice the use of the resulting table in the subquery. This is terrible, in my opinion. Only advanced SQL programmers know how to write this (I often asked this question at work interviews, I have never seen anyone get it right). And it should not be difficult!

Well, Kallen puts this at the disadvantage of closing composition in SQL. In some cases this may be true, but my experience is much more prosaic - most developers are terrible about SQL. They only know the most basic things, these basic things are used incorrectly, as they try to find procedural solutions in a set-based language. I had to argue about the benefits of a database hosted by 3NF in the same company I was in with all the other developers, they just didn't get it. Talented guys (most of them :), but they don’t know about SQL or databases.

Put it in C # or Ruby or Python <insert, and the developers will be happy again. They can stick to procedural / OO thinking and create code that suits them well.

I know that this will not bring me any votes, perhaps quite the opposite, but this is my opinion. Arel looks interesting, by the way.

---

As a complement to the comments I made above, over the course of six months and used the Sequel library many times, I can say that it is really beautiful, and now I feel that I will use it ahead of using direct SQL. Not only is it incredibly powerful and allows me to do simple and advanced things, not too many scratches on my head (there are always some), it can output the SQL code that it used, and it will also let me dump SQL if I feel , what I need.

This in no way negates my comments on how most developers understand SQL (I recently said that a developer who negotiates with others says that normalization was a relic of a time when storage space was expensive. dear!) that the development of the Sequel library was obviously done by those who really understand the database. If you know the design of SQL and db etc., then it gives you more energy faster. I can't say the same about the other ORMs that I used, but maybe others will think differently.

Answer 2

You have already suffered greatly from the causes.

Here are thoughts from the creator of Arel.