What is the terpal equivalent of `Jdbi.open()` and JDBC's `.connection.use`?

[cies]

What is the equivalent of these "db scopes" in Jdbi and JDBC, in terpal-sql?

jdbi.open().use { db ->
    val orgDao = dbtx.attach(OrganizationDao::class.java)
    val orgs = orgDao.listOrganizations()
    // ...
}

db.database.connection.use { jdbc ->
     jdbc.prepareStatement("....").execute()
}

Are these connections? Or sessions? Or transactions? (nah, they are for sure not transactions; transactions are opened in a different way)

[deusaquilus]

The closest thing that Terpal has to a session is a transaction (i.e. that you do via controller.transaction). When you do ControllerQuery.runOn(controller) it grabs a connection from the data-sources, uses it, and then closes it immediately afterward. This is a typical pattern with Hikari and connection-pool-based data sources which I think is >95% of JDBC usage these days.

When you do controller.transaction { ... } you can call ControllerQuery.runOnTransaction inside and it will either open a new session (i.e. java.sql.Connection) or use an exising one if something else inside controller.transaction already opened one.

If someone asks, I can introduce a runOnConnection for legacy purposes but nobody's asked for that yet.

[cies]

Hmmm... Interesting. I'm working on a JVM-based web dev stack based on http4. Wrt db sessions/transaction there are 2-ways to do it:

1. Every request (that needs to access the db) gets a session/connection and can decide for itself if it needs a transaction.
2. Every request (that needs to access the db) gets a transaction.

There are pros and cons to both. Rails does not automatically open a db transaction for each request (https://www.reddit.com/r/rails/comments/8yumej/is_wrapping_all_controller_actions_in_a/). SpringBoot will only do it if the @Transactional annotation is used (https://www.reddit.com/r/SpringBoot/comments/147sm5w/why_not_just_always_use_the_transactional/), otherwise it will wrap individual queries in transactions.

I would prefer to leave the use of db transactions to the programmer, as:

• They add overhead. In general. But also specifically adds load to the db when the transaction becomes is not quickly committed.
• They can be very annoying when a request does many things (e.g. a call to some external API could fail, resulting in a db transaction being rolled back, while you'd actually want something to be saved/updated in the db). This results in ugly code (first hand experience with this when using a web framework with one automatic transaction per request).

Usually a db transaction is only needed when two or more db updates/inserts are among the queries executed within that request cycle (but not always -- it really depends on the task at hand!). Sometimes you may want two separate db transactions in a request cycle, and often none at all (only reads, or mostly reads and only one write).