Save Relational Data Node in Pathways

From FojiSoft Docs

The Save Relational Data node in Pathways allows workflows to insert or update rows in a relational database. It provides the flexibility to define target tables and dynamically populate column values using either Handlebars Templates or Scripts, ensuring seamless data manipulation.

Node Properties

1. Name

  • Description: A unique identifier for the node within the workflow.
  • Usage: Provide a descriptive name to indicate the purpose of the node (e.g., "Update Invoice Records" or "Insert New Claims").
  • Example: "Save Payment Details"

2. Target Table

  • Description: Specifies the name of the relational database table where the data will be saved.
  • Usage: Select the appropriate table for data insertion or updates.
  • Example: invoices, customers, or orders

Data Section

The Data section allows you to define which columns in the target table are updated or populated, along with their corresponding values.

3. Column

  • Description: Specifies the column in the target table to update or populate.
  • Usage: Select the column that corresponds to the data being saved.
  • Example: invoice_status, payment_date, or customer_email

4. Expression

  • Type: Handlebar Template or Script
  • Description: Defines the value to be assigned to the column.
  • Options:
    • Handlebar Template: Use placeholders to inject dynamic values into the column.

Example:

{{payment_status}}

  • Where payment_status is a variable from the workflow.
  • Script: Write custom logic to generate or calculate the column value dynamically.

Example (Script):

if payment_completed:

   return "Completed"

else:

   return "Pending"

  • Expression Field:
    • Status: Required for each column being updated.
    • Usage: Define the value for the column dynamically to adapt to varying workflow scenarios.

How It Works

  1. Define Target Table:
    • Select the relational database table where data will be inserted or updated.
  2. Configure Columns and Values:
    • Specify the columns to be updated in the Column field.
    • Define the corresponding values using either Handlebar Templates or Scripts.
  3. Save Data:
    • When the workflow reaches this node, the defined data is saved to the target table.
  4. Insert or Update Behavior:
    • Depending on the database configuration, this node can either insert a new row or update an existing row based on matching criteria (e.g., a primary key).

Use Case Examples

1. Insert New Payment Records

  • Name: Insert Payment Records
  • Target Table: payments
  • Data:
    • Column: payment_id
      • Expression: {{payment_id}}
    • Column: amount
      • Expression: {{payment_amount}}
    • Column: status

Expression (Script):t

if payment_confirmed:

   return "Confirmed"

else:

   return "Pending"

  • Outcome: Inserts a new payment record into the payments table.

2. Update Invoice Status

  • Name: Update Invoice Status
  • Target Table: invoices
  • Data:
    • Column: status
      • Expression: {{invoice_status}}
    • Column: updated_at

Expression (Script):

return current_timestamp()

  • Outcome: Updates the status and timestamp of invoices in the invoices table.

Best Practices

  1. Define Columns Explicitly:
    • Specify only the columns that need to be updated to optimize performance.
  2. Parameterize Values:
    • Use dynamic templates or scripts for flexibility, especially in workflows handling varying data.
  3. Test Queries:
    • Validate database changes in a test environment before deploying to production.
  4. Maintain Data Integrity:
    • Ensure primary keys or unique constraints are respected to prevent unintended data duplication.

The Save Relational Data node is a powerful tool for managing relational database updates in Pathways, offering robust customization options through Handlebar Templates and Scripts. This flexibility ensures compatibility with diverse data processing needs.

Retrieved from "https://docs.foji.io/index.php?title=Save_Relational_Data_Node_in_Pathways&oldid=2767"