I am trying to make a simple API in Sinatra as practice. I have code like below:
require 'pg'
...
db_params = {
host: 'localhost',
dbname: 'dbname',
user: 'user',
password: 'password'
}
psql = PG::Connection.new(db_params)
before { content_type 'application/json' }
after { psql.close }
get '/' do
beer = psql.exec_params('SELECT * FROM beers LIMIT 20')
...
end
I notice that when I hit the URL, I get my results. If I hit it again relatively quickly, it returns that the connection has been closed. This makes sense since I'm closing the connection after the response is sent. However, my assumption was that it would open another connection on the next request. I may be misunderstanding how the PG gem works. I want to make sure I am using it correctly. Do I need to worry about closing it each time? If I don't manually close it, will it leave the connection open and drain resources, or will it close the connection automatically at the end of the response?
As written, it doesn't open a new connection each time. It only opens a connection when you call PG::Connection.new, which is positioned so that it's basically start-up only. As a result, when you close it after the first request, it is closed permanently.
How you want to handle your connection depends on what your expected load characteristics are. You could open and close a connection every time, you could maintain a connection pool, you could use a single long-lived connection.
If this is just for your test API, it will be easiest for you to either open and close every time, or just open a single long-lived connection and restart your server if the connection happens to time out at any point.