MuleSoft Database Connector
The Database Connector lets your Mule application interact with relational databases — reading data, inserting records, updating rows, deleting entries, and calling stored procedures. It supports MySQL, PostgreSQL, Oracle, Microsoft SQL Server, and any database with a JDBC driver.
Setting Up the Database Connector
Add the Database Connector to your project by searching for "Database" in the Mule Palette and clicking Add to Project. The connector's Maven dependency gets added to your pom.xml automatically.
You also need the JDBC driver for your specific database. Add the driver as a dependency in pom.xml with the provided scope replaced by the appropriate classifier.
pom.xml Dependency for MySQL
<!-- Database Connector --> <dependency> <groupId>org.mule.connectors</groupId> <artifactId>mule-db-connector</artifactId> <version>1.14.0</version> <classifier>mule-plugin</classifier> </dependency> <!-- MySQL JDBC Driver --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.33</version> </dependency>
Database Connector Configuration
Create one connector configuration for each database your application connects to. This configuration is shared across all Database operations in the project.
MySQL Connection Configuration
Configuration Name: MySQL_Orders_Config
Connection Type: MySQL Connection
Host: db.mycompany.com
Port: 3306
Database: orders_db
User: mule_app_user
Password: ${db.password} ← stored in secure config properties
Connection Pool:
Min Pool Size: 2
Max Pool Size: 10
Acquire Timeout: 5000 ms
Max Wait Time: 30000 ms
Database Connector Operations
SELECT — Query Records
Flow: getActiveCustomersFlow
[Scheduler: every 1 hour]
│
▼
[Database: Select]
SQL: SELECT id, name, email, created_date
FROM customers
WHERE status = :status
ORDER BY created_date DESC
LIMIT :pageSize
Input Parameters:
status: "active"
pageSize: 100
│
▼
Payload becomes a List of Maps:
[
{ "id": 1, "name": "Alice", "email": "alice@x.com", "created_date": "2024-01-10" },
{ "id": 2, "name": "Bob", "email": "bob@x.com", "created_date": "2024-01-09" }
]
INSERT — Add New Records
Flow: createOrderFlow
[HTTP Listener: POST /orders]
│
▼
[Database: Insert]
SQL: INSERT INTO orders (customer_id, product_id, quantity, total, created_at)
VALUES (:customerId, :productId, :quantity, :total, :now)
Input Parameters:
customerId: #[payload.customerId]
productId: #[payload.productId]
quantity: #[payload.quantity]
total: #[payload.total]
now: #[now() as String {format: "yyyy-MM-dd HH:mm:ss"}]
Auto Generated Keys: enabled
│
▼
Payload becomes:
[{ "GENERATED_KEY": 10042 }]
[Set Variable: newOrderId = payload[0].GENERATED_KEY]
UPDATE — Modify Existing Records
[Database: Update]
SQL: UPDATE orders
SET status = :newStatus,
updated_at = :now
WHERE id = :orderId
Input Parameters:
newStatus: "shipped"
now: #[now() as String {format: "yyyy-MM-dd HH:mm:ss"}]
orderId: #[vars.orderId]
Payload becomes: { "affectedRows": 1 }
DELETE — Remove Records
[Database: Delete]
SQL: DELETE FROM sessions WHERE expires_at < :cutoff
Input Parameters:
cutoff: #[(now() - |PT24H|) as String {format: "yyyy-MM-dd HH:mm:ss"}]
Payload becomes: { "affectedRows": 42 }
Stored Procedure
[Database: Stored Procedure]
SQL: {call sp_calculate_monthly_revenue(:month, :year, :result)}
Input Parameters:
month: 1
year: 2024
Output Parameters:
result: NUMERIC
│
▼
Payload: { "result": 157823.50 }
Bulk Insert for Performance
When inserting many records at once, use Bulk Insert instead of calling Insert in a loop. Bulk Insert sends all records to the database in one round trip, which is dramatically faster.
[Database: Bulk Insert]
SQL: INSERT INTO products (sku, name, price) VALUES (:sku, :name, :price)
Input Parameters: #[payload]
payload must be a List of Maps:
[
{ "sku": "PEN-01", "name": "Blue Pen", "price": 1.50 },
{ "sku": "BOK-05", "name": "Notebook", "price": 4.99 },
{ "sku": "BAG-03", "name": "Laptop Bag","price": 24.99 }
]
Result: inserts all 3 rows in one database call (not 3 separate calls)
Parameterized Queries and SQL Injection Prevention
Always use input parameters (:paramName) instead of string concatenation. String concatenation opens your application to SQL injection attacks. MuleSoft's Database Connector uses prepared statements under the hood when you use input parameters, which prevents injection entirely.
Safe vs Unsafe Queries
UNSAFE (SQL Injection Risk):
SQL: "SELECT * FROM users WHERE name = '" ++ payload.name ++ "'"
If payload.name = "'; DROP TABLE users; --"
→ SQL becomes: SELECT * FROM users WHERE name = ''; DROP TABLE users; --'
→ Entire users table gets deleted!
SAFE (Parameterized):
SQL: SELECT * FROM users WHERE name = :name
Input Parameters: { name: payload.name }
MuleSoft sends the value as a parameter, not as SQL text.
Injection attempt has no effect — treated as literal text.
Database Transactions
When multiple database operations must all succeed or all fail together, wrap them in a transaction using the Try scope with a transaction type set to LOCAL.
[Try Scope: Transaction Type = LOCAL]
│
├── [Database: Insert order into orders table]
├── [Database: Update inventory count]
└── [Database: Insert payment record]
Error Handler inside Try:
On Error Propagate: ANY
→ Rolls back all three operations
→ Either all three succeed or none do
Streaming Large Result Sets
For SELECT queries returning thousands of rows, enable Streaming in the Select operation. Streaming reads rows one at a time from the database cursor instead of loading all rows into memory. Use streaming with For Each or Batch Processing to process large result sets without out-of-memory errors.
[Database: Select with Streaming]
SQL: SELECT * FROM large_transactions_table
Streaming Strategy: REPEATABLE_FILE_STORE_STREAM
│
▼
[For Each: process each row one at a time]
[Transform: map row to target format]
[HTTP Request: send to target API]