database/sql

type Driver interface {
    Open(name string) (driver.Conn, error)
}

type QueryerContext interface {
    QueryContext(ctx context.Context, query string, args []driver.NamedValue) (driver.Rows, error)
}

package open

import (
    "database/sql"
    "log"

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

func SQLOpen() {
    db, err := sql.Open("pgx", "postgres://pgx_md5:secret@localhost:5432/pgx_test")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()
}

package open

import (
    "context"
    "log"

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

func PGXOpen() {
    ctx := context.Background()
    conn, err := pgx.Connect(ctx, "postgres://pgx_md5:secret@localhost:5432/pgx_test")
    if err != nil {
        log.Fatal(err)
    }
    defer conn.Close(ctx)
}

func IsItAliveQuestionMark(ctx context.Context) {
    db, err := sql.Open("pgx", "postgres://pgx_md5:secret@localhost:5432/pgx_test")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    if err := db.PingContext(ctx); err != nil {
        log.Fatal(err)
    }
}

func Query(ctx context.Context, db *sql.DB) {
    rows, err := db.QueryContext(ctx, "SELECT id, name FROM users")
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    for rows.Next() {
        var id int
        var name string
        if err := rows.Scan(&id, &name); err != nil {
            log.Fatal(err)
        }

        log.Println(id, name)
    }

    if err = rows.Err(); err != nil {
        log.Fatal(err)
    }
}

func QueryRow(ctx context.Context, db *sql.DB) {
    var id int
    var name string
    err := db.QueryRowContext(ctx, "SELECT id, name FROM users WHERE id = $1", 1).Scan(&id, &name)
    if err != nil {
        if errors.Is(err, sql.ErrNoRows) {
            log.Println("nothing found")
            return
        }

        log.Fatal(err)
    }

    log.Println(name)
}

func Exec(ctx context.Context, db *sql.DB) {
    res, err := db.ExecContext(
        ctx,
        "UPDATE users SET name = $1 WHERE id = $2",
        "William Mandella", 1,
    )
    if err != nil {
        log.Fatal(err)
    }

    lastID, _ := res.LastInsertId()
    rowsAffected, _ := res.RowsAffected()

    log.Println(lastID, rowsAffected)
}

type Result interface {
    LastInsertId() (int64, error)
    RowsAffected() (int64, error)
}

type NamedArg struct {
    Name  string
    Value interface{}
}

func Insert(ctx context.Context, db *sql.DB) {
    _, err := db.ExecContext(
        ctx,
        "INSERT INTO users(name) VALUES(@name)",
        sql.Named("name", "Amos Burton"),
    )
    if err != nil {
        log.Fatal(err)
    }
}

type NullString struct {
    String string
    Valid  bool
}

func Insert(ctx context.Context, db *sql.DB, name interface{}) {
    _, err := db.ExecContext(
        ctx,
        "INSERT INTO users(name) VALUES(@name)",
        sql.Named("name", name),
    )
    if err != nil {
        log.Fatal(err)
    }
}

func DoStuff(ctx context.Context, db *sql.DB) {
    // Nulls
    Insert(ctx, db, nil)
    Insert(ctx, db, sql.NullString{})

    // Values
    Insert(ctx, db, "The Shrike")
    Insert(ctx, db, sql.NullString{String: "The Shrike", Valid: true})
}

func Results(ctx context.Context, db *sql.DB) {
    rows, err := db.QueryContext(ctx, "SELECT name FROM users WHERE id = $1", 1)
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    for rows.Next() {
        var s sql.NullString
        if err := rows.Scan(&s); err != nil {
            log.Fatal(err)
        }

        if s.Valid {
            //
        } else {
            //
        }
    }
}

func Conn(ctx context.Context, db *sql.DB) {
    c, err := db.Conn(ctx)
    if err != nil {
        log.Fatal(err)
    }
    defer c.Close()

    _ = c.PingContext(ctx)
}

func Begin(ctx context.Context, db *sql.DB) {
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        log.Fatal(err)
    }
    defer tx.Rollback()

    _, err = tx.ExecContext(ctx, `UPDATE users SET name = "Tyador Borlú" WHERE id = 1`)
    if err != nil {
        log.Println(err)
        return
    }

    if err = tx.Commit(); err != nil {
        log.Println(err)
        return
    }
}

func Prepare(ctx context.Context, db *sql.DB) {
    stmt, err := db.PrepareContext(ctx, "SELECT name FROM users WHERE id = $1")
    if err != nil {
        log.Fatal(err)
    }
    defer stmt.Close()

    for i := 1; ; i++ {
        var name string
        if err = stmt.QueryRowContext(ctx, i).Scan(&name); err != nil {
            log.Fatal(err)
        }

        log.Println(name)
    }
}

type DBStats struct {
    MaxOpenConnections int // Maximum number of open connections to the database; added in Go 1.11

    // Pool Status
    OpenConnections int // The number of established connections both in use and idle.
    InUse           int // The number of connections currently in use; added in Go 1.11
    Idle            int // The number of idle connections; added in Go 1.11

    // Counters
    WaitCount         int64         // The total number of connections waited for; added in Go 1.11
    WaitDuration      time.Duration // The total time blocked waiting for a new connection; added in Go 1.11
    MaxIdleClosed     int64         // The total number of connections closed due to SetMaxIdleConns; added in Go 1.11
    MaxIdleTimeClosed int64         // The total number of connections closed due to SetConnMaxIdleTime; added in Go 1.15
    MaxLifetimeClosed int64         // The total number of connections closed due to SetConnMaxLifetime; added in Go 1.11
}

func NoContext(ctx context.Context, db *sql.DB) {
    // У Conn() нет версии без контекста
    c, err := db.Conn(ctx)
    if err != nil {
        log.Fatal(err)
    }
    defer c.Close()

    // Потенциально вечный Ping
    _ = db.Ping()
}

func RowsExhaust(ctx context.Context, db *sql.DB) {
    rows, err := db.QueryContext(ctx, "SELECT id, name FROM users")
    if err != nil {
        log.Fatal(err)
    }

    if rows.Next() {
        var id int
        var name string
        if err := rows.Scan(&id, &name); err != nil {
            log.Fatal(err)
        }

        log.Println(id, name)
    }
}

func ConnExhaust(ctx context.Context, db *sql.DB) {
    c, err := db.Conn(ctx)
    if err != nil {
        log.Fatal(err)
    }

    _ = c.PingContext(ctx)
}

func TxExhaust(ctx context.Context, db *sql.DB) {
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        log.Println(err)
        return
    }

    _, err = tx.ExecContext(ctx, `UPDATE users SET name = "Surl/Tesh-echer" WHERE id = 1`)
    if err != nil {
        log.Println(err)
        return
    }

    if err = tx.Commit(); err != nil {
        log.Println(err)
    }
}

