RFC: Visual editor for SQL queries

[Janpot]

Summary

Bring a simpler workflow to Toolpad for writing queries for postgres and MySql databases.

Motivation

#1952

Design

Add another option when creating queries. Name it "PostgreSQL query". After choosing it, an editor opens, similar to the "HTTP request" one.

At the top the user can choose the PostgreSQL datasource in an autocomplete. If none is available, they get the option to create a new data source.

Connection editor

A dialog for adding a postgres connection to Toolpad. In the file system, connection consists of two parts:

1. The connection definition in the resources folder:

   # resources/myPgConnection.yml
   kind: 'connection'
   apiVersion: '1',
   spec:
     kind: 'postgresql'
     connectionString: MY_PG_CONNECTION_STRING
     ssh: MY_PG_SSH_KEY

2. The .env file

   # .env
   MY_PG_CONNECTION=postgres://postgres:[email protected]:5432/dummy
   MY_PG_SSH_KEY=...

The dialog presents fields for user/password/host/port/... and a "test connection" button. Hitting the "save" button stores all the parameters in the resource and .env files. We can give the option for choosing your own env variable name. We should always avoid overwriting existing env variables without prior user consent.

query editor

All connections under resources that are of the postgresql kind are available in the connection dropdown. The query editor itself has a code editor for SQL queries and a preview table.

parameter interpolation

We could support mustache to inject parameters in SQL but with the following restrictions:

• Interpolations will be replaced with parameters $0, $1 to avoid unwanted SQL injection. This means that

  SELECT * FROM my_table WHERE my_table.column = {{mySelect.value}};

  first gets translated to

  SELECT * FROM my_table WHERE my_table.column = $0;

  and passed to the db as such. There will be no string replacement of interpolations in queries. i.e. we won't translate that into

  SELECT * FROM my_table WHERE my_table.column = "foo";

  Note this means that not all interpolation is possible. i.e. the following is invalid:

  SELECT * FROM {{myTableSelect.value}}

• the SQL query itself won't be available anywhere in the runtime on the browserside. The query is stored serverside and only parameters are transferred when executing it.

• The query gets stored in the page as

    queries:
    - name: query1
      query:
        kind: postgres
        connection: myPostgres
        sql: |
          SELECT * FROM my_table WHERE my_table.column = $0;
      parameters:
        - name: $0
          value:
            $$jsExpression: |
              "query1_value"

To simplify an initial implementation, we could opt for starting out without mustache support and instead add a parameters section as in the pre-13 implementation.

The rest of the query works analog to the rest queries

Trade-offs

• The mustache support may look like it gives you extra freedom, but it doesn't unlock anything that wasn't possible before with explicitly defined parameters. The queries will be harder to debug since what's running on the db will be different from what's in your text editor and the error messages won't always align with your expectations.

Alternatives

As an alternative we we could work towards a concept that is better integrated with the custom functions and which progressively allows ejecting queries to code files

[prakhargupta1]

The query editor itself has a code editor for SQL queries and a preview table.

Instead of writing the query in the editor, we should create a .SQL (and a .ts) file for the user in the IDE and let them write it there.
I think it would be a better UX if we let the users manage their resources themselves.

When this query is executed, we'll show the output in a preview table.
Related issue: #1973