Migrating the Database Connector | MuleSoft Documentation
# Migrating the Database Connector
The main changes to the Database connector from Mule 3 to Mule 4 include:
  * All database operations (such as Select, Insert, Update, Delete, and others) have been refactored into separate operations. There are also new, dedicated bulk operations for Mule 4.
  * Use of DataWeave: Database operations, such as Insert and Update, support DataWeave version 2 transformations. You can construct the data sets that insert or update the database without message side effects, and you no longer use message enrichers.
  * You can perform dynamic and static queries in similar ways.
  * The Mule streaming framework is automatically configured for your operations. You can execute a select statement and process the results asynchronously without worrying about leaking connections.


## What’s Covered Here?
  * Database Connection Configurations
    * Data Source Reference
    * Examples: Detailed Connector Configurations
  * Migrating Dynamic Queries
  * Streaming Large Results
  * Migrating Bulk Operations
  * Migrating the Bulk Execute Operation
  * Transactional Actions
  * 

## Database Connection Configurations
The Database connectors for Mule 3 and Mule 4 support Derby, MySQL, Oracle, and generic database configurations. For Mule 4, Microsoft SQL Server and Data Source Reference connections are also supported.
The XML for these connection configurations has changed in Mule 4:
  * Mule 3: Database elements such as `<db:derby-config />` are the top-level elements.
  * Mule 4: `<db:config />` is the top-level element for all database connection configurations in Mule 4, and specific database configurations are nested under separate `<db:config />` elements:
    * Derby: `<db:derby-connection />`
    * Microsoft SQL Server: `<db:mssql-connection />`
    * MySQL: `<db:my-sql-connection />`
    * Oracle: `<db:oracle-connection />`
    * Generic: `<db:generic-connection />`
    * Data Source Reference Connection: `<db:data-source-connection />`


Mule 3 Example: Derby Connection Configuration
```
<db:derby-config name="DerbyConfig" url="derbydatabase.com/MydatabaseTest"/>
```

Notice that `<db:derby-config />` in the Mule 3 example above changes to `<db:config />` in the Mule 4 example below, and the attribute for the URL to the database changes from `url` to `database` in Mule 4. The `<db:derby-connection />` child element is new in Mule 4.
Mule 4 Examples: Derby Connection Configuration
```
<db:config name="DerbyConfig">
 <db:derby-connection database="derbydatabase.com/MydatabaseTest" />
</db:config>
```

Also note that in Mule 4, additional `<db:derby-connection />` settings (XML attributes) are available:
  * `create="true|false"`: Whether to create the database if it does not exist.
  * `subsubProtocol="directory"`: Type of SubsubProtocol to be used by Derby. The available options are `directory`, `memory`, `classpath` and `jar`.


The next section describes connection configurations for other databases.
### Data Source Reference
These topics provide Mule 4 XML examples for other database connections and include connector-specific settings:
  * Mule 4 Example: Data Source Reference
  * Mule 4 Example: Generic
  * Mule 4 Example: Microsoft SQL Server
  * Mule 4 Example: MySQL
  * Mule 4 Example: Oracle JDBC Database
  * Mule 4 Example: Microsoft SQL Server
  * Example: Maven dependency setting for a Microsoft SQL Server driver


Note that the Mule 3 example for Derby in the previous section shows most of the main differences between Mule 3 and Mule 4 for database connection configurations, including My SQL, Oracle, and so on. It also shows the main migration pattern from Mule 3 to Mule 4 database connections:
  * Derby: Database Connection Configurations


#### Mule 4 Example: Data Source Reference
To use a Data Source Reference you need to:
1 - Create a XML Spring Beans file creating the Spring Bean representing the `DataSource`
datasource-spring-beans.xml
```
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="
        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-current.xsd">
  <bean id="jdbcDataSource" class="org.enhydra.jdbc.standard.StandardDataSource" destroy-method="shutdown">
    <property name="driverName" value="org.apache.derby.jdbc.EmbeddedDriver"/>
    <property name="url" value="jdbc:derby:muleEmbeddedDB;create=true"/>
  </bean>
</beans>
```

