Skip to content

Martini Workflows Database Node

Overview

A Workflow Database Node lets you perform read and write operations against any SQL or NoSQL database supported by Martini. By integrating database queries directly into your workflow, you can automate data access, transformation, and storage without manual coding. This streamlines backend logic and enables robust data-driven automation.

Generic Guide

This document provides general guidance for working with Database Nodes that applies to all supported databases. However, specific configurations, query syntax, operation types, and data mapping details vary by database type. For precise instructions tailored to your database, see Database-Specific Configuration at the end of this guide.

What You Will Learn

  • How to add a Database Node to your workflow
  • How to configure database queries and data mapping
  • How to connect and transform data between your workflow and databases
  • How to work with batch operations and cursors for efficient data processing

When To Use This

Use this when you need to:

  • Execute read and write operations against supported SQL or NoSQL databases in Martini.
  • Automate data flows between your workflow and external databases.
  • Integrate database logic directly into your workflow for seamless backend automation.

Supported Databases for Workflow Database Node

The Workflow Database Node supports the following SQL and NoSQL databases:

SQL:

  • Hypersonic SQL (v2.0+ File)
  • Hypersonic SQL (v2.0+ Remote)
  • MySQL
  • Oracle OCI 8
  • Oracle OCI 9+
  • Oracle (Thin)
  • PostgreSQL (v7.0 and later)
  • PrestoDB
  • Snowflake
  • Microsoft SQL Server

NoSQL:

  • Cassandra
  • MongoDB

Prerequisites

Adding a Database Node

Getting Started: Adding Nodes

  1. Navigate to your Martini package and open your workflow in the Workflow Designer.
  2. Click the Add Node button in the toolbar on the top left.
  3. Select Database Query from the available node types.
  4. Drag the Database Query Node to your desired position in the workflow.
  5. Connect it to other nodes using workflow edges.

Expected result: The Database Query Node appears in your workflow, ready for configuration.

What's next: Proceed to Configuration Panel.

Configuration Panel

The configuration panel is the main interface for Database Nodes. It contains two tabs: Query and Data Mapping.

Query Tab

The Query Tab is where you configure how your node interacts with the database.

Fields and Configuration

Field Description
Connection Name Select the database connection you want to use.
Type Choose the operation type (Insert, Update, Select, etc.).
Query/Statement Enter the database query or operation.

For detailed examples for each field and additional properties specific to each database type, see Database-Specific Configuration.

Step-by-Step Setup

  1. Select your database connection from the dropdown.
  2. Choose the operation type (e.g., Insert, Select Single).
  3. Enter your query, using placeholders for dynamic values.
  4. Save your configuration.

The Query Tab determines what data is needed and what results are returned. The Data Mapping Panel (next tab) lets you map workflow properties to these inputs and outputs.

Data Mapping Panel

The Data Mapping Panel visually connects workflow properties to database node inputs and outputs. You can map workflow inputs to database node inputs and route outputs from executed queries back into your workflow. You can also declare new properties and set expressions.

Getting Started: Data Mapping

  1. Click the expand icon on your Database Query Node.
  2. The panel appears in the lower half of the Workflow Designer.

Expected result: The Mapper Panel shows two main areas: Input (left) and Output (right).

Input Panel Layout (Left)

1
2
3
4
5
6
| Workflow Properties      | Mapping Lines | Database Node Input Properties |
|--------------------------|---------------|--------------------------------|
|                          |               | input Model                    |
|                          |               | +--$martiniConnectionPool      |
|        [input1]          |   ------->    | +--[databaseNodeIn1]           |
|        [input2]          |   ------->    | +--[databaseNodeIn2]           |

Database-Specific Properties

The Database Node Input Properties shown above are generic examples. Actual property names, structure, and available options vary significantly by database type (SQL, MongoDB, Cassandra). For specific input properties and their configurations, see Database-Specific Configuration.

Common Database Node Input Properties: Key Terms

Property Purpose
$martiniConnectionPool Allows you to set the connection pool dynamically within the workflow. Defaults to the configured Martini connection pool if not set.
[databaseNodeInX] Custom input properties generated from database query or operation placeholders. Workflow properties can be mapped to these to dynamically set values needed for read and write operations.

Output Panel Layout (Right)

1
2
3
4
5
| Database Node Output Properties | Mapping Lines | Workflow Current/Output Properties |
|---------------------------------|---------------|------------------------------------|
|      output Model               |               |                                    |
|      +--[databaseNodeOut1]      |   ------->    |         [workflowOut1]             |
|      +--[databaseNodeOut2]      |   ------->    |         [workflowOut2]             |

Database-Specific Properties

The Database Node Output Properties vary by database type and operation. Property names, cursor behavior, and result structures differ between SQL, MongoDB, and Cassandra nodes. For detailed information about output properties specific to your database type, see Database-Specific Configuration.

Property Description
inputCursor Batch read operations only. Returns a read-only cursor object containing query results. Connect this cursor to a Repeat Node to automatically loop through each database record. Useful for processing large result sets efficiently and iterating through database records in your workflow without loading everything into memory. See Batch Read Operations for detailed usage.
outputCursor Batch write operations only. Returns a cursor object for batch write operations that can be written to. Connect this cursor to a Repeat Node to perform bulk operations efficiently. See Batch Write Operations for detailed usage.
[databaseNodeOutputX] Query Output Properties consist of the column names or document fields returned by your database query. These properties are automatically available after the query executes, and you can map them to workflow properties for use in subsequent nodes.

To know more about how to use this data mapping panel to map values, create and manage properties, etc, See Data Mapping.

Working with Batch Operations and Cursors

Batch operations allow you to efficiently process large datasets by working with multiple database records at once. The Database Node can return an inputCursor or outputCursor for batch operations, which you can iterate through using a Repeat Node.

When to Use Batch Operations

Use batch operations when you need to:

  • Perform bulk insert, update, or delete operations
  • Stream data from one database to another
  • Generate reports from large datasets
  • Import/export data in chunks

Batch Read Operations

Batch read operations retrieve multiple records from the database and return them as a cursor for efficient iteration.

Real-World Example: Processing Products with Tax

Business scenario: Your workflow needs to process products from the database and apply a tax on the price before returning them.

Sample data: 

1
2
3
4
5
-- Products table
product_id | name          | price   | category     | tax_category
1          | Laptop Pro    | 1299.99 | Electronics  | standard
2          | Coffee Mug    | 12.50   | Kitchen      | standard
3          | Desk Chair    | 89.99   | Furniture    | reduced

Expected workflow input: Tax rate parameters (e.g., standard tax rate, reduced tax rate)

Expected workflow output: Array of processed products with tax applied

Setting Up Batch Read

Prerequisites: Configure Workflow Outputs

Before configuring your Database Node, you need to define the workflow's output structure:

  1. Open the Workflow Input/Output panel by clicking the Configure Input/Output Properties icon in the toolbar
  2. In the Output Properties section, click Add
  3. Select Model from the dropdown and name it processedProducts
  4. Convert it to an Array by right-clicking on processedProducts → Convert To → Array

  5. Add properties to the model:

    • Click on processedProducts to expand it
    • Click the Add button to add a new property
    • Select String and name it name
    • Click Add again to add another property
    • Select Double and name it priceWithTax

For detailed instructions on managing workflow inputs and outputs, see Workflow Inputs & Outputs.

Step 1: Configure Your Database Query

  1. Add a Database Node and configure the connection:

    • Add a Database Node to your workflow
    • Click the expand icon on your Database Query Node to open the configuration panel
    • Ensure you are in the Query Tab (should be selected by default)
    • Select your database connection from the Connection Name dropdown

  2. Choose the batch operation type based on your database:

    • SQL databases: Select "Select Multi"
    • MongoDB: Select "Find Many"
    • Cassandra: Select "Select Multi"

  3. Write your database query using the appropriate syntax:

    • SQL databases: SELECT product_id, name, price, category, tax_category FROM products WHERE category = :category AND price >= :minPrice
    • MongoDB: {"category": "{{category:string}}", "price": {"$gte": "{{minPrice:double}}"}} with projection {"product_id": 1, "name": 1, "price": 1, "category": 1, "tax_category": 1}
    • Cassandra: SELECT product_id, name, price, category, tax_category FROM products WHERE category = :category AND price >= :minPrice ALLOW FILTERING

    About Query Placeholders

    These queries use placeholders that allow dynamic queries. For more information on those and proper syntax for each database node type, see Database-Specific Configuration.

  4. Map input parameters (if your query has placeholders):

    • Click the expand icon on your Database Node
    • Click the Data Mapping Tab
    • In the Input Panel, drag workflow properties to the database input parameters
    • For more details on how to map properties, see Data Mapping

Step 2: Connect Database Results to Your Workflow

  1. Open the Data Mapper Panel on your Database Node (if not already open)

    • Click the expand icon on your Database Query Node
    • Click the Data Mapping Tab

  2. Find the inputCursor property in the Output Panel (right side)

  3. Create a workflow property to contain the cursor:

    • In the Output Panel, drag inputCursor to the right to the Workflow Current/Output Properties area to create a new property named productsCursor
    • This property will hold the database cursor for iteration in your workflow

