Skip to main content

External Data Sources (EDS) - Complete Configuration and Usage Guide

1. 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)

2. EDS Configuration by Type​

2.1. MySQL Configuration​

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

Connection Parameters:

{
  host: "localhost:3306",          // Server address with optional port
  user: "username",                // Database username
  password: "password",            // Database password
  database: "database_name",       // Target database name
  options: {
    "charset": "utf8mb4",
    "timezone": "+00:00",
    "acquireTimeout": 60000,
    "timeout": 60000
  }
}

Configuration Steps:

  1. Navigate to EDS management in SoftyFlow IDE
  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

2.2. PostgreSQL Configuration​

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

Connection Parameters:

{
  user: "username",                // Database username
  host: "localhost",               // Server hostname/IP
  database: "database_name",       // Target database name
  password: "password",            // Database password
  port: 5432,                      // PostgreSQL port (default: 5432)
  options: {
    "ssl": false,
    "connectionTimeoutMillis": 30000,
    "idleTimeoutMillis": 30000,
    "max": 10
  }
}

Advanced Configuration:

{
  "ssl": {
    "rejectUnauthorized": false,
    "ca": "certificate_content",
    "key": "private_key_content",
    "cert": "certificate_content"
  },
  "statement_timeout": 30000,
  "query_timeout": 30000,
  "application_name": "SoftyFlow"
}

2.3. Oracle Database Configuration​

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

Connection Parameters:

{
  user: "username",                        // Oracle username
  password: "password",                    // Oracle password
  connectString: "localhost:1521/XEPDB1", // Connection string format
  options: {
    "poolMin": 1,
    "poolMax": 10,
    "poolIncrement": 1,
    "poolTimeout": 60
  }
}

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

2.4. Microsoft SQL Server Configuration​

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

Connection Parameters:

{
  user: "username",                // SQL Server username
  password: "password",            // SQL Server password
  database: "database_name",       // Target database
  server: "localhost:1433",        // Server with optional port
  options: {
    "encrypt": true,
    "trustServerCertificate": true,
    "requestTimeout": 30000,
    "connectionTimeout": 30000
  }
}

Authentication Options:

// 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"
    }
  }
}

2.5. Snowflake Configuration​

Snowflake is a cloud-based data warehousing platform.

Connection Parameters:

{
  account: "account_name.region",  // Snowflake account identifier
  username: "username",           // Snowflake username
  password: "password",           // Snowflake password
  database: "database_name",      // Target database (optional)
  warehouse: "warehouse_name",    // Compute warehouse (optional)
  schema: "schema_name",          // Target schema (optional)
  options: {
    "role": "role_name",
    "timeout": 60000,
    "clientSessionKeepAlive": true
  }
}

Multi-Factor Authentication:

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

2.6. API Configuration​

API EDS allows integration with REST APIs and web services.

Basic Configuration:

{
  baseURL: "https://api.example.com",      // Base API URL (optional)
  timeout: 30000,                          // Request timeout in milliseconds
  connectionUrl: "/health",                // Test connection endpoint
  headers: {                               // Global headers
    "Content-Type": "application/json",
    "Accept": "application/json"
  },
  runScript: `                             // Pre-execution script
    // Fetch authentication token
    const response = await SF.utils.axios.get('/auth/token', {
      headers: { 'X-API-Key': 'your-api-key' }
    });
    return { 
      authToken: response.data.token 
    };
  `,
  endpoints: [                             // API endpoint definitions
    // ... endpoint configurations
  ]
}

Endpoint Configuration:

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

{
  type: "retrieve",                        // Operation type
  method: "GET",                           // HTTP method
  value: "/{{SF_table}}/{{SF_source._id}}", // URL pattern
  headers: {                               // Endpoint-specific headers
    "Authorization": "Bearer {{authToken}}"
  },
  params: {                                // Query parameters
    "include": ["relations"],
    "fields": "{{SF_source.fields}}"
  },
  response: `                              // Response transformation
    return SF_result.data;
  `
}

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)

Example Complete API Configuration:

