Repository navigation
Improve "Using transactions" documentation #3192
Description
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:
./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)./main.go:41:20: cannot use conn (variable of type *pgx.Conn) as *sql.DB value in argument to bumpCounter
Questions
- Why this error happens in the code exactly copied from the documentation?
- 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!
Reacted by Olivier Nguyen Quoc
Activity
bbonagura9 commentedon Feb 12, 2024
I personally solved it with this:
pgx.Connas the db parameter typectxonBegin,RollbackandCommitDoes anyone think this should be fixed on the documentation? I can open a PR if you think so.
kyleconroy commentedon Mar 4, 2024
Sorry about the confusion. That documentation was written when we only supported github/lib/pq. You'll need to update it to use
pgxif you're using pgx.@bbonagura9 Instead of updating the example, could you create a second one for pgx? That would be excellent.