Sqlx: Enable using `jmoiron/sqlx` in combination (in transaction) with other go database libraries

[gima]

_Consider the following:_

1. I would like to use "high-level-sql-helper-library" for CRUD, because it is tedious to write by hand.
2. I would also like to use jmoiron/sqlx for raw SQL, which is not supported by the high level SQL library.
3. Operations performed by both of the libraries need to run in a transaction.

_Now there is a problem._

Both of the two libraries should use one and the same transaction.
The "high-level-sql-helper-library" supports using sql.Tx if given.

jmoiron/sqlx does not support exposing the sql.Tx that it has started, nor does it support an externally-started sql.Tx.

Either of those options would allow jmoiron/sqlx to fully inter-operate with other golang database libraries.

You can - for example - replace "high-level-sql-helper-library" with upper/db, for which I opened a feature request (upper/db#57) about this same problem.

---

_I deeply request this feature: to expose sql.Tx that was started by sqlx and for sqlx to be able to use sql.Tx which was started externally._

[jmoiron]

You can reach the Tx started in sqlx a number of ways. Firstly, db.Begin() returns an sql.Tx. However, if you are using sqlx.Tx for some of sqlx's extended features, you can access the sql.Tx like this:

tx := db.MustBegin()
tx.Tx // this is an sql.Tx

You can also wrap an existing Tx:

// assume tx is an sql.Tx from some other library
txx := sqlx.Tx{Tx: tx}

There are some limitations to this; specifically, the sqlx named query features will fail unless you are using a database with the default bindvar type (which is '?', so either MySQL or sqlite). You could technically get around this by creating an sqlx.Tx using an sqlx.DB, and then replacing its Tx with your own, but this leaves a hanging and useless transaction. I would consider a NewTx function that takes an sql.Tx, but to recover the features you will need to supply a driverName, and I'm not sure that makes a lot of sense from an API standpoint (it's unclear why that'd be necessary).

Let me know if this helps.

[gima]

Sorry it has taken me this long to get back to this.

I totally missed the Tx.Tx in the api, damn me. That shoulds about good for the direction of sqlx starting the Tx and someone else using it.

I see that passing dataSoureName to another function than the constructor seems quite awkwards from the design point of view.

A question/suggestion: Couldn't the already-instantiated sqlx.DB have a method similar to Beginx that would take an sql.Tx as it's parameter and find out the dataSourceName from the sqlx.DB (or from where it's stored, since it's already known)?

This suggestion would require an already-existing connection to the database, but imo that sounds reasonable, and api-wise a clean(?) solution.

[jmoiron]

Hmm. You could do that, but if you had the db already you could use db.DriverName()

The issue is that sqlx.Tx has a private driverName attribute that will not be settable.

Much of the sqlx API is still accessible on the sql datatypes by using the function implementations instead of relying on the method ones, eg. sqlx.Select(sqltx, ...). For creating an sqlx.Tx (eg, if you need it in another function) with the full NamedQuery features, I see two options:

• add a method to db which creates a new sqlx.Tx for a given sql.Tx
• add a New function, like NewTx, which does the same, but requires a driverName param

I admit this is a hole in the API, but it's pretty small, and I'm not sure such a function would find a lot of use.

[gima]

I can only say from my point of view that in my opinion this "extension point" could work as well as an encouragement towards clean separation of concern. This way future libraries (or those updating) could take into account the fact that they wouldn't have to half-assedly implement the raw-ish SQL support that is nevertheless needed every now and then with every orm's. They could just open up their API the same way and let, for example, sqlx take care of that part. This way neither has to step on the other's shoes and everyone lives happily ever after.

Commenting directly on the two options that you presented:

I've not used the package-level functions, but as I see, they mirror some of the functionality present as methods on various structs for the purpose of using sqlx "stand-alone" (to complement raw sql library).

I don't see a reason why the new functionality shouldn't be mirrored the same way. One method for DB and one function for the package (which would of course require the driverName as an additional parameter).

Did I manage to convince? :3

[jmoiron]

On second thought I think the behavior of named queries on sqlx.Tx{Tx: Tx} is fine; if this is for extension writers, they can always use sqlx.Rebind or db.Rebind to get the right bindtype. I'm moving away from doing named -> native bindtype in favor of a more composable API that centers around the ? var anyway.

If you can give me a concrete example of something that cannot be done because of this limitation in a real package, please reopen and I will reconsider adding to the API.

[zwass]

@jmoiron your proposed syntax of sqlx.Tx{Tx: Tx} doesn't initialize the mapper, and results in segfaults when using tx.Select. Should the user also be manually initializing a mapper? If so, like sqlx.Tx{Tx: tx, Mapper: reflectx.NewMapperFunc("db", sqlx.NameMapper)} for the default behavior? Seems like it would be nice to provide a helper as this is not (no longer?) necessarily trivial to understand.

[dsemenchuk]

Thanks @zwass for this workaround. Saved a lot of time.