Clojurians Log v2

What do you mean by "attach" in this case?

I have a ds which will connect to a central sqlite db {:dbname db-central.sqlite} and then when I know the tenant I can attach another database by running the sqlite statement ATTACH DATABASE 'db-tenant.sqlite' AS tenant . When I do this, I can run sql statements against either the central or the tenant db without having to create two separate connections.

Ah, I see... there's no support for that in next.jdbc. I guess I'd recommend writing a wrapper that does the ATTACH and then does whatever op you need?

(although, personally, I'd probably maintain a connection per tenant)

Through a connection pool?

No, since they're all different DBs. A connection pool only makes sense for a single, specific DB.

I mean, I might have a connection-pooled datasource for each tenant, but it really depends on traffic levels/patterns.

I’d probably maintain a connection per tenantwould these be stored in a global var? I’m trying to picture the architecture. Right now, I pass in a ds at the top level and then create a connection whenever it’s needed. Is the suggestion that I would create a connection and then store it and when said tenant comes back, I grab their connection? or perhaps something less involved?

It really would depend on traffic levels/patterns. But I'd probably have a hash map from tenant name to <something>, with an entry for :central as well. How exactly the selection is made for any specific operation would depend on how the code handles tenants today...

I like Component a lot for managing stateful resources (like DB connections/pools) so I'd probably have that in the mix somewhere.

General question: what closes the connections? My understanding is that they stay alive for as long as the application in running, but is there a reason/need to close connections after a while (sqlite may be different because it’s a file on the same server as the app runtime). My assumption is that when you noted maintain a connection per tenant you also meant to keep the connections alive (for perhaps as long as the user is active for example)

It depends on a lot of things.

At work, we use MySQL and connection pools via HikariCP. We pass around a datasource -- a connection pooled ds from HikariCP -- and we just pass it to next.jdbc which takes care of getting a connection (from the pool) and closing it afterward (returning it to the pool).

If we want to keep a connection around for a while, we use with-open on it, per next.jdbc docs.

(or with-transaction in the very rare cases we want an actual TX on a connection)

I'm using honey sql to dynamically build up some SQL based on user input. I really like the way the helpers automatically combine e.g.

(-> (h/select :id) (h/select :name))
=> {:select [:id :name]}

-> (h/select :id) (h/select :name) (h/left-join :wibble [:= :wibble-fk :wibble/id]) (h/left-join :wibble [:= :wibble-fk :wibble/id]))
=> {:select [:id :name], :left-join [:wibble [:= :wibble-fk :wibble/id] :wibble [:= :wibble-fk :wibble/id]]}

FWIW I don't remember there being any such helpers. I were in the same situation once and decided that using HoneySQL data as an accumulator for a process with many independent steps should be avoided if feasible. It's better to come up with something closer to your domain and approach and then, when such a piece of data is fully ready, turn it into a HoneySQL map.

That's a good idea. I can then have special logic to add a single left join if and only if it is needed, and ensure I'm not adding it twice. I was starting from smaller independent helpers and building up, but starting from the top down is a better idea. Thanks!

I think you need the cond-> macro that updates the data structure conditionally as follows:

(cond-> {:select ...}
    
    (or is-this? is-that?)
    (add-left-join)
    
    is-that?
    (add-select-fields)

There's a #honeysql channel FYI

Thanks Sean didn’t spot that thank you.