3 ms·
> 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 toge
by lmm 3y ago
> 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.
- preseinger 3y agohuh? 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 agoi'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.