Skip to content

SQL Database Node

Overview

The SQL Database Node enables you to perform SQL operations—such as querying, inserting, updating, and deleting records—directly within your Martini workflow. This node integrates with any supported SQL database via JDBC, allowing you to automate data access and transformation without manual coding.

For general details on database nodes and a list of supported databases (both NoSQL and SQL), see Workflow Database Node.

What You Will Learn

  • How to add and configure an SQL Database Node in your workflow
  • How to use SQL queries with dynamic placeholders
  • How to map workflow properties to SQL parameters and results

When to Use This

Use this node when you need to:

  • Execute SQL operations (read/write) against supported databases like MySQL, PostgreSQL, Oracle, and SQL Server.
  • Automate data flows between your workflow and an external SQL database.
  • Integrate SQL logic directly into your workflow for seamless backend automation.

See more use cases in Workflow Database Node: When To Use This.

Supported Databases

The SQL Database Node supports a wide range of SQL databases via JDBC. You can connect to any of the following:

Popular SQL Databases:

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

Embedded & Remote:

  • Hypersonic SQL (v2.0+ File)
  • Hypersonic SQL (v2.0+ Remote)

Prerequisites

Before you start

  • Set up an SQL database: You need access to an SQL database. This can be either:

    • A running SQL database server (e.g., MySQL, PostgreSQL, SQL Server)
    • An embedded database (e.g., HSQL File) that runs within Martini Designer

  • Install Martini Designer

    Martini Designer is required to build and manage workflows. See
    Installation guide

  • Configure an SQL database connection in Martini

    Martini needs to know how to connect to your database.
    See: Creating and Configuring an SQL Connection in Martini

While using this feature

Getting Started

The goal of this Getting Started section is to help you quickly set up and run a workflow that inserts data into an SQL database using the SQL Database Node. You'll walk through a practical example that demonstrates how to connect to your database, map workflow properties, and retrieve generated IDs—all with minimal setup.

Follow these steps for a fast hands-on experience:

  1. A database and a table named GUEST containing the columns firstname, lastname, and email must be configured within your SQL database (e.g., MySQL, PostgreSQL).
  2. Create a workflow.
  3. Add the properties customerFirstName, customerLastName, and customerEmail to your workflow's inputs and outputs or declare them as new properties in the workflow.
  4. Add an SQL Database Node (lets your workflow interact with your database) in your created workflow, and connect to your Start Trigger (the node that begins workflow execution) or any other trigger node.
  5. Click the expand icon on the node (this opens the Configuration Panel).
  6. In the Query Tab, set:
    • Connection Name: your configured SQL connection name
    • Type: Insert
    • Query: Use SQL with placeholders (variables like :firstName that get replaced with workflow values), e.g.: 
      1
2
      INSERT INTO CUSTOMER (FIRSTNAME, LASTNAME, EMAIL)
VALUES (:firstName, :lastName, :email)
  7. Open the Data Mapping Panel (where you link workflow properties to SQL placeholders) and map workflow properties to query placeholders:
    • customerFirstName → firstName
    • customerLastName → lastName
    • customerEmail → email
  8. To retrieve generated IDs (such as auto-incremented primary keys) for use in your workflow, map the output property:
    • generatedKeys → newCustomerId

      Note: Your table must generate keys automatically (e.g., AUTO_INCREMENT, SERIAL). The $ prefix in $getGeneratedKeys means it's a special node setting—find and set this in the node's advanced options in the Configuration Panel (default is true for Insert).

  9. Run your workflow. The new customer is inserted, and the generated customer ID is available in newCustomerId for use in subsequent nodes.

Adding an SQL Database Node

To add an SQL Database Node to your workflow:

  1. Add a new Database Node by following these steps.

  2. Click the expand icon on the node.
    What you'll see: A configuration panel appears with two tabs—Query and Data Mapping—where you configure your database interaction.

  3. Once your node is added, proceed to the Configuration Panel section to set up your SQL queries and data mappings.

Configuration Panel

The Configuration Panel is where you set up your SQL operation and map workflow properties to database fields. This panel lets you control how your workflow interacts with your database.

Initial Setup Steps

  1. Go to the Query Tab.
    What you'll see: Fields for selecting your connection, operation type, and entering your SQL statement.

  2. In the Connection Name field, select your configured SQL connection from the dropdown.
    Martini will recognize your connection type and prepare the node for SQL configuration.

  3. Once your connection is selected, you can configure the specific SQL operation and data mappings as described in the following sections.

The Configuration Panel contains two tabs: Query Tab and Data Mapping Panel. For a full walkthrough, see Configuration Panel.

Query Tab

This panel allows you to set up your SQL operation and define any placeholders you need for dynamic values.

Fields and Configuration:

Property Required Example Value Description
Connection Name Yes sql-main Name of your SQL connection.
Note: Martini will be able to pick up the connection configuration and type from the name.
Query Yes SELECT * FROM GUEST; SQL statement to execute
Query Placeholders No :propertyName

Example:
INSERT INTO GUEST (ID, FIRSTNAME, LASTNAME) VALUES (:id, :firstname, :lastname);		 Use placeholders to insert workflow values into your SQL queries dynamically.

Placeholders like :id, :firstname, and :lastname will appear as the input properties id, firstname, and lastname in the Data Mapping Panel.

See Data Mapping Panel for more information on the layout and descriptions of the Data Mapping Panel.
Type Yes Insert SQL operation type.
Supported types include:
- Delete
- Delete Batch
- Insert
- Insert Batch
- Select Multi
- Select Single
- Universal
- Update
- Update Batch

Data Mapping Tab

The Data Mapping Tab allows you to access the SQL Database Node's Mapper Panel. It uses Martini's standard Data Mapper Panel with the Double Mapper Layout to connect and transform data between your workflow and your SQL database.

The Data Mapping Panel visually connects workflow properties to SQL database node inputs and outputs. You can map workflow inputs to the SQL database node inputs and route outputs from executed operations 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 Mapper Layout (Left)

1
2
3
4
5
6
7
8
| Workflow Properties      | Mapping Lines | SQL Database Node Input Properties |
|--------------------------|---------------|----------------------------------------|
|                          |               | input Model                            |
|                          |               | +--$martiniConnectionPool              |
|                          |               | +--$batchSize                          |
|                          |               | +--$parentJDBCBatchCursor              |
|                          |               | +--$getGeneratedKeys                   |
|        [input1]          |   ------->    | +--[databaseNodeIn1]                   |

For complete details on using the Data Mapper Panel, including creating map lines, set expressions, and property management, see the Data Mapping Guide.

SQL Database Node Input Properties: Key Terms

Property Purpose Applicable Operations
$martiniConnectionPool Allows you to set the connection pool dynamically within the workflow. Defaults to the configured Martini connection pool if not set. All operations
$batchSize Controls how many records are processed together in batch operations before sending to the database. For example, if set to 50, the node will collect 50 records before executing them as a single database operation. This improves performance by reducing database round trips. Default value is 100 records per batch. Batch operations (Insert Batch, Update Batch, Delete Batch)
$parentJDBCBatchCursor Ensures parent table records are inserted before child table records in batch operations, preventing foreign key constraint violations. For example, when inserting customers and their orders together, this ensures customer records are saved before order records that reference them. Leave empty if your batch doesn't involve related tables with foreign key relationships. Batch operations (Insert Batch, Update Batch, Delete Batch)
$getGeneratedKeys Controls whether to retrieve auto-generated primary keys (like auto-increment IDs) after INSERT operations. Set to true to get the new record's ID, or false if you don't need it. For example, when inserting a new customer record, setting this to true lets you capture the customer ID that was automatically generated by the database. Default is true for INSERT operations. Insert, Insert Batch
[databaseNodeInX] Custom input properties generated from the SQL query placeholders. Workflow properties can be mapped to these to dynamically set values needed for SQL read and write operations.
See Custom Input Properties for details.		 All operations with placeholders
Custom Input Properties

Custom input properties (represented as [databaseNodeInX] in this documentation) are generated from your SQL query placeholders. In the actual UI, these properties match the placeholder names you define in your SQL query, and will appear in the Data Mapping Panel for you to map workflow properties to.

For example, if your SQL query is SELECT * FROM companies WHERE name = :companyName, where the placeholder is :companyName, the property companyName will appear as a custom input property in the Data Mapping Panel.

Eg:

1
2
3
4
5
6
7
8
| Workflow Properties      | Mapping Lines | SQL Database Node Input Properties     |
|--------------------------|---------------|----------------------------------------|
|                          |               | input Model                            |
|                          |               | +--$martiniConnectionPool              |
|                          |               | +--$batchSize                          |
|                          |               | +--$parentJDBCBatchCursor              |
|                          |               | +--$getGeneratedKeys                   |
|        [input1]          |   ------->    | +--companyName                         |

For write operations, suppose you have an SQL insert statement like INSERT INTO companies (id, name, industry) VALUES (:id, :name, :industry). Martini will generate input properties in the SQL Database Node for id, name, and industry, allowing you to dynamically map workflow values to these properties in the Data Mapping Panel.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
| Workflow Properties      | Mapping Lines | SQL Database Node Input Properties |
|--------------------------|---------------|------------------------------------|
|                          |               | input Model                        |
|                          |               | +--$martiniConnectionPool          |
|                          |               | +--$batchSize                      |
|                          |               | +--$parentJDBCBatchCursor          |
|                          |               | +--$getGeneratedKeys               |
|        [input1]          |   ------->    | +--id                              |
|                          |               | +--name                            |
|                          |               | +--industry                        |