Step 3: Add and Configure a Repeat Node

  1. Add a Repeat Node after your Database Node and connect them with an edge

  2. Configure the Repeat Node:

    • Click the expand icon on your Repeat Node
    • Set Repeat On to "Array"
    • Set Input Array to productsCursor
    • Set Output Array to processedProducts (this is the model array we created at the start of this example)

To understand how to configure a Repeat Node in more detail, See Repeat Node.

Step 4: Process Each Database Record

  1. Add processing nodes to the each edge of the Repeat Node:

    • Add a Map Node to map the product name
    • Add another Map Node to calculate tax based on tax_category and apply it to the price

  2. Access record data - each iteration provides fields from the database table/collection like product_id, name, price, category, tax_category

  3. Map results to output:

    • In the first Map Node (for product name):

      • Map the name: Drag productsCursor.name to processedProducts.name

    • In the second Map Node (for tax calculation):

      • Calculate price with tax: Double click on processedProducts.priceWithTax → In the "Edit set Expression" dialog → Enter the expression: 
        1
        productsCursor.price * (1 + (productsCursor.tax_category == 'reduced' ? 0.05 : productsCursor.tax_category == 'exempt' ? 0.00 : 0.10))

Expected result: Your workflow processes all matching products and outputs an array of processed product records with tax applied to their prices.

Batch Write Operations

Batch write operations allow you to insert or update multiple records efficiently. The implementation varies by database type.

Real-World Example: Bulk Product Import

Business scenario: Import 1,000 products from a supplier file into your database.

Sample input data: 

1
2
3
4
5
[
  {"name": "Laptop Pro", "price": 1299.99, "category": "Electronics"},
  {"name": "Coffee Mug", "price": 12.50, "category": "Kitchen"},
  {"name": "Desk Chair", "price": 89.99, "category": "Furniture"}
]

Expected result: All products inserted into database with success confirmation.

Setting Up Batch Write

Step 1: Prepare Your Data Source

  1. Create your input data in one of these ways:

    • Workflow Input: Create an Array Property named productsToInsert
    • An Array property from a previous node

Step 2: Configure Your Database Write Operation

  1. Add a Database Node and configure the connection:

    • Add a Database Node to your workflow
    • Click the expand icon on your Database Query Node to open the configuration panel
    • Ensure you are in the Query Tab (should be selected by default)
    • Select your database connection from the Connection Name dropdown

  2. Choose the batch operation type in the Query Tab based on your database:

    • SQL databases: Select "Insert Batch"
    • MongoDB: Select "Insert Many"
    • Cassandra: Select "Insert Batch"

  3. Write your database operation (Applicable only to SQL and Cassandra):

    • SQL: INSERT INTO products (name, price, category) VALUES (:name, :price, :category)
    • Cassandra: INSERT INTO products (name, price, category) VALUES (:name, :price, :category)

Step 3: Handle Batch Results

  1. Open the Data Mapper Panel on your Database Node (if not already open)

    • Click the expand icon on your Database Query Node
    • Click the Data Mapping Tab

  2. Find the outputCursor property in the Output Panel (right side)

  3. Create a workflow property from the cursor:

    • In the Output Panel, drag outputCursor to the right to the Workflow Current/Output Properties area to create a new property
    • Or drag it to an existing workflow property in the Workflow Current/Output Properties area
    • This property will allow you to use the cursor to execute the batch operation

Step 4: Process Batch Results

  1. Add a Repeat Node after your Database Node and connect them with an edge

  2. Configure the Repeat Node:

    • Click the expand icon on your Repeat Node
    • Set Repeat On to "Array"
    • Set Input Array to productsToInsert
    • Set Output Array to the workflow property you mapped the outputCursor to

  3. Map the data you want to write to the database

    • Add a Map Node: Create a Map Node and connect it to the each edge of the Repeat Node
    • Open the Map Node's Data Mapper Panel: Click the expand icon on the Map Node to open its mapping interface
    • Map your input data to the cursor:
      • On the left side: Find your input property containing the array of data you want to write
      • On the right side: Find the workflow property that holds your outputCursor
      • Drag to create connections between properties

Result: Your workflow now efficiently writes multiple database records in batches, and processes the results of each operation.

Batch Delete Operations

Batch delete operations allow you to delete multiple records efficiently based on specified criteria. The implementation varies by database type.

Real-World Example: Bulk Product Deletion

