<story><title>Goodbye MongoDB, Hello PostgreSQL</title><url>http://developer.olery.com/blog/goodbye-mongodb-hello-postgresql/</url></story><parent_chain><item><author>moe</author><text>&lt;i&gt;How exactly are you going to &amp;quot;just write SQL&amp;quot; if the actual query statement needs to change based on the user input?&lt;/i&gt;&lt;p&gt;How about something like this:&lt;p&gt;&lt;pre&gt;&lt;code&gt;  s = Select.new
  s.add &amp;quot;WHERE title LIKE #{title}&amp;quot; if title
  s.add &amp;quot;WHERE price &amp;lt;= #{price_range}&amp;quot; if price_range
  s.add &amp;quot;LIMIT #{limit}&amp;quot; if limit
  s.add &amp;quot;OFFSET #{offset}&amp;quot; if offset
  s.add &amp;quot;id, blah, boo FROM products&amp;quot;
  s.execute
&lt;/code&gt;&lt;/pre&gt;
Note how I deliberately shuffled the order and didn&amp;#x27;t bother with escaping.&lt;p&gt;Also note how anyone who knows SQL could immediately work with this, learning curve: 5 seconds.&lt;p&gt;Why is there no ORM that works like this?</text></item><item><author>ad_hominem</author><text>Let&amp;#x27;s say you have a product search screen in your application.  There&amp;#x27;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&amp;#x2F;max prices (WHERE price &amp;lt;= and&amp;#x2F;or WHERE price &amp;gt;=), 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).&lt;p&gt;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&amp;#x27;m not aware of that would allow all these combinations in some kind of prepared statement?&lt;p&gt;edit: The question I&amp;#x27;m asking is pointed towards the people who are implying that you can &amp;quot;just use SQL&amp;quot; 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&amp;#x27;m asking.</text></item><item><author>bigpeopleareold</author><text>I once agreed with this, but now I don&amp;#x27;t. I just want to write SQL (dammit!).&lt;p&gt;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.&lt;p&gt;CTEs and SQL functions in PostgreSQL strike a good balance in the composibility department for me, while still being able to write SQL.</text></item><item><author>YorickPeterse</author><text>It&amp;#x27;s not so much about not wanting to write&amp;#x2F;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&amp;#x2F;template system. In both cases there&amp;#x27;s little
validation of the query&amp;#x27;s correctness (syntax wise) until you actually run it.&lt;p&gt;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&amp;#x2F;re-used without having to concat
strings.&lt;p&gt;So tl;dr: it&amp;#x27;s about composability, not being &amp;quot;lazy&amp;quot; or &amp;quot;ignorant&amp;quot; to SQL.</text></item><item><author>jgrahamc</author><text>As a greying developer I am most amused by people discovering that &amp;#x27;old&amp;#x27; technologies like SQL databases work really well.&lt;p&gt;The only useful piece of advice I can give a younger developer is... be careful when drinking the newtech koolaid.&lt;p&gt;And one more thing:&lt;p&gt;&lt;pre&gt;&lt;code&gt;    star = Sequel.lit(&amp;#x27;*&amp;#x27;)

    User.select(:locale)
      .select_append { count(star).as(:amount) }
      .select_append { ((count(star) &amp;#x2F; sum(count(star)).over) * 100.0).as(:percentage) }
      .group(:locale)
      .order(Sequel.desc(:percentage))
&lt;/code&gt;&lt;/pre&gt;
just makes me want to cry. Learn SQL rather than wrapping it.</text></item></parent_chain><comment><author>zo1</author><text>Python SQLAlchemy works a bit like this, if you use it that way:&lt;p&gt;&lt;pre&gt;&lt;code&gt;    query = session.query(Products)

    if limit:
        query = query.limit(limit_val)

    if offset:
        query = query.offset(offset_val)
&lt;/code&gt;&lt;/pre&gt;
Sort is a bit tricky, but as I&amp;#x27;m a noob at SQL Alchemy, I had some not-so-pretty boilerplate code implemented for decorating queries with custom sorts. But essentially it boiled down to:&lt;p&gt;&lt;pre&gt;&lt;code&gt;    if sort_type == SORT_REVERSE_CONST:
         sort_obj = sort_obj.desc()
    else:
         sort_obj = sort_obj.asc()

    query = query.order_by(sort_obj)
&lt;/code&gt;&lt;/pre&gt;
And if you want to search through dynamic tables, change the initial query instantiation to something along these lines:&lt;p&gt;&lt;pre&gt;&lt;code&gt;    query = session.query(retrieve_db_object(table_name))
&lt;/code&gt;&lt;/pre&gt;
And to execute:&lt;p&gt;&lt;pre&gt;&lt;code&gt;    return query.all()&lt;/code&gt;&lt;/pre&gt;</text></comment>
<story><title>Goodbye MongoDB, Hello PostgreSQL</title><url>http://developer.olery.com/blog/goodbye-mongodb-hello-postgresql/</url></story><parent_chain><item><author>moe</author><text>&lt;i&gt;How exactly are you going to &amp;quot;just write SQL&amp;quot; if the actual query statement needs to change based on the user input?&lt;/i&gt;&lt;p&gt;How about something like this:&lt;p&gt;&lt;pre&gt;&lt;code&gt;  s = Select.new
  s.add &amp;quot;WHERE title LIKE #{title}&amp;quot; if title
  s.add &amp;quot;WHERE price &amp;lt;= #{price_range}&amp;quot; if price_range
  s.add &amp;quot;LIMIT #{limit}&amp;quot; if limit
  s.add &amp;quot;OFFSET #{offset}&amp;quot; if offset
  s.add &amp;quot;id, blah, boo FROM products&amp;quot;
  s.execute
&lt;/code&gt;&lt;/pre&gt;
Note how I deliberately shuffled the order and didn&amp;#x27;t bother with escaping.&lt;p&gt;Also note how anyone who knows SQL could immediately work with this, learning curve: 5 seconds.&lt;p&gt;Why is there no ORM that works like this?</text></item><item><author>ad_hominem</author><text>Let&amp;#x27;s say you have a product search screen in your application.  There&amp;#x27;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&amp;#x2F;max prices (WHERE price &amp;lt;= and&amp;#x2F;or WHERE price &amp;gt;=), 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).&lt;p&gt;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&amp;#x27;m not aware of that would allow all these combinations in some kind of prepared statement?&lt;p&gt;edit: The question I&amp;#x27;m asking is pointed towards the people who are implying that you can &amp;quot;just use SQL&amp;quot; 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&amp;#x27;m asking.</text></item><item><author>bigpeopleareold</author><text>I once agreed with this, but now I don&amp;#x27;t. I just want to write SQL (dammit!).&lt;p&gt;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.&lt;p&gt;CTEs and SQL functions in PostgreSQL strike a good balance in the composibility department for me, while still being able to write SQL.</text></item><item><author>YorickPeterse</author><text>It&amp;#x27;s not so much about not wanting to write&amp;#x2F;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&amp;#x2F;template system. In both cases there&amp;#x27;s little
validation of the query&amp;#x27;s correctness (syntax wise) until you actually run it.&lt;p&gt;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&amp;#x2F;re-used without having to concat
strings.&lt;p&gt;So tl;dr: it&amp;#x27;s about composability, not being &amp;quot;lazy&amp;quot; or &amp;quot;ignorant&amp;quot; to SQL.</text></item><item><author>jgrahamc</author><text>As a greying developer I am most amused by people discovering that &amp;#x27;old&amp;#x27; technologies like SQL databases work really well.&lt;p&gt;The only useful piece of advice I can give a younger developer is... be careful when drinking the newtech koolaid.&lt;p&gt;And one more thing:&lt;p&gt;&lt;pre&gt;&lt;code&gt;    star = Sequel.lit(&amp;#x27;*&amp;#x27;)

    User.select(:locale)
      .select_append { count(star).as(:amount) }
      .select_append { ((count(star) &amp;#x2F; sum(count(star)).over) * 100.0).as(:percentage) }
      .group(:locale)
      .order(Sequel.desc(:percentage))
&lt;/code&gt;&lt;/pre&gt;
just makes me want to cry. Learn SQL rather than wrapping it.</text></item></parent_chain><comment><author>ivanr</author><text>There is. MyBatis, which is a minimal ORM that aims to keep you as close to SQL as possible, has support for dynamic SQL: &lt;a href=&quot;https://mybatis.github.io/mybatis-3/dynamic-sql.html&quot; rel=&quot;nofollow&quot;&gt;https:&amp;#x2F;&amp;#x2F;mybatis.github.io&amp;#x2F;mybatis-3&amp;#x2F;dynamic-sql.html&lt;/a&gt;&lt;p&gt;Edit [responding to moe, below]: that&amp;#x27;s a matter of taste. I prefer to have my SQL _outside_ my code. If I have to write a little XML to make it happen, so be it. Additionally, unlike your example, a strict separation of SQL and data ensures SQL injection is not possible. That&amp;#x27;s also a worthy goal.</text></comment>