Skip to content

Improve "Using transactions" documentation #3192

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Open
bbonagura9 opened this issue Feb 11, 2024 · 2 comments
Open

Improve "Using transactions" documentation #3192

bbonagura9 opened this issue Feb 11, 2024 · 2 comments
Labels
📚 postgresql documentation Improvements or additions to documentation 🔧 golang

Comments

@bbonagura9
Copy link

bbonagura9 commented Feb 11, 2024

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
Copy link
Author

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
Copy link
Collaborator

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
📚 postgresql documentation Improvements or additions to documentation 🔧 golang
Projects
None yet
Development

No branches or pull requests

2 participants