6 ms·

It's not so much about not wanting to write/understand SQL (both are still very much required), but about composability. If you want to re-use bits of a SQL que

It's not so much about not wanting to write/understand SQL (both are still very much required), but about composability. If you want to re-use bits of a SQL query written as a string literal your only option is string concatention or using some kind of string builder/template system. In both cases there's little validation of the query's correctness (syntax wise) until you actually run it. While I agree that many ORMs go too far or even worse, not implement certain powerful features, Sequel (and similar tools I imagine) strikes a nice balance. The particular examples I gave on their own are not super useful, but we already have quite a few queries that are composed/re-used without having to concat strings. So tl;dr: it's about composability, not being "lazy" or "ignorant" to SQL.

bigpeopleareold 12y ago
I once agreed with this, but now I don't. I just want to write SQL (dammit!). I can never, ever remember the intricacies of the Sequel API or any one of these query builder APIs. I am always looking up something that is rather trivial because I am thinking in SQL, the language, and always have to convert back to Ruby or whatever language I am working in. CTEs and SQL functions in PostgreSQL strike a good balance in the composibility department for me, while still being able to write SQL.
- deleted 12y ago
  [deleted]
- ad_hominem 12y ago
  Let's say you have a product search screen in your application. There's a text field for filtering on product title (WHERE title LIKE), one for filtering on UPC (WHERE upc LIKE), a couple range filters for min/max prices (WHERE price <= and/or WHERE price >=), and then on the results screen the user sort on a few different columns (ORDER BY) as well as paginate and set number of results per-page (LIMIT + OFFSET). How exactly are you going to just write SQL if the actual query statement needs to change based on the user input? Are you going to hardcode every possible permutation, or are you going to use a query builder? Or are there some advanced Postgres features that I'm not aware of that would allow all these combinations in some kind of prepared statement? edit: The question I'm asking is pointed towards the people who are implying that you can "just use SQL" as static statements that are not dynamically assembled. Like a static function or prepared statement that takes some parameters, and at runtime you only pass in those parameters - not rejigger the actual SQL statement fragments. Your ad-hoc query builder implementations with SQL injection vulnerabilities are not relevant to what I'm asking.
  - mbreese 12y ago
    You might be over thinking this... something like this might work out just fine. (I wouldn't necessarily do things this way, but rather keep a list of clauses and join them with " AND " to avoid keeping track of the "WHERE"s and "AND"s, but you get the point...) sql = 'SELECT * FROM products' args = [] if title: sql += ' WHERE title LIKE %?%' args.append(title) if upc: if args: sql += ' AND' else: sql += ' WHERE' sql += ' upc LIKE %?%' args.append(upc) if price_min: if args: sql += ' AND' else: sql += ' WHERE' sql += ' price >= ?' args.append(price_min) if price_max: if args: sql += ' AND' else: sql += ' WHERE' sql += ' price <= ?' args.append(price_max) if order_by_name: sql += ' ORDER BY name' elif order_by_price_low: sql += ' ORDER BY price' elif order_by_price_high: sql += ' ORDER BY price DESC' conn.query(sql, args)
    - ad_hominem 12y ago
      This is a query builder. If you're using your programming language to dynamically assemble the SQL statement fragments at runtime, then you're using a query builder regardless of if it is a library with a fancy DSL that assembles an in-memory SQL AST or some kind of ad-hoc string concatenation you rolled yourself like this. The question I'm asking is pointed towards the people who are implying that you can "just use SQL" as static statements that are not dynamically assembled. Like a static function or prepared statement that takes some parameters, and at runtime you only pass in those parameters - not rejigger the actual SQL statement fragments.
      - mbreese 12y ago
        Yes, logically, it's a query builder. But it's one that doesn't force any extra dependencies in my project and is very easy to troubleshoot. Seriously, how is this different from "just use SQL". SQL was never really intended to be used by itself (except for manually typing queries into a console). Yes, what I wrote is code that writes code, but it's far different from a larger ORM or querying framework.
- dustingetz 12y ago
  Orms are not about syntax, they're about things you can't natively "think about" in SQL, like inheritance, composition, references and graph navigation
  - collyw 12y ago
    I prefer them for taking away the grunt work of needing to code updates and inserts for everything.
mbreese 12y ago
How often do you really reuse bits of a SQL query? SQL is the language that an ORM will generate. The question is, how much work is done to avoid using SQL and is it worth it? The only time that I've found ORMs useful for composing queries when I have to dynamically create a query at runtime based upon user input. And even in that case, today, I'd probably still just concatenate strings for a proper SQL statement.
- vidarh 12y ago
  With Sequel I reuse bits all the time, because you can generally very easily chain statements that return dataset's until you force evaluation of the query.
- scardine 12y ago
  I use this a lot for report-builder interfaces, and according to my personal experience SQLAlchemy in Python maps really well to raw SQL while adding a lot in terms of DRY, portability and security. I guess the underlying question is how much your development style involves meta-programming (and how much someone enjoy reinventing the wheel with their own query builder engine instead of trusting a mature ORM).
- jbergens 12y ago
  It happens a lot for me. Basically you have to choose between copy-pasting a lot of sql code or trying to reuse parts when the business logic has multiple cases that are similar but not exactly equal.
- collyw 12y ago
  I think there is a problem of tooling in SQL, which means it is difficult to reuse it. I spent the other day writing a big query, joining about 10 tables. So usually I start by joining the two main ones. Check that gives me the results I want. Add in another table or conditions, check again. Repeat until all tables are joined into the query and conditions are added. Then I noticed the results of my GROUP_CONCAT were not as I expected. I had a couple of suspect joins that I tried removing. Same problem. In these situations, it is often easier to go through the same process from scratch of adding in one table at a time and ensure that it is working. We need some kind of unit test equivalent for SQL.
jballanc 12y ago
I'd argue it's less about composability, and more about laziness (in the computational sense). As pointed out elsewhere, if you have all of the information needed to generate a dynamic query, it's often not a huge leap from an ORM to composing strings (especially given how relatively flexible SQL syntax can be). However, sometimes I want one part of my program to be responsible for one bit of a query, and a separate piece to be responsible for something else. To take a trivial example, say I want one object/function to be responsible for doing the right sorting, another to be responsible for any pagination, and yet another to be able to group results when needed. In that case, having a programmatic abstraction over a query (whether it be an object or a datatype, doesn't matter) can be very useful.
- Udik 12y ago
  I agree with you example, in that case it is useful. It's just that it is a pain in the ass in so many other cases. I always wonder why developers (usually young and enthusiastic ones...) pick a set of the most challenging requirements they can think of and then use them to justify the usage of some library or pattern that makes life a nightmare in the simpler cases, which are obviously the majority of the use cases. It's like using a tank for going to work, because the roads might possibly have been destroyed and there might be enemies shooting at you. Then ending up destroying and having to rebuild your own garage because the tank is too big to fit in. Complete nonsense.
  - jballanc 12y ago
    > I agree with you example, in that case it is useful. It's just that it is a pain in the ass in so many other cases. It's a pain, because what we really want in such cases is a datatype server, not a relational database. For all of SQL's merits, the NoSQL backlash was not without cause. For too long the SQL hammer has been wielded to pound in too many screws. Of course, programmers being programmers, NoSQL turned into just another hammer used in just as many inappropriate situations. The example I outlined is not a theoretical one. I've dealt with such situations...but I've done so with Datomic, which is designed with datatype-composability in mind. Similarly, I've found a healthy mix of SQL, Redis, memcache, and Datomic all in moderation can go a long way. ...but I've still not found a solution where Mongo is the obvious answer.
