External Data Sources (EDS) - Complete Guide

Overview​

External Data Sources (EDS) are a cornerstone feature of the Softyflow platform, enabling your applications to seamlessly connect and interact with a diverse range of external systems and databases. By configuring an EDS, you can read, write, and manipulate data from existing enterprise systems, third-party APIs, cloud data warehouses, and more, all within the unified Softyflow development environment.

This guide provides a comprehensive walkthrough for configuring each supported EDS type, managing access rights across different environments, and adhering to best practices for security, performance, and maintainability.

Supported EDS Types​

Softyflow offers robust, out-of-the-box support for the following external data source types:

• Relational Databases: MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server
• Cloud Data Warehouses: Snowflake
• API Integrations: Fully customizable REST APIs
• Directory Services: LDAP (currently in Beta)

2. EDS Configuration by Type​

The following sections provide detailed instructions for configuring the specific connection parameters and advanced options for each supported data source. All configurations are managed within the Softyflow IDE by navigating to Resources → External Database (EDS).

2.1. MySQL​

MySQL is one of the world's most popular open-source relational database management systems, known for its reliability, performance, and ease of use.

Configuration Steps:

1. In the EDS section, create a new data source and select "MySQL" as the type.
2. Fill in the required connection parameters (Host, User, Password, Database) for each environment: TEST, UAT, and PROD.
3. (Optional) Configure advanced settings using a JSON object in the options field for finer control over the connection behavior.
4. Crucially, use the Test Connection button for each environment before saving to validate that the parameters are correct and the database is accessible.

The MySQL EDS configuration interface. This screen allows you to define distinct connection details for TEST, UAT, and PROD environments, ensuring a reliable and isolated development workflow.

Common Advanced Options: The JSON options field allows for advanced configuration to meet specific security or performance needs.

{
  "charset": "utf8mb4",
  "timezone": "Z",
  "ssl": {
    "rejectUnauthorized": true,
    "ca": "..."
  },
  "acquireTimeout": 30000,
  "timeout": 60000
}

• charset: Defines the character encoding. utf8mb4 is highly recommended to support a full range of Unicode characters, including emojis.
• timezone: Sets the database timezone (e.g., Z for UTC).
• ssl: Configures SSL for a secure, encrypted connection. It is critical to set rejectUnauthorized to true in production.
• acquireTimeout: The maximum time (in milliseconds) to wait for a connection from the pool.
• timeout: The maximum time (in milliseconds) a query is allowed to run before being aborted.

2.2. PostgreSQL​

PostgreSQL is a powerful, open-source object-relational database system renowned for its standards compliance, reliability, and extensive feature set.

Connection Parameters: The configuration for PostgreSQL mirrors that of MySQL, requiring the standard host, user, password, and database details.

The PostgreSQL connection parameter interface. Note the `options` field, which is used for advanced security and performance tuning.

Advanced Configuration Example: This JSON object configures a secure PostgreSQL connection with specific timeouts. The ssl object can disable rejection of unauthorized certificates, which is useful for development environments with self-signed certificates, but should be avoided in production.

{
  "ssl": {
    "rejectUnauthorized": false,
    "ca": "...",
    "key": "...",
    "cert": "..."
  },
  "statement_timeout": 30000,
  "query_timeout": 30000,
  "application_name": "Softyflow"
}

• statement_timeout: Aborts any statement that takes more than the specified number of milliseconds. This prevents long-running queries from impacting overall database performance.
• application_name: Sets a custom application name in PostgreSQL logs, making it easier to identify and debug Softyflow-related activities.

2.3. Oracle Database​

Oracle Database is a multi-model database management system widely used in large-scale enterprise environments.

Connection Parameters: Configuration requires a user, password, and a connectString. The options object can be used to configure the connection pool, which significantly improves performance by reusing established database connections.

The Oracle Database connection parameter interface, highlighting the `connectString` field used to identify the database instance.

Connect String Formats: Softyflow supports multiple standard Oracle connect string formats:

