Add support for v5 of the pgx Go driver

What do you want to change?

This proposal is a work in progress

v5.0.0 of jackc/pgx is coming in September (jackc/pgx#1273). The changes are significant. I think it's a good opportunity to make some backwards-incompatible changes to fix some long standing issues with our pgx support

What issues should be fixed?

What database engines need to be changed?

PostgreSQL

What programming language backends need to be changed?

Go

Moved pgx/v4 e2e tests to the separated dir (sqlc-dev#1823)

Added support for v5 of the pgx Go driver (sqlc-dev#1823)

Added e2e tests for pfx v5 (sqlc-dev#1823)

Added support for v5 of the pgx Go driver (sqlc-dev#1823)

Added e2e tests for pfx v5 (sqlc-dev#1823)

@kyleconroy

Hi @kyleconroy !

I decided to try to implement the support pgx v5 in the sqlc. I should have discussed the implementation details with you before I started to work. Sorry for this. But It doesn't take too much time, so I opened 3 PRs:

[PART 1] pgx v5 support (#1823) #1873 - To support two version of pgx in the e2e tests at the same time we move 'pgx/v4' tests to the separated v4 directory. Then new 'pgx/v5' e2e tests will be located in the v5 dir.
[PART 2] pgx v5 support (#1823) #1874 - support pgx v5 in the sqlc
[PART 3] pgx v5 support (#1823) #1875 - added e2e tests for pgx v5

@mcdoker18

@mcdoker18 wow, thank you! I'm a bit short on time right now, so please excuse the slow review times. I'm hoping to get these reviewed within the next week.

Looking forward to this!

hopefully this gets merged

Moved pgx/v4 e2e tests to the separated dir (#1823) (#1873)

@mcdoker18

Did I miss something?

kjconroy/sqlc:latest docker image uses the latest release of sqlc 1.16.0 which doesn't include the pgx/v5 support. You can build the main branch from source.

https://github.com/kyleconroy/sqlc/blob/main/docs/guides/development.md#building

thank @mcdoker18 , I successfully solved it according to the above！！！ However, it is really recommended to update the document and release the corresponding version in a timely manner, so as not to have to go to the issue to find the answer.

Hi，I encountered a type conversion or search execution problem when using sqlc2 and pgx/v5.See if any experts can help me:
The problem is that I used the following sql script to generate a simple search method：

-- name: SearchExamTemplatesByNameOrGradeSubjectOrCreatorName :many
SELECT *
FROM noquestion_content_exam_template 
WHERE (template_name LIKE ANY(@search_key_words::text[]))
OR (grade_subject LIKE ANY(@search_key_words::text[]))
OR (creator_name LIKE ANY(@search_key_words::text[]))
ORDER BY updated_at DESC
LIMIT $1
OFFSET $2;

Then the go method generated by sqlc is as follows：

const searchExamTemplatesByNameOrGradeSubjectOrCreatorName = `-- name: SearchExamTemplatesByNameOrGradeSubjectOrCreatorName :many
SELECT id, template_name, grade_subject, question_total_num, creator_name, created_at, updated_at
FROM noquestion_content_exam_template 
WHERE (template_name LIKE ANY($3::text[]))
OR (grade_subject LIKE ANY($3::text[]))
OR (creator_name LIKE ANY($3::text[]))
ORDER BY updated_at DESC
LIMIT $1
OFFSET $2
`

type SearchExamTemplatesByNameOrGradeSubjectOrCreatorNameParams struct {
	Limit          int32
	Offset         int32
	SearchKeyWords pgtype.Array[string]
}

func (q *Queries) SearchExamTemplatesByNameOrGradeSubjectOrCreatorName(ctx context.Context, arg SearchExamTemplatesByNameOrGradeSubjectOrCreatorNameParams) ([]NoquestionContentExamTemplate, error) {
	rows, err := q.db.Query(ctx, searchExamTemplatesByNameOrGradeSubjectOrCreatorName, arg.Limit, arg.Offset, arg.SearchKeyWords)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []NoquestionContentExamTemplate{}
	for rows.Next() {
		var i NoquestionContentExamTemplate
		if err := rows.Scan(
			&i.ID,
			&i.TemplateName,
			&i.GradeSubject,
			&i.QuestionTotalNum,
			&i.CreatorName,
			&i.CreatedAt,
			&i.UpdatedAt,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

The key now is to generate the parameter SearchKeyWords of pgtype. Array[string] type in the upper part.After I assigned the parameter in the format, I found that the structure searched by using the search method passed by the parameter was empty. However, if I change the type of this parameter to the pgtype.FlatArray[string], everything will be OK. I don't know what caused this?? But now sqlc always converts text[] type to pgtype.Array[String] type. I try to use the override parameter in sqlc.yaml to make the following configuration to force the change of its conversion rules:

 overrides: 
       - db_type: "pg_catalog.text[]"
          go_type: 
               import: "github.com/jackc/pgx/v5"
  	       package: "pgtype"
	       type: "FlatArray"

When I reexecuted sqlc generate --experimental, no error was reported, but it was found that the generated SearchKeyWords parameter was still of the pgtype. Array[string] type, and the pgtype. FlatArray[string] type was not generated as expected.

Did I miss something? Thanks anybody ~

In my models.go netip doesn't get imported in the generated code when using the inet type in a table

Hi!
do I need to do anything special to use a pgxpool? I currently get this:

./main.go:137:29: cannot use db (variable of type *"github.com/jackc/pgx/v5/pgxpool".Pool) as type sqlc.DBTX in argument to sqlc.New:
	*"github.com/jackc/pgx/v5/pgxpool".Pool does not implement sqlc.DBTX (missing ExecContext method)

(sqlc is current, afaics)

@alicebob

@alicebob You need to manually build sqlc from main branch and run with experimental flag

$ go install github.com/kyleconroy/sqlc@main
$ $GOPATH/bin/sqlc --experimental generate

@Davincible

@Davincible thanks! I was on @latest, not @main. That fixed it (with the -experimental). Now on with the rest of the pgx4 -> pgx5 changes I need to do...

@Davincible

@Davincible
Just a little tip...

I tried run as you said but i've got this following error:
package github.com/kyleconroy/sqlc is not a main package

Then, i ran as get started documentation saids:
go install github.com/kyleconroy/sqlc/cmd/sqlc@main

And it works!

@Davincible

@Davincible
Just a little tip...

I tried run as you said but i've got this following error:
package github.com/kyleconroy/sqlc is not a main package

Then, i ran as get started documentation saids:
go install github.com/kyleconroy/sqlc/cmd/sqlc@main

And it works!

Oh right yes, you're right. Forgot to add the last part

Any estimate date for the docker image be updated containing support for pgx/v5?

after yesterday's release, have these issues been all addressed?

@dht-hedaihua

I gave a try on pgx/v5 since sqlc@1.17.0 release but I was not able to make array parameters work as @dht-hedaihua mentioned.

From this query:

-- GetAllByIDs :many
SELECT * FROM something WHERE id = ANY($1::uuid[];)

sqlc now generates the following function (diff pgx/v4 vs pgx/v5):

-func (q *Queries) GetAllByIDs(ctx context.Context, dollar_1 []uuid.UUID) ([]*Item, error)
+func (q *Queries) GetAllByIDs(ctx context.Context, dollar_1 pgtype.Array[uuid.UUID]) ([]*Item, error)

However, pgtype.Array needs to be built with Dims and Valid fields.

I think that sqlc should generate this function with a pgtype.FlatArray parameter instead.

@junyan-rippling

@junyan-rippling the v1.17.0 release contained a lot of improvements per https://github.com/kyleconroy/sqlc/releases/tag/v1.17.0 but there are still some outstanding PRs that did not get merged before this release:

Postgresql 2D array in database schema generated as 1D array in models.go #1494 - (fixed in PR feat: add support for Postgres multi dimensional arrays in Go #1651)
Column override with query alias produces wrong type #1752 (fixed in PR fix: Override types of aliased columns and named parameters #1884)
PR feat: add support for Postgres multi dimensional arrays in Go #1651

@kyleconroy

@kyleconroy @mcdoker18 What is the benefit of using the pgtype.FlatArray or pgtype.Array with pgxv5 instead of a standard go slice in models/queries? Here #1651 I was going to represent multidimensional arrays with a standard multi-dim go slice. I was working on implementing the switch to FlatArray/Array today in that same branch, but found the pgtype.Array to be a bit difficult to work with in some code I was testing. And more generally, is it preferred to return a basic go type or a sql_package specific type (e.g. pgtype.Text or pgtype.Array) from query functions and in models by default?

@colli173

@colli173 sqlc 1.17.2 has been released with greatly improved support for pgx/v5. By default, generated code will use slices []T instead of pgtype.Array[T]. Going to close out this issue in favor of individual issues that for pgx.

Navigation Menu

Repository navigation

Add support for v5 of the pgx Go driver #1823

What do you want to change?

What issues should be fixed?

What database engines need to be changed?

What programming language backends need to be changed?

12 remaining items

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Add support for v5 of the pgx Go driver #1823

Description

What do you want to change?

What issues should be fixed?

What database engines need to be changed?

What programming language backends need to be changed?

Activity

mcdoker18 commented on Oct 2, 2022

kyleconroy commented on Oct 4, 2022

advdv commented on Oct 16, 2022

brlala commented on Oct 17, 2022

12 remaining items

dht-hedaihua commented on Dec 24, 2022

dht-hedaihua commented on Dec 24, 2022

Davincible commented on Jan 5, 2023

alicebob commented on Jan 10, 2023

Davincible commented on Jan 10, 2023

alicebob commented on Jan 10, 2023

BertBR commented on Jan 11, 2023

Davincible commented on Jan 11, 2023

go-aegian commented on Jan 21, 2023

junyan-rippling commented on Feb 14, 2023

gnuletik commented on Feb 14, 2023

StevenACoffman commented on Feb 14, 2023

colli173 commented on Feb 14, 2023

kyleconroy commented on Feb 22, 2023

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions