View source for Save Relational Data Node in Pathways

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''':

<nowiki>{{payment_status}}</nowiki>

* 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''' ===

# '''Define Target Table''':
#* Select the relational database table where data will be inserted or updated.
# '''Configure Columns and Values''':
#* Specify the columns to be updated in the '''Column''' field.
#* Define the corresponding values using either Handlebar Templates or Scripts.
# '''Save Data''':
#* When the workflow reaches this node, the defined data is saved to the target table.
# '''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''': <nowiki>{{payment_id}}</nowiki>
** '''Column''': amount
*** '''Expression''': <nowiki>{{payment_amount}}</nowiki>
** '''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''': <nowiki>{{invoice_status}}</nowiki>
** '''Column''': updated_at

'''Expression''' (Script):

return current_timestamp()

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

----

=== '''Best Practices''' ===

# '''Define Columns Explicitly''':
#* Specify only the columns that need to be updated to optimize performance.
# '''Parameterize Values''':
#* Use dynamic templates or scripts for flexibility, especially in workflows handling varying data.
# '''Test Queries''':
#* Validate database changes in a test environment before deploying to production.
# '''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.
[[Category:ForgeAI]]
[[Category:Pathways]]