You can map workflow properties to these custom input properties, allowing you to dynamically set the values needed for SQL read and write operations at runtime, enabling powerful data-driven operations.

Output Mapper Layout (Right)

The output mapper shows the data returned from your SQL database operations. Different SQL operation types produce different outputs that you can map to workflow properties for further processing.

For complete details on using the Data Mapper Panel, including creating map lines, set expressions, and property management, see the Data Mapping Guide.

SELECT Single Operations

When you execute Select Single queries, your SQL database returns a single record through an output model with properties matching the column names from the fetched record:

1
2
3
4
5
6
| Database Node Output Properties | Mapping Lines | Workflow Output Properties |
|---------------------------------|---------------|---------------------------|
|      output                     |               |      [workflowOutput]     |
|      +--columnName1             |   ------->    |                           |
|      +--columnName2             |   ------->    |                           |
|      +--columnName3             |   ------->    |                           |

Output Properties for SELECT Single

Property Type Description
output Model A model object containing properties for each column in the returned record. Column names become property names that you can map to workflow properties.

💡 Tips:

  • The output model properties match exactly the column names returned by your SELECT query
  • If no record is found, the output model's properties will all be null

SELECT Multi Operations

When you execute Select Multi queries, your SQL database returns data through an inputCursor that allows you to process results efficiently:

1
2
3
| Database Node Output Properties | Mapping Lines | Workflow Output Properties |
|---------------------------------|---------------|---------------------------|
|      inputCursor                |   ------->    |      [workflowOutput]     |

Output Properties for SELECT Multi

Property Type Description
inputCursor Cursor A cursor object containing the query results. You can iterate through this cursor using a Repeat Node to process each row individually.

💡 Tips:

  • Use a Repeat Node after your SQL Database Node to iterate through each row in the cursor
  • The cursor automatically handles memory management for large result sets

Need help processing multiple records? For a detailed guide on how to loop through large result sets efficiently, see Batch Read Operations.

Write Operations (INSERT, UPDATE, DELETE)

Write operations allow you to modify data in your SQL database. Both single and batch operations share common output properties but with different behaviors.

Common Output Properties for Write Operations

Property Type Description Single Operations Batch Operations
updateCount Integer Number of records affected by the operation Total records affected Total records affected across entire batch
generatedKeys Array Auto-generated primary keys (e.g., auto-increment IDs). Your table must generate keys automatically (e.g., AUTO_INCREMENT, SERIAL). Keys from single Insert when $getGeneratedKeys is true All keys from Insert Batch when $getGeneratedKeys is true
inputCursor Cursor Cursor for iterating through batch data Not available Available for all batch operations

Single Write Operations (INSERT, UPDATE, DELETE)

1
2
3
4
| Database Node Output Properties | Mapping Lines | Workflow Output Properties |
|---------------------------------|---------------|---------------------------|
|      updateCount                |   ------->    |      [workflowOutput]     |
|      generatedKeys              |   ------->    |      [workflowOutput]     |

Batch Write Operations (Insert Batch, Update Batch, Delete Batch)

1
2
3
4
5
| Database Node Output Properties | Mapping Lines | Workflow Output Properties |
|---------------------------------|---------------|---------------------------|
|      outputCursor               |   ------->    |      [workflowOutput]     |
|      updateCount                |   ------->    |      [workflowOutput]     |
|      generatedKeys              |   ------->    |      [workflowOutput]     |

💡 Key Differences:

  • Single operations: Only updateCount and generatedKeys (Insert only)
  • Batch operations: Includes inputCursor for iteration plus updateCount and generatedKeys (Insert Batch only)
  • generatedKeys: Only available for Insert and Insert Batch operations when $getGeneratedKeys is true
  • Batch processing: Use a Repeat Node with inputCursor to process each record in the batch

Need help processing multiple records? For a detailed guide on how to loop through large result sets efficiently, see Batch Write Operations.

For complete details on using the Data Mapper Panel, including creating map lines, set expressions, and property management, see the Data Mapping Guide.

Troubleshooting

Problem Detection Cause Fix Affected Versions
Connection error Node fails with connection error Invalid or missing SQL connection Verify connection settings in Martini v2.2+
Placeholder not mapped Workflow fails or returns null values Required input property not mapped Map all placeholders in Data Mapping Panel v2.2+
SQL syntax error Error message from database Invalid SQL statement Check SQL syntax and placeholders v2.2+

See Workflow Database Node: Troubleshooting for more.

Helpful Resources