cockroachdb / cockroachdb/docs

update pgx example

Open
#16,572 0 comments 0 reactions 1 assignee Claimed by @ianjevans View on GitHub
Dominant language
HTML
Stars
212
Forks
476
Avg merge
40m
Merged PRs (30d)
3

Description

Pgx for go has a v5 out which our new crdb-go wrapper supports. We can use this code example instead of the current one to keep this updated:
```go
package main

import (
"context"
"log"
"os"

"github.com/cockroachdb/cockroach-go/v2/crdb/crdbpgxv5"
"github.com/google/uuid"
"github.com/jackc/pgx/v5"
)

func insertRows(ctx context.Context, tx pgx.Tx, accts [4]uuid.UUID) error {
// Insert four rows into the "accounts" table.
log.Println("Creating new rows...")
if _, err := tx.Exec(ctx,
"INSERT INTO accounts (id, balance) VALUES ($1, $2), ($3, $4), ($5, $6), ($7, $8)", accts[0], 250, accts[1], 100, accts[2], 500, accts[3], 300); err != nil {
return err
}
return nil
}

func printBalances(conn *pgx.Conn) error {
rows, err := conn.Query(context.Background(), "SELECT id, balance FROM accounts")
if err != nil {
log.Fatal(err)
}
defer rows.Close()
for rows.Next() {
var id uuid.UUID
var balance int
if err := rows.Scan(&id, &balance); err != nil {
log.Fatal(err)
}
log.Printf("%s: %d\n", id, balance)
}
return nil
}

func transferFunds(ctx context.Context, tx pgx.Tx, from uuid.UUID, to uuid.UUID, amount int) error {
// Read the balance.
var fromBalance int
if err := tx.QueryRow(ctx,
"SELECT balance FROM accounts WHERE id = $1", from).Scan(&fromBalance); err != nil {
return err
}

if fromBalance < amount {
log.Println("insufficient funds")
}

// Perform the transfer.
log.Printf("Transferring funds from account with ID %s to account with ID %s...", from, to)
if _, err := tx.Exec(ctx,
"UPDATE accounts SET balance = balance - $1 WHERE id = $2", amount, from); err != nil {
return err
}
if _, err := tx.Exec(ctx,
"UPDATE accounts SET balance = balance + $1 WHERE id = $2", amount, to); err != nil {
return err
}
return nil
}

func deleteRows(ctx context.Context, tx pgx.Tx, one uuid.UUID, two uuid.UUID) error {
// Delete two rows into the "accounts" table.
log.Printf("Deleting rows with IDs %s and %s...", one, two)
if _, err := tx.Exec(ctx,
"DELETE FROM accounts WHERE id IN ($1, $2)", one, two); err != nil {
return err
}
return nil
}

func main() {
// Read in connection string
config, err := pgx.ParseConfig(os.Getenv("DATABASE_URL"))
if err != nil {
log.Fatal(err)
}
config.RuntimeParams["application_name"] = "$ docs_simplecrud_gopgx"
conn, err := pgx.ConnectConfig(context.Background(), config)
if err != nil {
log.Fatal(err)
}
defer conn.Close(context.Background())

// Insert initial rows
var accounts [4]uuid.UUID
for i := 0; i < len(accounts); i++ {
accounts[i] = uuid.New()
}

err = crdbpgx.ExecuteTx(context.Background(), conn, pgx.TxOptions{}, func(tx pgx.Tx) error {
return insertRows(context.Background(), tx, accounts)
})
if err == nil {
log.Println("New rows created.")
} else {
log.Fatal("error: ", err)
}

// Print out the balances
log.Println("Initial balances:")
printBalances(conn)

// Run a transfer
err = crdbpgx.ExecuteTx(context.Background(), conn, pgx.TxOptions{}, func(tx pgx.Tx) error {
return transferFunds(context.Background(), tx, accounts[2], accounts[1], 100)
})
if err == nil {
log.Println("Transfer successful.")
} else {
log.Fatal("error: ", err)
}

// Print out the balances
log.Println("Balances after transfer:")
printBalances(conn)

// Delete rows
err = crdbpgx.ExecuteTx(context.Background(), conn, pgx.TxOptions{}, func(tx pgx.Tx) error {
return deleteRows(context.Background(), tx, accounts[0], accounts[1])
})
if err == nil {
log.Println("Rows deleted.")
} else {
log.Fatal("error: ", err)
}

// Print out the balances
log.Println("Balances after deletion:")
printBalances(conn)
}
```

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.