Am I connecting to the database correctly?

Help & Support
1

I would appreciate advice on the recommended way for connecting to postgres and using that connection. Currently, for every action that deals with the database I have a function as shown below, and I establish a connection within that function. Is that the correct approach?

Two main reasons why I am asking this:

  1. When I was using Go and RethinkDB, I was connecting once globally, and was using that connection in each of my functions. Maybe that was not right either, but it worked pretty well for the loads I ever had. So, I just want to make sure that connecting in each function again and again is okay.

  2. Once in 10 times or so, some simple queries take quite long to respond (3-4 seconds). I have a default local Postgres installation with very little data (less than 3MB), and I am the only one connected to it. So, I am not sure what is causing these slow queries. Probably, I should look into Postgres more, but at least want to make sure I am connecting correctly from Crystal.

Thank you very much!

def update_user()
   conn = DB.open mydburl
   begin
       res = conn.query_all(.... the query here ...)
   rescue ex
      log.error(ex.message)
   else
      * do things with the result *
   ensure
      conn.close
   end
end
2

Hmmm… it seems crystal-db lacks documentation about this.

You should call DB.open once in your app and reuse that connection. You can use a constant for that, something like:

MY_DB = DB.open(ENV["DB_URL"])

Or lazily through a class property.

crystal-db manages a pool connection so you don’t have to care about that. Opening a connection is slow so you want to avoid opening and closing connections all the time. There’s also no need to close MY_DB, or you can close it when the app exists (but it’s not necessary).

3

I am so glad I asked!

Thank you so much @asterite. You have been really helpful.