Hacker News Reader

Comment by lmm | Hacker News Reader

> it's pretty rare for queries to be dynamically composed from arbitrary sub-queries

I'm talking static, not dynamic. You still need to compose two pieces together into a single query, and you can either use an ORM to help with that or not.

> the interface between the application and the DB is actually a string! it's not an abstract data type, it doesn't benefit from being modeled by types

No it isn't. You can't send an arbitrary string to the database and expect it to work. At the very least you benefit from having an interface that's structured enough to tell you whether your parentheses are balanced and your quotes are matched rather than having to figure that out at runtime.

View on HN

huh?

when your app queries the db, the query is not composed from several pieces, it is well-defined in the relevant method

    fn search(q string) -> result
        return db.query(`SELECT id, text FROM table WHERE text LIKE $1;`, q)

this is a single query, not multiple

the db accepts a string and parses it to an AST, it does not accept a typed value

this means the interface is the string

unbalanced parens and whatever other invalid syntax is obviously caught by tests

> when your app queries the db, the query is not composed from several pieces, it is well-defined in the relevant method

> this is a single query, not multiple

And when you want to query for multiple related things together, the whole point of having a relational database? For different purposes you need different views on your data, and those views are generally constructed out of a bunch of shared fragments; you can either figure out a way to share them, or copy-paste them everywhere you use them.

> the db accepts a string and parses it to an AST, it does not accept a typed value

> this means the interface is the string

The DB accepts a structured query, not a string. It might be represented as a string on the wire, but if that was what mattered then we'd use byte arrays for all our variables since everything's a byte array at runtime.

> unbalanced parens and whatever other invalid syntax is obviously caught by tests

Tests are a poor substitute for types.

i'm not sure what you're thinking about when you say "multiple related things"

every "view" on your DB should be modeled as a separate function

every possible "thing" that's input to a function which queries the database should be transformed into a part of the query string by that function

> The DB accepts a structured query, not a string. It might be represented as a string on the wire, but if that was what mattered then we'd use byte arrays for all our variables since everything's a byte array at runtime.

...no

the API literally receives a string and passes it directly to the DB's query parser

if the DB accepted structured queries, then the API would rely on something like protobuf to parse raw bytes to native types -- it doesn't

like `echo SELECT * FROM whatever; | psql` does not parse the `SELECT * FROM whatever;` string to a structured type, it sends the string directly to the database

> every "view" on your DB should be modeled as a separate function

OK, and when a significant amount of what those functions do is shared, how do you share it? (E.g. imagine we're building, IDK, some kind of CMS, and in one view we have authors (based on some criteria) and posts by those authors, and in another we have tags and posts under those tags, and in another we have date ranges and posts in that date range. How do you share the "fetching posts" bit of SQL between those three different (parameterized) queries?)

> if the DB accepted structured queries, then the API would rely on something like protobuf to parse raw bytes to native types -- it doesn't

> like `echo SELECT * FROM whatever; | psql` does not parse the `SELECT * FROM whatever;` string to a structured type, it sends the string directly to the database

In both cases the parsing happens on the server, not the client. "echo abcde | psql" and "curl -D abcde http://my-protobuf-service/" are both doing the same kind of thing - passing an unstructured string to a server which will fail to parse it and give some kind of error - and both equally useless.

> How do you share the "fetching posts" bit of SQL between those three different (parameterized) queries?)

your application has a fetch posts method, that method takes input including (optional) author(s), tag(s), etc., it builds a query that includes WHERE clauses for every provided parameter

the code that converts an author to a WHERE clause can be a function, the point is it outputs a string, or something that is input to a builder and results in a string

i'm not sure what a "fetching posts bit of SQL" is, a query selects specific rows, qualified by where clauses that filter the result set, joins that modify it, etc.

> the code that converts an author to a WHERE clause can be a function, the point is it outputs a string, or something that is input to a builder and results in a string

So you do string->string processing, and you explicitly won't treat the queries you're generating as structured values? Enjoy your rampant SQL injection vulnerabilities.

no?

creating a query string that's parameterized on input usually means you model those input parameters as `?` or `$1` or whatever, and provide them explicitly as part of the query

nobody is doing printfs of values

Ok so now your WHERE clauses are no longer strings, you have to have some structured representation of them that knows which parameters go with which clauses, and something that understands the structure of your queries enough to line up the parameters when you stitch together different WHERE clauses to make your full query - exactly the kind of thing you were saying was unnecessary.

Reply on news.ycombinator.com