Business scenario: Delete multiple selected products from your database based on a list of product IDs provided by the user (e.g., from a multi-selection interface).

Sample input data: 

1
[1, 3, 5, 7, 9]

Sample existing data in database: 

1
2
3
4
5
6
7
-- Products table
product_id | name          | price   | category     | tax_category
1          | Laptop Pro    | 1299.99 | Electronics  | standard
2          | Coffee Mug    | 12.50   | Kitchen      | standard
3          | Desk Chair    | 89.99   | Furniture    | reduced
4          | Monitor       | 299.99  | Electronics  | standard
5          | Keyboard      | 79.99   | Electronics  | standard

Expected result: Products with IDs 1, 3, and 5 are deleted from the database with success confirmation.

Setting Up Batch Delete

Step 1: Prepare Your Data Source

  1. Create your input data in one of these ways:

    • Workflow Input: Create an Array Property of type Integer named productIdsToDelete:

      1. Open the Workflow Input/Output panel by clicking the Configure Input/Output Properties icon in the toolbar
      2. In the Input Properties section, click Add
      3. Select Integer from the dropdown and name it productIdsToDelete
      4. Right-click on the productIdsToDelete property
      5. From the context menu, select Toggle Array to convert it to an array of integers

    • An Array property from a previous node containing the product IDs to delete

Step 2: Configure Your Database Delete Operation

  1. Add a Database Node and configure the connection:

    • Add a Database Node to your workflow
    • Click the expand icon on your Database Query Node to open the configuration panel
    • Ensure you are in the Query Tab (should be selected by default)
    • Select your database connection from the Connection Name dropdown

  2. Choose the batch operation type in the Query Tab based on your database:

    • SQL databases: Select "Delete Batch"
    • MongoDB: Select "Delete Many" (MongoDB delete operations work differently - see MongoDB Database Node for specific configuration)
    • Cassandra: Select "Delete Batch"

  3. Write your database operation:

    • SQL: DELETE FROM products WHERE product_id = :product_id
    • Cassandra: DELETE FROM products WHERE product_id = :product_id

Step 3: Handle Batch Results

  1. Open the Data Mapper Panel on your Database Node (if not already open)

    • Click the expand icon on your Database Query Node
    • Click the Data Mapping Tab

  2. Find the outputCursor property in the Output Panel (right side)

  3. Create a workflow property from the cursor:

    • In the Output Panel, drag outputCursor to the right to the Workflow Current/Output Properties area to create a new property named deletionResults
    • This property will allow you to use the cursor to execute the batch deletion operation

Step 4: Process Batch Deletions

  1. Add a Repeat Node after your Database Node and connect them with an edge

  2. Configure the Repeat Node:

    • Click the expand icon on your Repeat Node
    • Set Repeat On to "Array"
    • Set Input Array to productIdsToDelete
    • Set Output Array to deletionResults

  3. Map the product IDs for deletion

    • Add a Map Node: Create a Map Node and connect it to the each edge of the Repeat Node
    • Open the Map Node's Data Mapper Panel: Click the expand icon on the Map Node to open its mapping interface

    • Map your input data to the cursor:

      • On the left side: Find productIdsToDelete (the current product ID being processed in the iteration)
      • On the right side: Find deletionResults.product_id (or the corresponding field based on your database type)
      • Drag to create a connection between the product ID and the cursor field

Result: Your workflow efficiently deletes multiple database records in batches based on the provided product IDs, and processes the results of each deletion operation.

Troubleshooting Common Issues

Connection Problems

Problem Symptoms Solution
Connection timeout Database Node shows error: "Connection timeout" 1. Check database server is running
2. Verify connection string in connection configuration
3. Check network connectivity
Authentication failed Error: "Access denied" or "Login failed" 1. Verify username/password in connection settings
2. Check database user permissions
3. Ensure user has access to target tables
Database not found Error: "Database does not exist" 1. Verify database name in connection string
2. Check database exists on server
3. Ensure correct server instance

Query Issues

Problem Symptoms Solution
Syntax error in query Error: "Syntax error near..." 1. Check query syntax for your database type
2. Verify table/column names exist
3. Test query in database management tool first
No results returned outputCursor is empty 1. Verify WHERE conditions are correct
2. Check if data exists in database
3. Test query with simpler conditions
Parameter mapping error Error: "Parameter not set" 1. Check all query placeholders (?) have mapped inputs
2. Verify parameter data types match
3. Use Data Mapping Panel to verify connections

Database-Specific Configuration

Each database type has its own configuration requirements and options. Refer to the following pages for details on setting up and using specific database nodes in Martini workflows:

Helpful Resources