JobListTx missing similar error to JobList on metadata w/ SQLite

Howdy!

I'm testing out the SQLite integration. We use metadata to provide an easy way to find particular jobs when we don't know the ID ahead of time.

Our code uses JobListTx rather than JobList, and we receive this fun error from the driver:

SQL logic error: unrecognized token: "@" (1)

I went to write a reproducer, and accidentally used JobList. To my surprise, there was a much better error!

JobListResult.Metadata is not supported on SQLite

Looking at the code, it looks like the error was added to JobList but not JobListTx.

Full reproducers below:

NOT GREAT ERROR:

package main

import (
	"context"
	"database/sql"
	"encoding/json"
	"log"

	"github.com/riverqueue/river"
	"github.com/riverqueue/river/riverdriver/riversqlite"
	"github.com/riverqueue/river/rivermigrate"
	_ "modernc.org/sqlite"
)

type job struct{}

func (j job) Kind() string { return "job" }

func main() {
	db, err := sql.Open("sqlite", ":memory:")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	db.SetMaxOpenConns(1)

	driver := riversqlite.New(db)

	migrator, err := rivermigrate.New(driver, nil)
	if err != nil {
		log.Fatal(err)
	}

	ctx := context.Background()
	if _, err := migrator.Validate(ctx); err != nil {
		log.Fatal(err)
	}

	if _, err := migrator.Migrate(ctx, rivermigrate.DirectionUp, nil); err != nil {
		log.Fatal(err)
	}

	riverClient, err := river.NewClient(driver, &river.Config{})
	if err != nil {
		log.Fatal(err)
	}

	metadata, err := json.Marshal(map[string]string{
		"foo": "bar",
	})
	if err != nil {
		log.Fatal(err)
	}

	if _, err := riverClient.Insert(ctx, job{}, &river.InsertOpts{Metadata: metadata}); err != nil {
		log.Fatal(err)
	}

	tx, err := db.Begin()
	if err != nil {
		log.Fatal(err)
	}
	defer tx.Rollback()

	jobs, err := riverClient.JobListTx(ctx, tx, river.NewJobListParams().Metadata(string(metadata)))
	if err != nil {
		log.Fatal(err)
	}

	if err := tx.Commit(); err != nil {
		log.Fatal(err)
	}

	for _, j := range jobs.Jobs {
		log.Printf("job: %+v", j)
	}
}

VS the GREAT ERROR:

package main

import (
	"context"
	"database/sql"
	"encoding/json"
	"log"

	"github.com/riverqueue/river"
	"github.com/riverqueue/river/riverdriver/riversqlite"
	"github.com/riverqueue/river/rivermigrate"
	_ "modernc.org/sqlite"
)

type job struct{}

func (j job) Kind() string { return "job" }

func main() {
	db, err := sql.Open("sqlite", ":memory:")
	if err != nil {
		log.Fatal(err)
	}

	db.SetMaxOpenConns(1)

	driver := riversqlite.New(db)

	migrator, err := rivermigrate.New(driver, nil)
	if err != nil {
		log.Fatal(err)
	}

	ctx := context.Background()
	if _, err := migrator.Validate(ctx); err != nil {
		log.Fatal(err)
	}

	if _, err := migrator.Migrate(ctx, rivermigrate.DirectionUp, nil); err != nil {
		log.Fatal(err)
	}

	riverClient, err := river.NewClient(driver, &river.Config{})
	if err != nil {
		log.Fatal(err)
	}

	metadata, err := json.Marshal(map[string]string{
		"foo": "bar",
	})
	if err != nil {
		log.Fatal(err)
	}

	if _, err := riverClient.Insert(ctx, job{}, &river.InsertOpts{Metadata: metadata}); err != nil {
		log.Fatal(err)
	}

	jobs, err := riverClient.JobList(ctx, river.NewJobListParams().Metadata(string(metadata)))
	if err != nil {
		log.Fatal(err)
	}

	for _, j := range jobs.Jobs {
		log.Printf("job: %+v", j)
	}
}

[EDIT] depending on how quickly https://github.com/riverqueue/river/issues/570#issuecomment-2869139374 comes into play, this might be a non-issue!

Start in client.go at the JobList implementation around line 2018, then compare it with JobListTx around line 2046. Reproduce the metadata query with the SQLite integration and check issue 570's referenced discussion before deciding whether the same unsupported-metadata error is still needed. Done means JobListTx reports the clear SQLite limitation instead of the driver's token error.