Skip to main content

External Data Source

1. EDS Description​

Data storage plays a crucial role in SoftyFlow as it enables the persistent storage of information necessary for the functioning and operations of the platform. SoftyFlow incorporates MongoDB as its internal database management system, while also leveraging distinct external data source to enhance the performance of data storage. Additionally, SoftyFlow offers robust access control mechanisms, allowing administrator to manage and regulate access rights to the external data sources.

To access the External Data Source interface, you can find it by navigating to the "External Data Source (EDS)" tab, located in the top left pane, or simply navigate to the path /ide/database within the IDE

EDS

SoftyFlow encompasses popular database management systems such as MySQL, PostgreSQL, Oracle, Microsoft SQL Server, and other data sources as API, LDAP (beta), and Snowflake.

EDS

2. EDS Types​

2.1. MySQL​

MySQL is a popular relational database management system (RDBMS) used for storing, organizing, and managing data.

EDS

To connect to a MySQL database, you need to provide the following connection details:

  • host (Server address): This is the IP address or hostname of the MySQL server you want to connect to. For example, "localhost" for a local database or a specific IP address for a remote database. This construction can include the concatenation of Ip adress and port number because the port may have been modified (Example: myIpAdresse:PortNumber).

  • user (Username): This is the username used to connect to the MySQL database. The username should have the necessary privileges to access the specified database.

  • password: This is the password associated with the username for authentication. Make sure to provide the correct password corresponding to the specified username.

  • database: This is the name of the database you want to access once connected. Make sure this database exists on the MySQL server.

  • options : You can also specify additional connection options such as character encoding, password validation level, error handling mode, etc. These options may vary depending on the libraries or tools you are using to connect to MySQL.

It is important to note that when accessing a remote MySQL database, you need to ensure that the MySQL server allows remote connections and that firewall settings allow communication on the port used by MySQL.

2.2. PostgreSQL​

PostgreSQL is a powerful open-source relational database management system (RDBMS). It offers many advanced features and is known for its reliability, robustness, and compliance with SQL standards.

EDS

To connect to a PostgreSQL database, you need to provide the following connection details:

  • user (Username): This is the username used to connect to the PostgreSQL database. The username should have the necessary privileges to access the specified database.

  • host (Server address): This is the IP address or hostname of the PostgreSQL server you want to connect to. For example, "localhost" for a local database or a specific IP address for a remote database.

  • database: This is the name of the database you want to access once connected. Make sure this database exists on the PostgreSQL server.

  • password: This is the password associated with the username for authentication. Make sure to provide the correct password corresponding to the specified username.

  • Port: By default, PostgreSQL uses port 5432. However, the port may have been modified on the PostgreSQL server you want to connect to.

  • options : You can also specify additional connection options such as SSL Mode, Connection Timeout, etc. The specific options and syntax for specifying these additional parameters may vary depending on the programming language or library you are using to connect to PostgreSQL.

Make sure you have the appropriate privileges and correct connection information to establish a successful connection to a PostgreSQL database.

2.3. Oracle Database​

Oracle is a widely used relational database management system (RDBMS) known for its scalability, security, and extensive feature set.

EDS

To connect to an Oracle database, you need to provide the following connection details:

  • user (Username): This is the username you want to use to connect to the Oracle database. The username must exist in the database and have the necessary privileges to access the desired objects.

  • password: This is the password associated with the specified username for authentication.

  • connectString: This construct comprises the concatenation of the IP address or hostname of the machine hosting the Oracle database, followed by the port number and the name of the specific database (Example: myIpAdresse:1521/DBName).

2.4. Microsoft SQL Server​

Microsoft SQL Server is a relational database management system (RDBMS) developed by Microsoft. It is widely used in various applications and offers a range of features for data storage, retrieval, and management.

EDS

To connect to a Microsoft SQL Server database, you need to provide the following connection details:

  • user (Username): If you are using SQL Server Authentication, you need to provide the username associated with the SQL Server instance.

  • password: If you are using SQL Server Authentication, you need to provide the corresponding password for the specified username.

  • database: This is the name of the specific database within the SQL Server instance that you want to connect to.

  • server: This is the IP address or hostname of the Microsoft SQL Server server you want to connect to. For example, "localhost" for a local database or a specific IP address for a remote database. This construction can include the concatenation of Ip adress and port number because the port may have been modified (Example: myIpAdresse:PortNumber).

  • options: In addition to the basic connection details, SQL Server allows for additional connection parameters and options, such as Connection timeout, Encrypting the connection, ect.

2.5. Api​

