3 ms·

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

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

lmm 3y ago
> 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.
- preseinger 3y ago
  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
  - lmm 3y ago
    > 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/ 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.
    - preseinger 3y ago
      > 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.
      - lmm 3y ago
        > 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.

lmm 3y ago

> 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.

preseinger 3y ago
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
- lmm 3y ago
  > 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/ 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.
  - preseinger 3y ago
    > 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.
    - lmm 3y ago
      > 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.