Is required to configure the Maven dependency which contains the DataSource implementation. For this example, `org.enhydra.jdbc.standard.StandardDataSource`, the required dependency is: `<dependency>` `<groupId>com.experlog</groupId>` `<artifactId>xapool</artifactId>` `<version>1.5.0</version>` `</dependency>`  
---  
2 - Create Data Source Connection
Import the `datasource-spring-beans.xml` file using the **Mule Spring Module** [link to Spring Module]  
---  
Create the DB Config with the DataSource Connection, and add as `dataSourceRef` the Spring Bean ID of the DataSource Bean created in `datasource-spring-beans.xml`.  
```
<spring:config name="spring-beans" files="datasource-spring-beans.xml"/> **(1)**
<db:config name="db-datasource">
  <db:data-source-connection dataSourceRef="jdbcDataSource"> **(2)**
    <reconnection failsDeployment="true">
      <reconnect frequency="4000" count="4"/>
    </reconnection>
    <db:column-types>
      <db:column-type id="124"
              typeName="column_type"
              className="class_name"/>
    </db:column-types>
    <db:pooling-profile maxPoolSize="6" minPoolSize="1"
              acquireIncrement="2" preparedStatementCacheSize="6"
              maxWait="1" maxWaitUnit="MINUTES"/>
  </db:data-source-connection>
</db:config>
```

This setting is unique to Data Source Reference:
  * `dataSourceRef="my_ref_here"`: Reference to a JDBC DataSource object. This object is typically created using Spring. When using XA transactions, an XADataSource object must be provided.


#### Mule 4 Example: Generic
```
<db:config name="generic_connection">
 <db:generic-connection url="url_here" driverClassName="driver_class_name_here"
  transactionIsolation="READ_COMMITTED" useXaTransactions="true"/>
</db:config>
```

These setting are unique to a Generic database:
  * `url="url_here"` for the JDBC URL to be used to connect to the database.
  * `driverClassName="driver_class_name_here"` for the fully-qualified name of the database driver class.


The Maven dependency which contains the Class referenced in the `driverClassName` parameter should be configured in the Mule Application. Also, due to new Mule 4 ClassLoading mechanism, this dependency must be declared as a Shared Library to be exported to the DB Connector.   
---  
See also, Database Transactions.
#### Mule 4 Example: Microsoft SQL Server
```
<db:config name="Microsoft_SQL_Server">
 <db:mssql-connection host="my_host" user="myuser" password="mypassword" databaseName="mydatabase" >
 </db:mssql-connection>
</db:config>
```

See also, Database Transactions.
#### Mule 4 Example: MySQL
```
<db:config name="MySQL">
 <db:my-sql-connection host="myhost" user="myuser" password="mypassword" database="mydatabase" >
 </db:my-sql-connection>
</db:config>
```

See also, Database Transactions.
#### Mule 4 Example: Oracle JDBC Database
```
<db:config name="Oracle_Connection">
 <db:oracle-connection host="myhost" user="myuser" password="mypassword" instance="myinstance" >
 </db:oracle-connection>
 <expiration-policy />
</db:config>
```

See also, Database Transactions.
#### Example: Maven dependency setting for a Microsoft SQL Server driver
Microsoft SQL Server, MySQL, Derby, Oracle configurations require a driver.
```
<dependency>
 <groupId>com.microsoft.sqlserver</groupId>
 <artifactId>mssql-jdbc</artifactId>
 <version>6.2.2.jre8</version>
</dependency>
```

Because of the new Mule 4 ClassLoading mechanism, this dependency must be declared as a Shared Library to be exported to the DB Connector. Using Studio, this will be automatically configured.   
---  
### Examples: Detailed Connector Configurations
In Mule 4, you can add child elements for these settings under the database connection configurations (such as `<db:mysql-config />`):
  * Database Transactions
    * Transactional Actions
    * Transaction Isolation
    * XA Transactions
  * Connection Properties
  *   * Pooling Profile
  * Reconnection Strategies
See also, Database Transactions.
#### Mule 4 Example: Oracle JDBC Database
```
<db:config name="Oracle_Connection">
 <db:oracle-connection host="myhost" user="myuser" password="mypassword" instance="myinstance" >
 </db:oracle-connection>
 <expiration-policy />
</db:config>
```

See also, Database Transactions.
#### Example: Maven dependency setting for a Microsoft SQL Server driver
Microsoft SQL Server, MySQL, Derby, Oracle configurations require a driver.
```
<dependency>
 <groupId>com.microsoft.sqlserver</groupId>
 <artifactId>mssql-jdbc</artifactId>
 <version>6.2.2.jre8</version>
</dependency>
```

Because of the new Mule 4 ClassLoading mechanism, this dependency must be declared as a Shared Library to be exported to the DB Connector. Using Studio, this will be automatically configured.   
---  
### Examples: Detailed Connector Configurations
In Mule 4, you can add child elements for these settings under the database connection configurations (such as `<db:mysql-config />`):
  * Database Transactions
    * Transactional Actions
    * Transaction Isolation
    * XA Transactions
  * Connection Properties
  *   * Pooling Profile
  * Reconnection Strategies


