Server: Go client

Prerequisites

A running server, and go get github.com/jackc/pgx/v5.

Connect and query

package main

import (
	"context"
	"fmt"

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

func main() {
	ctx := context.Background()
	conn, err := pgx.Connect(ctx,
		"postgres://[email protected]:5433/main?sslmode=disable")
	if err != nil {
		panic(err)
	}
	defer conn.Close(ctx)

	var id int
	var body string
	err = conn.QueryRow(ctx,
		"SELECT id, body FROM notes WHERE id = $1", 1).Scan(&id, &body)
	if err != nil {
		panic(err)
	}
	fmt.Println(id, body)
}

Breakdown: sslmode=disable matches the cleartext listener. $1 numbered placeholders are bound from the trailing args and sent over the extended protocol. QueryRow(...).Scan(...) reads a single row into typed Go variables.

Multiple rows

rows, err := conn.Query(ctx, "SELECT id, body FROM notes ORDER BY id DESC LIMIT 50")
if err != nil { panic(err) }
defer rows.Close()

for rows.Next() {
	var id int
	var body string
	if err := rows.Scan(&id, &body); err != nil { panic(err) }
	fmt.Println(id, body)
}
if err := rows.Err(); err != nil { panic(err) }

Transactions

tx, err := conn.Begin(ctx)
if err != nil { panic(err) }

_, err = tx.Exec(ctx, "UPDATE accounts SET balance = balance - $1 WHERE id = $2", 100, 1)
if err != nil { tx.Rollback(ctx); panic(err) }

_, err = tx.Exec(ctx, "UPDATE accounts SET balance = balance + $1 WHERE id = $2", 100, 2)
if err != nil { tx.Rollback(ctx); panic(err) }

if err := tx.Commit(ctx); err != nil { // returns once the WAL is durable
	panic(err)
}

Breakdown: the engine runs the transaction for real; Commit blocks until durable. Inspect the returned *pgconn.PgError code for a serialization/conflict (40001) and retry the whole transaction if so.

Pooling

import "github.com/jackc/pgx/v5/pgxpool"

pool, err := pgxpool.New(ctx, "postgres://[email protected]:5433/main?sslmode=disable")
if err != nil { panic(err) }
defer pool.Close()

var n int
_ = pool.QueryRow(ctx, "SELECT count(*) FROM notes").Scan(&n)

For serverless bursts, front the server with a transaction-mode pooler — see Connection pooling.

Next