rikkus 12y ago
Here's an example in C#. Imagine you're querying a database of products (here represented by integers). Users can enter filter parameters - you want to build your query dynamically based upon what they enter. With LINQ, you can do this kind of composing with no effort. You also get to run the same code on any kind of Queryable, so if you feel like doing some of the work in RAM and some using a DB, your query is usually going to be very similar, if not the same. Yes, you still have to understand what you're querying against and how you should build your queries to make best use, but I'd much rather write this kind of code than try to concatenate SQL. IQueryable<int> Source() { return Enumerable.Range(0, int.MaxValue).AsQueryable(); } class UserFilter { public bool? EvensOnly { get; set; } public int? Minimum { get; set; } public int? Maximum { get; set; } } IEnumerable<int> Search(IQueryable<int> source, int currentPage, int pageSize, UserFilter filter) { var result = source; if (filter.EvensOnly.HasValue && filter.EvensOnly.Value) { result = result.Where(i => i % 2 == 0); } if (filter.Minimum.HasValue) { result = result.Where(i => i >= filter.Minimum.Value); } if (filter.Maximum.HasValue) { result = result.Where(i => i <= filter.Maximum.Value); } return result.Skip(currentPage * pageSize).Take(pageSize); } void Main() { var currentPage = 4; var pageSize = 5; var filter = new UserFilter { EvensOnly = true, Minimum = 1000 }; var searchResults = Search(Source(), currentPage, pageSize, filter); }
- zaroth 12y ago
  FYI, you can also write it this way, although you may find it less readable, it emits the exactly same query to the DB, and I personally find it much more pleasing; IEnumerable<int> Search(...) { return result .Where(i => !f.EvensOnly || i % 2 == 0) .Where(i => !f.Minimum.HasValue || i >= f.Minimum) .Where(i => !f.Maximum.HasValue || i <= f.Maximum) .Skip(currentPage * pageSize).Take(pageSize); } Nit: EvensOnly should not be nullable since it has only two states.
  - rikkus 12y ago
    I find this more readable. In fact, the code here looks more like this: .FilterByEvensOnly() .FilterByMinimum() .FilterByMaximum() Obviously the example is trivialised and the real filters are more complex, requiring joins, but the pattern gives a very readable way of writing an efficient query. Unfortunately EF produces an unreadable query when there are a few dozen filters, but the LINQ code is readable, so there hasn't been any difficulty debugging.