#### Database Transactions
##### Transaction Isolation
Transactions are for the transaction isolation level to set on the driver when connecting the database.
  * **NOT_CONFIGURED** (Default)
  * **READ_COMMITTED** (`transactionIsolation="READ_COMMITTED"`)
  * **READ_UNCOMMITTED** (`transactionIsolation="READ_UNCOMMITTED"`)
  * **REPEATABLE_READ** (`transactionIsolation="REPEATABLE_READ"`)
  * **SERIALIZABLE** (`transactionIsolation="SERIALIZABLE"`)


Mule 3 Example: Transaction Isolation
```
<db:mysql-config name="db_config" host="0.0.0.0" port="3306" transactionIsolation="READ_COMMITTED"/>
```

Mule 4 Example: Transaction Isolation
```
<db:config name="db_config" >
 <db:my-sql-connection host="0.0.0.0" port="3306" transactionIsolation="READ_COMMITTED"/>
</db:config>
```

##### XA Transactions
Connections can be configured to work in XA Transactions, in both **Mule 3** and **Mule 4** this can be configured in the connection configuration setting `useXaTransactions="true"`
Mule 3 Example: XA Transactions
```
<db:mysql-config name="db_config" host="0.0.0.0" port="3306" useXaTransactions="true"/>
```

Mule 4 Example: XA Transactions
```
<db:config name="db_config" >
 <db:my-sql-connection host="0.0.0.0" port="3306" useXaTransactions="true"/>
</db:config>
```

#### Connection Properties
**MySQL** and **Microsoft SQL Server** database configurations provide connection property settings, these properties are injected to the JDBC Connection as additional properties.
Mule 3 Example: Connection Properties
```
<db:mysql-config name="db_config" host="0.0.0.0" port="3306" user="root" password="my-secret-pw">
  <db:connection-properties>
    <db:property key="userId" value="123123"/>
    <db:property key="permissions" value="SYS"/>
  </db:connection-properties>
</db:mysql-config>
```

Mule 4 Example: Connection Properties
```
<db:config name="db_config" >
 <db:my-sql-connection host="0.0.0.0" port="3306" user="root" password="my-secret-pw">
  <db:connection-properties >
   <db:connection-property key="userId" value="123123" />
   <db:connection-property key="permissions" value="SYS" />
  </db:connection-properties>
 </db:my-sql-connection>
</db:config>
```

#### Column Types
  * Column types (`<db:column-types/>`) for non-standard column types.


#### Pooling Profile
  * Pooling profile (`<db:pooling-profile/>`) for JDBC Data Sources capable of connection pooling connections. It’s almost identical to Connection Pooling in Mule 3.


#### Reconnection Strategies
Reconnection strategy settings are similar in Mule 3 and Mule 4. However, custom reconnection strategies are not available in Mule 4.
The examples below show changes to the XML for these settings:
  * **None** : Default. Same as Do Not Use A Reconnection Strategy in Mule 3.
  * **Standard** : Same as Standard Reconnection in Mule 3. Both have the same options: Frequency (ms), Reconnection Attempts
  * **Forever** : Same as Reconnect Forever in Mule 3.
Mule 3 Examples: Reconnection Settings
```
<db:mysql-config ...>
 <reconnect frequency="4000" count="4"/>`
</db:mysql-config>
<db:mysql-config ...>
 <reconnect-forever frequency="4000"/>`
</db:mysql-config>
```

Mule 4 Examples: Reconnection Settings
```
<db:mssql-connection ...>
 <reconnection failsDeployment="true">
  <reconnect frequency="4000" count="4"/>
 </reconnection>
</db:mssql-connection>
<db:mssql-connection ...>
 <reconnection >
  <reconnect-forever frequency="4000" />
 </reconnection>
</db:mssql-connection>
```



## Database Connector Operations
  * Query for SQL query text and input parameters
  * Streaming strategy settings (as shown here in Streaming Large Results)
  * Transactional Actions
  * Query settings
  * Error mapping for errors, including DB:BAD_SQL_SYNTAX, DB:QUERY_EXECUTION
  * Mule 4 input and output metadata for attributes, payload, and variables