{
  baseURL: "https://jsonplaceholder.typicode.com",
  timeout: 30000,
  connectionUrl: "/posts/1",
  headers: {
    "Content-Type": "application/json"
  },
  runScript: `return { timestamp: Date.now() }`,
  endpoints: [
    {
      type: "retrieve",
      method: "GET",
      value: "/{{SF_table}}/{{SF_source._id}}",
      headers: "{}",
      params: "{}",
      response: "return SF_result"
    },
    {
      type: "list",
      method: "GET", 
      value: "/{{SF_table}}",
      headers: "{}",
      params: `{
        _page: SF_page,
        _limit: SF_limit,
        _sort: SF_sortby,
        _order: SF_direction === 1 ? 'asc' : 'desc'
      }`,
      response: "return SF_result"
    },
    {
      type: "insert",
      method: "POST",
      value: "/{{SF_table}}",
      headers: "{}",
      params: "SF_postData",
      response: "return SF_result"
    }
  ]
}

2.7. LDAP Configuration (Beta)​

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

Connection Parameters:

{
  URL: "ldap://localhost:389/",           // LDAP server URL
  DN: "cn=admin,dc=example,dc=com",       // Bind Distinguished Name
  password: "admin_password",              // Bind password
  options: {
    "timeout": 30000,
    "connectTimeout": 30000,
    "tlsOptions": {
      "rejectUnauthorized": false
    }
  }
}

LDAPS (Secure LDAP):

{
  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
    }
  }
}

3. Environment Configuration​

3.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.

3.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

4. Access Rights Management​

4.1. Role-Based Access Control​

Configure access rights for different user roles:

{
  roles: [
    {
      group: { _id: "admin", name: "Administrators" },
      rights: {
        create: true,
        read: true,
        update: true,
        delete: true
      }
    },
    {
      group: { _id: "users", name: "Regular Users" },
      rights: {
        create: false,
        read: true,
        update: false,
        delete: false
      }
    }
  ]
}

4.2. 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

5. Using EDS in Actions​

5.1. Available EDS Actions​

Insert Operations​

insertTable (EDS)

{
  action: "insertTable (EDS)",
  parameters: {
    clientDatabase: { _id: "my_eds", name: "My Database" },
    table: "users",
    statement: {
      name: "John Doe",
      email: "john@example.com",
      created_at: new Date()
    },
    validationSchema: {
      name: "string().required()",
      email: "string().email().required()"
    }
  }
}

insertMany (EDS)

{
  action: "insertMany (EDS)",
  parameters: {
    clientDatabase: { _id: "my_eds", name: "My Database" },
    table: "products", 
    data: [
      { name: "Product 1", price: 29.99 },
      { name: "Product 2", price: 39.99 }
    ]
  }
}

Query Operations​

findOne (EDS)

{
  action: "findOne (EDS)",
  parameters: {
    dumpVar: "selectedUser",
    clientDatabase: { _id: "my_eds", name: "My Database" },
    table: "users",
    find: { id: "{{userId}}" }
  }
}

findMany (EDS)

{
  action: "findMany (EDS)",
  parameters: {
    dumpVar: "userList",
    clientDatabase: { _id: "my_eds", name: "My Database" },
    table: "users",
    find: { 
      status: "active",
      department: "{{selectedDepartment}}"
    }
  }
}

Update Operations​

updateTables (EDS)

{
  action: "updateTables (EDS)",
  parameters: {
    clientDatabase: { _id: "my_eds", name: "My Database" },
    table: "users",
    findTable: { id: "{{userId}}" },
    updatedTable: {
      last_login: new Date(),
      status: "active"
    }
  }
}

Delete Operations​

delete (EDS)

{
  action: "delete (EDS)",
  parameters: {
    clientDatabase: { _id: "my_eds", name: "My Database" },
    table: "temp_data",
    find: { 
      created_at: { "$lt": "{{cutoffDate}}" }
    }
  }
}

Advanced Operations​

executeStatement (EDS)

