Gorm integration

[mgdigital]

Hi, great project!

I'm looking at using this to replace Asynq in my application and remove the Redis dependency. I'm wondering how I can integrate this with Gorm and insert jobs as part of a Gorm transaction - they are both using the PGX driver so it should be possible but Gorm is wrapping transactions in layers of abstraction. A dirty workaround might be to get Gorm to execute the insert query here: https://github.com/riverqueue/river/blob/master/internal/dbsqlc/river_job.sql.go#L450 but that doesn't seem ideal.

Gorm is a popular ORM so I expect easy integration would improve adoption of River...

[bgentry]

Hi @mgdigital, there are a couple of challenges to work through here:

1. Although River has a concept of "drivers", this was a last-minute addition and you can probably see signs of this throughout the interfaces. There are a lot of places with hard dependencies on pgx.Tx for example which might be hard to cleanly abstract with database-agnostic interfaces. Not necessarily impossible but it will take some careful thought and a lot of work.
2. There's a distinction between Clients where you're inserting jobs, and those where you're working. The worker Clients are doing a lot more database-specific stuff beyond just simple queries including LISTEN/NOTIFY which is tightly tied to pgx.Conn as of today and would be pretty difficult to abstract away.

In short this isn't something that's likely to happen soon, though I do hope we can get there eventually. It's tough that the Go ecosystem has such a fragmented database driver landscape 😕

[brandur]

Opened #351. We'll need some additional docs, but it make use of River with GORM possible.

[arp242]

The worker Clients are doing a lot more database-specific stuff beyond just simple queries including LISTEN/NOTIFY which is tightly tied to pgx.Conn as of today and would be pretty difficult to abstract away.

If you use sql.Open("pgx", connect), then github.com/jackc/pgx/v5/stdlib basically just wraps pgx.Conn a bit to be compatible with stdlib.

The difficult scenario now is situations where you do use pgx, but not "directly" but via gorm, or some other abstraction.

riverpgxv5.New(nil) doesn't really work because this expects pgx.Tx, but I only have stdlib *sql.Tx because of this abstraction.

The bit that's still missing is:

river.NewClient(riverpgxv5.NewStdlib(nil), ...)
// Returns: *river.Client[*sql.Tx]
// Or perhaps: *river.Client[riverpgxv5.TxInterface]

Or something...

All of this is a bit of an annoying problem, because in principle everything that's needed is right there: we use pgx, we have a pgx connection, and we can use it (in principle). But it's also not easily accessible, at least, not as far as I could see.

[bgentry]

Hi @arp242, wanted to make sure you’ve seen that River now has a database/sql driver. Here’s the doc on using that with GORM: https://riverqueue.com/docs/gorm

I guess we forgot to mark this issue as closed so I’ll do that now. Please let us know if you run into any issues with using it!

[arp242]

Yes, I'm using that for the time being. But that's only for "poll mode".

What I want is "listener mode". I think we can make that work one way or the other when pgx's "stdlib mode" is used, as described above.

I suppose this could be a new issue (I'm not even using gorm, but my own abstraction), but it seems similar enough to "full gorm integration" that it didn't seem worth opening a new issue for it.

[bgentry]

@arp242 ah yep, I think #352 is one possible approach there. To your point we have the underlying fully-capable pgx connection here, all we need is to teach the database/sql driver how to use it when it's available. Would love to get your thoughts on that one if possible.

In your app, are you pretty much fully onboard with GORM and leveraging that all throughout the codebase?

[arp242]

Ah cheers, that seems very similar. I'll have a look at that later.

In your app, are you pretty much fully onboard with GORM and leveraging that all throughout the codebase?

I'm actually using zdb, but that's kind of a detail as the problems are the same GORM. I really don't want to give that up just for river, partly because it works very well for me, partly because it's a lot of work rewriting everything, and partly because SQLite support is on the roadmap for us and this abstracts that a bit.

Doing a hard dependency on GORM would probably be a mistake – ideally it should work with database/sql, sqlx, GORM, what-have-you – anything that uses pgx "under the hood" via stdlib.

[arp242]

The way I solved this, in case it might be useful for someone reading this issue in the future:

// pgx connection pool.
pool, _ := pgxpool.NewWithConfig(context.Background(), cfg)

// database/sql, can be used with gorm, bun, what-have-you
db := stdlib.OpenDBFromPool(pool)

And then in the code I have "worker", to run background jobs:

func newWorker() {
	// The pgx.Pool we created earlier
	driver := riverpgxv5.New(connections.Pool)

	workers := river.NewWorkers()
	river.AddWorker(workers, &alfred.FTMStoreWorker{})

	rc, err := river.NewClient(driver, &river.Config{
		Workers: workers,
	})
	// ...
}

And "sched" to schedule jobs:

func newSched() {
	// The stdlib.OpenDBFromPool() we created earlier.
	driver := riverdatabasesql.New(connections.DB)

	rc, err := river.NewClient(driver, &river.Config{
	})
	// ...
}

It's actually a little bit more invoked than than in passing the types around and it's obviously example code, but this is basically the gist of it.

This:

• Has running jobs with pgx listen/notify

• And scheduling jobs can still happen with stdlib-compatible code, without requiring the pgx Tx object.

Should work with more or less anything that's stdlib-compatible.