Connection pool, datasource and clojure

Hello

I’ve been working on building my first REST API in Clojure. I’ve been dabbling in Clojure for a while on and off and am now attempting to make a more formal go at it with a real project. The relevant libraries I’m using are for this question are:
reitit
ring
integrant
hikaricp
hugsql

After doing some research on this and how to use integrant and Ring in this scenario I found several pieces of information suggesting passing the datasource into the handler via middleware which I have working.

But last night I realized i can’t recall from my Java days 15 years ago how that actually is working under the hood. As a point of reference, in a Ruby on Rails application default scenario, ActiveRecord grabs an actual database connection when a controller is instantiated and holds onto it until the response is returned which can have really bad characteristics if you have say a long running downstream API call to an external system because you can run out of database connections under load even though the conn was only needed for a few milliseconds.

So my question boils down to the difference between a datasource and an actual connection to the DB. Is passing the datasource in JDBC parlance simply a reference to said datasource where e.g. hugsql uses the datasource to grab a connection and returns it immediately after the query returns? Or is the way my application is setup more akin to ActiveRecord where assoc’ing the datasource in middleware is grabbing a connection and hanging onto it until the ring handler returns a response?

Thanks for any clarity.

3 Likes

Clojure has no magic so the behavior will be whatever you program it to do.

Since you’re using HikariCP, you have a way to create a connection pooled datasource, and you can assoc that into the Ring request via middleware.

Once you have a CPD, code that calls get-connection on it (assuming it is wrapped in a Clojure wrapper, such as next.jdbc, behind the scenes), is going to get a new-or-reused connection from the pool and then should return it to the pool when it is done. That will most likely be within a function (via with-open).

I don’t know what HugSQL does behind the scenes, but I know it can use next.jdbc (or clojure.java.jdbc) and I would expect it to call get-connection on the CPD for each HugSQL operation and then .close each connection, returning it to the pool.

Without details of how you are plumbing these libraries together, I can’t provide more details in my answer.

Thanks Sean for the quick reply. I am using next.jdbc as well (sorry I forgot to mention that in my list).

Thanks again!

May I suggest that you use HoneySQL instead of HugSQL. HugSQL generates functions from your SQL files, which eventually becomes a pain to manage if your IDE does not support identifying those functions (Cursive that I use does not). I am now in the quite tedious process of rewriting hundreds of HugSQL queries into HoneySQL. A lot of work that you will be spared if you switch now :blush:

To connect the dots referred to above — With JDBC at the bottom of the heap, all Clojure solutions involve the JDBC DriverManager (singleton), which dispenses DataSources from among those configured on the classpath, which might include a connection-pooled DataSource. The DataSource, in turn, dispenses JDBC Connections. Indeed one must be mindful to close a Connection in a timely fashion (a pooling DataSource intercepts close and returns the Connection to the pool).

On the other hand, while something like “say a long running API call to an external system” is usually a non-problem with Connections, unless you go down the swampy, downright malarial path of trying to make such a call in the middle of a transaction… you have some choices. You probably would rather respond to your web client in respectable time, regardless of the speed of the slow service. Ideally, there is nothing, nothing, in the path between web request and web response, that you have no control over. Consider a queue. Preferably a queue serviced not by a “background” thread in your webapp, but by a separate process altogether.

1 Like

Thanks for the tip. I have only glanced at HoneySQL previously but on the surface I tend to dislike the idea of a DSL wrapping a DSL. But hearing your story I will take another look at it just to be sure! :grinning:

Thanks Phill. - Yes I agree - I wasn’t suggesting I’d do the long running API in the middle thing - just that I worked at a place that used Ruby on Rails and that’s exactly what was happening. Message queue’s in that case are the answer. I was more trying to understand how all these libraries use the actual connection from the datasource… It appears from what’s been said that Hug/Honey SQL would likely close the connection immediately after the query was made which would return it to the pool.

I’m a fan of HoneySQL too. I introduced it at my old workplace and mostly everyone on the team seemed to like it. Even though it’s a DSL it’s also just Clojure maps, so in terms of compositionality there is really nothing new to learn.

The tricky part - as far as I remember - was having to look up syntax beyond basic queries (for joins etc.) which is honestly something I have to look up when I do it in regular SQL too. I also introduced some new grammar which was a bit tricky, but doable. If I remember correctly, maps in Clojure are obviously unordered, so HoneySQL keeps an ordered list of the allowed operations (= keys in the map) which you can amend.

I generally like to spend the least possible amount of time directly interfacing with databases and it’s been more than a year since I did anything that interfaced with a SQL db, so take my advice with a grain of salt! :stuck_out_tongue:

1 Like

Thanks Simon - I’m definitely going to look at HoneySQL. Not being averse to SQL I guess is also a reason I veered towards HugSQL but it’s worth a look for sure. I have a lot of things to pick and choose from which is the clojure way so I’m trying to not have to learn a ton of libraries to build a fairly straight forward (At the moment :wink:) database backed REST API.

My rule of thumb here is:

  • If you have static SQL code with only a few placeholders/variants and you like to keep SQL code separate from Clojure code, use HugSQL,
  • If you have dynamic SQL code that needs to be built programmatically, based on conditions, and can get arbitrarily complex, use HoneySQL,
  • If you just need CRUD operations, use next.jdbc's “friendly SQL functions” directly for reading/writing hash maps from/to database tables.
2 Likes

Thanks Sean - that’s a great list. I also didn’t realize next.jdbc had friendly sql functions - I’ll take a look!

Something I’d like to add to that, which is an excellent point in favor of next.jdbc and component, which you’ve recommended on many occasions:
next.jdbc building on top of protocols plays really well with component. It lets me easily and cleanly write a Datasource component which implements the protocols of both libraries and it Just Works.
I like it even better than the component wrapper provided by the connections namespace because it requires no other semantics for using it as an argument.

You’re aware of next.jdbc.connection/component which creates a component for a connection-pooled datasource automatically, right?

Yes, perhaps it got missed at the tail end of my previous comment - next.jdbc.connection/component returns the component wrapped in a function, which changes the call semantics. Wrapping it in a record which implements the protocol does not change the call semantics, which means it can be dropped-in.

I had missed that last comment, yes.

We have often added clojure.lang.IFn implementations to our records so we were already using the function call semantics. We like that approach a lot for component's that “just wrap” some state: we can consistently just “call” a component that wraps state – our environment/configuration file component's are like that too.