Database Management in SoftyFlow

SoftyFlow provides two main approaches for data management: SQL databases via External Data Sources (EDS) and MongoDB Collections. This guide covers both approaches with practical usage examples.

Before diving into database integration, ensure you've completed your project setup and understand how to use databases in your web interfaces and processes.

1. Overview​

SQL via EDS (External Data Sources)​

Connect to existing SQL databases (MySQL, PostgreSQL, Oracle, SQL Server, Snowflake) and APIs for enterprise data integration. Learn more about EDS integration.

MongoDB Collections​

Use SoftyFlow's built-in MongoDB for rapid prototyping and application-specific data storage.

2. SQL via EDS​

2.1. Quick Setup​

2.2. Common Use Cases​

Enterprise Integration: Connect to existing CRM, ERP, or legacy systems using our integration features Data Warehousing: Access analytics data from Snowflake or similar platforms for reporting Cross-System Integration: Sync data between multiple business systems

3. MongoDB Collections​

3.1. Quick Setup​

Collections are created directly in SoftyFlow IDE with automatic schema flexibility.

3.2. Common Use Cases​

Rapid Prototyping: Quick data storage without database setup Application Settings: Store configuration and user preferences
Temporary Data: Session data, logs, and temporary calculations Flexible Schema: Documents with varying structures

4. Usage in Widgets​

Integrate your databases with web interface widgets to create dynamic, data-driven interfaces.

4.1. Dropdown/Select Widgets​

Use Select widgets to display database data as dropdown options.

SQL via EDS​

MongoDB Collections​

4.2. Table Widgets​

Display database data in interactive Table widgets for comprehensive data management.

SQL via EDS​

MongoDB Collections​

4.3. Advanced Filtering​

Dynamic Filters (SQL)​

MongoDB Query Filters​

5. Usage in Actions​

Integrate databases with web interface actions and process design for dynamic data handling.

5.1. Form Submission​

SQL via EDS​

MongoDB Collections​

5.2. Data Loading​

Complex SQL Query​

MongoDB Aggregation​

5.3. Update Operations​

Bulk Update (SQL)​

MongoDB Update​

6. Forms Integration​

6.1. Dynamic Form Generation​

EDS-Based Form​

Collection-Based Form​

6.2. Conditional Field Loading​

7. Best Practices​

7.1. When to Use SQL via EDS​

✅ Use for:

• Existing enterprise systems integration
• Complex reporting and analytics
• Large datasets with established relationships
• Multi-system data synchronization
• Regulatory compliance requirements

7.2. When to Use MongoDB Collections​

✅ Use for:

• Rapid application development
• Flexible, evolving data structures
• Application-specific configurations
• Temporary or session data
• Small to medium datasets

7.3. Performance Tips​

SQL Optimization​

// Use indexed fields in queries
{
  query: { 
    indexed_field: "value",        // Fast
    non_indexed_field: "value"     // Slower
  }
}

// Limit result sets
{
  edsSource: "SELECT * FROM large_table WHERE active = 1 LIMIT 100"
}

MongoDB Optimization​

// Create indexes for frequently queried fields
db.collection.createIndex({ "status": 1, "created_at": -1 })

// Use projection to limit returned fields
{
  action: "find",
  parameters: {
    collection: "users",
    object: { active: true },
    options: { 
      projection: { name: 1, email: 1, _id: 1 }
    }
  }
}

8. Security Considerations​

8.1. SQL Security​

• Use parameterized queries to prevent SQL injection
• Implement role-based access control
• Encrypt sensitive data in transit and at rest
• Regular security audits and updates

8.2. MongoDB Security​

• Validate input data before insertion
• Use appropriate field-level permissions
• Implement data sanitization
• Regular backup and recovery procedures

9. Migration Between Systems​

9.1. From MongoDB to SQL​

This documentation provides practical examples for both SQL and MongoDB usage in SoftyFlow applications. Choose the appropriate data storage method based on your specific requirements and existing infrastructure.

Next Steps​

Now that you understand database integration, continue building your application:

• Web Interface Design - Create interfaces that display and interact with your data
• Process Design - Design processes that use your database data
• Integration - Connect with external data sources and APIs
• Reporting - Create reports from your database data
• Test & Deploy - Test your database integrations across environments
• Monitor & Run - Monitor your data-driven processes

For hands-on experience, try our step-by-step tutorials or watch our database video tutorials.

For advanced database features, explore our EDS documentation, MongoDB integration, and learn about using databases in Select widgets and Table widgets.