Repository navigation
What is the terpal equivalent of Jdbi.open() and JDBC's .connection.use? #10
Description
Activity
The closest thing that Terpal has to a session is a transaction (i.e. that you do via
controller.transaction). When you doControllerQuery.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 callControllerQuery.runOnTransactioninside and it will either open a new session (i.e.java.sql.Connection) or use an exising one if something else insidecontroller.transactionalready opened one.If someone asks, I can introduce a
runOnConnectionfor legacy purposes but nobody's asked for that yet.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:
- Every request (that needs to access the db) gets a session/connection and can decide for itself if it needs a transaction.
- 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
@Transactionalannotation 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).
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)