Skip to main content

Transactors

A Transactor is how you run database operations. It obtains a connection, runs your code inside a transaction, and handles commit, rollback, and cleanup automatically.

By default, each call is wrapped in a transaction: auto-commit off, commit on success, rollback on error, close always.

Setting Up​

Each supported database has a typed config builder — your IDE will autocomplete all available options. Call .transactor() at the end of the chain:

// PostgreSQL
val pgTx =
    PostgresConfig.builder(
            "localhost", 5432, "mydb", "user", "pass")
        .sslmode(PgSslMode.REQUIRE)
        .transactor()

// DuckDB (in-memory)
val duckTx =
    DuckDbConfig.inMemory().transactor()

// DuckDB (file-based)
val duckFileTx =
    DuckDbConfig.builder("/tmp/analytics.db")
        .threads(4)
        .memoryLimit("2GB")
        .transactor()

// MariaDB / MySQL
val mariaTx =
    MariaDbConfig.builder(
            "localhost", 3306, "mydb", "user", "pass")
        .transactor()

// Oracle
val oracleTx =
    OracleConfig.builder(
            "localhost", 1521, "xe", "user", "pass")
        .serviceName("XEPDB1")
        .transactor()

// SQL Server
val mssqlTx =
    SqlServerConfig.builder(
            "localhost", 1433, "mydb", "user", "pass")
        .encrypt(SqlServerEncrypt.TRUE)
        .transactor()

// DB2
val db2Tx =
    Db2Config.builder(
            "localhost", 50000, "mydb", "user", "pass")
        .transactor()

Connection Settings​

Override connection-level defaults by passing ConnectionSettings:

var settings = ConnectionSettings.builder()
    .transactionIsolation(TransactionIsolation.READ_COMMITTED)
    .readOnly(true)
    .schema("app")
    .connectionInitSql("SET search_path TO app")
    .build();

var tx = config.transactor(settings);
// or: config.transactor(settings, strategy)
SettingDescription
transactionIsolationREAD_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
autoCommitOverride the driver's default auto-commit mode
readOnlyHint to the driver that connections are read-only
catalogSet the default catalog
schemaSet the default schema
connectionInitSqlSQL executed once when each connection is created

Connection Pooling​

For production, use HikariDataSourceFactory from the foundations-jdbc-hikari module:

var pool = HikariDataSourceFactory.create(config);
var tx = pool.transactor();

Single Connection Mode​

SingleConnectionDataSource reuses one connection across all calls — needed for DuckDB in-memory, where each new connection creates a separate database:

var ds = SingleConnectionDataSource.create(config);
var tx = ds.transactor();

Strategies​

The default strategy wraps each call in a transaction. Pass a different built-in strategy to .transactor():

StrategyBehavior
defaultStrategy()begin, commit, close
autoCommitStrategy()no transaction management, just close
rollbackOnErrorStrategy()begin, commit on success, rollback on error, close
testStrategy()begin, rollback (not commit), close — keeps test data isolated
var tx = config.transactor(Transactor.testStrategy());

Strategies can be thoroughly customized with composable hooks for transaction lifecycle and observability. See Strategies for details.