6.1 Database Connector: Select, Insert, Update, Stored Procedures & Watermarking

Database Connector: Select, Insert, Update, Stored Procedures & Watermarking

Relational databases remain core Systems of Record across enterprise IT landscapes. In Mule 4, the Database Connector provides a standardized, reactive, and non-blocking interface to interact with any relational database that supports the Java Database Connectivity (JDBC) standard—including MySQL, Oracle, PostgreSQL, Microsoft SQL Server, and IBM Db2. Understanding how to configure connection pools, execute CRUD operations, parameterize SQL queries, run stored procedures, and configure automated watermarking is essential for building robust Mule applications.

---

1. Database Connector Architecture & Maven Dependencies

Unlike legacy Mule runtimes that bundled database drivers, Mule 4 follows a modular, decoupled Maven dependency model. The Database Connector module itself provides the integration engine, but the specific JDBC driver JAR must be supplied as a project dependency.

+-----------------------------------------------------------------------------------------+
|                         DATABASE CONNECTOR ARCHITECTURE                                 |
|                                                                                         |
|   +---------------------------------------------------------------------------------+   |
|   | Mule Application Flow (<db:select>, <db:insert>, <db:stored-procedure>)        |   |
|   +---------------------------------------------------------------------------------+   |
|                                            |                                            |
|                                            v                                            |
|   +---------------------------------------------------------------------------------+   |
|   | Mule Database Connector Extension (mule-db-connector artifact)                 |   |
|   +---------------------------------------------------------------------------------+   |
|                                            |                                            |
|                                            v                                            |
|   +---------------------------------------------------------------------------------+   |
|   | JDBC Driver (Shared Library in pom.xml: MySQL / Oracle / PostgreSQL / MSSQL)    |   |
|   +---------------------------------------------------------------------------------+   |
|                                            | (JDBC Protocol over TCP/IP)                |
|                                            v                                            |
|   +---------------------------------------------------------------------------------+   |
|   | Relational Database Engine (MySQL, Oracle DB, AWS RDS PostgreSQL, etc.)         |   |
|   +---------------------------------------------------------------------------------+   |
+-----------------------------------------------------------------------------------------+

Maven POM Configuration (pom.xml)

To use a specific database driver (e.g., MySQL), you must declare the driver dependency and register it inside the mule-maven-plugin configuration as a shared library:

<dependencies>
    <!-- Mule Database Connector Dependency -->
    <dependency>
        <groupId>org.mule.connectors</groupId>
        <artifactId>mule-db-connector</artifactId>
        <version>1.14.0</version>
        <classifier>mule-plugin</classifier>
    </dependency>
    <!-- JDBC Driver Dependency -->
    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
        <version>8.3.0</version>
    </dependency>
</dependencies>

<build>
    <plugins>
        <plugin>
            <groupId>org.mule.tools.maven</groupId>
            <artifactId>mule-maven-plugin</artifactId>
            <version>4.1.0</version>
            <extensions>true</extensions>
            <configuration>
                <sharedLibraries>
                    <sharedLibrary>
                        <groupId>com.mysql</groupId>
                        <artifactId>mysql-connector-j</artifactId>
                    </sharedLibrary>
                </sharedLibraries>
            </configuration>
        </plugin>
    </plugins>
</build>

[!IMPORTANT] Shared Library Declaration is Mandatory If the JDBC driver dependency is present in <dependencies> but omitted from <sharedLibraries> in the mule-maven-plugin configuration, the Mule runtime classloader will fail at runtime with a java.lang.ClassNotFoundException or Cannot load driver class error during application startup.

---

2. Global Database Configurations & Connection Pooling

Mule provides pre-configured connection types for major databases as well as a Generic Connection for any standard JDBC driver:

Connection Pooling Configuration

Every database connection should configure a Connection Pool to manage TCP socket connections efficiently and prevent database connection exhaustion under high concurrency:

<db:config name="Database_Config" doc:name="Database Config">
    <db:my-sql-connection 
        host="${db.host}" 
        port="${db.port}" 
        user="${db.user}" 
        password="${db.password}" 
        database="${db.databaseName}">
        <db:pooling-profile 
            maxPoolSize="20" 
            minPoolSize="5" 
            acquireIncrement="2" 
            maxIdleTime="30" 
            maxIdleTimeExcessConnections="10" />
    </db:my-sql-connection>
</db:config>

• maxPoolSize: The maximum number of active database connections the pool can open simultaneously (default: 5).
• minPoolSize: The minimum number of idle connections maintained in the pool ready for immediate use.
• maxIdleTime: Seconds a connection can remain idle before being closed.

---

3. Core Database Operations & Return Data Types

Understanding what each database operation returns is a primary topic on the Developer I exam:

