External Data Sources (EDS) - Complete Guide

Overview​

External Data Sources (EDS) in Softyflow enable seamless integration with existing systems and databases. This comprehensive guide covers the configuration of all supported EDS types and their usage in actions and widgets.

Supported EDS Types​

Softyflow supports the following external data source types:

• Relational Databases: MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server
• Cloud Data Warehouses: Snowflake
• API Integrations: REST APIs with full customization
• Directory Services: LDAP (Beta)
• Additional Sources: Google Sheets (partial support) ??

EDS Configuration by Type​

1. MySQL Configuration​

MySQL is a popular open-source relational database management system.

Configuration Steps:

1. In the Softyflow IDE, navigate to Resources → External Database (EDS)
2. Select "MySQL" as the EDS type
3. Configure connection parameters for each environment (TEST, UAT, PROD)
4. Set advanced options in JSON format
5. Test the connection before saving

Common Options:

• charset: Character encoding (utf8mb4 recommended)
• timezone: Database timezone
• ssl: SSL configuration object
• acquireTimeout: Connection acquisition timeout
• timeout: Query execution timeout

Connection Parameters:

2. PostgreSQL Configuration​

PostgreSQL is a powerful open-source object-relational database system.

Connection Parameters:

Advanced Configuration:

This JSON object configures advanced settings for a PostgreSQL connection. It includes SSL options for secure connections, such as disabling unauthorized certificate rejection and providing certificate authority, key, and certificate content. It also sets timeouts for statements and queries to 30 seconds and names the application "Softyflow" for easier identification in database logs.

3. Oracle Database Configuration​

Oracle Database is an enterprise-grade relational database management system.

Connection Parameters:

This code provides the connection parameters for an Oracle Database. It specifies the user, password, and connectString needed to establish a connection. The options object configures the connection pool, setting the minimum and maximum number of connections, the increment step, and the pool timeout.

Connect String Formats:

• Simple: hostname:port/service_name
• TNS: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=service_name)))
• Easy Connect: hostname:port/service_name:server_type/instance_name

4. Microsoft SQL Server Configuration​

Microsoft SQL Server is a relational database management system developed by Microsoft.

Connection Parameters:

This snippet shows the connection parameters for a Microsoft SQL Server database. It includes the user, password, database, and server address. The options object enables encryption, trusts the server certificate (useful for self-signed certificates in development), and sets request and connection timeouts.

Authentication Options:

This code demonstrates two alternative authentication methods for SQL Server. The first part shows how to configure Windows Authentication (NTLM) by providing a domain and credentials. The second part illustrates how to connect using Azure Active Directory password-based authentication, which requires a username (in email format) and password.

// Windows Authentication
{
  "domain": "DOMAIN_NAME",
  "authentication": {
    "type": "ntlm",
    "options": {
      "userName": "username",
      "password": "password",
      "domain": "domain"
    }
  }
}

// Azure Active Directory
{
  "authentication": {
    "type": "azure-active-directory-password",
    "options": {
      "userName": "user@domain.com",
      "password": "password"
    }
  }
}

5. Snowflake Configuration​

Snowflake is a cloud-based data warehousing platform.

Connection Parameters:

This JSON object defines the connection parameters for a Snowflake data warehouse. It includes the account identifier (including the region), username, and password. Optional parameters like database, warehouse, and schema can be specified. The options object allows setting a user role, a timeout, and keeping the client session alive.

Multi-Factor Authentication:

This snippet configures key-pair authentication for Snowflake, which is a form of multi-factor authentication. It uses the snowflake_jwt authenticator and requires a privateKey and an optional passphrase to decrypt the key. This is a more secure alternative to password-based authentication.

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

6. API Configuration​

API EDS allows integration with REST APIs and web services.

Basic Configuration:

This code block shows the basic configuration for an API-based External Data Source. It sets a baseURL for all requests, a global timeout, and a connectionUrl to test the API's health. It also defines global headers and a runScript that executes before any endpoint is called, which is useful for tasks like fetching an authentication token. The endpoints array will contain the specific API call definitions.

Endpoint Configuration:

Each API EDS supports the following standard endpoints that map to database operations:

This snippet defines a single retrieve endpoint for an API EDS. It specifies the HTTP method (GET) and a dynamic value (URL) pattern that uses template variables like {{SF_table}} and {{SF_source._id}}. It also shows how to add endpoint-specific headers (e.g., for authorization) and params. Finally, the response script allows for transforming the data returned from the API call.

Available Endpoint Types:

1. retrieve: Get single record by ID
2. list: Get multiple records with pagination
3. count: Count records matching criteria
4. insert: Create new record
5. insertMany: Create multiple records
6. update: Update existing record
7. delete: Delete record
8. execute: Custom operations