### Migrating Parameterized Queries
In Both **Mule 3** and **Mule 4** input parameters are supplied as key-value pairs. Those keys are used with the semicolon character (:) to reference a parameter value by name, this means that written SQL parameterized queries in **Mule 3** doesn’t need changes to be used in **Mule 4**. This approach is recommended to avoid SQL injection attacks, and it allows the connector to perform optimizations that improve the overall performance of the Mule app.
#### Input Parameters
  * In **Mule 3** each Input Parameter was defined as a `<db:in-param/>` element inside the `<db:select/>` operation which had an `name` attribute for the parameter name, and a `value` attribute which holds the parameter value.
  * In **Mule 4** , the input parameters are defined as Map `<db:input-parameters>`, where the key is the name of the parameter, and the value as the parameter value. This map can only be created using DataWeave.


Mule 3 Example: Parameterized SELECT Query
```
<db:select config-ref="databaseConfig">
  <db:parameterized-query>
    SELECT * FROM employees WHERE last_name = :last_name
  </db:parameterized-query>
  <db:in-param name="last_name" value="#[flowVars.lastName]"/>
</db:select>
```

Mule 4 Example: Parameterized SELECT Query
```
<db:select config-ref="databaseConfig">
 <db:sql>
  SELECT * FROM employees WHERE last_name = :last_name
 </db:sql>
 <db:input-parameters>
  #[
   {
    last_name : vars.lastName
   }
  ]
 </db:input-parameters>
</db:select>
```

### Migrating Dynamic Queries
Sometimes, you not only need to parameterize the WHERE clause, but also parameterize parts of the query itself. Example use cases for this would be queries which need to hit online/historic tables depending on a condition, or complex queries where the project columns need to vary.
In Mule 3, the concept of select was split in parameterized and dynamic queries, and you couldn’t use both at the same time. You had to choose between having a dynamic query or having the advantages of using parameters (SQL Injection protection, PreparedStatement optimization, etc.). Furthermore, the syntax to do one or the other was different, so you had to learn two different ways of doing the same thing.
But with the new Database Connector in Mule 4, you can now use both methods at the same time by using expressions in the query:
Mule 3 Example: Dynamic SELECT Query
```
<db:select config-ref="databaseConfig">
 <db:dynamic-query>
  SELECT * FROM #[flowVars.table] WHERE name = #[flowVars.name]
 </db:dynamic-query>
</db:select>
```

Mule 4 Example: Dynamic SELECT Query
```
<db:select config-ref="databaseConfig">
 <db:sql>
  #['SELECT * FROM $(vars.table) WHERE name = $(vars.name)']
 </db:sql>
</db:select>
```
### Migrating Dynamic Queries
Sometimes, you not only need to parameterize the WHERE clause, but also parameterize parts of the query itself. Example use cases for this would be queries which need to hit online/historic tables depending on a condition, or complex queries where the project columns need to vary.
In Mule 3, the concept of select was split in parameterized and dynamic queries, and you couldn’t use both at the same time. You had to choose between having a dynamic query or having the advantages of using parameters (SQL Injection protection, PreparedStatement optimization, etc.). Furthermore, the syntax to do one or the other was different, so you had to learn two different ways of doing the same thing.
But with the new Database Connector in Mule 4, you can now use both methods at the same time by using expressions in the query:
Mule 3 Example: Dynamic SELECT Query
```
<db:select config-ref="databaseConfig">
 <db:dynamic-query>
  SELECT * FROM #[flowVars.table] WHERE name = #[flowVars.name]
 </db:dynamic-query>
</db:select>
```

Mule 4 Example: Dynamic SELECT Query
```
<db:select config-ref="databaseConfig">
 <db:sql>
  #['SELECT * FROM $(vars.table) WHERE name = $(vars.name)']
 </db:sql>
</db:select>
```

GOTCHA: The Mule 3 example is less performant and vulnerable to SQL Injection attacks since the entire query is being treated as a string instead of using PreparedStatements
## Streaming Large Results
Streaming can handle results queries to big database tables, which might return tens of thousands of records, especially for integration use cases. Streaming is a great solution for this.
### Enabling Streaming
In **Mule 3** , streaming is disabled by default, so you have to enable it to use it.
In **Mule 4** , streaming is always on but you can configure the strategy through which the runtime will achieve repeatable streaming. For more information on this, please refer to the streaming documentation page.
```
TIP: Note that disabling repeatable streaming will prevent DataWeave and other components to process the a stream many times, even in parallel.
```

