What is the right way to work with DB and DB connection pool?

Question

In my code I open DB at start of program and pass db variable to other methods. I think it's stupid and not right. But what should I do? Should I open db connection in each method? But this way also doesn't look right... And I have a lot of errors: DB::ConnectionRefused, DB::PoolTimeout, DB::PoolRetryAttemptsExceeded So it something wrong with my code.

def main_meth
db = DB.open("postgres://blabla@localhost:5432/bla?retry_attempts=8&retry_delay=3&max_pool_size=50&max_idle_pool_size=10&checkout_timeout=0.1") # there is always same story with or without params.
begin

db.scalar("")
...
another_meth(params, db)

channel = Channel(Nil).new(20)
    groups.each do |group|
      spawn one_more_meth(group, channel, db)
    end
    groups.size.times { channel.receive }

 ensure
      db.close
    end
end

def another_meth(p, db)
deeper_meth(db)
end

def one_more_meth(group, channel, db)
...
db.query_all
...
channel.send(nil)
end

Answer 1

I ran into a similar issue and I found is that when doing db.query you have to make sure to either:

save the result sets to a variable and after usage close them

rs = db.query("")
Class.from_rs(rs)
rs.close

OR use a block

db.query("") do |rs|
    Class.from_rs(rs)
end