PostgreSQL and not using `lib/pq` for arrays

[papisz]

What do you want to change?

Current state

In current state of the library, using queries with arrays generates code with a dependency to github.com/lib/pq, which is in maintenance mode.

Example from the docs:

func (q *Queries) ListAuthorsByIDs(ctx context.Context, ids []int) ([]Author, error) {
	rows, err := q.db.QueryContext(ctx, listAuthors, pq.Array(ids))

It would be great if it was possible to not have pq dependency here.

Implications

This might lead to some misleading situations, example:

The pgx lib, which is used often as a replacement for lib/pq, is possible to use in such way:

db, err := sql.Open("pgx", os.Getenv("DATABASE_URL"))

This means that if you use sqlc in the default database/sql mode with such command, you end up with:

• imported pq dependency
• but actually you're using pgx as a driver

Thank you in advance for looking into this issue.

What database engines need to be changed?

PostgreSQL

What programming language backends need to be changed?

Go

[brandur]

While working on riverqueue/river#351, we found ourselves in what would seem to be an odd position where we wanted to provide a database/sql interface for use with packages like Bun and GORM, but where our users are all almost certainly using Pgx under the hood since it's really the only plausible driver these days for Postgres in the Go ecosystem.

I found myself wanting pretty much exactly what's described here — an Sqlc generated package that exposes database/sql primitives, but doesn't use anything from lib/pq given the library's no longer maintained and has a random assortment of missing features like multi-dimensional arrays.

@kyleconroy Do you know if this questions come up anywhere else? I'm not sure if the best solution would look like a brand new Sqlc driver, or just an option for the existing one. If the latter, one tricky part is that you might need to be able to configure Pgx v4 vs. Pgx v5 to be used internally. Maybe that means it should be an extra option for the pgx/v4 and pgx/v5 (e.g. expose_database_sql: true) instead.