I started using recently sqlc
with jackc/pgx/v5
. I would like to be able to use pgxpool
as well, but there is really no good way to use pgxpool
after sqlc
takes over in the flow. For instance, this is how I initialize the connection pool:
var err error
var pool *pgxpool.Pool
if pool, err = pgxpool.New(context.Background(), url); err != nil {
log.Panicf(...)
}
defer pool.Close()
queries := db.New(pool) // queries *Queries
Basically, I just make queries
available to the entire application whenever a database interaction is needed, but I don't pass around pool
.
Moreover, since sqlc
manages connections automatically, I'm not sure the implications of using something like the following snippet, because there are lots of manual steps involved and some overlapping:
ctx := context.Background()
conn, _ := pool.Acquire(ctx)
tx, _ := conn.Begin(ctx)
defer tx.Rollback(ctx)
queries := db.New(pool)
queries.WithTx(tx).OneOfTheAutogeneratedQueries(ctx)
defer conn.Release()
tx.Commit(ctx)
Anyone in the same situation? Is there a better way to approach this? I was under the assumption that the auto-generated code by sqlc
would provide a mechanism to manage transactions accordingly, but looks like there is still a need for the pool's reference in order to create a pgx.Tx
type, in addition to close resources programmatically as well.
When you use
sqlc
it typically generates an interface that abstracts away the underlying driver-level database connection or connection pool. this interface is namedDBTX
by defaultIt's important to note a few things here
pgxpool.Pool
andpgxpool.Conn
implement theDBTX
Interfacepgx.Tx
also implements theDBTX
interfaceDBTX
interface, this is to abstract the operations that you might perform on a database connection or transactionNow if it is your wish to manually manage the database connections you can set the
emit_methods_with_db_argument
option to true in thesqlc.yaml
file. when you do so, the generated code will look as followsNow query methods will have the following signature
Notice how the generated query methods accept a
DBTX
as a second parameter. you can pass here a single connection, a connection pool or even a transactionI am not sure if i understand your question correctly, but we can achieve this by simple dependency injection. you can define a struct that represents your API or APP like this
And then, in your main or wherever you're initializing the application:
Now you can execute a transaction like so
You will do the same for a singular query, instead of starting a transaction you will simply acquire a connection and pass that as
DBTX
.That being said, you don't necessarily need to this,
Exec
,Query
andQueryRow
will acquire a connection from the connection pool and release it at the end. Without usingemit_methods_with_db_argument
we can do a transaction like soUsing this, your app struct needs only to contain a reference to the
Queries
struct