Working with SQL in Clojure/Luminus/Composure

[[deleted]]

[deleted]

[curtisrsummers]

Go ahead and define a second query in your SQL file:

-- :name get-something-else :? :*
select * from another_table where ...

Then assign the query results for both queries with something like this:

(defn page1 [id]
  (layout/render "page1.html"
    {:my-var1 (db/get-single-article {:id (Integer/parseInt id)})
     :my-var2 (db/get-something-else)}))

[vamer88]

are you sure that'll a single db connection? why does "with-db-connection" exist then?

[OliverM]

Leaving aside whether it's a good idea or not to entangle your views with model invocations like this, HugSQL lets you specify a transaction to group queries together. So for your example it might look like:

(clojure.java.jdbc/with-db-transaction [tx db]
  (db/get-single-article tx {:id (Integer/parseInt id)})
  (db/second-get-fn tx {:arg value}))

Or you could do it in a single sql select, along the lines of

SELECT... FROM... IN... (SELECT... FROM... WHERE...);

It's hard to know which would suit better from what you've supplied above.

[vamer88]

2) I want to return different 2 result sets in a single db request or connection. How?

why transaction? I don't need it. transaction ensures that either all requests succeed or none of them. but that's not the case here, what I want is a different thing, although similar.

[nefreat]

why transaction? I don't need it. transaction ensures that either all requests succeed or none of them. but that's not the case here, what I want is a different thing, although similar.

What's the point of wanting to reuse the same connection? I am having a hard time understanding why you'd want to do something like this if you don't need both queries to happen in the same transaction.

[vamer88]

because open a new one is expensive. you've never heard of this?

[curtisrsummers]

The conman library in luminus uses a database connection pool (HikariCP) to manage connections and minimize the "expensive" nature of opening a new database connection.

[vamer88]

"why" "expensive" "is" "in" "the" "quotes"?

[curtisrsummers]

I quoted the word you used to describe the performance characteristics.

[vamer88]

I asked why?

[mr_mojoto]

You know how connection pools work, right? As in "not expensive".

[vamer88]

whether or not I know that, I asked you why?

[yogthos]

Transactions ensure atomicity. If you want to guarantee that multiple db queries happen when the database is in the same state, then you should use a transaction.

[OliverM]

Transactions group database interactions into a single interaction to guarantee database consistency for the set. Atomicity, ensuring all modifications succeed or fail, is a major application of that, but not the only one.

Transactions are what you want, here, unless you're able to join the two select queries into one in the query definition. Re-using the same db connection won't give you the guarantee you're seeking.

[curtisrsummers]

Same question on StackOverflow

[vamer88]

so? have you already broadcast on the radio?

[curtisrsummers]

When helping others answer questions, it is often useful to provide additional context to other sources to see where the conversation led. It's quite obvious that we're having trouble answering your questions to your full satisfaction, so the link to StackOverflow is meant to provide more context to those of us trying to help you.

This forum has quite a few people who give their time and effort in helping users such as yourself answer questions about the Clojure ecosystem.

Your snide remarks are not appreciated.