{
  action: "executeStatement (EDS)",
  parameters: {
    dumpVar: "queryResult",
    clientDatabase: { _id: "my_eds", name: "My Database" },
    statement: `
      SELECT u.name, u.email, COUNT(o.id) as order_count
      FROM users u
      LEFT JOIN orders o ON u.id = o.user_id
      WHERE u.created_at >= '{{startDate}}'
      GROUP BY u.id, u.name, u.email
      ORDER BY order_count DESC
    `
  }
}

countTables (EDS)

{
  action: "countTables (EDS)",
  parameters: {
    dumpVar: "totalUsers",
    clientDatabase: { _id: "my_eds", name: "My Database" },
    table: "users",
    find: { status: "active" }
  }
}

5.2. Action Usage Examples​

Form Submission with EDS​

// Event: onSubmit
[
  {
    action: "validateForm",
    parameters: {
      attachForm: { formRef: "userForm" },
      scroll: true,
      errorList: true
    }
  },
  {
    action: "insertTable (EDS)",
    parameters: {
      clientDatabase: { _id: "crm_db", name: "CRM Database" },
      table: "customers",
      attachForm: { formRef: "userForm" },
      validationSchema: {
        name: "string().required()",
        email: "string().email().required()",
        phone: "string().optional()"
      }
    }
  },
  {
    action: "alertMessage",
    parameters: {
      type: "success",
      message: "Customer created successfully!"
    }
  },
  {
    action: "clearForm",
    parameters: {
      attachForm: { formRef: "userForm" }
    }
  }
]

Data Loading with Error Handling​

// Event: onLoad
[
  {
    action: "if",
    parameters: {
      condition: "{{companyId}} !== ''",
      actions: {
        if: [
          {
            action: "findMany (EDS)",
            parameters: {
              dumpVar: "employees",
              clientDatabase: { _id: "hr_db", name: "HR Database" },
              table: "employees",
              find: { 
                company_id: "{{companyId}}",
                status: "active"
              }
            }
          },
          {
            action: "refreshWidget",
            parameters: {
              elementId: "employeeTable"
            }
          }
        ],
        else: [
          {
            action: "alertMessage",
            parameters: {
              type: "warning",
              message: "Please select a company first"
            }
          }
        ]
      }
    }
  }
]

6. Using EDS in Widgets​

6.1. Table Widget with EDS​

The Table widget can connect directly to EDS for data display:

{
  name: "Employee Table",
  model: "employees",
  options: {
    remote: "EDS",                           // Use EDS as data source
    clientDatabase: {                        // EDS connection reference
      _id: "hr_database",
      name: "HR Database"
    },
    edsSource: "employees",                  // Table/resource name
    query: {                                 // Query filter
      department: "{{selectedDepartment}}",
      status: "active"
    },
    sortBy: "last_name",                     // Default sort field
    direction: 1,                            // Sort direction (1=ASC, -1=DESC)
    limit: 25,                               // Records per page
    columns: [
      {
        field: "employee_id",
        label: "ID", 
        type: "string",
        sortable: true,
        width: "80px"
      },
      {
        field: "first_name", 
        label: "First Name",
        type: "string",
        filterable: true,
        sortable: true
      },
      {
        field: "last_name",
        label: "Last Name", 
        type: "string",
        filterable: true,
        sortable: true
      },
      {
        field: "hire_date",
        label: "Hire Date",
        type: "date",
        filterable: true,
        sortable: true,
        dateFormat: "YYYY-MM-DD"
      },
      {
        field: "salary",
        label: "Salary",
        type: "currency",
        sortable: true,
        currency_local: "en-US",
        currency: "USD"
      },
      {
        field: "actions",
        label: "Actions",
        type: "button",
        buttons: [
          {
            title: "Edit",
            icon: "el-icon-edit",
            type: "primary",
            action: "editEmployee"
          },
          {
            title: "Delete",
            icon: "el-icon-delete", 
            type: "danger",
            action: "deleteEmployee"
          }
        ]
      }
    ]
  }
}

6.2. Select/Dropdown Widget with EDS​