Udik 12y ago
The composability requirement only justifies itself when... your queries need composability. I'm absolutely in favour of using a query builder when it avoids the need to fiddle with strings, but at the same time any decent programmer can write in half a day a query builder that satisfies the vast majority of the use cases. So the question is: once you have covered those cases, do you need to use the query builder to write static queries? No. Do you need an extremely complex query builder to build extremely complex queries? Again, no. SQL is great for writing very readable and optimized static queries, which are almost all the SQL queries you need when you're not dealing with search forms. Bottom line: use your favourite query builder or write a tiny one, but use it only when the resulting code is simpler than the plain SQL + string joining.
- YorickPeterse 12y ago
  We already have quite a bunch of query fragments we use to compose bigger queries, so I'd say in those cases it's more than justified.
deleted 12y ago
[deleted]
kiyoto 12y ago
I agree with this 100%. SQL works incredibly well for purely declarive, relatively simple operations. However, the moment you start doing "Do this, then that, then that", it starts to look (literally the appearance) daunting. I myself come from the R/Python background, and my hypothesis is this difficulty around composability (especially for dplyr/pandas crowd) is what deters data scientists from writing more SQL.
gfody 12y ago
> So tl;dr: it's about composability, not being "lazy" or "ignorant" to SQL. If you want composability you should factor the common sql fragments out to parameterized views (table valued functions), or just regular views. Doing this across a large project can really improve performance as well.
frik 12y ago
Use an IDE like JetBrain's IDEA that checks the SQL syntax against the database.
bborud 12y ago
> In both cases there's little validation of the query's > correctness (syntax wise) until you actually run it. Yes? So? When is this really a problem? Even if you do generate queries dynamically. When does this become such a big problem that it is worthwhile killing readability and bogging down the system with lots of extra complexity? As for ORMs: I've done software for 25 years. I still have not seen a single example of ORMs resulting in better software. Not a single time.

bigpeopleareold 12y ago

I once agreed with this, but now I don't. I just want to write SQL (dammit!). I can never, ever remember the intricacies of the Sequel API or any one of these query builder APIs. I am always looking up something that is rather trivial because I am thinking in SQL, the language, and always have to convert back to Ruby or whatever language I am working in. CTEs and SQL functions in PostgreSQL strike a good balance in the composibility department for me, while still being able to write SQL.

