Recommended usage of conn and tx

[ghost]

I have a number of service methods which take a pgx.Conn as parameter. In order to run transactions across many services, I am acquiring a single connection from a pool, starting a transaction, and then passing the acquired connection into the service methods to do some logic. Is this the typical way of using this library, or should I be passing the Tx into the service methods?

I guess I’m wondering: is the tx always pinned to the connection that it was started on? If I pass that connection around it’s equivalent to passing around the tx?

[jackc]

The tx is pinned to the original connection. Technically, you could use the connection after starting a tx but its a bad idea. Pass the tx around instead.

But I also suggest that your service methods take neither a Conn or a Tx. You should define the interface that your method needs and accept that. Then your service methods can work with a pool, a connection, or a transaction.

[audrenbdb]

Impl ex below :

type psqlDB interface {
	Begin(ctx context.Context) (pgx.Tx, error)
	Exec(ctx context.Context, sql string, arguments ...interface{}) (pgconn.CommandTag, error)
	Query(ctx context.Context, sql string, optionsAndArgs ...interface{}) (pgx.Rows, error)
	QueryRow(ctx context.Context, sql string, optionsAndArgs ...interface{}) pgx.Row
}

func psqlInsertOfficeAddressFn() psqlInsertOfficeAddress {
	return func(ctx context.Context, a address, userID int, db psqlDB) error {
		const query = `INSERT INTO office_addresses(user_id, address_id) VALUES($1, $2)`
		_, err := db.Exec(ctx, query, userID, a.id)
		if err != nil {
			return err
		}
		return nil
	}
}

I return a function because I prefer it in my scenario but you see the idea. I can now pass to my insert address a tx or whatever and it will work fine. :)

[mattfung]

Possibly relevant, there have been discussion of adding an interface that allows decoupling of query logic with Txn, Conn in the go source repo. Hopefully they sort out something eventually
golang/go#14468