PostgreSQL Implementation and Stub in GO

 

In this post we will display interface implementation and stub for PostgreSQL.

Interface

We include a query and a execute statement functions.

type PostgresqlApi interface {
    QueryRowsRw(
        destination any,
        sql string,
        args ...any,
    )

    ExecuteStatement(
        sql string,
        args ...any,
    )
}

Implementation

For PostgreSQL cluster we have 2 types of connections: First a connection to the read-write node, and second a connection to the replica of this node with is the read-only connection. To reduce the load, we can use the read-only connection if we can stand a short delay of non up to date data due to sync delay from the master node to the replica.

type PostgresqlImpl struct {
    rw      *pgxpool.Pool
    ro      *pgxpool.Pool
    context context.Context
}

func createConnection(
    connectionString string,
) *pgxpool.Pool {

    ctx, cancel := context.WithTimeout(
        context.Background(),
        Config.PostgresqlConnectionTimeout,
    )

    defer cancel()

    log.Info(
        "postgres connection string: %v",
        connectionString,
    )

    config, err := pgxpool.ParseConfig(connectionString)
    kiterr.RaiseIfError(err)

    config.ConnConfig.User = Config.PostgresqlUser
    config.ConnConfig.Password = Config.PostgresqlPassword

    log.V5(
        "postgres user: %v",
        config.ConnConfig.User,
    )

    log.V5(
        "postgres password: %v",
        config.ConnConfig.Password,
    )

    pool, err := pgxpool.NewWithConfig(ctx, config)

    if err != nil {

        kiterr.RaiseError(
            "connection to %v failed: %v",
            connectionString,
            err,
        )
    }

    return pool
}

func ProducePostgresqlImpl() *PostgresqlImpl {

    return &PostgresqlImpl{
        rw:      createConnection(Config.PostgresqlRwUrl),
        ro:      createConnection(Config.PostgresqlRoUrl),
        context: context.Background(),
    }
}

func (p *PostgresqlImpl) Close() {
    p.rw.Close()
    p.ro.Close()
}

For the query implementation we automatically convert the results to a struct with annotations. Example of usage is below.

type StepRow struct {
    SessionId   string `db:"session_id"`
    StepId      string `db:"step_id"`
    StepDetails string `db:"step_details"`
}

func LoadAllSteps(
    postgresApi postgres.PostgresqlApi,
) []*StepRow {

    var steps []*StepRow

    sql := `
SELECT
    session_id,
    step_id,
    step_details
FROM steps
`

    postgresApi.QueryRowsRw(&steps, sql)

    return steps
}

Stub

We running tests we do want to access an external process such as PostgreSQL. This is where SQLite power is shown. The stub uses in-memory SQLite database who supports the same mainstream SQL syntax as PostgreSQL. This is extremely powerful testing tool.

Here again we have the convert of the results into a struct.

import (
    "database/sql"
    "reflect"

    _ "github.com/mattn/go-sqlite3"
)

type PostgresqlStub struct {
    LogAction bool
    logger    *log.PrefixLogger
    db        *sql.DB
}

func ProducePostgresqlStub() *PostgresqlStub {

    db, err := sql.Open(
        "sqlite3",
        ":memory:",
    )

    kiterr.RaiseIfError(err)

    return &PostgresqlStub{
        LogAction: false,
        logger:    log.ProducePrefixLogger(
            "postgres stub ",
        ),
        db: db,
    }
}

Final Note
We have create a PostgreSQL wrapper and a stub. This hides the implementation details for the using code, and enables us to run both production code and tests code using the interface.