Best practice for long-running pgxpool

[pgrandinetti]

In a package that is part of a web service running 24/7 I plan to:

• initialize a *pgxPool in the init() func
• have all other funcs in the same package so that they use the same *pgxPool (mentioned above)
• as a consequence, the entire application will use one *pgxPool only.

Is this a good practice and efficient way to use pgxpool ?

Minimal example for additional clarity:

package storagefn

import (
	"context"
	"log"
	"os"

	"github.com/jackc/pgx/v5/pgxpool"
)

var (
	// All functions in this package should use this Pool to execute queries.
	ConnPool *pgxpool.Pool
)

func init() {
	var err error

	dbUri := os.Getenv("DATABASE_URI")
	ConnPool, err = pgxpool.New(context.Background(), dbUri)
	if err != nil {
		log.Fatalf("unable to create storage connection pool: %s", err)
	}
}

[jackc]

From a pgx point of view what you are proposing will work. I usually have a single *pgxpool.Pool for my web apps.

However, I prefer the pool creation to be directly called rather than rely on package init(). That makes it easier to get the database connection string from a CLI param or some other place than an envvar.

In addition, I have found it helpful (albeit seemingly inconvenient at first) to pass the pool around as needed rather than use a global. This allows me to run tests that each have their own database.

[pgrandinetti]

Thank you. If not in the init() and with a package-scoped var, how do you ensure there's only one *pgxpool.Pool in the application? (E.g., you implement a singleton pattern, or ..?)

[jackc]

I generally create the pool in func main() or the equivalent and pass it to the services that need it.

See https://github.com/jackc/booklog/blob/bf1145e7ba5851fac54dfaeb129d349d613205d5/cmd/serve.go#L44C3-L44C9 and https://github.com/jackc/booklog/blob/bf1145e7ba5851fac54dfaeb129d349d613205d5/cmd/serve.go#L75 for an example from one of my side projects using viper and cobra.

[pgrandinetti]

Thanks. Do you also have examples of the tests that you mentioned earlier?

I have found it helpful (albeit seemingly inconvenient at first) to pass the pool around as needed rather than use a global. This allows me to run tests that each have their own database.

[jackc]

Thanks. Do you also have examples of the tests that you mentioned earlier?

https://github.com/jackc/booklog/blob/master/test/browser/user_registration_test.go. You'll need to follow the code a bit as their are several sets of helper code that work together to get a separate database per test.