{
  name: "Department Select",
  model: "selectedDepartment",
  options: {
    remote: "EDS",
    clientDatabase: {
      _id: "hr_database",
      name: "HR Database"
    },
    edsSource: "departments",
    query: { active: true },
    labelProperty: "name",
    valueProperty: "id",
    sortBy: "name",
    direction: 1,
    placeholder: "Select Department..."
  }
}

6.3. Autocomplete Widget with EDS​

{
  name: "Employee Search",
  model: "selectedEmployee", 
  options: {
    remote: "EDS",
    clientDatabase: {
      _id: "hr_database",
      name: "HR Database"
    },
    edsSource: "employees",
    query: {
      status: "active",
      department: "{{userDepartment}}"
    },
    labelProperty: "$.first_name + ' ' + $.last_name + ' (' + $.employee_id + ')'",
    valueProperty: "employee_id",
    searchProperty: "last_name",
    limit: 10,
    minLength: 2,
    placeholder: "Search employees..."
  }
}

6.4. Checkbox Widget with EDS​

{
  name: "Skills Selection",
  model: "selectedSkills",
  options: {
    remote: "EDS",
    clientDatabase: {
      _id: "skills_db",
      name: "Skills Database"
    },
    edsSource: "skills",
    query: { category: "{{skillCategory}}" },
    labelProperty: "name",
    valueProperty: "id",
    sortBy: "name",
    direction: 1,
    multiple: true
  }
}

7. Advanced EDS Usage​

7.1. Dynamic Query Building​

// In widget configuration
{
  query: `{
    status: "active",
    ${{{includeArchived}} ? '' : 'archived: false,'}
    department: {{selectedDepartment ? '"' + selectedDepartment + '"' : 'null'}},
    created_at: {
      $gte: "{{startDate}}",
      $lte: "{{endDate}}"
    }
  }`
}

7.2. Aggregation Queries​

For SQL databases, you can use complex queries:

{
  edsSource: `
    SELECT 
      d.name as department_name,
      COUNT(e.id) as employee_count,
      AVG(e.salary) as avg_salary,
      MAX(e.salary) as max_salary
    FROM departments d
    LEFT JOIN employees e ON d.id = e.department_id
    WHERE e.status = 'active'
    GROUP BY d.id, d.name
    ORDER BY employee_count DESC
  `,
  isAggregation: true
}

7.3. API Endpoint Customization​

For API EDS, customize endpoints for specific use cases:

{
  type: "execute",
  method: "POST",
  value: "/reports/{{SF_table}}/generate",
  headers: `{
    "Authorization": "Bearer {{authToken}}",
    "Content-Type": "application/json"
  }`,
  params: `{
    filters: SF_source,
    format: "json",
    includeTotals: true
  }`,
  response: `
    // Transform API response to match expected format
    return {
      data: SF_result.results,
      total: SF_result.metadata.total,
      page: SF_result.metadata.page
    };
  `
}

8. Testing and Debugging​

8.1. Connection Testing​

Always test EDS connections before using them:

  1. Use the "Test Connection" button in the EDS configuration
  2. Verify connectivity across all environments
  3. Test with sample queries to ensure proper data access

8.2. API Testing​

For API EDS, use the built-in API testing interface:

  1. Configure test parameters in the API test drawer
  2. Test different methods (retrieve, list, insert, etc.)
  3. Verify response formatting and data structure
  4. Check error handling and status codes

8.3. Common Issues and Solutions​

Connection Timeouts:

  • Increase timeout values in options
  • Check network connectivity
  • Verify firewall rules

Authentication Failures:

  • Verify credentials and permissions
  • Check authentication methods
  • Ensure proper SSL/TLS configuration

Query Errors:

  • Validate SQL syntax for database EDS
  • Check field names and data types
  • Verify query parameters and formatting

API Response Issues:

  • Check API endpoint URLs and methods
  • Verify header and parameter configuration
  • Review response transformation scripts

9. Best Practices​

9.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

9.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

9.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

10. Troubleshooting Guide​

10.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

10.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)

10.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 and usage in SoftyFlow. For specific implementation details or advanced use cases, consult the SoftyFlow documentation or contact support.