Support for pgx / pgxpool PostgreSQL

[zeroallox]

Greetings,

I would love to use SQLBoiler with the native pgx / pgxpool PostgreSQL driver

https://github.com/jackc/pgx

One can already do this via their pgx <> stdlib wrapper however you're then stuck with the limitations of the database/sql interface.

See: https://github.com/jackc/pgx#choosing-between-the-pgx-and-databasesql-interfaces

[stephenafamo]

In theory, all that is needed to be able to use any of that in the boil methods is to implement the boil.ContextExecutor interface.
Since it is only a few methods (2 I think), if it doesn't already satisfy the interface, you could easily create a wrapper around it.

Once that is done, you can pass it into any of the boil methods.

[zeroallox]

Thanks for the reply!

I gave this a fiddle today...

Creating a wrapper around sql.Result is doable as that's an interface.

Having my wrapper return concrete *sql.Row and *sql.Row does not seem possible as these are concrete types defined in database/sql

First attempt:
https://gist.github.com/zeroallox/b655b5849aa4ea4bd24767a6e80e6b19

[stephenafamo]

This is interesting. Perhaps in sqlboiler/v5 instead of directly using sql.Row and sql.Rows we could use an interface that these satisfy instead.
That should make it easier to wrap around.

In the meantime, this seems like a blocker.

[zeroallox]

Ahh, ok, no problem!

What I've ended up doing is to use queries.BuildQuery(query) on the return of models.NewQuery(...mods), models.SomeObject(...mods) etc and then send the raw statement to pgx and then rows.Scan() as normal. This allows me to half use SQLBoiler as intended.

My largest operations are mass inserts (using pgx's support for COPY) or mass reads (done as described above). So it's not like i'm sending zillions small operations quickly to where it matters.

So it's kind of worked out actually :P

It would be dandy though if SQLBoiler did indeed take an interface as you were suggesting.

Thank you so much for the help and feedback! <3

[audrenbdb]

This is likely not possible with the current global database that cannot be generic:

var (
	// currentContextDB is a global database handle for the package
	currentContextDB ContextExecutor
	// timestampLocation is the timezone used for the
	// automated setting of created_at/updated_at columns
	timestampLocation = time.UTC
)

Otherwise such draft could work at with *sql.DB and easily swappable with a PGX implem:

// Executor can perform SQL queries.
type Executor[Rs Rows, R Row] interface {
	Exec(query string, args ...interface{}) (R, error)
	Query(query string, args ...interface{}) (Rs, error)
	QueryRow(query string, args ...interface{}) R
}

// ContextExecutor can perform SQL queries with context
type ContextExecutor[Rs Rows, R Row] interface {
	Executor[Rs, R]

	ExecContext(ctx context.Context, query string, args ...interface{}) (R, error)
	QueryContext(ctx context.Context, query string, args ...interface{}) (Rs, error)
	QueryRowContext(ctx context.Context, query string, args ...interface{}) R
}

// Rows is the result of a query. Its cursor starts before the first row of the result set.
// Use Rows.Next to advance through the rows.
// See [sql.Rows] for concrete implementation.
type Rows interface {
	Close() error
	Columns() ([]string, error)
	Err() error
	Next() bool
	NextResultSet() bool
	Scan(dest ...any) error
}

// Row is the result of calling QueryRow to select a single row.
type Row interface {
	Err() error
	Scan(dest ...any) error
}

// Transactor can commit and rollback, on top of being able to execute queries.
type Transactor[Rs Rows, R Row] interface {
	Commit() error
	Rollback() error

	Executor[Rs, R]
}

Instead of generic, we could have an executor that is not natively implemented by sql.DB. Such as:

// Executor can perform SQL queries.
type Executor interface {
	Exec(query string, args ...interface{}) (Row, error)
	Query(query string, args ...interface{}) (Rows, error)
	QueryRow(query string, args ...interface{}) R
}

And have a function that returns an executor from an sql.DB. I.E:

func ExecutorFromDB(*sql.DB) ContextExecutor

That would be trivial to implement. I could work on it for v5.

Thoughts ?

[stephenafamo]

This is a really good idea, however, this project is currently on maintenance (see top of README), so working on and releasing a v5 is unlikely.