sql 2021-10-11 | Slack Archive

Jakub Holý (HolyJak)11:10:38

Hello! Do you have any tips for ensuring that an extra statement is executed whenever I check a connection from a Conn. Pool and when I return it back? I am thinking about making a wrapper/proxy for the DataSource and Connection objects (and I have asked about that in https://clojurians.slack.com/archives/C03S1KBA2/p1633952449070600) but perhaps I am missing some other, simpler way? The statements in question are Postgres' set role <some name> and reset role (since we are using schemas for multi-tenancy and want to ensure that a user only has access to her data).

vlaaad13:10:00

Do you use next.jdbc? Perhaps it could be done on that level, so you wrap only get-connection

vlaaad13:10:41

i.e. reify Sourceable that wraps your data source

Jakub Holý (HolyJak)15:10:29

I do. Wrapping get-connection is easy, I can implement next-jdbc/Connectable , so executing st. when I check out a connection is trivial. What is messy is executing something when I return it, which happens via the Connection/Closeable's .close The problem is that Connectable/get-connection must return a java.sql.Connection and to override its .close I would need through a whole mess to wrap it somehow... 😭

vlaaad16:10:55

Ah yes, doing stuff on close is a different issue..

seancorfield17:10:05

This is something that connection pool libraries do automatically -- are you using a connection pool?

seancorfield17:10:19

Both c3p0 and HikariCP have hooks to automatically run queries to validate connections at different points. Ah, you'd need it to be dynamic for the role stuff wouldn't you...

👍 1

Jakub Holý (HolyJak)17:10:38

I use Hikari. Though if it can run a query on connection returned, that would help that one can be static

2021-10-11

Channels