deleted 12y ago
[deleted]
ad_hominem 12y ago
Let's say you have a product search screen in your application. There's a text field for filtering on product title (WHERE title LIKE), one for filtering on UPC (WHERE upc LIKE), a couple range filters for min/max prices (WHERE price <= and/or WHERE price >=), and then on the results screen the user sort on a few different columns (ORDER BY) as well as paginate and set number of results per-page (LIMIT + OFFSET). How exactly are you going to just write SQL if the actual query statement needs to change based on the user input? Are you going to hardcode every possible permutation, or are you going to use a query builder? Or are there some advanced Postgres features that I'm not aware of that would allow all these combinations in some kind of prepared statement? edit: The question I'm asking is pointed towards the people who are implying that you can "just use SQL" as static statements that are not dynamically assembled. Like a static function or prepared statement that takes some parameters, and at runtime you only pass in those parameters - not rejigger the actual SQL statement fragments. Your ad-hoc query builder implementations with SQL injection vulnerabilities are not relevant to what I'm asking.
- mbreese 12y ago
  You might be over thinking this... something like this might work out just fine. (I wouldn't necessarily do things this way, but rather keep a list of clauses and join them with " AND " to avoid keeping track of the "WHERE"s and "AND"s, but you get the point...) sql = 'SELECT * FROM products' args = [] if title: sql += ' WHERE title LIKE %?%' args.append(title) if upc: if args: sql += ' AND' else: sql += ' WHERE' sql += ' upc LIKE %?%' args.append(upc) if price_min: if args: sql += ' AND' else: sql += ' WHERE' sql += ' price >= ?' args.append(price_min) if price_max: if args: sql += ' AND' else: sql += ' WHERE' sql += ' price <= ?' args.append(price_max) if order_by_name: sql += ' ORDER BY name' elif order_by_price_low: sql += ' ORDER BY price' elif order_by_price_high: sql += ' ORDER BY price DESC' conn.query(sql, args)
  - ad_hominem 12y ago
    This is a query builder. If you're using your programming language to dynamically assemble the SQL statement fragments at runtime, then you're using a query builder regardless of if it is a library with a fancy DSL that assembles an in-memory SQL AST or some kind of ad-hoc string concatenation you rolled yourself like this. The question I'm asking is pointed towards the people who are implying that you can "just use SQL" as static statements that are not dynamically assembled. Like a static function or prepared statement that takes some parameters, and at runtime you only pass in those parameters - not rejigger the actual SQL statement fragments.
    - mbreese 12y ago
      Yes, logically, it's a query builder. But it's one that doesn't force any extra dependencies in my project and is very easy to troubleshoot. Seriously, how is this different from "just use SQL". SQL was never really intended to be used by itself (except for manually typing queries into a console). Yes, what I wrote is code that writes code, but it's far different from a larger ORM or querying framework.
dustingetz 12y ago
Orms are not about syntax, they're about things you can't natively "think about" in SQL, like inheritance, composition, references and graph navigation
- collyw 12y ago
  I prefer them for taking away the grunt work of needing to code updates and inserts for everything.

mbreese 12y ago

How often do you really reuse bits of a SQL query? SQL is the language that an ORM will generate. The question is, how much work is done to avoid using SQL and is it worth it? The only time that I've found ORMs useful for composing queries when I have to dynamically create a query at runtime based upon user input. And even in that case, today, I'd probably still just concatenate strings for a proper SQL statement.

vidarh 12y ago
With Sequel I reuse bits all the time, because you can generally very easily chain statements that return dataset's until you force evaluation of the query.
scardine 12y ago
I use this a lot for report-builder interfaces, and according to my personal experience SQLAlchemy in Python maps really well to raw SQL while adding a lot in terms of DRY, portability and security. I guess the underlying question is how much your development style involves meta-programming (and how much someone enjoy reinventing the wheel with their own query builder engine instead of trusting a mature ORM).
jbergens 12y ago
It happens a lot for me. Basically you have to choose between copy-pasting a lot of sql code or trying to reuse parts when the business logic has multiple cases that are similar but not exactly equal.
collyw 12y ago
I think there is a problem of tooling in SQL, which means it is difficult to reuse it. I spent the other day writing a big query, joining about 10 tables. So usually I start by joining the two main ones. Check that gives me the results I want. Add in another table or conditions, check again. Repeat until all tables are joined into the query and conditions are added. Then I noticed the results of my GROUP_CONCAT were not as I expected. I had a couple of suspect joins that I tried removing. Same problem. In these situations, it is often easier to go through the same process from scratch of adding in one table at a time and ensure that it is working. We need some kind of unit test equivalent for SQL.

jballanc 12y ago

I'd argue it's less about composability, and more about laziness (in the computational sense). As pointed out elsewhere, if you have all of the information needed to generate a dynamic query, it's often not a huge leap from an ORM to composing strings (especially given how relatively flexible SQL syntax can be). However, sometimes I want one part of my program to be responsible for one bit of a query, and a separate piece to be responsible for something else. To take a trivial example, say I want one object/function to be responsible for doing the right sorting, another to be responsible for any pagination, and yet another to be able to group results when needed. In that case, having a programmatic abstraction over a query (whether it be an object or a datatype, doesn't matter) can be very useful.

Udik 12y ago
I agree with you example, in that case it is useful. It's just that it is a pain in the ass in so many other cases. I always wonder why developers (usually young and enthusiastic ones...) pick a set of the most challenging requirements they can think of and then use them to justify the usage of some library or pattern that makes life a nightmare in the simpler cases, which are obviously the majority of the use cases. It's like using a tank for going to work, because the roads might possibly have been destroyed and there might be enemies shooting at you. Then ending up destroying and having to rebuild your own garage because the tank is too big to fit in. Complete nonsense.
- jballanc 12y ago
  > I agree with you example, in that case it is useful. It's just that it is a pain in the ass in so many other cases. It's a pain, because what we really want in such cases is a datatype server, not a relational database. For all of SQL's merits, the NoSQL backlash was not without cause. For too long the SQL hammer has been wielded to pound in too many screws. Of course, programmers being programmers, NoSQL turned into just another hammer used in just as many inappropriate situations. The example I outlined is not a theoretical one. I've dealt with such situations...but I've done so with Datomic, which is designed with datatype-composability in mind. Similarly, I've found a healthy mix of SQL, Redis, memcache, and Datomic all in moderation can go a long way. ...but I've still not found a solution where Mongo is the obvious answer.

