4 ms·

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 h

by 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") }
- deathanatos 7y ago
  SQLAlchemy has this. It sits alongside and works in tandem with the ORM, and I've used both in a projects (and also raw SQL), each where appropriate to the use case. https://docs.sqlalchemy.org/en/13/core/tutorial.html https://docs.sqlalchemy.org/en/13/core/tutorial.html
reificator 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! Absolutely, prepared statements would not be appropriate for that. > With an ORM you can construct the prepared statements programmatically. That's not limited to ORMs though. You can also just use a query builder. In fact I would argue that an ORM that provides that functionality is a query builder with additional ORM features on top.
marcus_holmes 7y ago
this is absolutely not true. You can use functions to create basic "getters" for application data structures. Layering these through further functions for limits/offsets/etc is simple (and you can write those layers in a generic way). You just have to get used to the mental mindshift that all your data manipulation code lives in the database, and your application simply consumes data from single function calls.

by anko 7y ago

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") }
deathanatos 7y ago
SQLAlchemy has this. It sits alongside and works in tandem with the ORM, and I've used both in a projects (and also raw SQL), each where appropriate to the use case. https://docs.sqlalchemy.org/en/13/core/tutorial.html https://docs.sqlalchemy.org/en/13/core/tutorial.html

reificator 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! Absolutely, prepared statements would not be appropriate for that. > With an ORM you can construct the prepared statements programmatically. That's not limited to ORMs though. You can also just use a query builder. In fact I would argue that an ORM that provides that functionality is a query builder with additional ORM features on top.

marcus_holmes 7y ago

this is absolutely not true. You can use functions to create basic "getters" for application data structures. Layering these through further functions for limits/offsets/etc is simple (and you can write those layers in a generic way). You just have to get used to the mental mindshift that all your data manipulation code lives in the database, and your application simply consumes data from single function calls.