• Easy Connect / Easy Connect Plus: hostname:port/service_name
• Two-Part Naming (TNS) Format: `(DESCRIPTION=...)(CONNECT_DATA=(SERVICE_NAME=...)))

2.4. Microsoft SQL Server​

Microsoft SQL Server is a comprehensive relational database management system from Microsoft, offering a wide array of tools for data management, business intelligence, and analytics.

Connection Parameters: The configuration includes user, password, database, and the server address. The options object is particularly important for managing encryption and authentication.

The MS SQL Server connection parameter interface. The `options` field here enables encryption and certificate trust.

Alternative Authentication Methods: In addition to standard SQL authentication, Softyflow supports Windows Authentication (NTLM) and Azure Active Directory for seamless integration with Microsoft ecosystems.

• Windows Authentication (NTLM):

  {
    "domain": "YOUR_DOMAIN",
    "authentication": {
      "type": "ntlm",
      "options": {
        "userName": "username",
        "password": "password"
      }
    }
  }
  

• Azure Active Directory Password Authentication:

  {
    "authentication": {
      "type": "azure-active-directory-password",
      "options": {
        "userName": "user@yourdomain.com",
        "password": "password"
      }
    }
  }
  

2.5. Snowflake​

Snowflake is a modern, cloud-native data warehousing platform designed for high performance and scalability.

Connection Parameters: Configuration requires your Snowflake account identifier (e.g., xy12345.east-us-2.azure), username, and password. You can also specify the default database, warehouse, and schema to simplify queries.

The Snowflake connection parameter interface. Specify your account, credentials, and default compute/storage resources.

Key-Pair Authentication (for MFA): For enhanced security, especially when multi-factor authentication is enabled, you should use key-pair authentication. This method uses a privateKey and an optional passphrase instead of a password.

{
  "authenticator": "snowflake_jwt",
  "privateKey": "-----BEGIN ENCRYPTED PRIVATE KEY-----\n...\n-----END ENCRYPTED PRIVATE KEY-----",
  "passphrase": "your_key_passphrase"
}

2.6. API​

The API data source type is a versatile tool that allows you to integrate any RESTful API or web service into your Softyflow application. This is ideal for connecting to microservices, legacy systems, or third-party SaaS platforms like Stripe, Salesforce, or Google Maps. For more in-depth information, you can also consult the dedicated API documentation.

Basic Configuration: The main configuration establishes a baseURL for all requests, a global timeout, and a connectionUrl for health checks. You can also define global headers (e.g., for an API key) and a runScript that executes before any endpoint is called—perfect for tasks like dynamically fetching and caching an OAuth 2.0 authentication token.

The basic configuration for an API data source, where you set the root URL and global request settings.

Endpoint Configuration: Each API EDS can have multiple endpoints, which map standard data operations (like list, retrieve, update) to specific API calls. You define the HTTP method, URL value, headers, and parameters for each. A response script allows you to transform the incoming data to fit your application's data model.

Defining a 'retrieve' endpoint for an API. This example shows how to map a Softyflow operation to a `GET` request on a specific API URL.

Available Endpoint Types:

• retrieve: Fetch a single record by its ID.
• list: Fetch multiple records, with built-in support for pagination and filtering.
• count: Count the total number of records that match a query.
• insert: Create a new record.
• insertMany: Create multiple records in a single API call.
• update: Modify an existing record.
• delete: Remove a record.
• execute: A flexible type for custom operations that do not fit the standard CRUD (Create, Read, Update, Delete) model.

Available Context Variables: Within your endpoint configuration, you can use dynamic context variables to construct your requests, enabling powerful, data-driven integrations.

A list of the context variables available for building dynamic API requests in Softyflow.

• SF_source: An object containing the query parameters sent from the front-end widget or action (e.g., search terms, filters).
• SF_table: The name of the table or resource being queried.
• SF_postData: The data payload for POST, PUT, or PATCH operations.
• SF_limit: The maximum number of records to retrieve (for pagination).
• SF_sortby: The field name to sort the results by.
• SF_page: The current page number for pagination.
• SF_direction: The sort direction (1 for ascending, -1 for descending).

2.7. LDAP (Beta)​

LDAP (Lightweight Directory Access Protocol) integration allows Softyflow to connect to directory services like Microsoft Active Directory or OpenLDAP. This is useful for authenticating users or retrieving user information. Please note this feature is currently in Beta and may evolve.

Connection Parameters: Configuration requires the server URL, a bind DN (Distinguished Name) of a service account, and its password. The options object allows for fine-tuning, including tlsOptions to handle secure connections and self-signed certificates.

The LDAP connection parameter interface, used for integrating with directory services.

LDAPS (Secure LDAP) Configuration: For a secure connection over SSL/TLS, use the ldaps:// protocol in the URL and provide the necessary certificate authority (CA) information to ensure the server's identity is trusted.

{
  "URL": "ldaps://ldap.example.com:636/",
  "DN": "cn=service_account,ou=services,dc=example,dc=com",
  "password": "service_account_password",
  "options": {
    "tlsOptions": {
      "ca": ["-----BEGIN CERTIFICATE-----\n...\n-----END CERTIFICATE-----"],
      "rejectUnauthorized": true
    }
  }
}

3. Environment and Access Control​

3.1. Multi-Environment Setup​

Softyflow is architected to support a professional DevOps lifecycle by providing three distinct, isolated environments for each EDS configuration:

• TEST: Used for initial development and unit testing on a developer's local machine.
• UAT: A shared environment for User Acceptance Testing and quality assurance, closely mirroring production.
• PROD: The live production environment accessed by end-users.

This fundamental separation allows you to use different database credentials, API endpoints, or server addresses for each stage, creating a safe and reliable deployment pipeline from development to production.

3.2. Role-Based Access Control (RBAC)​

Securing your data is paramount. Softyflow enables granular control over data access by allowing you to define which user roles can perform specific actions (Create, Read, Update, Delete). Access rights are configured via a simple yet powerful JSON object that maps permissions to your defined user groups. For more details on creating and managing user roles, please see our Role Management documentation.

Defining role-based access rights for an EDS. In this example, 'Admins' have full CRUD access, while 'Viewers' can only read data.

Available Permissions:

• create: Allows users in the specified group to insert new records.
• read: Allows users to query and retrieve data.
• update: Allows users to modify existing records.
• delete: Allows users to remove records from the data source.

4. Integrating with Web Interface Widgets​

External Data Sources (EDS) are primarily leveraged to populate and interact with widgets in the Softyflow Web Modeler, enabling the creation of dynamic, data-driven user interfaces. This integration allows you to bind UI components directly to your external databases, providing a seamless flow of information between your systems and your Softyflow applications.

Configuration and Data Binding​

In the Web Modeler, widgets that support data binding (e.g., Selects, Tables, Autocompletes) can be configured to use an EDS as their data source. This is typically done through the widget's configuration panel, where you can select the desired EDS and specify the query or method to retrieve the data.

Configuring a "Category" dropdown to be populated from the "Test Mysql" EDS.

The binding process involves:

1. Selecting the Data Source: Choosing "EDS" as the data source type.
2. Specifying the Database: Selecting the pre-configured EDS connection (e.g., "Test Mysql").
3. Defining the Data Retrieval: Writing a SQL query or selecting a predefined method to fetch the required data. For example, a SELECT id, name FROM categories query could populate a dropdown with category options.

Common Use Cases​

• Dynamic Dropdowns: Populating Select widgets with options fetched from a database table, such as a list of countries, products, or user roles.
• Data Tables: Displaying records from an external database in a Table widget, with support for pagination, sorting, and filtering.
• Autocomplete Fields: Providing suggestions in text inputs by querying a database for matching results as the user types.
• Master-Detail Views: Creating interactive UIs where selecting an item in one widget (e.g., a customer in a list) dynamically updates another widget (e.g., a table showing that customer's orders).

This direct integration abstracts the complexity of data access, allowing developers to focus on building the user experience without writing extensive backend code for data retrieval.

For more detailed implementation examples and advanced configurations, please refer to the Usage in Widgets section of our development guide.

5. Testing the Connection​

Before saving any EDS configuration, it is essential to use the Test Connection button available for each environment (TEST, UAT, PROD).

• A successful test will display a confirmation message, indicating that Softyflow was able to establish a connection with the external system using the provided credentials and parameters.
• An unsuccessful test will return an error message. These messages are designed to be informative and are the first step in diagnosing the problem.

Common error messages include:

• "Connection timed out": Softyflow could not reach the server. This is typically a network issue.
• "Authentication failed" / "Invalid credentials": The username or password is incorrect.
• "Database not found": The server was reached, but the specified database does not exist.
• "SSL/TLS error": There is a problem with the security certificate configuration.

6. Security Best Practices​

6.1. Credential Management:​

Never hardcode credentials directly in configuration files. Use environment variables or a dedicated secrets management system (like HashiCorp Vault or AWS Secrets Manager) to handle sensitive information. Rotate passwords and API keys regularly.

6.2. Data Protection:​

Always enable encryption in transit (SSL/TLS) for all database and API connections, especially in UAT and PROD environments. Sanitize all user-supplied inputs on the server-side to prevent SQL injection and other injection attacks.

6.3. Access Control:​

Apply the principle of least privilege. Only grant the permissions that a user role absolutely needs to perform its functions. Regularly audit data access logs to monitor for suspicious activity.

7. Troubleshooting​

7.1. Network and Firewall Issues​

• Symptom: "Connection timed out" or "Cannot reach server".
• Solutions:
  • Verify Host and Port: Double-check that the server address/hostname and port are correct.
  • Firewall Rules: Confirm that there are no firewall rules on the Softyflow server, the database server, or any intermediate network device (like a cloud security group) that would block traffic on the specified port.
  • Ping/Telnet: From the machine running Softyflow, try to ping the database host or use telnet <host> <port> to check for basic network connectivity.

7.2. Credential and Permission Errors​

• Symptom: "Authentication failed," "Access denied," or similar credential-related errors.
• Solutions:
  • Check Credentials: Carefully verify the username, password, and any other authentication tokens (e.g., domain for NTLM).
  • User Permissions: Ensure the database user has the necessary permissions. For example, the user must have permission to connect to the database from the Softyflow server's IP address.
  • Password Expiration: Check if the service account's password has expired.

7.3. Query and API Endpoint Errors​

• Symptom: SQL syntax errors, API endpoint not found (404), or malformed request (400).
• Solutions:
  • External Testing: Isolate the issue by testing the exact query or API request using an external tool (like DBeaver for SQL or Postman/cURL for APIs). This helps determine if the problem is in the query itself or in the Softyflow configuration.
  • Verify Paths and Methods: For APIs, confirm the baseURL, endpoint path, and HTTP method are all correct. A common mistake is using GET instead of POST.
  • Check Parameters: Ensure that all required parameters, headers, and payload data are being sent in the correct format.

7.4. SSL/TLS and Certificate Issues​

• Symptom: "Untrusted certificate," "Certificate has expired," or "SSL handshake failed."
• Solutions:
  • Certificate Validity: Ensure the server's SSL certificate is valid, has not expir ed, and is issued by a trusted Certificate Authority (CA).
  • Self-Signed Certificates: For development environments using self-signed certificates, you may need to configure the connection to trust it (e.g., by setting rejectUnauthorized to false in the JSON options). This should never be done in production.
  • Full Certificate Chain: Some databases require the client to have the full certificate chain, including intermediate certificates. Ensure the ca property in your ssl options is configured correctly.

8. Performance Optimization​

8.1. Query Optimization:​

Ensure database tables have appropriate indexes on columns that are frequently used in WHERE, JOIN, and ORDER BY clauses. Use efficient queries, and always limit result sets with strict filtering and server-side pagination.

8.2. Connection Management:​

Configure connection pooling with appropriate size and timeout settings to balance performance and resource consumption. A properly tuned pool minimizes the overhead of establishing new connections.

8.3. Caching:​

For data that does not change often (e.g., a list of countries, product categories), implement a caching strategy to reduce the load on the external system and improve application responsiveness. Ensure you have a clear cache invalidation plan.

This comprehensive guide covers all aspects of EDS configuration in Softyflow.