What's the recommended way to initialize and keep prepared statements in Go?

Question

I'm writing my first Go project using sqlx and want to use Prepared statements.

I'm not sure what's the recommended practice to initialise and keep the Prepared statement variables in a nice manageable way.

I want them to be accessible only from the part of the code which actually has to use them, so far each statement is used by a single function, so global variables are not a good option (besides generally being frowned upon).

In C/C++ I would probably use a function static variable and initialise it the first time that the function is entered. This way the information about the statement content and the call which uses it are close to each other.

But from what I know so far there is no "method static variables" in Go, so what's the alternative?

I found references to Closures, which are anonymous functions, but is this the best way to achieve this? Am I aiming for the right thing from perspective of "prepared statements best practices"?

Answer 1

One way I have dealt with this is to initializes all the prepared statements I want to "keep alive" (i.e. those that are frequently used) in the main function and save them into a map which I then pass as an argument to the functions that need access to the prepared statements.

This doesn't meet your requirement of being only accessible from the functions that actually use them, but it does avoid globals and prevents having to re-prepare them when they are needed.

Using closures you could do something like this:

func main() {

    // initialize your database etc.
    getData, stmt := initGetData(db) // db is *sql.DB, initGetData is the function below
    defer stmt.Close()
    myResult := getData()
}

func initGetData(db *sql.DB) ((func() string), *sql.Stmt) {
    stmt, err := db.Prepare("SELECT something FROM some_table")
    if err != nil {
        log.Fatal(err)
    }
    return func() string {
        var result string
        err := stmt.QueryRow().Scan(&result)
        if err != nil {
            log.Fatal(err)
        }
        return result
    }, stmt
}

Doing it like this you have the prepared statement with your function that makes the query. But the statement is prepared only when the initGetData() function is called which returns the closure. The closure runs the query and has access to the prepared statement that was created when myQuery was called.

You then simply use getData() every time you need to run the query. This would meet your requirements.