3 ms·

I don't think JSON solves the problem, especially if you want users to be able to input complex queries, JSON will be a far worse experience. In his example, h

I don't think JSON solves the problem, especially if you want users to be able to input complex queries, JSON will be a far worse experience. In his example, he does say he is processing the input language and generating SQL via a library. I think that calling the input language SQL, in this case, is probably a red herring. It's a very simple language with a strict set of binary operators that happens to look like SQL. Like I said in another post, I do a similar thing for the product we make, but our input language is not SQL, but equally as expressive as the subset of SQL this blog post allows. I don't see an issue in failing to optimize queries. The database does query planning on the fly. You do have to be a bit careful that you don't generate queries that can DoS your DB, but all solvable and not drastically different than a developer adding a new query to the product. What we do does create a prepare statement on the fly. We process the input query and generate SQL and collect each value the user as input into arrays for the types the correspond to and then index those arrays. So, for example, the query "pr:123 and (user:foo or user:bar)" would turn to SQL like: "pull_request = ($1)[1] and (user = ($2)[1] or user = ($2)[2])" (this allows us to statically type our queries). In short, if your requirements that users should be able to generate complex queries, I think this is significantly better than JSON. And you can process an input language into JSON and the problem is effectively the same from that point.

Kwpolska 3y ago
For many applications, such as the bicycle shop in the examples, having users type in SQL or custom-language queries would be too difficult, considering most of the audience is not tech-savvy. Your user-friendly frontend UI would be happier to generate JSON instead of the custom textual format. Manually typing queries works for GitHub, but not much beyond it. Databases do query planning, but some of them cache query plans based on the SQL text before substituting arguments, and may optimise reoccurring queries more aggressively.
- sausagefeet 3y ago
  There are lots of applications where a query interface that looks like SQL is not valid and there are lots of applications where it is. There are also lots of applications where "some query language" that has a translation to SQL would be a perfectly pleasant human interface, including product search. Even if there are three applications, this blog post isn't saying every single problem has to be solved this way, so if you're one of the people solving one of those three problems, this could be a great solution for you. > Databases do query planning, but some of them cache query plans based on the SQL text before substituting arguments, and may optimise reoccurring queries more aggressively. Ok, and? Even if we go with your JSON solution, you still need to query the database at the end of the day, and your JSON query is not going to translate into the exact same SQL every time (unless you're doing very limited set of operations). I'm not sure what the real advice or information is here. Are you saying we should just never do some query language that translates to SQL at all? All SQL queries should just be dynamic on the input parameters? Even if your statement on query plan optimization is true, how do we know it matters for the product? Maybe slightly longer queries are totally fine in this context?

Kwpolska 3y ago

For many applications, such as the bicycle shop in the examples, having users type in SQL or custom-language queries would be too difficult, considering most of the audience is not tech-savvy. Your user-friendly frontend UI would be happier to generate JSON instead of the custom textual format. Manually typing queries works for GitHub, but not much beyond it. Databases do query planning, but some of them cache query plans based on the SQL text before substituting arguments, and may optimise reoccurring queries more aggressively.

sausagefeet 3y ago
There are lots of applications where a query interface that looks like SQL is not valid and there are lots of applications where it is. There are also lots of applications where "some query language" that has a translation to SQL would be a perfectly pleasant human interface, including product search. Even if there are three applications, this blog post isn't saying every single problem has to be solved this way, so if you're one of the people solving one of those three problems, this could be a great solution for you. > Databases do query planning, but some of them cache query plans based on the SQL text before substituting arguments, and may optimise reoccurring queries more aggressively. Ok, and? Even if we go with your JSON solution, you still need to query the database at the end of the day, and your JSON query is not going to translate into the exact same SQL every time (unless you're doing very limited set of operations). I'm not sure what the real advice or information is here. Are you saying we should just never do some query language that translates to SQL at all? All SQL queries should just be dynamic on the input parameters? Even if your statement on query plan optimization is true, how do we know it matters for the product? Maybe slightly longer queries are totally fine in this context?