How to avoid code-duplication with open/close database use-case (context management)?

Question

Just getting started with Go and I'm wondering about the following situation:

I have a pretty simple codebase where I simply want to open/close a database connection and execute a simple query. I can do this as follows (just showing the important bits here):

import (
    "database/sql"
    _ "github.com/lib/pq"
)

func (db *Database) ExecQueryA() {
    dbConn, err := sql.Open("postgres", db.psqlconn)
    if err != nil {
        panic(err)
    }
    defer dbConn.Close()

    _, err = db.Exec(...
    if err != nil {
        panic(err)
    }
}

The above idea works fine, but what if I want to write x more of these functions, I do not want to duplicate this part:

dbConn, err := sql.Open("postgres", db.psqlconn)
if err != nil {
    panic(err)
}
defer dbConn.Close()

At the start of each function (i.e. I want to avoid code duplication). In python I would write a context manager for this, I.e. I would use a with .. statement which would open and close the database connection for me. When using Go, what is the best way to avoid code duplication in this use case?

Is there a reason you want to call Open & Close for every Exec? As per the docs "the Open function should be called just once. It is rarely necessary to close a DB." (this example may help). — Brits
– Brits, Commented Jul 26, 2021 at 0:21
This is a good resource when starting out with SQL on Go, common pitfalls and good practice things - like not calling sql.Open everywhere : http://go-database-sql.org/ — Nathanael C.
– Nathanael C., Commented Jul 27, 2021 at 15:35

mkopriva · Accepted Answer · 2021-07-26 02:15:23Z

4

As Brits points out in the comment to your question, the *sql.DB does not need to be open and closed every time you intend to use it. Instead a single shared instance of *sql.DB, Opened once at the launch of your app, is a common and recommended practice.

... the Open function should be called just once. It is rarely necessary to close a DB.

Note that *sql.DB is not a connection, instead, it is a pool that manages multiple connections, opens as many as necessary (and possible), keeps idle ones around if necessary, frees them if unnecessary, etc. And most of all, it is safe for concurrent use.

DB is a database handle representing a pool of zero or more underlying connections. It's safe for concurrent use by multiple goroutines.

To answer your actual question, one pattern to reduce the repetition of obtaining-and-releasing resources is to pass a function literal to a wrapper function:

func (db *Database) run(f func(c *sql.DB)) {
    c, err := sql.Open("postgres", db.psqlconn)
    if err != nil {
        panic(err)
    }
    defer c.Close()
    
    f(c)
}

func (db *Database) ExecQueryA() {
    db.run(func(c *sql.DB) {
        _, err := c.Exec(...
        if err != nil {
            panic(err)
        }
    })
}

answered Jul 26, 2021 at 2:15

mkopriva

38.7k4 gold badges73 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gio Over a year ago

Thx. that is great info! The GoLang sql.Open doc (pkg.go.dev/database/sql#Open ) is not so straight forward about the connection pool unfortunately (altought it makes total sense), a good ref. for others coming here: go-database-sql.org/connection-pool.html

Collectives™ on Stack Overflow

How to avoid code-duplication with open/close database use-case (context management)?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related