Support adding additional options for DB connections in persist module and tooling

[daneshk]

Description: Currently, we only support the basic DB configuration in both tooling and persist runtime configuration. We need to add support to configure additional options currently supported by the MySQL connector[1]. This task is to discuss and implement this support

[database]
host = "localhost"
port = 3307
user = "${foo.tool_test_db_push_1.clients.root}"
password = "${tool_test_db_push_1.clients.password}"
database = "${foo.tool_test_db_push_1.clients.database}"

1. https://github.com/ballerina-platform/module-ballerinax-mysql/blob/master/ballerina/client.bal#L146

[kaneeldias]

We seem to have two options in adding support for this feature.

Option 1: Allow the user to configure all options as defined in the relevant SQL connector

In ballerinax/mysql we have the following possible connection configurations

public type Options record {|
    SecureSocket ssl?;
    FailoverConfig failoverConfig?;
    boolean useXADatasource = false;
    decimal connectTimeout = 30;
    decimal socketTimeout = 0;
    string serverTimezone?;
    boolean noAccessToProcedureBodies = false;
|};

We can allow the user to set the values for each of these options through configurable variables in Config.toml e.g.

[example.clients]
host = "localhost"
port = 3306
user = "root"
password = "Test123#"
database = "CONNECT_DB"
connectionOptions.connectTimeout = 50.0
connectionOptions.socketTimeout = 30.0

or

[example.clients]
host = "localhost"
port = 3306
user = "root"
password = "Test123#"
database = "CONNECT_DB"

[example.clients.connectionOptions]
connectTimeout = 50.0
socketTimeout = 30.0

With this option, we would directly pass these parameters when initializing the relevant DB client in the generated persist clients.

mysql:Client|sql:Error dbClient = new (host = host, user = user, password = password, database = database, port = port, options = connectionOptions);   

Option 2: Have a pre-determined set of configurable connection options

We would define a possible set of connection options that are common to all Ballerina SQL connectors and provide support for them. These configurations would be independent of the type of database used.

The user would use the Config.toml file to configure these options.

e.g.

[example.clients]
host = "localhost"
port = 3306
user = "root"
password = "Test123#"
database = "CONNECT_DB"
connectionOptions.timeout = 50.0

or

[example.clients]
host = "localhost"
port = 3306
user = "root"
password = "Test123#"
database = "CONNECT_DB"

[example.clients.connectionOptions]
timeout = 50.0

With this option, we would have to parse the provided configurations and pass them onto the DB client on initialization.

e.g.

// Util function in Persist module
public function getMySQLConnectionOptions(decimal? timeout) returns mysql:Options {
    mysql:Options options = {};

    if timeout != () {
        options.connectTimeout = <decimal>connectionTimeout;
    }

    // Set other connection options as well

    return options;
}

// In generated persist client (example with MySQL)
mysql:Options connectionOptions = getMySQLConnectionOptions(timeout); // Other connections options must also be passed
mysql:Client|sql:Error dbClient = new (host = host, user = user, password = password, database = database, port = port, options = connectionOptions);   

Evaluation

	Pros	Cons
Option 1	Can add support for all connection configurations allowed in the Ballerina SQL connectors with minimal effort. Future proof. (if a new connection config is added in the sql connector, persist module will automatically support it.	Not all connection configurations are relevant for the persist module. (e.g. noAccessToProcedureBodies in MySQL) Different Ballerina SQL connectors use different terminology for the same configuration. (e.g. connectTimeout in MySQL vs. loginTimeout in MSSQL) The user would have to refactor the configuration names in Config.toml were they to change databases. User would need to have an understanding of the underlying database and the configs associated with the relevant Ballerina SQL connector. (database layer would not be completely abstracted)
Option 2	User can migrate between different databases easily. (no need to rename the configs in Config.toml)	Support can only be added to a limited subset of connection configurations. (those supported by all the Ballerina SQL connectors) Not future proof. (If any new connection configs are added to the SQL connectors, the same would need to be added in Persist module)

[daneshk]

Thanks for the detailed explanation @kaneeldias

I prefer Option 01 as we don't need to do extra mapping at persist layer and do not need to worry about new configurations added to each connector. Even in prisma[1], the DB Url format defers from DB to DB, following each database's standards.

1. https://www.prisma.io/docs/reference/database-reference/connection-urls

@sameerajayasoma @anupama-pathirage @niveathika and others WDYT?