+-----------------------------------------------------------------------------------------+
|                          DATABASE OPERATION RETURN TYPES                                |
|                                                                                         |
|   1. <db:select>            ---> Array of Objects / Maps:                               |
|                                  [ { "ID": 101, "NAME": "Acme Corp", "STATUS": "A" },  |
|                                    { "ID": 102, "NAME": "Global Tech", "STATUS": "A" } ]|
|                                  (CursorProvider streaming iterator)                    |
|                                                                                         |
|   2. <db:insert>            ---> Result Object / Map:                                   |
|                                  { "affectedRows": 1, "generatedKeys": { "ID": 103 } }  |
|                                                                                         |
|   3. <db:update>            ---> Result Object / Map:                                   |
|                                  { "affectedRows": 5 }                                  |
|                                                                                         |
|   4. <db:delete>            ---> Result Object / Map:                                   |
|                                  { "affectedRows": 2 }                                  |
|                                                                                         |
|   5. <db:stored-procedure>  ---> Map of OUT Parameters & Result Sets:                   |
|                                  { "totalCount": 42, "resultSet1": [ { ... } ] }        |
+-----------------------------------------------------------------------------------------+

Operation Summary Matrix

---

4. Parameterized Queries vs. SQL Injection Hazards

When passing dynamic variables, query parameters, or flow attributes into database operations, you must always use parameterized queries.

Secure: Parameterized Queries with Named Parameters

Named parameters are prefixed with a colon (:paramName) inside the SQL text, and resolved via <db:input-parameters> using a DataWeave map:

<db:select config-ref="Database_Config" doc:name="Select Active Accounts">
    <db:sql><![CDATA[
        SELECT account_id, account_name, billing_city, credit_limit 
        FROM accounts 
        WHERE status = :accountStatus 
          AND billing_country = :country 
          AND credit_limit >= :minCredit
    ]]></db:sql>
    <db:input-parameters><![CDATA[#[
        {
            accountStatus: vars.requestedStatus default 'ACTIVE',
            country: attributes.queryParams.country,
            minCredit: attributes.queryParams.minCredit as Number default 0
        }
    ]]]></db:input-parameters>
</db:select>

Insecure Anti-Pattern: String Concatenation & Interpolation

Never concatenate or interpolate variables directly into the SQL string using DataWeave $(...) or string concatenation (++):

<!-- CRITICAL ANTI-PATTERN: DO NOT USE IN PRODUCTION -->
<db:select config-ref="Database_Config" doc:name="Insecure Select">
    <db:sql><![CDATA[
        SELECT * FROM accounts WHERE account_id = '$(attributes.queryParams.id)'
    ]]></db:sql>
</db:select>

Why String Interpolation Fails:

1. SQL Injection Vulnerability: If a malicious caller submits id=1' OR '1'='1, the entire database table is exposed or modified.
2. Type Coercion Failures: String interpolation converts dates, numbers, and nulls into raw strings, causing database SQL syntax and parser errors.
3. Loss of Prepared Statement Caching: Relational databases cannot reuse execution plans for interpolated strings, causing severe CPU degradation on database servers under high transaction volume.

---

5. Stored Procedures (<db:stored-procedure>)

Stored procedures encapsulate database-side business logic. The Mule Database Connector executes stored procedures using standard JDBC callable syntax {call procedure_name(:in_param, :out_param)}.

<db:stored-procedure config-ref="Database_Config" doc:name="Execute GetCustomerSummary">
    <db:sql><![CDATA[{call sp_get_customer_summary(:inCustomerId, :outTotalOrders, :outTotalBalance, :outAccountStatus)}]]></db:sql>
    <db:input-parameters><![CDATA[#[
        {
            inCustomerId: vars.customerId
        }
    ]]]></db:input-parameters>
    <db:output-parameters>
        <db:output-parameter key="outTotalOrders" type="INTEGER" />
        <db:output-parameter key="outTotalBalance" type="DECIMAL" />
        <db:output-parameter key="outAccountStatus" type="VARCHAR" />
    </db:output-parameters>
</db:stored-procedure>

Accessing Stored Procedure Results

After execution, the operation outputs a map where the keys match the defined <db:output-parameter> keys:

// Transform Message reading Stored Procedure OUT parameters
%dw 2.0
output application/json
---
{
    customerId: vars.customerId,
    totalOrdersCount: payload.outTotalOrders,
    outstandingBalance: payload.outTotalBalance,
    currentStatus: payload.outAccountStatus
}

