7 ms·

How exactly are you going to "just write SQL" if the actual query statement needs to change based on the user input? How about something like this: s = Sele

by moe 12y ago

How exactly are you going to "just write SQL" if the actual query statement needs to change based on the user input? How about something like this: s = Select.new s.add "WHERE title LIKE #{title}" if title s.add "WHERE price <= #{price_range}" if price_range s.add "LIMIT #{limit}" if limit s.add "OFFSET #{offset}" if offset s.add "id, blah, boo FROM products" s.execute Note how I deliberately shuffled the order and didn't bother with escaping. Also note how anyone who knows SQL could immediately work with this, learning curve: 5 seconds. Why is there no ORM that works like this?

ivanr 12y ago
There is. MyBatis, which is a minimal ORM that aims to keep you as close to SQL as possible, has support for dynamic SQL: https://mybatis.github.io/mybatis-3/dynamic-sql.html https://mybatis.github.io/mybatis-3/dynamic-sql.html Edit [responding to moe, below]: that'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's also a worthy goal.
- moe 12y ago
  MyBatis Does MyBatis have an API similar to what I outlined above? Because the page that you link to shows a horror that I can't even begin to describe: <select id="findActiveBlogWithTitleLike" resultType="Blog"> SELECT * FROM BLOG WHERE state = ‘ACTIVE’ <if test="title != null"> AND title like #{title} </if> </select> SQL mixed with proprietary XML soup? Talk about combining the worst from all worlds...
  - bigpeopleareold 12y ago
    Get rid of the XML and you have a good start, I'd say.
zo1 12y ago
Python SQLAlchemy works a bit like this, if you use it that way: query = session.query(Products) if limit: query = query.limit(limit_val) if offset: query = query.offset(offset_val) Sort is a bit tricky, but as I'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: if sort_type == SORT_REVERSE_CONST: sort_obj = sort_obj.desc() else: sort_obj = sort_obj.asc() query = query.order_by(sort_obj) And if you want to search through dynamic tables, change the initial query instantiation to something along these lines: query = session.query(retrieve_db_object(table_name)) And to execute: return query.all()
- moe 12y ago
  Python SQLAlchemy works a bit like this All ORMs work "a bit like this". I don't want "a bit like this". I want exactly like this. Because with my proposed interface I could be productive immediately and permanently. I would never have to refer to any documentation. Not once. With every other ORM this is a pipe dream. Their "fancy" chainable wrapper-APIs are nothing but a ball on a chain.
  - emodendroket 12y ago
    Good news! Almost every language offers some sort of string concatenation with no protection whatsoever against injection, which seems to be what you're asking for.
    - nknighthb 12y ago
      His proposal is not string concatenation, and it is not vulnerable to injection.
      - djur 12y ago
        "WHERE price <= #{price_range}" looks like raw interpolation to me. How do you make that not vulnerable to injection unless you're escaping all variables that might be used in a query?
