[SOLVED] Using Bob SQL Access Toolkit with pgx's conneciton pool implementation

Using Bob SQL Access Toolkit with pgx's conneciton pool implementation

I'm evaluating Bob SQL Access Toolkit for a new project. I would like to also use the pgx connection pool implementation.

This is what I have so far in order to integrate the libraries (error handling removed):

ctx := context.Background()
pool, err := pgxpool.New(context.Background(), os.Getenv("DATABASE_URL"))
// error handling
defer pool.Close()
conn, _ := pool.Acquire(ctx)
defer conn.Release()
query := psql.Select(sm.From("categories"), sm.Where(psql.S("id").EQ(psql.Arg(id)))) // id uuid.UUID and passed as argument to the function
result, err := bob.One(ctx, BOB_EXECUTOR?, query, scan.StructMapper[model.Category]())

Problem I'm facing is that Bob requires an executor, and that's where I'm not entirely sure how the integration between those two technologies could work.

Any pointers with this?

Solution

You can use stdlib.OpenDBPool() function for this, which creates a new *sql.DB instance from a given pool.

To make this *sql.DB conform to bob.Executor interface, wrap it in a bob.NewDB():

db := bob.NewDB(stdlib.OpenDBFromPool(pool))
defer func() { _ = db.Close() }()

Then simply pass this db to Bob's autogenerated code.