Help me understand the transaction example

[preslavrachev]

Looking at the generated code, Queries has a db attribute, which is essentially a reference to the database. In order to instantiate Queries, one needs to supply it with a concrete db instance. So far, so good.

The issue comes with transactions. To work with transactions, I need to create a new instance of Queries calling WithTx(). The problem is that the function now wants me to pass a transaction instance to it, coming from a concrete db instance:

var db *sql.DB
tx, err := db.Begin()
qtx := queries.WithTx(tx)

In my view, this breaks the basic principle of encapsulation. One of the reasons, I am going to pass a Queries instance around, and not a *sql.DB is to prevent it from from accessing the database directly. However, this piece of code wants me to do just that - pass a concrete instance around.

The only workaround I see at this point in time is wrapping Queries in a struct local to my code, which will also keep a reference to the DB and expose a Method for generating a transaction-capable instance.

Does anyone have a better suggestion?

[tombh]

I don't have an answer I'm afraid. I just wanted to say that you helped me understand why I was struggling with finding a clean solution to transactions. Now I understand that I just have to keep passing around a concrete database instance. I was convinced that that wasn't the case, but now that you've helped me understand, I can accept it and move on.

I think if I at least understood why it's like this, then I might not have spent so much fighting it.

[gilcrest]

Hey @preslavrachev - I just ignore the WithTx method, actually. When I initialize the queries struct, I use the sql.Tx created earlier, not a sql.DB. I initialize the Queries struct using the New function, which takes a DBTX interface, which can either be a sql.DB or a sql.Tx - they both satisfy the interface. I always use transactions with sqlc and never a sql.DB out of personal preference. I don't know why there is a WithTx method TBH, it does the exact same thing as the New function if you use a sql.Tx with it.

[gilcrest]

Most of my calls to sqlc are chained, something like:

_, err = datastore.New(tx).CreateMovie(ctx, createMovieParams)
	if err != nil {
		return nil, err
	}