Improve "Using transactions" documentation

[bbonagura9]

Overview

I was trying to implement a simple transaction following the documentation but it's not clear and the code return errors.

Details to reproduce

Files:

├── dbsqlc
│   ├── db.go
│   ├── models.go
│   └── query.sql.go
├── go.mod
├── go.sum
├── main.go
├── query.sql
├── schema.sql
└── sqlc.yaml

schema.sql

CREATE TABLE records (
  id SERIAL PRIMARY KEY,
  counter INT NOT NULL
);

query.sql

-- name: GetRecord :one
SELECT * FROM records
WHERE id = $1;

-- name: UpdateRecord :exec
UPDATE records SET counter = $2
WHERE id = $1;

sqlc.yaml

version: "2"
sql:
  - engine: "postgresql"
    queries: "query.sql"
    schema: "schema.sql"
    gen:
      go:
        package: "dbsqlc"
        out: "dbsqlc"
        sql_package: "pgx/v5"

main.go

package foo

import (
	"context"
	"database/sql"
	"foo/dbsqlc"

	"github.com/jackc/pgx/v5"
)

func bumpCounter(ctx context.Context, db *sql.DB, queries *dbsqlc.Queries, id int32) error {
	tx, err := db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()
	qtx := queries.WithTx(tx)  // ERROR 1
	r, err := qtx.GetRecord(ctx, id)
	if err != nil {
		return err
	}
	if err := qtx.UpdateRecord(ctx, dbsqlc.UpdateRecordParams{
		ID:      r.ID,
		Counter: r.Counter + 1,
	}); err != nil {
		return err
	}
	return tx.Commit()
}

func main() {
	ctx := context.Background()
	conn, err := pgx.Connect(ctx, "host=localhost user=*** password=*** sslmode=disable")
	if err != nil {
		panic(err)
	}
	defer conn.Close(ctx)

	queries := dbsqlc.New(conn)

	bumpCounter(ctx, conn, queries, 1)  // ERROR 2
}

Issues encontered

Just like the documentation indicates. But I came to two problems:

1. ./main.go:17:24: cannot use tx (variable of type *sql.Tx) as pgx.Tx value in argument to queries.WithTx: *sql.Tx does not implement pgx.Tx (missing method Begin)
2. ./main.go:41:20: cannot use conn (variable of type *pgx.Conn) as *sql.DB value in argument to bumpCounter

Questions

1. Why this error happens in the code exactly copied from the documentation?
2. Where the db parameter should come from? (This one maybe my skill issue :) )

Aditional info

$ sqlc version
v1.25.0

$ go version
go version go1.21.6 linux/amd64

module foo

go 1.21.6

require github.com/jackc/pgx/v5 v5.5.3

require (
	github.com/jackc/pgpassfile v1.0.0 // indirect
	github.com/jackc/pgservicefile v0.0.0-20221227161230-091c0ba34f0a // indirect
	golang.org/x/crypto v0.17.0 // indirect
	golang.org/x/text v0.14.0 // indirect
)

Thanks!

[bbonagura9]

I personally solved it with this:

5d4
< 	"database/sql"
11,12c10,11
< func bumpCounter(ctx context.Context, db *sql.DB, queries *dbsqlc.Queries, id int32) error {
< 	tx, err := db.Begin()
---
> func bumpCounter(ctx context.Context, db *pgx.Conn, queries *dbsqlc.Queries, id int32) error {
> 	tx, err := db.Begin(ctx)
16c15
< 	defer tx.Rollback()
---
> 	defer tx.Rollback(ctx)
28c27
< 	return tx.Commit()
---
> 	return tx.Commit(ctx)

• Using pgx.Conn as the db parameter type
• Adding the parameter ctx on Begin, Rollback and Commit

Does anyone think this should be fixed on the documentation? I can open a PR if you think so.

[kyleconroy]

Sorry about the confusion. That documentation was written when we only supported github/lib/pq. You'll need to update it to use pgx if you're using pgx.

@bbonagura9 Instead of updating the example, could you create a second one for pgx? That would be excellent.