Context Variables Available:

• SF_source: Data source and query parameters
• SF_table: Table/resource name
• SF_postData: Data for POST/PUT operations
• SF_limit: Maximum records to retrieve
• SF_sortby: Sort field name
• SF_page: Current page number
• SF_direction: Sort direction (1 or -1)

7. LDAP Configuration (Beta)​

LDAP (Lightweight Directory Access Protocol) integration for directory services.

Connection Parameters:

This code shows the connection parameters for an LDAP server. It specifies the server URL, the DN (Distinguished Name) for binding, and the password. The options object includes timeouts and tlsOptions to handle secure connections, in this case, allowing self-signed certificates.

LDAPS (Secure LDAP):

This snippet demonstrates how to configure a secure LDAP (LDAPS) connection. The URL uses the ldaps protocol. The tlsOptions are configured for enhanced security, requiring a trusted ca (Certificate Authority) and rejecting any unauthorized certificates.

{
  URL: "ldaps://ldap.example.com:636/",
  DN: "cn=service,ou=services,dc=example,dc=com",
  password: "service_password",
  options: {
    "tlsOptions": {
      "ca": ["certificate_content"],
      "rejectUnauthorized": true
    }
  }
}

Environment Configuration​

1. Multi-Environment Setup​

Softyflow supports three environments for each EDS:

• TEST: Development and testing environment
• UAT: User acceptance testing environment
• PROD: Production environment

Each environment can have different connection parameters, allowing for proper deployment workflows.

2. Configuration Best Practices​

Security:

• Use environment variables for sensitive data
• Enable SSL/TLS encryption where possible
• Implement proper authentication mechanisms
• Regularly rotate passwords and API keys

Performance:

• Configure appropriate connection pooling
• Set reasonable timeout values
• Optimize query patterns
• Monitor connection usage

Reliability:

• Test connections before deployment
• Implement retry mechanisms
• Configure proper error handling
• Monitor system health

Access Rights Management​

Role-Based Access Control​

Configure access rights for different user roles:

This JSON structure defines role-based access control for an EDS. It contains an array of roles. Each role links a user group (e.g., "Administrators") to a set of rights (create, read, update, delete). In this example, administrators have full permissions, while regular users can only read data.

Permission Types​

• create: Permission to insert new records
• read: Permission to query and retrieve data
• update: Permission to modify existing records
• delete: Permission to remove records

Best Practices​

1. Security Best Practices​

1. Credential Management:

   • Use environment-specific credentials
   • Regularly rotate passwords and API keys
   • Implement least-privilege access

2. Data Protection:

   • Enable encryption in transit (SSL/TLS)
   • Implement proper input validation
   • Sanitize query parameters

3. Access Control:

   • Configure role-based permissions
   • Audit data access patterns
   • Monitor for suspicious activity

2. Performance Optimization​

1. Query Optimization:

   • Use appropriate indexes on database tables
   • Limit result sets with proper filtering
   • Implement pagination for large datasets

2. Connection Management:

   • Configure appropriate connection pooling
   • Set reasonable timeout values
   • Monitor connection usage

3. Caching Strategies:

   • Cache static or slowly-changing data
   • Implement appropriate cache invalidation
   • Consider client-side caching for widgets

5.3. Error Handling​

1. Graceful Degradation:

   • Implement fallback mechanisms
   • Provide meaningful error messages
   • Log errors for debugging

2. Retry Logic:

   • Implement exponential backoff
   • Set maximum retry limits
   • Handle temporary failures

3. Monitoring:

   • Track EDS performance metrics
   • Monitor error rates and patterns
   • Set up alerting for critical failures

Troubleshooting Guide​

1. Connection Issues​

Problem: Cannot connect to database Solutions:

• Verify server address and port
• Check firewall and network settings
• Validate credentials and permissions
• Test from the same network as Softyflow

Problem: SSL/TLS connection errors Solutions:

• Verify SSL certificate validity
• Check SSL configuration options
• Update certificates if expired
• Disable SSL temporarily for testing

2. Query Issues​

Problem: SQL syntax errors Solutions:

• Validate SQL syntax for target database
• Check field names and table structure
• Use database-specific SQL features correctly
• Test queries directly in database tools

Problem: API endpoint not found Solutions:

• Verify API base URL and endpoint paths
• Check HTTP methods and parameters
• Review API documentation
• Test endpoints with external tools (Postman, curl)

3. Performance Issues​

Problem: Slow query performance

Solutions:

• Add appropriate database indexes
• Optimize query structure and joins
• Implement proper filtering and pagination
• Consider query result caching

Problem: High memory usage

Solutions:

• Reduce result set sizes
• Implement streaming for large datasets
• Optimize data transformation scripts
• Monitor and tune connection pools

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