FrontEnd - Design Flow for Adding New DB Connection and Trino Catalog Sync

The goal is to agree with the flow and to ensure we have the information needed in each request.

This flow starts when a user creates a new DB connection:

1. The Frontend collects the connection parameters based on the source type (Postgres, Mongo, etc.).

2. It calls the TrinoAPI endpoint to register the catalog. The payload should support several DB types so there are specific attributes for the different DBs.

This is the Trino Swagger UI for creation a catalog:

https://datamite-ds.iti.es/dbq/docs/#/Trino%20catalogs/post_trinocatalog_create This an example payload:

{
  "type": "postgres",
  "hostName": "localhost",
  "user": "trino",
  "password": 123,
  "port": 54323,
  "dbName": "trinodb",
  "catalogName": "postgres",
  "fileName": "postgres",
  "schema": "public"
}

3. It calls the Streaming endpoint with the connection configuration. Additionally, catalog information (e.g., the name) is required. This information can be returned by the TrinoAPI from Step 2, so it can later be used by TrinoAPI when executing queries.

StreamingAPI (pipeline), has implemented an endpoint to create a new database (datasource)

https://datamite-ds.iti.es/pipeline/docs#/data%20sources/create_data_source_data_sources__post

The payload supports multiple source types and is extensible. If additional information is required from Trino, the model can be updated, which is a straightforward change.

4. The final mandatory step is to restart the Trino instance. As discussed with Vasislis, in Valencia, I proposed an approach that can be implemented for a cleaner solution.

Here, we need to consider: What happens if we restart the instance while another user is running a query. But we can asume this at this point. Handle errors when restarting the Trino instance, since a raised error prevents the connection from being used in Trino.

/cc @hugopeixoto , @anacosta , @edgarloba , @vasisiop , @jordiarjona