GFischer 12y ago
Microsoft's Entity Framework and LINQ work pretty much like that, only with nice IDE support. I'll try to write an example like that when I get home. Of course, they're not "just SQL", and I do understand where the above comments come from. And yes, sometimes I have to go look up how to do things I'd do trivially on SQL.
neilk 12y ago
What you're describing isn't an ORM, it's a SQL query abstraction. Knex is a very convenient, Promise-oriented query abstraction, and it has "raw" methods when only SQL snippets will do. http://knexjs.org/ http://knexjs.org/
lugg 12y ago
Generally I'll go with something like this for these conditional search based queries select id, blah, boo from products where 1 and (title like :title} or if(:title = '', false, true)) and (price <= :price_range or if(:price_range = -1, false, true) limit :limit offset :offset Its a trade off, bit of extra complexity in the query for simpler code logic. Really, I avoid using sql for these types of "search" / find methods, they're probably going to be slow as hell as soon as you get a sizable data set. I really despise orm's its essentially taking a black box (sql/database) and throwing it inside a much less tested, less optimized, less documented black box. For what reason, I have no idea, nobody has ever been able to convince me there is a problem that needs solving. Almost every argument i've heard amounts to problems sql already solved a decade ago. I can only speculate but I'll hazard a guess it comes down to lack of enthusiasm to really learn sql properly (even though the person uses it / debugs it daily)
encoderer 12y ago
> Why is there no ORM that works like this? Because you're only showing a query builder, the "relational" not the "object mapper". From an OOD point of view, if the end result of that query will be Product instances, why am I using a Select object to create them and why is it having to do some sort of string parsing to determine the objects I'm loading?
- moe 12y ago
  if the end result of that query will be Product instances, why am I using a Select object to create them Because we can just infer the type to be returned via the FROM-clause of the query. and why is it having to do some sort of string parsing to determine the objects I'm loading? Because, to cite the immortal Larry Wall: The computer should be doing the hard work. That's what it's paid to do, after all. -- Larry Wall It has to do the string parsing, escaping and intelligent validation so I, the human, can write s.add "count(*) / sum(count(*)) * 100 as percentage" instead of .select_append { ((count(star) / sum(count(star)).over) * 100.0).as(:percentage) } The difference is that most people can read and write the former without thinking. The latter version may not look much more complicated at a glance. But as we all know these seemingly trivial fragments, more often than not, take ungodly amounts of very frustrating trial & error before they play along.
  - djur 12y ago
    You don't have to pass a block to select_append. With Sequel, you can do this: # db is a Sequel::Database s = db[:foobars] s = s.select Sequel.lit("max(id) as best_id") s = s.select_append Sequel.lit("count(*) / sum(count(*)) * 100 as percentage") s.sql # ==> "SELECT max(id) as best_id, count(*) / sum(count(*)) * 100 as percentage FROM `foobars`" The only real difference here is Sequel.lit, which is needed for security (any secure DB interface needs to somehow be notified that strings are safe to put into a query without escaping). If I'm writing code that leans heavily on Sequel to build queries, I'll make a private method #sql that is an alias for Sequel.lit. It's entirely feasible to use Sequel this way. I've used it like that in production. I loathe the query building DSL, myself, but it's strictly optional.
solidsnack9000 12y ago
> Why is there no ORM that works like this? Because no one wants to write a SQL fragment parser. You would have write a parser that: a) Could be started at an arbitrary place in a query and figure out what it needed to do. b) Would be SQL-dialect aware. (Maybe -- it might be possible to skip this.) Were it not for (a), I'd imagine you could quickly build this by using some kind of debug feature of the database -- sending it a query to parse and getting back a parse tree.
jbergens 12y ago
Micro ORMs usually works very much like this. There is a problem when you want sub records, they are handled differently in each library or not handled at all.
rmc 12y ago
Now you have to be aware of SQL escaping to prevent SQL injection attacks.
- onion2k 12y ago
  Everyone who works with SQL should be aware of SQL escaping. There's nothing wrong using a library to do it for you, but what you should have an understanding of what the library is actually doing. Otherwise how could you possibly test it? Just by hoping it's right?
cmdkeen 12y ago
One of the things I like about EntityFramework is that you write LINQ, which odds are these days every C# dev should be familiar with. So, standby a few gotchas (which SQL has as well), the code you write to query an in memory collection is the same code you write to query the database. This also makes it fairly easy to test because you can create in memory representations of your data and check you filters, projections, orders etc.
qooleot 12y ago
Have you thought about JSON as a partial query placeholder, and all the native methods available for traversal, merging, plucking, etc.? For example, here is : { type: 'select', columns: [{ expression: 'orgJobs.org_job_code', alias: 'job_code' }], joins: [{ table: 'org_jobs', alias: 'orgJobs' }], limit: 100, offset: 0 } which yields: select orgJobs.org_job_code as job_code from org_jobs as orgJobs limit 100 offset 0 and so its easy to do things like: query.limit = 100; and of course: query.columns.push(additionalColumn); and use something like lodash/underscore (or equivalent for your language) to add collection/array/object helper methods so you never have to write for-loops for things like mapping and property plucking.
tracker1 12y ago
Stuff like your example is where, in general I prefer a sproc, or something more like the following... http://www.ivc.com/blog/better-sql-strings-in-io-js-nodejs-part-2/ http://www.ivc.com/blog/better-sql-strings-in-io-js-nodejs-p...
merb 12y ago
there is, it's called anorm. https://www.playframework.com/documentation/2.3.x/ScalaAnorm https://www.playframework.com/documentation/2.3.x/ScalaAnorm You don’t need another DSL to access relational databases SQL is already the best DSL for accessing relational databases. We don’t need to invent something new. Moreover the SQL syntax and features can differ from one database vendor to another.

