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

Salesforce Certified MuleSoft Developer I Exam

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Creating Application Networks & API-Led Connectivity

1.1 The IT Delivery Gap & API-Led Connectivity Architecture 1.2 Application Network Foundations & Center for Enablement (C4E) 1.3 Anypoint Platform Architecture & Core Components

2 Chapter 2: Designing APIs with RAML & API Designer

2.1 RAML 1.0 Fundamentals, Data Types & Resource Hierarchy 2.2 Reusable API Fragments, ResourceTypes & Traits 2.3 API Mocking Service, Documentation & Anypoint Exchange Publishing

3 Chapter 3: Accessing and Modifying Mule Events

3.1 Mule 4 Event Architecture: Message, Payload, Attributes & Variables 3.2 Flow Variable Management & Target Variables Pattern 3.3 Accessing Event Metadata, Inbound Attributes & Context Properties

4 Chapter 4: Structuring Mule Applications

4.1 Modular Flow Composition: Flows, Subflows & Private Flows 4.2 Configuration Property Files & Environment Management 4.3 Secure Properties Tool, Encryption & Maven Project Packaging

5 Chapter 5: Building API Implementation Interfaces

5.1 APIkit Scaffolding, Router Configuration & Interface Generation 5.2 HTTP Listener Configuration, Path Matching & Query/URI Parameters 5.3 Interface vs. Implementation Separation & Backend Flow Wiring

6 Chapter 6: Using Connectors & External Systems

6.1 Database Connector: Select, Insert, Update, Stored Procedures & Watermarking 6.2 HTTP Request Connector: Headers, Query Params, Response Timeouts & Target Variables 6.3 Salesforce Connector, JMS Messaging & File/FTP Connectors

7 Chapter 7: Processing Records & Batch Integration

7.1 For Each Scope: Payload Iteration, Batch Size & Streaming Data 7.2 Batch Job Architecture: Input, Process, and Complete Phases 7.3 Batch Step Accept Expression, Failure Handling & Batch Aggregator 7.4 Object Store: Persisting Data Between Flow Executions

8 Chapter 8: Transforming Data with DataWeave 2.0

8.1 DataWeave 2.0 Syntax, Type System & Selectors 8.2 Core Functions: map, filter, pluck, reduce, groupBy & orderBy 8.3 Custom Functions, Format Transformations & Type Coercion 8.4 Calling Mule Flows from DataWeave & Reusing DataWeave Modules

9 Chapter 9: Routing Events & Flow Control

9.1 Choice Router: Conditional Expressions & Default Routing 9.2 Scatter-Gather Router: Parallel Execution, Aggregation & Error Traps 9.3 Async Scope, Round Robin & First Successful Routing 9.4 Validating Mule Events with the Validation Module

10 Chapter 10: Handling Errors & Exception Management

10.1 Mule 4 Error Object Hierarchy & Custom Error Types 10.2 On-Error Continue vs. On-Error Propagate Mechanisms 10.3 Try Scope Exception Isolation & Global Error Handling Strategies

11 Chapter 11: Debugging and Troubleshooting Mule Applications

11.1 Logger Configuration, Categories & Thread/Context Diagnostics 11.2 Anypoint Studio Debugger: Breakpoints, Expression Watches & Step-Through 11.3 MUnit 2.x Testing: Unit Tests, Mocking Connectors & Assertions

12 Chapter 12: Deploying and Managing APIs and Integrations

12.1 CloudHub 1.0 & 2.0 Architecture: Workers, vCores & Clustering 12.2 Runtime Manager: Application Management, Alerts, Insights & Logs 12.3 API Manager: Auto-Discovery, API Gateway, SLA Tiers & Standard Policies

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

Key Takeaways Explain This Topic AI

The Mule 4 Database Connector relies on standard JDBC drivers declared as shared library dependencies in the project's pom.xml, supporting generic JDBC, MySQL, Oracle, PostgreSQL, and Microsoft SQL Server configurations with configurable connection pooling.

The Database Select operation ( db:select ) returns a streaming CursorProvider representing an array of key-value maps/objects, enabling low-memory streaming over large result sets without loading all rows into RAM simultaneously.

Parameterized SQL queries utilizing the :paramName syntax with db:input-parameters are mandatory to prevent SQL injection attacks, enable database query-plan caching, and ensure safe type mapping, whereas DataWeave string concatenation ($(...)) inside SQL statements creates critical security vulnerabilities.

The Stored Procedure operation ( db:stored-procedure ) supports IN, OUT, and IN-OUT parameters, returning a map containing named output parameters and cursor result sets accessible directly via payload or target variables.

The Database On Table Row listener provides automatic row-by-row event generation and state watermarking over monotonically increasing ID or timestamp columns, utilizing an Object Store to poll only net-new or modified records.

Last updated: August 2026

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>

<dependency>
<groupId>org.mule.connectors</groupId>
<artifactId>mule-db-connector</artifactId>
<version>1.14.0</version>
<classifier>mule-plugin</classifier>
</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 Type XML Element Typical Driver Class Default Port
MySQL Connection <db:my-sql-connection> com.mysql.cj.jdbc.Driver 3306
Oracle Connection <db:oracle-connection> oracle.jdbc.OracleDriver 1521
PostgreSQL Connection <db:generic-connection> / <db:my-sql-connection> org.postgresql.Driver 5432
MSSQL Connection <db:mssql-connection> com.microsoft.sqlserver.jdbc.SQLServerDriver 1433
Generic Connection <db:generic-connection> Custom user-specified driver class Variable
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

Operation XML Element Primary Input Output Payload Structure

Select <db:select> SQL SELECT statement + Input Parameters Array<Object> ( org.mule.runtime.core.internal.streaming.object.CursorProvider ) containing key-value row maps

Insert <db:insert> SQL INSERT statement + Input Parameters Object containing affectedRows (integer) and optional generatedKeys map

Update <db:update> SQL UPDATE statement + Input Parameters Object containing affectedRows (integer count of modified rows)

Delete <db:delete> SQL DELETE statement + Input Parameters Object containing affectedRows (integer count of deleted rows)

Bulk Insert <db:bulk-insert> SQL INSERT + Collection Payload ( Array<Object> ) Array of integers ( Array<Number> ) indicating affected rows per batch element

Bulk Update <db:bulk-update> SQL UPDATE + Collection Payload ( Array<Object> ) Array of integers ( Array<Number> ) indicating affected rows per batch element

Stored Procedure <db:stored-procedure> Callable SQL {call proc_name(:in, :out)} Map containing named output parameters and cursor result sets

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 ( ++ ):

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

SQL Injection Vulnerability: If a malicious caller submits id=1' OR '1'='1 , the entire database table is exposed or modified.

Type Coercion Failures: String interpolation converts dates, numbers, and nulls into raw strings, causing database SQL syntax and parser errors.

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>

</flow>

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

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.

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.

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 .