Skip to content

DB Connector Reference

This documentation provides a reference guide for the DB Connector. The DB Connector allows you to connect to relational databases via JDBC and perform various operations including executing queries, modifying data, calling stored procedures, and managing transactions.

Connection configurations

The DB Connector utilizes connection pooling for efficient database interaction. You need to configure a connection before using the connector operations.

Connection parameters

Common parameters for configuring a database connection:

Connection Parameters

Parameter Name Description Required
Connection Name A unique name to identify this connection configuration. Yes
JDBC Driver Class The fully qualified name of the JDBC driver class (e.g., com.mysql.cj.jdbc.Driver, org.postgresql.Driver). Standard driver classes are supported. Yes
Database URL The JDBC URL for connecting to the database (e.g., jdbc:mysql://localhost:3306/mydatabase, jdbc:postgresql://localhost:5432/mydatabase). Yes
Database Username The username for database authentication. Yes
Database Password The password for database authentication. Yes
JDBC Driver Select Options Choose "Use Default Driver" to use the default driver for the connection else you can add your own driver with the options "Select Local Driver" or "Add Maven Dependency". All the standard drivers are supported. Yes
Allow access to database metadata If this option is enabled, the VS Code extension will connect to the database during development and display the available tables and columns. This enhances the overall developer experience by providing easier access to database metadata.

Additional parameters for connection

Parameter Name Description Required
maxActiveConnections Maximum number of active connections that can be allocated from this pool at the same time. Optional
maxIdleConnections Maximum number of connections that can remain idle in the pool, without extra ones being released. Optional
minIdleConnections Minimum number of connections that can remain idle in the pool, without extra ones being created. Optional
poolConnectionAgedTimeout The time in milliseconds after which an aged connection should be removed from the pool. Optional
maxWaitTime The maximum number of milliseconds that the pool will wait (when no connections are available) for a connection to be returned before throwing an exception. Optional
evictionCheckInterval The interval in milliseconds between runs of the idle object evictor thread. Optional
minEvictionTime The minimum amount of time in milliseconds an object may sit idle in the pool before it is eligible for eviction by the idle object evictor. Optional
exhaustedAction Specifies the behavior when the pool is exhausted (e.g., 'FAIL', 'BLOCK', 'GROW'). Optional

Example: MySQL Connection
<?xml version="1.0" encoding="UTF-8"?>
<localEntry key="DBCon11" xmlns="http://ws.apache.org/ns/synapse">
    <db.init>
        <connectionType>MYSQL</connectionType>
        <dbUrl>jdbc:mysql://localhost:3306/supermarket</dbUrl>
        <dbUser>user1</dbUser>
        <dbPassword>password</dbPassword>
        <driverClass>com.mysql.cj.jdbc.Driver</driverClass>
        <userConsent>true</userConsent>
        <dbDriver>MySQL</dbDriver>
        <exhaustedAction>WHEN_EXHAUSTED_BLOCK</exhaustedAction>
        <name>DBCon11</name>
    </db.init>
</localEntry>            
Example: PostgreSQL Connection
<?xml version="1.0" encoding="UTF-8"?>
<localEntry key="PostgreSQLCon" xmlns="http://ws.apache.org/ns/synapse">
    <db.init>
        <connectionType>POSTGRESQL</connectionType>
        <dbUrl>jdbc:postgresql://localhost:5432/supermarket</dbUrl>
        <dbUser>user1</dbUser>
        <dbPassword>password</dbPassword>
        <driverClass>org.postgresql.Driver</driverClass>
        <userConsent>true</userConsent>
        <dbDriver>PostgreSQL</dbDriver>
        <exhaustedAction>WHEN_EXHAUSTED_BLOCK</exhaustedAction>
        <name>PostgreSQLCon</name>
    </db.init>
</localEntry>        

Operations

Operations use the configKey attribute to reference a pre-defined connection configuration.

select

Executes a SQL SELECT query using prepared statements.

Parameter NameDescriptionRequired
QueryThe Raw SQL SELECT statementYes
Table NameThe name of the table to be queried.Optional
Table ColumnsA comma-separated list of column names to be returned in the result set.Optional
Response ColumnsA comma-separated list of data types for the columns (e.g., VARCHAR, INTEGER, DOUBLE).Optional
Order ByThe column name to order the result set by.Optional
LimitThe maximum number of rows to return from the result set.Optional
OffsetThe number of rows to skip before starting to return results.Optional
Fetch SizeThe number of rows to fetch at a time from the database.Optional
Max RowsThe upper limit on the number of rows returned/affected by a query, regardless of the total number of rows in the table.Optional
Query TimeoutThe maximum time in milliseconds to wait for the query to execute.Optional
Output Variable NameIf specified, the result set is stored in this context variable.Optional
Overwrite Message BodyIf 'true' (default), the result set overwrites the payload. Set to 'false' if using 'responseVariable'.Optional

Sample configuration

<db.select configKey="DBCon1">
    <query>SELECT * FROM `Products` WHERE `product_id` = '${params.queryParams.productId}'</query>
    <columnNames>product_id</columnNames>
    <columnTypes>VARCHAR</columnTypes>
    <format>json</format>
    <table>Products</table>
    <columns>[]</columns>
    <queryType>online</queryType>
    <orderBy></orderBy>
    <limit></limit>
    <offset></offset>
    <queryTimeout></queryTimeout>
    <fetchSize></fetchSize>
    <maxRows></maxRows>
    <responseVariable>db_select_1</responseVariable>
    <overwriteBody>false</overwriteBody>
</db.select>

Sample response (JSON in db_select_1 variable)

[
    {
        "product_id": "PROD001",
        "name": "Super Widget",
        "price": 19.99,
        "stock_quantity": 148
    },
    {
        "product_id": "PROD002",
        "name": "Mega Widget",
        "price": 29.99,
        "stock_quantity": 75
    },
    {
        "product_id": "PROD003",
        "name": "Ultra Widget",
        "price": 39.99,
        "stock_quantity": 50
    }
]
(Note: The exact JSON structure might vary based on the database schema and query.)

insert

Executes a SQL INSERT statement using prepared statements. Can retrieve auto-generated keys.


Parameter NameDescriptionRequired
QueryThe Raw SQL INSERT statementYes
Table NameThe name of the table to be inserted into.Optional
Table ColumnsA comma-separated list of column names to be inserted.Optional
Query TimeoutThe maximum time in milliseconds to wait for the query to execute.Optional
Output Variable NameIf specified, the result set is stored in this context variable.Optional
Overwrite Message BodyIf 'true' (default), the result set overwrites the payload. Set to 'false' if using 'responseVariable'.Optional

Sample configuration

<db.insert configKey="DBCon11">
    <query>INSERT INTO `orders` (`customer_id`, `total_amount`) VALUES (${params.queryParams.customerId}, ${vars.db_select_1.payload.rows[0].price * params.queryParams.quantity})</query>
    <columnNames>customerid, totalamount</columnNames>
    <columnTypes>INT, DECIMAL</columnTypes>
    <format>json</format>
    <table>orders</table>
    <columns>[]</columns>
    <queryType>online</queryType>
    <queryTimeout></queryTimeout>
    <responseVariable>db_insert_1</responseVariable>
    <overwriteBody>false</overwriteBody>
</db.insert>

Sample result (in insertResult variable)

{
    "generatedKeys": [
        { "GENERATED_KEY": 12345 }
    ],
    "affectedRows": 1
}

delete

Executes a SQL DELETE statement using prepared statements.

Parameter NameDescriptionRequired
QueryThe Raw SQL DELETE statementYes
Table NameThe name of the table to be deleted from.Optional
Table ColumnsA comma-separated list of column names involved in the WHERE clause.Optional
Query TimeoutThe maximum time in milliseconds to wait for the query to execute.Optional
Output Variable NameIf specified, the result set is stored in this context variable.Optional
Overwrite Message BodyIf 'true' (default), the result set overwrites the payload. Set to 'false' if using 'responseVariable'.Optional

Sample configuration

<db.delete configKey="DBCon22">
    <query>DELETE FROM `OrderItems` WHERE `order_item_id` = 1 AND `product_id` = 'PROD001'</query>
    <columnNames>order_item_id, productid</columnNames>
    <columnTypes>INT, VARCHAR</columnTypes>
    <format>json</format>
    <table>OrderItems</table>
    <columns>[]</columns>
    <queryType>online</queryType>
    <queryTimeout></queryTimeout>
    <responseVariable>db_delete_1</responseVariable>
    <overwriteBody>false</overwriteBody>
</db.delete>
Sample result (in db_delete_1 variable)
{
    "affectedRows": 1 // Number of rows affected by the delete operation
}

callProcedure

Calls a database stored procedure or function.

Parameter NameDescriptionRequired
QueryThe Raw SQL CALL statementYes
Stored Procedure NameThe name of the table to be queried.Optional
ParametersA comma-separated list of column names to be returned in the result set.Optional
Fetch SizeThe maximum time in milliseconds to wait for the query to execute.Optional
Max RowsThe maximum time in milliseconds to wait for the query to execute.Optional
Query TimeoutThe maximum time in milliseconds to wait for the query to execute.Optional
Output Variable NameIf specified, the result set is stored in this context variable.Optional
Overwrite Message BodyIf 'true' (default), the result set overwrites the payload. Set to 'false' if using 'responseVariable'.Optional

Sample configuration (Conceptual)

<db.call configKey="DBCon33">
    <query>CALL `GetEmployeesByDepartment`(2, '3')</query>
    <columnNames>deptID, deptName</columnNames>
    <columnTypes>INT, VARCHAR</columnTypes>
    <format>json</format>
    <table>GetEmployeesByDepartment</table>
    <columns>[]</columns>
    <queryType>online</queryType>
    <queryTimeout></queryTimeout>
    <fetchSize></fetchSize>
    <maxRows></maxRows>
    <responseVariable>db_call_51</responseVariable>
    <overwriteBody>false</overwriteBody>
</db.call>
Sample result (in db_call_51 variable)
{
    "outputParameters": {
        "deptID": 2,
        "deptName": "Sales"
    },
    "resultSets": [ /* Optional: if procedure returns result sets */ ]
}

executeQuery

Executes an arbitrary SQL statement. Use when you need to execute complex queries. Parameters can be passed in the SQL statement using the ? placeholder. The parameters are passed in the order they appear in the SQL statement. Prepared statement support is currently available only for executeQuery operation.

Parameter NameDescriptionRequired
QueryThe SQL statement to execute.Yes
Prepared StatementIf 'true', the SQL statement is treated as a prepared statement. If 'false', it is treated as a regular SQL statement.Yes
Result SetIf 'true', the result is treated as a result set. If 'false', it is treated as an update count.Yes
Fetch SizeThe maximum time in milliseconds to wait for the query to execute.Optional
Transaction IsolationThe maximum time in milliseconds to wait for the query to execute.Optional
Max RowsThe maximum time in milliseconds to wait for the query to execute.Optional
Query TimeoutThe maximum time in milliseconds to wait for the query to execute.Optional
Output Variable NameIf specified, the result set is stored in this context variable.Optional
Overwrite Message BodyIf 'true' (default), the result set overwrites the payload. Set to 'false' if using 'responseVariable'.Optional

Sample configuration

<db.executeQuery configKey="DBCon1">
    <sql>SELECT o.order_id, o.order_date, o.status, o.total_amount, c.customer_name, c.email, oi.product_id, p.name AS product_name, oi.quantity, oi.unit_price FROM Orders o JOIN Customers c ON o.customer_id = c.customer_id JOIN Order_Items oi ON o.order_id = oi.order_id JOIN Products p ON oi.product_id = p.product_id WHERE o.order_id = ?</sql>
    <columns>[[1,"INTEGER"]]</columns>  
    <isPreparedStatement>true</isPreparedStatement>
    <isResultSet>true</isResultSet> 
    <resultType>JSON</resultType>
    <responseVariable>db_execute_query_1</responseVariable>
    <overwriteBody>false</overwriteBody>
</db.executeQuery>

Sample result (in db_execute_query_1 variable)

[{
    "order_id": 101,
    "order_date": "2025-04-30 14:23:45",
    "status": "Shipped",
    "total_amount": 199.99,
    "customer_name": "John Doe",
    "email": "[email protected]",
    "product_id": "PROD001",
    "product_name": "Super Widget",
    "quantity": 2,
    "unit_price": 19.99
}]

beginTransaction

Starts a new database transaction associated with the current message context. Subsequent DB operations within the same flow (until commit or rollback) will participate in this transaction.

Parameter NameDescriptionRequired
isolationLevelSpecifies the transaction isolation level (e.g., TRANSACTION_READ_COMMITTED, TRANSACTION_REPEATABLE_READ, TRANSACTION_SERIALIZABLE). Default depends on the database.Optional

The isolationLevel parameter can take one of the following values: - TRANSACTION_READ_UNCOMMITTED: Allows reading uncommitted changes (dirty reads). - TRANSACTION_READ_COMMITTED: Prevents dirty reads; allows non-repeatable reads. - TRANSACTION_REPEATABLE_READ: Prevents dirty reads and non-repeatable reads; allows phantom reads. - TRANSACTION_SERIALIZABLE: Prevents dirty reads, non-repeatable reads, and phantom reads. This is the highest isolation level.

Sample configuration

<db.beginTransaction configKey="DBCon1">
    <timeout>300</timeout>
    <isolationLevel>TRANSACTION_READ_COMMITTED</isolationLevel>
</db.beginTransaction>

commitTransaction

Commits the database transaction associated with the current message context.

Sample configuration

<db.commitTransaction configKey="DBCon1">
    <enable>true</enable>
</db.commitTransaction>

rollbackTransaction

Rolls back the database transaction associated with the current message context.

Sample configuration

<db.rollbackTransaction configKey="DBCon1">
    <enable>true</enable>
</db.rollbackTransaction>

Note: For a practical example demonstrating transaction management and various operations, see DB Connector Example.