Transactors

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

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

Kotlin

// 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())

Java

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

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

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

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

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

// SQL Server — .encrypt(FALSE) is correct for local dev against the default container image
// (self-signed cert, no trust chain). Production should use .encrypt(TRUE) with a trusted
// certificate; the handshake failure against localhost is otherwise inscrutable.
Transactor mssqlTx =
    Transactor.create(
        SqlServerConfig.builder("localhost", 1433, "mydb", "user", "pass")
            .encrypt(SqlServerEncrypt.FALSE)
            .build());

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

Scala

// 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 — .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()
  )

Show entire file

Connection settings

Override connection-level defaults by passing ConnectionSettings:

Kotlin

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

val tx = Transactor.create(config, settings)

Java

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);

Scala

class ConnectionSettingsSetup:
  val config = PgConfig.builder("localhost", 5432, "mydb", "user", "pass").build()

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

  val tx = Transactor.create(config, settings)

Show entire file

Setting	Description
transactionIsolation	READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
autoCommit	Override the driver's default auto-commit mode
readOnly	Hint to the driver that connections are read-only
catalog	Set the default catalog
schema	Set the default schema
connectionInitSql	SQL 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();

Test mode

Call .rollbackOnly() to roll back instead of committing — ideal for test isolation:

var tx = Transactor.create(config).rollbackOnly();

Observability

Attach a QueryListener to observe all queries and transactions:

var tx = Transactor.create(config).withListener(myListener);

See Observability for details.

Raw JDBC access

Transactor.create() returns TransactorJdbc — a subtype of Transactor that exposes the underlying JDBC connection:

TransactorJdbc tx = Transactor.create(config);

// Raw JDBC when you need it
tx.executeJdbc(conn -> {
    var meta = conn.getMetaData();
    return meta.getTables(null, null, "%", null);
});

This is an escape hatch for vendor-specific JDBC extensions, DatabaseMetaData, advisory locks, or migration tooling. executeJdbc is only available on TransactorJdbc.

Error handling

Database errors are thrown as DatabaseException — a sealed class with dialect-specific subtypes:

• DatabaseException.Postgres — structured PostgreSQL error with all ErrorResponse fields (schema, table, constraint, position caret, etc.)
• DatabaseException.SqlServer — structured SQL Server error with severity, procedure name, line number
• DatabaseException.Jdbc — wraps SQLException for other databases

See Error Handling for pattern matching examples and field details.