How to manage database connection in Clojure?

Questions & Help How to?
1

Hi guys, I was wondering how do you guys usually keep your database connection in Clojure? During my search, I stumbled upon this great question: https://stackoverflow.com/questions/39579023/clojure-best-approach-for-singleton-like-data-connection. But it doesn’t address my concern about the flexibility of the connection created. I wanted the connection to be global in order to be accessed from anywhere so I was thinking def or defonce should do it. But I also want to be able to pass values for creating the connection, I don’t want to rely heavily on configuration reading tools like environ etc to determine my connection configuration.

This way I heavily depend on env, which is provided by environ.

(def conn-options {:jdbc-url (env :database-url)})

(defonce conn (delay (make-datasource conn-options)))

Ideally, I want something like

(defn create-connection [& params] (make-datasource params))

But by using it will result in the connection being created every time the function is invoked. That’s why I was looking for something like a Singleton. I realize maybe a better approach is possible in Clojure, but being a beginner, this is the only way I can think about.

2

Generally, you would create and “hold-on” to your database connection within one namespace, then expose functions in that namespace that other namespaces can use to invoke db operations, such as select, insert, update and so on.

What I do is create the database connection (using next-jdbc as my go-to-library for Clojure and SQL operations), then store that reference in an atom. I then use that connection within that atom for all my operations.

I’ve put together a simple example that uses next-jdbc and integrant to show this working.

Note, you do not have to use Integrant. You don’t have to use any IoC tooling - this is just an example and the same effect can be achieved by invoking the create connection fucntions directly within your “core/main” namespace.

1 Like
3 
(def conn (delay (create-connection conn-options)))

What’s wrong with that?

1 Like
4

If you have runtime parameters for the database connection, you can’t construct it in a def without some unidiomatic contortions. At best you’ll end up with a singleton atom and some initialization code in your main function to reset! that to the actual datasource or connection.

5

I find the use of delay here pretty idiomatic personally. Am I the only one?

Can’t think of a lot of other good use case for it in fact. You have a case where you need to delay the instantiation of the connection to after you have the params available. If they are only available at run-time, then it’s a pretty nice way to delay its creation till after.

If you prefer a push model, instead of a pull model (delay is pull), you can use promise instead and deliver the connection when you have the params.

I would only use atom if you didn’t want a single instantiation. If you expect re-instantiating the conn over and over with different params through the course of the app, then an atom is more appropriate.

P.S.: I didn’t bring it up before, but since your example shows jdbc, I don’t think you want to keep a single connection open for the entirety of your app. You most likely want to create one connection per request handling, so you can query the DB in parallel, and you want to close them so that the DB doesn’t max out its connections. Also, it is an easy way to deal with connections failing or timing out, and all that. After that, if you want to eliminate the connection creation overhead further, you should use a connection pool like https://github.com/tomekw/hikari-cp or https://github.com/metabase/connection-pool

6

The def ,,, delay form works if conn-options is also a global def but if your connection options are determined by external configuration and/or command-line or environmental differences, you won’t have a global def available for that – unless you use something like alter-var-root or an atom to set it after reading config / environment / whatever at startup in your main function. And I think that sort of global mutability is non-idiomatic. It’s also error prone because you can’t deref your conn unless you’ve already set up that global state.

Does that clarify my comment?

1 Like
7

I’d like to “+1” this part – and indeed @dharrigan’s example uses HikariCP with next.jdbc to create a connection pooled datasource at startup. (I prefer seeing the datasource passed through the whole call chain rather than as a global, but that’s why I like Component to define a “system” that you pass down the call chain, selecting out the specific parts that various functions need, so call dependencies are always explicit).

8

Hum… a little, except you don’t have to def conn-options. I’ve used that as a placeholder. The code in the deref would be polling logic to grab the parens from whatever runtime config source you want. For example:

(def conn
  (delay
    (create-connection
      {:jdbc-url (env :database-url)
       ... }))

That’s the assumption with delay, that you can poll for the params at runtime from somewhere which should be available by the time you first need the conn.

You can still split it out, either delaying the conn-options, or what I’d suggest is making it a function.

(defn get-conn-options []
  {:jdbc-url (env :database-url)
   ...}))

(def conn
  (delay
    (create-connection
      (get-conn-options))))

In a prod setting, I’d wrap the call to get-conn-options in a retry as well, within the delay itself, with some policy of how long to retry before just killing the application and raising an alarm.

If you can’t poll, that’s where you can instead use promise. That be nice say if you’re waiting for user input for it. You can just deliver it to the promise whenever its given to your app, and everything else can block waiting until it’s been provided if in a multi-threaded scenario.

(def conn
  (promise))

(defn -main [& args]
  (let [_ (println "What is your DB url?")
        url (read-line)
        _ (println "What is your username?"
        username (read-line)
        _ (println "What is your password?"
        password (read-line)]
    (deliver conn
      (create-connection
        {:url url :user username :pass password}))))

In my opinion, neither are mutating. That might be philosophical, but both delay and promise are single valued objects that can never change after being assigned a value. They just allow to control the exact time at which that value is assigned.

2 Likes
9

Fair points – and nice use of promise (instead of, for example, the atom in David’s code) – but it does mean you can’t tear down the connection and rebuild it with a different configuration, and you still have a single global meaning you can’t reuse code across different datasources, both of which impact testability and code reuse (and are issues that are addressed by using Component instead).

1 Like
10

My concern with delay is that it forces me to use another global such as conn-options, which forces me to depend upon another source like environment variables, config, etc to provide its parameter values. And I don’t want to use atom either since I don’t plan to change the connection parameters value in its runtime, I only need to set it once, but I need the connection to allow multiple ways for it to be parameterised.

I really like your suggestion on using promise, that’s what I was trying to achieve, by “pushing” the value to the connection instantiation.

In regards to your remarks on connection pooling, duly noted, in fact the example itself comes from hikari-cp README.

11

Ya, totally, the use of delay assumes you can grab the params from somewhere else that is accessible from the delay at the time it gets realized. Either a config file, a config store, an environment var, a java property, etc.

Most of the time though, that’s the case for DB configurations and other such config. So in that case, I believe it’s fine to use delay. But that’s not always the case, that’s why I showed promise as well, which is a great alternative if you need to push the configs to the var, and can’t poll for them from inside. It’s better then atom if you never plan on changing the connection after it’s been set.

I think that also depends. Having your DB connections in a global def doesn’t mean you should or need to have all your code base depend on it through global access. You can still write all your fns to take a connection as an argument. And then only the code at the boundary can access the global connection and orchestrate the calls to the inner fns.

What I mean is, you don’t need a DI framework to design your app using dependency injection. And if you don’t have a very large number of components and they don’t have a very complicated set of inter-dependencies with complicated tear-down requirements, using Component or other DI frameworks can be overkill as well in my opinion.

In such a case, you can still test things pretty easily, since most units takes the connection as an argument. And if you need to setup some more functional tests with mocked connections, you can just redef the global var.

It’s not as flexible as Component would let you have it, but in my opinion, if you don’t have many components, it can end up making for a much simpler code base that’s easier to work with. Most importantly, I think it’s a better place to start for a beginner. You should feel the need for Component or other DI framework. You must understand why you’re reaching for them, and what you get out of them. Otherwise you might confuse the framework for the abstraction, and the abstraction for the concrete utility.

3 Likes
12

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.