func TxDeadlock(ctx context.Context, db *sql.DB) {
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        log.Fatal(err)
    }
    defer tx.Rollback()

    _, _ = db.QueryContext(ctx, "SELECT id, name FROM users")
    _, _ = db.QueryContext(ctx, "SELECT id, name FROM users")
}

func QueryDeadlock(ctx context.Context, db *sql.DB) {
    rows, _ := db.QueryContext(ctx, "SELECT id, name FROM users")
    defer rows.Close()

    for rows.Next() {
        var id int
        var name string
        _ = rows.Scan(&id, &name)

        rowsAddrs, _ := db.QueryContext(
            ctx,
            "SELECT address FROM addresses WHERE user_id = $1",
            id,
        )
        defer rowsAddrs.Close()

        var addr string
        _ = rowsAddrs.Scan(&addr)

        log.Println(id, name, addr)
    }
}

func QueryDeadlockFixOne(ctx context.Context, db *sql.DB) {
    type Res struct {
        ID   int
        Name string
        Addr string
    }
    var values []Res
    rows, _ := db.QueryContext(ctx, "SELECT id, name FROM users")

    for rows.Next() {
        var res Res
        _ = rows.Scan(&res.ID, &res.Name)
        values = append(values, res)
    }
    rows.Close()

    for _, v := range values {
        _ = db.QueryRowContext(
            ctx, "SELECT address FROM addresses WHERE user_id = $1", v.ID,
        ).Scan(&v.Addr)
        log.Println(v)
    }
}

