This is asking users to generate SQL code client-side, parses the SQL server-side to make sure it's safe, and then generates new SQL for some reason. This seems needlessly complex, requiring more logic on the client side, and putting a lot of trust in the server-side validator. The examples also use non-parametrised SQL, which means the client needs to know how to correctly escape values, and the database might fail to optimise queries. I see no benefits and many disadvantages compared to a simple JSON structure, even if it's custom: it's trivial to generate and parse, it can be used with an ORM, and the server will probably use parametrised SQL, because that's easier than the alternative.
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.
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.
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?
'custom' part is the problem. Dealing with few vendor service APIs everyone has a different idea and syntax on how to implement simple AND, OR filter. Few weeks back spent half a day trying to figure out what the correct syntax is to do '<value> IN ...' query due to poor API documentation. Something more intuitive would be nice.
