when to prepare postgresql statements in a golang web server?

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)
  ...
}

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.

Note this is a longer article based on my readings and personal experience and will the form the basis of a blog article I am writing. I will try to explain the concepts with examples from an API service I am developing

Advantages of Prepared Statements

I try to prepare all my statements upon startup of my application. This can have some performance benefits but as mentioned in the other answer this checks the statement is valid and can fail startup. Combined with some self healing like on Kubernetes I have been able to avoid deploying a broken statement (due to an additional ,) and roll back to a known version of my application.

Disadvantages of global variables

Despite this I would not recommend using global variables to manage your prepared statements. These are difficult to test and make refactoring and expanding your application harder. Instead I use the pattern below:

Define a service interface

By defining an interface that can be passed to functions we can easily mock an implementation for testing.

// ExampleRetrievalService defines methods for retrieval
// of examples
type ExampleRetrievalService interface {
	// All returns all examples for a particular userID
	All(userID string) ([]Example, error)
}

Accept the interface in your http.Handler

By writing a handler that accepts an interface we can easily pass in different implementations. e.g. a postgres backed implementation or a redis or mysql etc.

// ExampleHandler takes an implementation of ExampleRetrievalService and uses it to return a list of Example objects
func ExampleHandler(exampleService ExampleRetrievalService) http.Handler {
	return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
        examples, err := exampleService.All(getUserID(r))
        [...]
	})
}

Implement service with database

In a postgres package I implement the service with a database backed struct. Note the attributes are prepared statements:

type exampleRetrievalService struct {
	retrieveAllStatement    *sqlx.Stmt
}

func (s *exampleRetrievalService) All(userID string) ([]ex.Example, error) {
	var examples []ex.Example
	return examples, s.retrieveAllStatement.Select(&examples, userID)
}

Upon service implementation create, prepare statements

const (
    selectAllForUserID = `SELECT * FROM example WHERE user_id=$1`
)

// NewExampleRetrievalService takes a `*sqlx.DB` and returns an implementation of
// `ex.ExampleRetrievalService` backed by PostgreSQL
func NewExampleRetrievalService(db *sqlx.DB) (*exampleRetrievalService, error) {

	retrieveAllStatement, err := db.Preparex(selectAllForUserID)

	if err != nil {
		errors.Wrap(err, "unable to prepare database transactions")
	}
	return &exampleRetrievalService{
		retrieveAllStatement:    retrieveAllStatement,
	}, err
}

Use the database creation function

In my main.go all I have to do is create a new implementation of my service and pass it to relevant functions. These can now be tested with mock implementations and I can easily swap implementations per function if required.

func main() {
    [...]
    db = sqlx.MustConnect("postgres", viper.GetString(keyPostgresConn))
	exampleRetrievalService, err = postgres.NewExampleRetrievalService(db)
    [...]
    r.Handle("/", ExampleHandler(exampleRetrievalService))
}

Using prepared statements upon service initialisation guarantees at least my SQL statements are correct. Defining interfaces that I pass to handlers instead of global variables means I can create test postgres implementations for integration testing. It may seem a little extra work but this structure provides plenty of flexibility to handle both simple and very complex systems.