Skip to main content

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.

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.

MongoDB Collections​

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

2. SQL via EDS​

2.1. Quick Setup​

// EDS Configuration Example (MySQL)
{
  host: "localhost:3306",
  user: "app_user", 
  password: "secure_password",
  database: "company_db"
}

2.2. Common Use Cases​

Enterprise Integration: Connect to existing CRM, ERP, or legacy systems Data Warehousing: Access analytics data from Snowflake or similar platforms 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.

// Sample Document Structure
{
  _id: ObjectId("..."),
  name: "John Doe",
  email: "john@company.com",
  department: "Sales",
  created_at: new Date(),
  permissions: ["read", "write"]
}

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​

4.1. Dropdown/Select Widgets​

SQL via EDS​

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

MongoDB Collections​

{
  name: "Category Select", 
  model: "selectedCategory",
  options: {
    remote: "collection",
    collection: "categories",
    query: { active: true },
    labelProperty: "name",
    valueProperty: "_id",
    sortBy: "order"
  }
}

4.2. Table Widgets​

SQL via EDS​

{
  name: "Employee Table",
  model: "employees",
  options: {
    remote: "EDS",
    clientDatabase: { _id: "hr_db", name: "HR Database" },
    edsSource: "employees",
    query: { status: "active" },
    columns: [
      { field: "employee_id", label: "ID", type: "string" },
      { field: "full_name", label: "Name", type: "string", filterable: true },
      { field: "department", label: "Department", type: "string", filterable: true },
      { field: "hire_date", label: "Hire Date", type: "date" },
      { field: "salary", label: "Salary", type: "currency" }
    ]
  }
}

MongoDB Collections​

{
  name: "Projects Table",
  model: "projects", 
  options: {
    remote: "collection",
    collection: "projects",
    query: { archived: false },
    columns: [
      { field: "title", label: "Project", type: "string", filterable: true },
      { field: "manager.name", label: "Manager", type: "string" },
      { field: "status", label: "Status", type: "select", filterable: true },
      { field: "deadline", label: "Deadline", type: "date" },
      { field: "budget", label: "Budget", type: "currency" }
    ]
  }
}

4.3. Advanced Filtering​

Dynamic Filters (SQL)​

// In table widget query
{
  query: `{
    status: "active",
    department: {{selectedDepartment ? '"' + selectedDepartment + '"' : 'null'}},
    hire_date: {
      $gte: "{{startDate}}",
      $lte: "{{endDate}}"
    }
  }`
}

MongoDB Query Filters​

{
  query: {
    $and: [
      { status: { $ne: "deleted" } },
      { "manager._id": "{{currentUser._id}}" },
      { deadline: { $gte: new Date() } }
    ]
  }
}

5. Usage in Actions​

5.1. Form Submission​

SQL via EDS​

[
  {
    action: "validateForm",
    parameters: { attachForm: { formRef: "customerForm" } }
  },
  {
    action: "insertTable (EDS)",
    parameters: {
      clientDatabase: { _id: "crm_db", name: "CRM Database" },
      table: "customers",
      attachForm: { formRef: "customerForm" },
      validationSchema: {
        name: "string().required()",
        email: "string().email().required()"
      }
    }
  },
  {
    action: "alertMessage",
    parameters: {
      type: "success",
      message: "Customer created successfully!"
    }
  }
]

MongoDB Collections​

[
  {
    action: "validateForm", 
    parameters: { attachForm: { formRef: "projectForm" } }
  },
  {
    action: "insert",
    parameters: {
      collection: "projects",
      attachForm: { formRef: "projectForm" },
      object: {
        created_by: "{{currentUser._id}}",
        created_at: new Date(),
        status: "planning"
      }
    }
  },
  {
    action: "refreshWidget",
    parameters: { elementId: "projectsTable" }
  }
]

5.2. Data Loading​

Complex SQL Query​

{
  action: "executeStatement (EDS)",
  parameters: {
    dumpVar: "salesReport",
    clientDatabase: { _id: "sales_db", name: "Sales Database" },
    statement: `
      SELECT 
        s.region,
        COUNT(o.id) as total_orders,
        SUM(o.amount) as total_revenue,
        AVG(o.amount) as avg_order_value
      FROM orders o
      JOIN salespeople s ON o.salesperson_id = s.id
      WHERE o.created_at >= '{{startDate}}'
      GROUP BY s.region
      ORDER BY total_revenue DESC
    `
  }
}

MongoDB Aggregation​

{
  action: "script",
  parameters: {
    dumpVar: "projectStats",
    script: `
      return await sf.db.collection('projects').aggregate([
        { $match: { status: { $ne: 'deleted' } } },
        { $group: {
          _id: '$status',
          count: { $sum: 1 },
          totalBudget: { $sum: '$budget' },
          avgBudget: { $avg: '$budget' }
        }},
        { $sort: { count: -1 } }
      ]).toArray();
    `
  }
}

5.3. Update Operations​

Bulk Update (SQL)​

{
  action: "executeStatement (EDS)",
  parameters: {
    clientDatabase: { _id: "inventory_db", name: "Inventory" },
    statement: `
      UPDATE products 
      SET price = price * 1.1 
      WHERE category = '{{selectedCategory}}' 
      AND last_updated < DATE_SUB(NOW(), INTERVAL 1 YEAR)
    `
  }
}

MongoDB Update​

{
  action: "updateMany",
  parameters: {
    collection: "tasks",
    findObject: { 
      project_id: "{{projectId}}", 
      status: "pending" 
    },
    updatedObject: {
      status: "in_progress",
      started_at: new Date(),
      assigned_to: "{{currentUser._id}}"
    }
  }
}

6. Forms Integration​

6.1. Dynamic Form Generation​

EDS-Based Form​

// Load form options from SQL database
{
  action: "findMany (EDS)",
  parameters: {
    dumpVar: "formOptions",
    clientDatabase: { _id: "config_db", name: "Configuration" },
    table: "form_fields",
    find: { 
      form_type: "customer_registration",
      active: true 
    }
  }
}

Collection-Based Form​

// Load form configuration from MongoDB
{
  action: "findOne",
  parameters: {
    dumpVar: "formConfig",
    collection: "form_templates",
    object: { name: "employee_onboarding" }
  }
}

6.2. Conditional Field Loading​

// Dependent dropdowns
[
  {
    action: "findMany (EDS)",
    parameters: {
      dumpVar: "departments",
      clientDatabase: { _id: "hr_db", name: "HR Database" },
      table: "departments",
      find: { company_id: "{{selectedCompany}}" }
    }
  },
  {
    action: "refreshWidget",
    parameters: { elementId: "departmentSelect" }
  }
]

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​

// Export MongoDB data
{
  action: "script",
  parameters: {
    script: `
      const data = await sf.db.collection('customers').find({}).toArray();
      return data.map(doc => ({
        id: doc._id.toString(),
        name: doc.name,
        email: doc.email,
        created_at: doc.created_at
      }));
    `
  }
}

// Import to SQL via EDS
{
  action: "insertMany (EDS)",
  parameters: {
    clientDatabase: { _id: "production_db" },
    table: "customers",
    data: "{{exportedData}}"
  }
}

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.