func QueryDeadlockFixTwo(ctx context.Context, db *sql.DB) {
    rows, _ := db.QueryContext(
        ctx,
        "SELECT u.id, u.name, a.address FROM users AS u, addresses as a WHERE u.id == a.user_id",
    )
    defer rows.Close()

    for rows.Next() {
        var id int
        var name string
        var addr string
        _ = rows.Scan(&id, &name, &addr)
        log.Println(id, name, addr)
    }
}

package sqlx

import (
    "log"

    _ "github.com/jackc/pgx/v5/stdlib"
    "github.com/jmoiron/sqlx"
)

func Open() {
    db, err := sqlx.Open("pgx", "postgres://pgx_md5:secret@localhost:5432/pgx_test")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()
}

func (r *Rows) StructScan(dest interface{}) error

func (r *Row) StructScan(dest interface{}) error

func StructScan(rows rowsi, dest interface{}) error

type rowsi interface {
    Close() error
    Columns() ([]string, error)
    Err() error
    Next() bool
    Scan(...interface{}) error
}

func Example(ctx context.Context, db *sqlx.DB) {
    rows, err := db.QueryContext(ctx, "SELECT id, name FROM users WHERE id = $1", 1)
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    for rows.Next() {
        var value struct {
            ID   int    `db:"id"`
            Name string `db:"name"`
        }
        if err := sqlx.StructScan(rows, &value); err != nil {
            log.Fatal(err)
        }

        log.Println(value)
    }

    if err = rows.Err(); err != nil {
        log.Fatal(err)
    }
}

func Insert(ctx context.Context, db *sqlx.DB) {
    _, err := db.NamedExecContext(
        ctx,
        "INSERT INTO users(name) VALUES(:name)",
        map[string]interface{}{
            "name": "Jukka Sarasti",
        },
    )
    if err != nil {
        log.Fatal(err)
    }
}

func Open() {
    dbFoo, _ := sql.Open("pgx", "host=foo")
    dbBar, _ := sql.Open("pgx", "host=bar")
    cluster, err := hasql.NewCluster(
        []hasql.Node{hasql.NewNode("foo", dbFoo), hasql.NewNode("bar", dbBar)},
        checkers.PostgreSQL,
    )
    if err != nil {
        log.Fatal(err)
    }

    node := cluster.Primary()
    if err == nil {
        log.Fatal(err)
    }

    log.Println("Node address", node.Addr())

    ctx, cancel := context.WithTimeout(context.Background(), time.Second)
    defer cancel()
    if err = node.DB().PingContext(ctx); err != nil {
        log.Fatal(err)
    }
}

func Example(ctx context.Context) {
    db, _ := sql.Open("clickhouse", "tcp://127.0.0.1:9000?debug=true")
    defer db.Close()

    // Начало батча
    tx, _ := db.BeginTx(ctx, nil)
    defer tx.Rollback()

    // Описание батча
    stmt, _ := tx.PrepareContext(ctx, "INSERT INTO example (id) VALUES (?)")
    defer stmt.Close()

    // Добавление данных
    for i := 0; i < 100; i++ {
        _, _ = stmt.ExecContext(ctx, i)
    }

    // Отправка батча в ClickHouse
    _ = tx.Commit()
}

func Example(ctx context.Context) {
    rdb := redis.NewUniversalClient(&redis.UniversalOptions{
        MasterName: "master",
        Addrs:      []string{":26379"},
    })
    defer rdb.Close()

    if err := rdb.Ping(ctx); err != nil {
        log.Fatal(err)
    }

    if err := rdb.Set(ctx, "key", "value", time.Hour).Err(); err != nil {
        log.Fatal(err)
    }

    value, err := rdb.Get(ctx, "key").Result()
    if err != nil {
        log.Fatal(err)
    }

    log.Println(value)
}