• IN-OUT Parameters: Configured via <db:in-out-parameters> when a single parameter passes initial data into the procedure and receives modified data upon completion.
• Cursor / Result Set OUT Parameters: For procedures that return cursors (e.g., Oracle SYS_REFCURSOR), declare <db:output-parameter key="orderCursor" type="CURSOR" />. The cursor is returned as an iterable list of maps inside payload.orderCursor.

---

6. Database Polling & Automated Watermarking

Integrating batch-oriented legacy databases often requires periodically checking for newly created or recently modified rows. Mule 4 provides the On Table Row inbound message source (<db:listener-on-table-row>) with built-in automatic watermarking.

+-----------------------------------------------------------------------------------------+
|                           ON TABLE ROW WATERMARKING FLOW                                |
|                                                                                         |
|   [Database Table: ACCOUNTS]                                                            |
|   +----+---------------+---------------------+                                          |
|   | ID | ACCOUNT_NAME  | LAST_MODIFIED       |                                          |
|   +----+---------------+---------------------+                                          |
|   | 1  | Acme Corp     | 2026-08-22 10:00:00 |                                          |
|   | 2  | TechGlobal    | 2026-08-22 10:15:00 |                                          |
|   | 3  | Nexus Group   | 2026-08-22 10:30:00 | <--- [Current Watermark Value]           |
|   +----+---------------+---------------------+                                          |
|                           |                                                             |
|                           v (Polls table WHERE LAST_MODIFIED > 10:30:00)                |
|   +---------------------------------------------------------------------------------+   |
|   | <db:listener-on-table-row> (Watermark Column: LAST_MODIFIED)                    |   |
|   | Emits ONE Mule Event per retrieved row:                                         |   |
|   | Event 1: payload = { ID: 4, NAME: "Alpha Ltd", LAST_MODIFIED: 10:45:00 }         |   |
|   | Event 2: payload = { ID: 5, NAME: "Beta Corp", LAST_MODIFIED: 10:50:00 }          |   |
|   +---------------------------------------------------------------------------------+   |
|                           |                                                             |
|                           v (Updates Watermark in Object Store to 10:50:00)             |
+-----------------------------------------------------------------------------------------+

Inbound XML Configuration (<db:listener-on-table-row>)

<flow name="poll-database-records-flow">
    <db:listener-on-table-row 
        config-ref="Database_Config" 
        table="accounts" 
        watermarkColumn="last_modified" 
        idColumn="account_id" 
        doc:name="On Table Row: Accounts">
        <scheduling-strategy>
            <fixed-frequency frequency="5" timeUnit="MINUTES" />
        </scheduling-strategy>
    </db:listener-on-table-row>
    
    <logger level="INFO" message="#['Processing single account row: ' ++ payload.account_id ++ ' Name: ' ++ payload.account_name]" />
    
    <!-- Rest of flow processes ONE row at a time -->
</flow>

Key Characteristics of <db:listener-on-table-row>:

1. Row-by-Row Execution: Unlike <db:select>, which returns the entire result set as an Array<Object> in one Mule event, On Table Row executes the Mule flow once per row returned. If the query finds 50 matching rows, 50 individual Mule events are dispatched through the flow.
2. Watermarking Storage: The highest value from watermarkColumn is automatically saved to an internal Object Store upon successful processing. In subsequent poll cycles, Mule appends a dynamic WHERE watermarkColumn > lastSavedWatermark filter.
3. ID Column: Used in combination with the watermark column to disambiguate rows sharing identical timestamp values.

---

7. Streaming Large Result Sets & Auto-Paging

When a <db:select> operation queries millions of records, loading the entire dataset into Java heap memory would cause java.lang.OutOfMemoryError failures. Mule 4 solves this via Cursor Streaming.

• The Database Select operation returns a CursorProvider object.
• Records are fetched from the database in batches governed by the fetchSize attribute (e.g., fetchSize="1000").
• Downstream components (such as DataWeave transforms, For Each scopes, or Batch Jobs) consume records iteratively from the stream.
• As long as the streaming payload is processed sequentially, memory consumption remains virtually constant regardless of whether the table contains 10 rows or 10,000,000 rows.

---

8. Exam Watch: Core Database Scenarios

[!IMPORTANT] Parameterized Queries vs String Interpolation Always select answers using :paramName inside <db:sql> paired with <db:input-parameters>. Exam options using $(vars.myVar) or '++ vars.myVar ++' inside SQL strings are incorrect anti-patterns.

[!WARNING] Select vs On Table Row Event Payloads <db:select> returns an Array<Object> (a collection of row maps). In contrast, <db:listener-on-table-row> triggers the flow with an individual Object (a single row map) for each database record.

[!TIP] Target Variables with Database Operations Use target="vars.dbResult" on <db:select> or <db:insert> operations when you must verify database existence or record counts without overwriting the original inbound HTTP or JMS payload.