JdbcClient

Introduction

The JdbcClient module manages pooled connections to JDBC-compatible relational databases.

Each module (instance) manages a single pooled connection to a single RDBMS (a destination). To configure connections to multiple servers, use multiple modules. To increase throughput, increase the number of module instances.

The module provides an internal event-bus service with a JSON-based protocol and a Java library for simplified use.

This module is used for all internal JDBC access, including the bundled JDBC valves.

Prerequisites

Add the database-specific JDBC driver to the server classpath before starting the module.

Configuration

Note

Module name: JdbcClient | JDBCClient

Properties

Name	Description	Default	Mandatory
name	Destination name. Unique identifier for a specific configuration, connection and service. This value must be unique for all JdbcClient configurations.	"default"
instances	Number of instances to deploy.	1
jdbc	JDBC connection and pool configuration object.	{}
jdbc.url	RDBMS-specific JDBC URL.	None
jdbc.username	Database username.	""
jdbc.password	Database password.	""
jdbc.max_pool_size	Maximum number of pooled database connections.	15
jdbc.idleTimeout	Seconds a connection may sit idle before being evicted. Set below the database server's idle timeout and any network-level TCP idle timeout, for example the Azure Load Balancer default of 240 seconds.	1800
jdbc.maxLifetime	Maximum age in seconds of a connection before it is recycled, regardless of activity.	3600
jdbc.poolCleanerPeriod	Interval in milliseconds at which the pool cleaner checks for idle or expired connections.	1000
jdbc.autoGeneratedKeys	Requests generated keys from supported JDBC drivers.	false
tz	Timezone used when mapping SQL timestamp values, for example "Europe/Stockholm".	System default
migrations	Optional Flyway migration configuration. Omit the object to skip migrations.	None

Example

{
    "name": "JdbcClient",
    "enabled": true,
    "config": {
        "name": "default",
        "jdbc": {
            "url": "jdbc:mysql://host/db",
            "username": "${env.JDBC_USERNAME}",
            "password": "${env.JDBC_PASSWORD}",
            "max_pool_size": 15,
            "idleTimeout": 1800,
            "maxLifetime": 3600
        }
    }
}

Driver

JDBC requires a RDBMS specific driver. This driver must be manually downloaded and added to the server class path (restart required).

Database migrations

The optional migrations object lets the JdbcClient module run caller-owned Flyway migrations against its configured jdbc.url. Migrations run once during module initialization and complete before the connection pool starts serving queries. Omit the entire object when the module must not run migrations.

The JdbcClient module does not select migration scripts automatically. The consuming module or product must provide the classpath locations and any schema-specific settings.

Name	Description	Default	Mandatory
migrations.locations	Classpath locations that Flyway scans for migration scripts.	[]	true*
migrations.schemas	Schemas that Flyway creates when missing and migrates. The first schema is also Flyway's default schema.	[]
migrations.baseline_on_migrate	Baseline a non-empty schema without a Flyway history table before migrating. Enable only when its existing contents are known to match the migration's initial state.	false
migrations.placeholders	String key-value pairs substituted into migration SQL by Flyway.	{}

* mandatory when migrations is configured

{
  "name": "JdbcClient",
  "enabled": true,
  "config": {
    "name": "example-database",
    "jdbc": {
      "url": "jdbc:postgresql://db.example/example",
      "username": "${env.EXAMPLE_DB_USERNAME}",
      "password": "${env.EXAMPLE_DB_PASSWORD}"
    },
    "migrations": {
      "locations": ["classpath:db/migration/example"],
      "schemas": ["EXAMPLE"],
      "baseline_on_migrate": false,
      "placeholders": {
        "example_value": "configured-value"
      }
    }
  }
}

Audit

The JdbcClient can record an audit trail for write operations (INSERT, UPDATE, DELETE) and optionally read operations (SELECT). Audit logging is disabled by default and must be explicitly enabled per operation type.

When audit is enabled, the caller is expected to supply an AuditContext containing the subject (the authenticated user performing the operation) and a correlation ID. The JDBC valves automatically construct this context from the session and the pipe trace ID.

Note

Audit events are published fire-and-forget on the internal event bus. If no audit module is running, events are silently dropped — no errors are returned to the caller.

Configuration

The audit block is placed at the top level of the module config, alongside jdbc.

Properties

Name	Description	Default	Mandatory
audit.create	Record an audit event for INSERT statements.	false
audit.update	Record an audit event for UPDATE statements.	false
audit.delete	Record an audit event for DELETE statements.	false
audit.read	Record an audit event for SELECT statements.	false

Example

{
  "name": "JdbcClient",
  "enabled": true,
  "config": {
    "name": "default",
    "jdbc": {
      "url": "jdbc:postgresql://localhost/mydb",
      "username": "${env.AUDIT_DB_USERNAME}",
      "password": "${env.AUDIT_DB_PASSWORD}"
    },
    "audit": {
      "create": true,
      "update": true,
      "delete": true
    }
  }
}

Audit event content

Each audit event contains the following information extracted from the SQL statement and its parameters:

Field	Description
operation	CREATE (INSERT), UPDATE, DELETE, or READ (SELECT)
entityType	Name of the primary table
entityId	WHERE clause with ? placeholders substituted by actual parameter values
changes	Column names and values from SET (UPDATE) or VALUES (INSERT) clauses
subject	Authenticated user from the pipe session, or anonymous
traceId	Pipe trace ID used as correlation ID

Connection Pool

Caution

Breaking change: The c3p0 connection pool was removed in a previous version. Pool health is now configured via jdbc.* keys in the module config. Any -Dc3p0.* properties in JAVA_TOOL_OPTIONS from previous deployments have no effect and should be removed.

The defaults protect existing deployments that have no explicit pool configuration. Set idleTimeout below whichever threshold is lower — the database server's own idle timeout or any network-level TCP idle timeout (e.g. Azure Load Balancer default: 240 s).

Key	Default	Description
jdbc.max_pool_size	15	Maximum number of pooled connections.
jdbc.idleTimeout	1800 s	Evict connections idle longer than this.
jdbc.maxLifetime	3600 s	Recycle connections older than this regardless of activity.
jdbc.poolCleanerPeriod	1000 ms	How often the pool cleaner checks for idle or expired connections.