rikkus 12y ago

Here's an example in C#. Imagine you're querying a database of products (here represented by integers). Users can enter filter parameters - you want to build your query dynamically based upon what they enter. With LINQ, you can do this kind of composing with no effort. You also get to run the same code on any kind of Queryable, so if you feel like doing some of the work in RAM and some using a DB, your query is usually going to be very similar, if not the same. Yes, you still have to understand what you're querying against and how you should build your queries to make best use, but I'd much rather write this kind of code than try to concatenate SQL. IQueryable<int> Source() { return Enumerable.Range(0, int.MaxValue).AsQueryable(); } class UserFilter { public bool? EvensOnly { get; set; } public int? Minimum { get; set; } public int? Maximum { get; set; } } IEnumerable<int> Search(IQueryable<int> source, int currentPage, int pageSize, UserFilter filter) { var result = source; if (filter.EvensOnly.HasValue && filter.EvensOnly.Value) { result = result.Where(i => i % 2 == 0); } if (filter.Minimum.HasValue) { result = result.Where(i => i >= filter.Minimum.Value); } if (filter.Maximum.HasValue) { result = result.Where(i => i <= filter.Maximum.Value); } return result.Skip(currentPage * pageSize).Take(pageSize); } void Main() { var currentPage = 4; var pageSize = 5; var filter = new UserFilter { EvensOnly = true, Minimum = 1000 }; var searchResults = Search(Source(), currentPage, pageSize, filter); }

zaroth 12y ago
FYI, you can also write it this way, although you may find it less readable, it emits the exactly same query to the DB, and I personally find it much more pleasing; IEnumerable<int> Search(...) { return result .Where(i => !f.EvensOnly || i % 2 == 0) .Where(i => !f.Minimum.HasValue || i >= f.Minimum) .Where(i => !f.Maximum.HasValue || i <= f.Maximum) .Skip(currentPage * pageSize).Take(pageSize); } Nit: EvensOnly should not be nullable since it has only two states.
- rikkus 12y ago
  I find this more readable. In fact, the code here looks more like this: .FilterByEvensOnly() .FilterByMinimum() .FilterByMaximum() Obviously the example is trivialised and the real filters are more complex, requiring joins, but the pattern gives a very readable way of writing an efficient query. Unfortunately EF produces an unreadable query when there are a few dozen filters, but the LINQ code is readable, so there hasn't been any difficulty debugging.

Udik 12y ago

The composability requirement only justifies itself when... your queries need composability. I'm absolutely in favour of using a query builder when it avoids the need to fiddle with strings, but at the same time any decent programmer can write in half a day a query builder that satisfies the vast majority of the use cases. So the question is: once you have covered those cases, do you need to use the query builder to write static queries? No. Do you need an extremely complex query builder to build extremely complex queries? Again, no. SQL is great for writing very readable and optimized static queries, which are almost all the SQL queries you need when you're not dealing with search forms. Bottom line: use your favourite query builder or write a tiny one, but use it only when the resulting code is simpler than the plain SQL + string joining.

YorickPeterse 12y ago
We already have quite a bunch of query fragments we use to compose bigger queries, so I'd say in those cases it's more than justified.

deleted 12y ago

[deleted]

kiyoto 12y ago

I agree with this 100%. SQL works incredibly well for purely declarive, relatively simple operations. However, the moment you start doing "Do this, then that, then that", it starts to look (literally the appearance) daunting. I myself come from the R/Python background, and my hypothesis is this difficulty around composability (especially for dplyr/pandas crowd) is what deters data scientists from writing more SQL.

gfody 12y ago

> So tl;dr: it's about composability, not being "lazy" or "ignorant" to SQL. If you want composability you should factor the common sql fragments out to parameterized views (table valued functions), or just regular views. Doing this across a large project can really improve performance as well.

frik 12y ago

Use an IDE like JetBrain's IDEA that checks the SQL syntax against the database.

bborud 12y ago

> In both cases there's little validation of the query's > correctness (syntax wise) until you actually run it. Yes? So? When is this really a problem? Even if you do generate queries dynamically. When does this become such a big problem that it is worthwhile killing readability and bogging down the system with lots of extra complexity? As for ORMs: I've done software for 25 years. I still have not seen a single example of ORMs resulting in better software. Not a single time.