by moe 12y ago

ivanr 12y ago

There is. MyBatis, which is a minimal ORM that aims to keep you as close to SQL as possible, has support for dynamic SQL: https://mybatis.github.io/mybatis-3/dynamic-sql.html https://mybatis.github.io/mybatis-3/dynamic-sql.html Edit [responding to moe, below]: that'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's also a worthy goal.

moe 12y ago
MyBatis Does MyBatis have an API similar to what I outlined above? Because the page that you link to shows a horror that I can't even begin to describe: <select id="findActiveBlogWithTitleLike" resultType="Blog"> SELECT * FROM BLOG WHERE state = ‘ACTIVE’ <if test="title != null"> AND title like #{title} </if> </select> SQL mixed with proprietary XML soup? Talk about combining the worst from all worlds...
- bigpeopleareold 12y ago
  Get rid of the XML and you have a good start, I'd say.

zo1 12y ago

Python SQLAlchemy works a bit like this, if you use it that way: query = session.query(Products) if limit: query = query.limit(limit_val) if offset: query = query.offset(offset_val) Sort is a bit tricky, but as I'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: if sort_type == SORT_REVERSE_CONST: sort_obj = sort_obj.desc() else: sort_obj = sort_obj.asc() query = query.order_by(sort_obj) And if you want to search through dynamic tables, change the initial query instantiation to something along these lines: query = session.query(retrieve_db_object(table_name)) And to execute: return query.all()

moe 12y ago
Python SQLAlchemy works a bit like this All ORMs work "a bit like this". I don't want "a bit like this". I want exactly like this. Because with my proposed interface I could be productive immediately and permanently. I would never have to refer to any documentation. Not once. With every other ORM this is a pipe dream. Their "fancy" chainable wrapper-APIs are nothing but a ball on a chain.
- emodendroket 12y ago
  Good news! Almost every language offers some sort of string concatenation with no protection whatsoever against injection, which seems to be what you're asking for.
  - nknighthb 12y ago
    His proposal is not string concatenation, and it is not vulnerable to injection.
    - djur 12y ago
      "WHERE price <= #{price_range}" looks like raw interpolation to me. How do you make that not vulnerable to injection unless you're escaping all variables that might be used in a query?

GFischer 12y ago

Microsoft's Entity Framework and LINQ work pretty much like that, only with nice IDE support. I'll try to write an example like that when I get home. Of course, they're not "just SQL", and I do understand where the above comments come from. And yes, sometimes I have to go look up how to do things I'd do trivially on SQL.

neilk 12y ago

What you're describing isn't an ORM, it's a SQL query abstraction. Knex is a very convenient, Promise-oriented query abstraction, and it has "raw" methods when only SQL snippets will do. http://knexjs.org/ http://knexjs.org/

lugg 12y ago

Generally I'll go with something like this for these conditional search based queries select id, blah, boo from products where 1 and (title like :title} or if(:title = '', false, true)) and (price <= :price_range or if(:price_range = -1, false, true) limit :limit offset :offset Its a trade off, bit of extra complexity in the query for simpler code logic. Really, I avoid using sql for these types of "search" / find methods, they're probably going to be slow as hell as soon as you get a sizable data set. I really despise orm's its essentially taking a black box (sql/database) and throwing it inside a much less tested, less optimized, less documented black box. For what reason, I have no idea, nobody has ever been able to convince me there is a problem that needs solving. Almost every argument i've heard amounts to problems sql already solved a decade ago. I can only speculate but I'll hazard a guess it comes down to lack of enthusiasm to really learn sql properly (even though the person uses it / debugs it daily)

encoderer 12y ago

> Why is there no ORM that works like this? Because you're only showing a query builder, the "relational" not the "object mapper". From an OOD point of view, if the end result of that query will be Product instances, why am I using a Select object to create them and why is it having to do some sort of string parsing to determine the objects I'm loading?

