How to express SQL queries in yaml format?

Question

I am experimenting with some random idea of end-user expressing a query in yaml format. This yaml file would be fed into some intermediate transformer to convert yaml to SQL statement.

Following is sample yaml snippet, but when I think of having conditions, expressions, subqueries, joins. And the yaml becomes more complex as queries tends to be more complex.

Question: how to define a SQL query in yaml format and yet cover all the scenarios? (Are there any existing libraries that can do this kind of magic)

table:
    schema: test
    name: test_table
  select:
    columns:
      - column: * # retrieve all columns     
  where:
    columns:
      - column: user_id
      - column: customer_id
  limit:
    value: 10
  # groupBy
  # orderBy

This might quickly get more complicated than you may think. Representing a SQL query as a data structure is far from trivial. — GMB, Mar 04 '20 at 16:30
I cannot answer your question, but are you sure this is a good idea? What goal do you have, letting users query your data? Then why not through SQL? SQL can be taught using external resources; your YAML-to-SQL translation layer won't be documented anywhere and will never have the expressive power that SQL has. So again, why? Do you think this is easier for the users? — CodeCaster, Mar 04 '20 at 16:32
I am envisioning this as a static query. Means once defined it can't be changed. Eg: Select * from users where user_id = 'abc' limit 10 ; — Mozhi, Mar 04 '20 at 17:14
How would you express queries [like this](https://stackoverflow.com/a/60540534/330315) or [this](https://stackoverflow.com/a/60544131/330315) or [this](https://stackoverflow.com/a/60487116/330315) in Yaml? — , Mar 05 '20 at 11:17
Yeah you are right , the more complex the query becomes less readable and complex to define such things in yaml. Agreed. — Mozhi, Mar 05 '20 at 12:38
@Mozhi - You should definitely checkout SQLAthanor - https://sqlathanor.readthedocs.io/en/latest/api.html#sqlathanor.schema.Table.from_yaml — kaizer1v, Sep 23 '20 at 12:53

score 4 · Accepted Answer · answered Mar 05 '20 at 10:44

The answer to your question is: You serialize the syntax tree of the SQL statement to YAML.

Have a quick look at pg_query to get an impression of how complex this tree looks even for simple queries (this tool uses Postgres' SQL parser to generate a syntax tree of an SQL statement). This complexity is required for covering all the scenarios.

Now you can of course try and simplify this structure in a way that it retains all information necessary to reconstruct the original syntax tree. And this is exactly what the SQL query language does! No user wants to write the explicit structure down when they can use a well-defined and well-supported syntax for expressing the query.

You would basically create an entirely new, YAML-based language somewhere between the syntax tree and the original SQL language. Using it will require the user to know both YAML and the semantics of your structure. So if your goal is to enhance user-friendliness, this is very unlikely to be a way of achieving it.

How to express SQL queries in yaml format?

1 Answers1