when to prepare postgresql statements in a golang web server?

Question

I have a web server that connects to a postgresql database. As I understand, postgresql driver manages a connection pool internally so I made the database connection a global variable.

I am using prepared statements and I do not know whether it is a good idea to prepare them in advance in my main function before the server has started, or do it in my request handlers (as below). I am new to golang. I think it is more efficient to make the statements global, but I am not sure. Please help.

var db *sql.DB

func main() {
  router = pat.New()
  router.Get("/", handler)
  db, e := sql.Open("postgres", "...")
  ...
  http.ListenAndServe("127.0.0.1", router)
}

func handler(w http.ResponseWriter, r *http.Request) {
  s, e := db.Prepare("select * from mytable where field=$1")
  r, e := s.Exec(123)
  ...
}

Answer 1

It all depends on your use case. As a rule of thumb, I would say that you should prepare your statements before running your server, for multiple reasons:

• You can fail to start if a statement doesn't prepare correctly. If you prepare them on-the-fly, a failing statement could invalid the entire program long after it has been started.
• You don't have to handle concurrency if you prepare them beforehand: if preparing when needed, you have to use a syncing mechanism to ensure a statement won't be prepared multiple times in parallel, which would end with your SQL server going down in flames…
• It is way simpler to handle.

As for your database handle, you should make the statements global to have them handy at any time without having to pass pointers around. If you find yourself juggling with many statements, like more than 10-15 (arbitrary number), you will probably find it easier to put all DB-related stuff (DB initialization, queries, etc) into a sub-package of your main package.