### Migrating Bulk Operations
#### Enabling Bulk Mode
In **Mule 3** , you set bulk functionality through a `bulkMode="true"` setting on **Insert, Update, and Delete** operations.
In **Mule 4** , you use dedicated Bulk Insert (`<db:bulk-insert>`), Bulk Update (`<db:bulk-update>`), and Bulk Delete (`<db:bulk-delete>`) database, instead. This means that operations no longer change their behavior depending on the received payload.
#### Consuming bulk data
In **Mule 3** , in a bulk query, the connector worked similar to the `<for-each/>` component, it took the current payload value, expecting to be a collection of items, and executes the `<db:in-param />` expressions with each value of the collection.
In **Mule 4** , is slightly different, instead of consuming the payload and executing expressions for each element in a collection, is required to inject a collection of entries.
##### Examples:
Mule 3 Example: Select with a Parameterized Query
```
<db:insert config-ref="db_config" bulkMode="true">
  <db:parameterized-query>
   INSERT INTO customers (id, name, lastName)
   VALUES (:id, :name, :lastName)
  </db:parameterized-query>
  <db:in-param name="id" value="#[payload.id]"/>
  <db:in-param name="name" value="#[payload.name]"/>
  <db:in-param name="lastName" value="#[payload.lastName]"/>
</db:insert>
```

Mule 4 Example: Using the Bulk Operation to Get Different Values
```
<db:bulk-insert config-ref="db_config" >
 <db:sql>
  INSERT INTO customers (id, name, lastName)
  VALUES (:id, :name, :lastName)
 </db:sql>
 <db:bulk-input-parameters>
 #[[{
   id: 2,
   name: 'George',
   lastName: 'Costanza'
  },
  {
   id: 3,
   name: 'Cosmo',
   lastName: 'Kramer'
  }]]
 </db:bulk-input-parameters>
</db:bulk-insert>
```

### Migrating the Bulk Execute Operation
The `<db:bulk-execute>` operation from the Mule 3 connector is replaced by the `<db:execute-script>` operation.
It’s important to note that even though the Mule 3 version uses the word `bulk`, it is not an actual bulk operation as in the examples above. Instead, it executes many individual statements using the same connection, but it does not create bulk prepared statements. So, in Mule 4, the operation is named `execute-script` to better describe what it is doing.
Mule 4 Example: Using the `execute-script` operation
```
<db:execute-script config-ref="dbConfig">
  <db:sql>
    update PLANET set NAME='Mercury' where POSITION=0;
    update PLANET set NAME='Mercury' where POSITION=4
  </db:sql>
</db:execute-script>
```

You can also extract the statements to an external file:
Mule 4 Example: Using the `execute-script` operation with an external file
```
<db:execute-script config-ref="dbConfig" file="bulk-script.sql" />
```

### Transactional Actions
When database operations are executed inside a transaction, they can determine how to interact with the transaction.
Available Transactional Actions:
  * **ALWAYS_JOIN**
  * **JOIN_IF_POSSIBLE**
  * **NOT_SUPPORTED**


Mule 3 Example: Transactional Action
```
<db:select config-ref="db_config" transactionalAction="ALWAYS_JOIN">
  <db:parameterized-query>
   SELECT * FROM people
  </db:parameterized-query>
</db:select>
```

Mule 4 Example: Transactional Action
```
<db:select config-ref="db_config" transactionalAction="ALWAYS_JOIN">
 <db:sql>
  SELECT * FROM people
 </db:sql>
</db:select>
```

### Output Target
In **Mule 3** the DB Connector had the capability of save the execution output in a desired target variable. In **Mule 4** , this can still be done, but with a few differences.
In **Mule 3** what was considered as output was just the execution result, so when configuring, for example: `target="#[flowVars.targetVar]"` this meant that the query execution will be saved in the flow variable `targetVar`.
In **Mule 4** is very similar, you have a `target` parameter which is the name of the target Flow Variable, this can’t be an expression, and the new parameter in **Mule 4** is `targetValue`, this is an expression that is evaluated against the operation result. For example this can be: `#[payload]`, to save the entire payload, or `#[payload[0]]` to just save the first row of a Select Query.
Mule 3 Example: Output Target
```
<db:select config-ref="db_config" target="#[flowVars.targetVar]">
  <db:parameterized-query>
   SELECT * FROM People
  </db:parameterized-query>
</db:select>
```

Mule 4 Example: Output Target
```
<db:select config-ref="db_config" target="targetVar" targetValue="#[payload]">
 <db:sql>
  SELECT * from People
 </db:sql>
</db:select>
```

To use the Database connector, simply add it to your application using the Studio palette or add the following dependency in your `pom.xml` file:
```
<dependency>
  <groupId>org.mule.connectors</groupId>
  <artifactId>mule-db-connector</artifactId>
  <version>1.2.0</version> <!-- or newer -->
  <classifier>mule-plugin</classifier>
</dependency>
```

## See Also
  * Database Connector Overview
  * Streaming in Mule 4.0.