3 ms·

I think the opposite is True: SQL is backward to a programming language. Creating statements by concatenating strings? An ORM makes real objects and types you c

I think the opposite is True: SQL is backward to a programming language. Creating statements by concatenating strings? An ORM makes real objects and types you can actually instantiate and use, or even type check.

rjmunro 7y ago
You don't create statements by concatenating strings. Use prepared queries with placeholders and pass values. Databases will compile the query once, then can run it many times. Sometimes you have to concatenate, e.g. to specify order by clauses. This is something database vendors need to fix. There need to be ways to tell the database to modify a prepared statement without changing it's SQL. You might be able to do it with a crazy IIF, but then it's likely not to use indexes well.
- aeyes 7y ago
  Prepared statements on anything but Oracle are only asking for trouble, if the distribution of your parameters does not match that of the query the statement was compiled with you can easily end up with bad query plans. Furthermore these issues are hard to analyze (no parameters in logs) and even harder to solve. I have hit this problem in just about any system using prepared statements. The performance overhead of query planning is minimal compared to the headaches it prevents.
- balfirevic 7y ago
  > You don't create statements by concatenating strings. > Sometimes you have to concatenate, e.g. to specify order by clauses. So, should I concatenate or not? > This is something database vendors need to fix. How long do you propose I wait before giving up and using a library that allows me to safely and sanely compose queries?
- Can_Not 7y ago
  How would you make a table or view that has: deleted_at TIMESTAMP created_at TIMESTAMP user_id INTEGER body TEXT all _optionally_ filterable (greater than, less than, like, equal, etc.) where applicable without an ORM, without string concatenation?
reificator 7y ago
> I think the opposite is True: SQL is backward to a programming language. Creating statements by concatenating strings? An ORM makes real objects and types you can actually instantiate and use, or even type check. That you view the situation as ORM or concatenation terrifies me. No wonder SQL injection remains so prevalent. Prepared statements are what you use here. You provide a query with placeholders for the values you want to fill in, and then you call that prepared statement with the arguments you need. These are typically type-checked and hardened against SQL injection, but of course you should verify with your particular language/library combination.
- anko 7y ago
  The problem is prepared statements aren't composable. So saying i want this query but also with this extra where in there and a limit and an offset means you have to write a whole new prepared statement. Or concatenate strings! With an ORM you can construct the prepared statements programmatically.
  - ekimekim 7y ago
    It sounds to me like there's a reasonable middle ground here, where you treat your SQL query more like an AST than a string. So you can do something like myquery = select("col1").where("col2 < {}", my_value), then later myquery.limit(10).offset(20). You're still working with SQL as a language, but you're manipulating it directly instead of trying to go via string formatting.
    - reificator 7y ago
      Sounds like you want a query builder. A quick search for "query builder golang" (We are on HN after all) provides the following library: https://github.com/fragmenta/query https://github.com/fragmenta/query An example from the README: // In your handlers, construct queries and ask your models for the data // Find a simple model by id page, err := pages.Find(1) // Start querying the database using chained finders q := page.Query().Where("id IN (?,?)",4,5).Order("id desc").Limit(30) // Build up chains depending on other app logic, still no db requests if shouldRestrict { q.Where("id > ?",3).OrWhere("keywords ~* ?","Page") }
takeda 7y ago
SQL is not compatible with programming languages because in programming language you are describing how to do something, while in SQL you are describing what the result supposed to be. What would be great to have first class support in the language (or perhaps IDE) that would understand database schema and database types so you could have error highlighting and autocompletion. Something like JOOQ for Java seems to do that. Seems like a good idea at the surface, though I don't currently program in Java and don't see anything similar in other languages, so I'm wondering what's wrong with it

rjmunro 7y ago

You don't create statements by concatenating strings. Use prepared queries with placeholders and pass values. Databases will compile the query once, then can run it many times. Sometimes you have to concatenate, e.g. to specify order by clauses. This is something database vendors need to fix. There need to be ways to tell the database to modify a prepared statement without changing it's SQL. You might be able to do it with a crazy IIF, but then it's likely not to use indexes well.

aeyes 7y ago
Prepared statements on anything but Oracle are only asking for trouble, if the distribution of your parameters does not match that of the query the statement was compiled with you can easily end up with bad query plans. Furthermore these issues are hard to analyze (no parameters in logs) and even harder to solve. I have hit this problem in just about any system using prepared statements. The performance overhead of query planning is minimal compared to the headaches it prevents.
balfirevic 7y ago
> You don't create statements by concatenating strings. > Sometimes you have to concatenate, e.g. to specify order by clauses. So, should I concatenate or not? > This is something database vendors need to fix. How long do you propose I wait before giving up and using a library that allows me to safely and sanely compose queries?
Can_Not 7y ago
How would you make a table or view that has: deleted_at TIMESTAMP created_at TIMESTAMP user_id INTEGER body TEXT all _optionally_ filterable (greater than, less than, like, equal, etc.) where applicable without an ORM, without string concatenation?

reificator 7y ago

> I think the opposite is True: SQL is backward to a programming language. Creating statements by concatenating strings? An ORM makes real objects and types you can actually instantiate and use, or even type check. That you view the situation as ORM or concatenation terrifies me. No wonder SQL injection remains so prevalent. Prepared statements are what you use here. You provide a query with placeholders for the values you want to fill in, and then you call that prepared statement with the arguments you need. These are typically type-checked and hardened against SQL injection, but of course you should verify with your particular language/library combination.

anko 7y ago
The problem is prepared statements aren't composable. So saying i want this query but also with this extra where in there and a limit and an offset means you have to write a whole new prepared statement. Or concatenate strings! With an ORM you can construct the prepared statements programmatically.
- ekimekim 7y ago
  It sounds to me like there's a reasonable middle ground here, where you treat your SQL query more like an AST than a string. So you can do something like myquery = select("col1").where("col2 < {}", my_value), then later myquery.limit(10).offset(20). You're still working with SQL as a language, but you're manipulating it directly instead of trying to go via string formatting.
  - reificator 7y ago
    Sounds like you want a query builder. A quick search for "query builder golang" (We are on HN after all) provides the following library: https://github.com/fragmenta/query https://github.com/fragmenta/query An example from the README: // In your handlers, construct queries and ask your models for the data // Find a simple model by id page, err := pages.Find(1) // Start querying the database using chained finders q := page.Query().Where("id IN (?,?)",4,5).Order("id desc").Limit(30) // Build up chains depending on other app logic, still no db requests if shouldRestrict { q.Where("id > ?",3).OrWhere("keywords ~* ?","Page") }

takeda 7y ago

SQL is not compatible with programming languages because in programming language you are describing how to do something, while in SQL you are describing what the result supposed to be. What would be great to have first class support in the language (or perhaps IDE) that would understand database schema and database types so you could have error highlighting and autocompletion. Something like JOOQ for Java seems to do that. Seems like a good idea at the surface, though I don't currently program in Java and don't see anything similar in other languages, so I'm wondering what's wrong with it