moe 12y ago
if the end result of that query will be Product instances, why am I using a Select object to create them Because we can just infer the type to be returned via the FROM-clause of the query. and why is it having to do some sort of string parsing to determine the objects I'm loading? Because, to cite the immortal Larry Wall: The computer should be doing the hard work. That's what it's paid to do, after all. -- Larry Wall It has to do the string parsing, escaping and intelligent validation so I, the human, can write s.add "count(*) / sum(count(*)) * 100 as percentage" instead of .select_append { ((count(star) / sum(count(star)).over) * 100.0).as(:percentage) } The difference is that most people can read and write the former without thinking. The latter version may not look much more complicated at a glance. But as we all know these seemingly trivial fragments, more often than not, take ungodly amounts of very frustrating trial & error before they play along.
- djur 12y ago
  You don't have to pass a block to select_append. With Sequel, you can do this: # db is a Sequel::Database s = db[:foobars] s = s.select Sequel.lit("max(id) as best_id") s = s.select_append Sequel.lit("count(*) / sum(count(*)) * 100 as percentage") s.sql # ==> "SELECT max(id) as best_id, count(*) / sum(count(*)) * 100 as percentage FROM `foobars`" The only real difference here is Sequel.lit, which is needed for security (any secure DB interface needs to somehow be notified that strings are safe to put into a query without escaping). If I'm writing code that leans heavily on Sequel to build queries, I'll make a private method #sql that is an alias for Sequel.lit. It's entirely feasible to use Sequel this way. I've used it like that in production. I loathe the query building DSL, myself, but it's strictly optional.

solidsnack9000 12y ago

> Why is there no ORM that works like this? Because no one wants to write a SQL fragment parser. You would have write a parser that: a) Could be started at an arbitrary place in a query and figure out what it needed to do. b) Would be SQL-dialect aware. (Maybe -- it might be possible to skip this.) Were it not for (a), I'd imagine you could quickly build this by using some kind of debug feature of the database -- sending it a query to parse and getting back a parse tree.

jbergens 12y ago

Micro ORMs usually works very much like this. There is a problem when you want sub records, they are handled differently in each library or not handled at all.

rmc 12y ago

Now you have to be aware of SQL escaping to prevent SQL injection attacks.

onion2k 12y ago
Everyone who works with SQL should be aware of SQL escaping. There's nothing wrong using a library to do it for you, but what you should have an understanding of what the library is actually doing. Otherwise how could you possibly test it? Just by hoping it's right?

cmdkeen 12y ago

One of the things I like about EntityFramework is that you write LINQ, which odds are these days every C# dev should be familiar with. So, standby a few gotchas (which SQL has as well), the code you write to query an in memory collection is the same code you write to query the database. This also makes it fairly easy to test because you can create in memory representations of your data and check you filters, projections, orders etc.

qooleot 12y ago

Have you thought about JSON as a partial query placeholder, and all the native methods available for traversal, merging, plucking, etc.? For example, here is : { type: 'select', columns: [{ expression: 'orgJobs.org_job_code', alias: 'job_code' }], joins: [{ table: 'org_jobs', alias: 'orgJobs' }], limit: 100, offset: 0 } which yields: select orgJobs.org_job_code as job_code from org_jobs as orgJobs limit 100 offset 0 and so its easy to do things like: query.limit = 100; and of course: query.columns.push(additionalColumn); and use something like lodash/underscore (or equivalent for your language) to add collection/array/object helper methods so you never have to write for-loops for things like mapping and property plucking.

tracker1 12y ago

Stuff like your example is where, in general I prefer a sproc, or something more like the following... http://www.ivc.com/blog/better-sql-strings-in-io-js-nodejs-part-2/ http://www.ivc.com/blog/better-sql-strings-in-io-js-nodejs-p...

merb 12y ago

there is, it's called anorm. https://www.playframework.com/documentation/2.3.x/ScalaAnorm https://www.playframework.com/documentation/2.3.x/ScalaAnorm You don’t need another DSL to access relational databases SQL is already the best DSL for accessing relational databases. We don’t need to invent something new. Moreover the SQL syntax and features can differ from one database vendor to another.