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. Pass the config to Transactor.create():

// PostgreSQL
val pgTx =
    Transactor.create(
        PgConfig.builder(
                "localhost", 5432, "mydb", "user", "pass")
            .sslmode(PgSslMode.REQUIRE)
            .build())

// DuckDB (in-memory)
val duckTx =
    Transactor.create(DuckDbConfig.inMemory().build())

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

// MariaDB / MySQL
val mariaTx =
    Transactor.create(
        MariaConfig.builder(
                "localhost", 3306, "mydb", "user", "pass")
            .build())

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

// SQL Server
// SQL Server — .encrypt(FALSE) for local dev (self-signed cert); use TRUE + trusted cert in prod
val mssqlTx =
    Transactor.create(
        SqlServerConfig.builder(
                "localhost", 1433, "mydb", "user", "pass")
            .encrypt(SqlServerEncrypt.FALSE)
            .build())

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

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 = Transactor.create(config, settings);
// or: Transactor.create(config, 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​

When a config requires single-connection mode (e.g. DuckDB in-memory), ConnectionSource.of() automatically reuses one connection across all callers — no special setup needed. DuckDB in-memory creates a separate database per connection, so this is detected and handled transparently:

// DuckDB in-memory: single-connection mode is automatic
var ds = ConnectionSource.of(DuckDbConfig.inMemory().build());
var tx = ds.transactor();

Strategies​

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

StrategyBehavior
defaultStrategy()begin, commit on success, rollback on error, close
autoCommitStrategy()no transaction management, just close
testStrategy()begin, rollback on success or error, close — keeps test data isolated
var tx = Transactor.create(config, Transactor.testStrategy());
testStrategy is for tests only

testStrategy() rolls back every execute / transact, including ones that succeeded — nothing persists. If you copy this into a script, a tutorial, a migration, or any production code, your DDL and INSERTs will silently disappear. For anything that should persist, use defaultStrategy().

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