An API is a set of rules and protocols that enables different software applications to communicate and interact with each other. APIs are used to facilitate integration and data exchange between different platforms, services, and systems.

EDS

To connect to an API, you typically need the following:

  • Base URL (optional): This is the URL that identifies the location where the API is available. The URL specifies the server address and optionally the path to access a specific API endpoint.

  • Timeout (ms): The maximum amount of time that the API will wait for a response from the server before considering the request as failed.

  • Endpoints: Initially, delineate the frequently employed request methods (GET, POST, PUT, PATCH, PURGE, and DELETE) along with the path leading to a specific API endpoint (Example: /users or /SF_source.source for dynamic use).

  • Headers : Are additional metadata included in an HTTP request or response. They provide additional information about the request, such as authentication information, accepted data formats, ect. Here headers are specific to each method.

You can set a global header in "Edit Header", which will be applied on all methods.

  • Params: These parameters are used to filter, sort, or limit the query results. Query parameters are typically added to the request URL and follow a specific syntax. Expected a JSON with JS.

Different variables are available in this context: 

SF_source : contains data source and query parameters if submiteds ({source: ... , query1: , query2: ..}).
SF_postData : post or update data.
SF_limit : Max limit element to retieve in table.
SF_sortby : Sort By element.
SF_page : Current Page.
SF_direction: Sorting Direction.
  • Response: It is possible to structure and arrange the format of the API response according to specific requirements and standards. Expected a JS return Function. SF_result is a variable available in this context, represents the data that has been received from the API.

Run Script : This feature provides the capability to execute scripts prior to invoking the API, enabling the retrieval of the authentication key or other relevant information

EDS

2.6. LDAP (Beta)​

LDAP (Lightweight Directory Access Protocol) is an open and widely used protocol for accessing and maintaining directory services over a network. It is commonly used for managing user authentication, storing organizational data, and providing directory information.

EDS

LDAP connections require the following parameters to establish a connection to an LDAP server:

  • URL: This construction can include the concatenation of the address of the LDAP server and port number (Example: ldap://myIpAdresse:PortNumber/).

  • DN: The Base DN represents the starting point in the LDAP directory tree from which to begin the search or the location where the data is stored. It typically looks like a hierarchical path, such as "dc=example,dc=com".

  • password: If the LDAP server requires authentication, you need to provide the password.

  • Options: LDAP is highly extensible and allows for a wide range of configuration options and customizations. Some additional parameters and settings you may encounter include such as Timeouts, Referrals, ect.

2.7. Snowflake​

Snowflake is a cloud-based data warehousing platform designed for analytics and business intelligence.

EDS

To connect to Snowflake, you need to provide the following connection details:

  • account (Account Name): This is the unique identifier for your Snowflake account. It is typically in the format "account_name.region".snowflakecomputing.com. The account name is provided by Snowflake when you create an account.

  • username: This is the username associated with your Snowflake account. It is used for authentication and determines the access privileges for the user.

  • password: The password is required to authenticate the provided username.

  • database: Snowflake databases are used to logically group objects such as tables, views, and stored procedures. If you want to connect to a specific database, you can provide its name.

  • warehouse: Snowflake uses warehouses to allocate resources for executing queries. You can specify a specific warehouse to use or let Snowflake automatically assign one.

  • schema: A schema is a logical container within a database that holds database objects. If you want to connect to a specific schema within a database, you can specify its name.

  • Option: Snowflake also supports additional connection options and settings, such as Connection String Options, Multi-Factor Authentication (MFA), ect.

3. Test Connexion​

To verify the connectivity with the external database, you can employ the 'Test Connection' button to execute a connectivity test.

4. EDS Access Rights​

Managing access rights to databases involves controlling the creation, modification, reading, and deletion of data, ensuring security and privacy. It allows the administration of user actions on a database, specifying their ability to create, modify, read, and delete data.

EDS

Select the role or user group for which you wish to enforce access management actions. Subsequently, designate the actions that are permissible for authorization.

  • create: The creation permission allows a user to create new databases, tables, views, indexes, or other objects within the database. It also includes the ability to create new users or roles within the database management system.

  • read: The reading permission allows a user to retrieve existing data from the database. They can execute queries to extract information from tables or views but are not permitted to modify or delete data.

  • update: The modification permission enables a user to update or modify existing data within the database. This includes changing field values in a table, adding or deleting rows, or modifying the structure of the database itself.

  • delete: The deletion permission grants a user the ability to remove specific data from the database. This can involve deleting rows or records from a table but does not affect the overall structure of the database.