Repository based on the pgx client

July 12, 2022

As we know, the pgx package, in addition to the database/sql compatible driver, also has its own DB client, which is faster and has more features, so it is even more recommended than the classic driver.

Let’s add a repository based on this client. Create a new file repository_postgresql_pgx.go in the website package. Then copy its contents, and let’s trace how such a repository differs from the one we previously created.

To use the pgxpool package, we need to add it to our project:
go get github.com/jackc/pgx/v4/pgxpool

This repository is very similar to our classic database/sql based repository. It differs actually only in two things:

Instead of using the sql.DB to connect with the database, we use a pgxpool.Pool object here. Like sql.DB, it represents a pool of connections to a database and is concurrency safe.
All the database methods we use are practically the same as in database/sql based repository, except that you always have to pass context.Context and there is no version without context. So we have the Query() method instead of QueryContext(), Exec() instead of ExecContext(), etc.

Using pgx client is therefore not significantly different from using a database/sql compatible driver. The authors of pgx recommend this way of connecting to PostgreSQL but explain in more detail when to choose which version here.

website/repository_postgresql_pgx.go

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
package website

import (
    "context"
    "database/sql"
    "errors"

    "github.com/jackc/pgconn"
    "github.com/jackc/pgx/v4/pgxpool"
)

type PostgreSQLPGXRepository struct {
    db *pgxpool.Pool
}

func NewPostgreSQLPGXRepository(db *pgxpool.Pool) *PostgreSQLPGXRepository {
    return &PostgreSQLPGXRepository{
        db: db,
    }
}

func (r *PostgreSQLPGXRepository) Migrate(ctx context.Context) error {
    query := `
    CREATE TABLE IF NOT EXISTS websites(
        id SERIAL PRIMARY KEY,
        name TEXT NOT NULL UNIQUE,
        url TEXT NOT NULL,
        rank INT NOT NULL
    );
    `

    _, err := r.db.Exec(ctx, query)
    return err
}

func (r *PostgreSQLPGXRepository) Create(ctx context.Context, website Website) (*Website, error) {
    var id int64
    err := r.db.QueryRow(ctx, "INSERT INTO websites(name, url, rank) values($1, $2, $3) RETURNING id", website.Name, website.URL, website.Rank).Scan(&id)
    if err != nil {
        var pgxError *pgconn.PgError
        if errors.As(err, &pgxError) {
            if pgxError.Code == "23505" {
                return nil, ErrDuplicate
            }
        }
        return nil, err
    }
    website.ID = id

    return &website, nil
}

func (r *PostgreSQLPGXRepository) All(ctx context.Context) ([]Website, error) {
    rows, err := r.db.Query(ctx, "SELECT * FROM websites")
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var all []Website
    for rows.Next() {
        var website Website
        if err := rows.Scan(&website.ID, &website.Name, &website.URL, &website.Rank); err != nil {
            return nil, err
        }
        all = append(all, website)
    }
    return all, nil
}

func (r *PostgreSQLPGXRepository) GetByName(ctx context.Context, name string) (*Website, error) {
    row := r.db.QueryRow(ctx, "SELECT * FROM websites WHERE name = $1", name)

    var website Website
    if err := row.Scan(&website.ID, &website.Name, &website.URL, &website.Rank); err != nil {
        if errors.Is(err, sql.ErrNoRows) {
            return nil, ErrNotExist
        }
        return nil, err
    }
    return &website, nil
}

func (r *PostgreSQLPGXRepository) Update(ctx context.Context, id int64, updated Website) (*Website, error) {
    res, err := r.db.Exec(ctx, "UPDATE websites SET name = $1, url = $2, rank = $3 WHERE id = $4", updated.Name, updated.URL, updated.Rank, id)
    if err != nil {
        var pgxError *pgconn.PgError
        if errors.As(err, &pgxError) {
            if pgxError.Code == "23505" {
                return nil, ErrDuplicate
            }
        }
        return nil, err
    }

    rowsAffected := res.RowsAffected()
    if rowsAffected == 0 {
        return nil, ErrUpdateFailed
    }

    return &updated, nil
}

func (r *PostgreSQLPGXRepository) Delete(ctx context.Context, id int64) error {
    res, err := r.db.Exec(ctx, "DELETE FROM websites WHERE id = $1", id)
    if err != nil {
        return err
    }

    rowsAffected := res.RowsAffected()
    if rowsAffected == 0 {
        return ErrDeleteFailed
    }

    return err
}

Table of Contents