docs: Add some more example code for running transactions (sqlc-dev#1583)

Jille · web-flow · commit edb956047a10 · 2022-05-02T09:02:56.000-07:00
As asked by sqlc-dev#1479
diff --git a/docs/howto/transactions.md b/docs/howto/transactions.md
@@ -2,7 +2,8 @@
 
 ```sql
 CREATE TABLE records (
-  id SERIAL PRIMARY KEY
+  id SERIAL PRIMARY KEY,
+  counter INT NOT NULL
 );
 
 -- name: GetRecord :one
@@ -21,7 +22,8 @@ import (
 )
 
 type Record struct {
-	ID int
+	ID      int
+	Counter int
 }
 
 type DBTX interface {
@@ -41,14 +43,38 @@ func (*Queries) WithTx(tx *sql.Tx) *Queries {
 }
 
 const getRecord = `-- name: GetRecord :one
-SELECT id FROM records
+SELECT id, counter FROM records
 WHERE id = $1
 `
 
 func (q *Queries) GetRecord(ctx context.Context, id int) (Record, error) {
 	row := q.db.QueryRowContext(ctx, getRecord, id)
 	var i Record
-	err := row.Scan(&i.ID)
+	err := row.Scan(&i.ID, &i.Counter)
 	return i, err
 }
 ```
+
+With pgx you'd use it like this for example:
+
+```go
+function bumpCounter(ctx context.Context, p *pgx.Conn, id int) error {
+	tx, err := db.Begin(ctx)
+	if err != nil {
+		return err
+	}
+	defer tx.Rollback()
+	q := db.New(tx)
+	r, err := q.GetRecord(ctx, id)
+	if err != nil {
+		return err
+	}
+	if err := q.UpdateRecord(ctx, db.UpdateRecordParams{
+		ID:      r.ID,
+		Counter: r.Counter+1,
+	}); err != nil {
+		return err
+	}
+	return tx.Commit()
+}
+```
