data.sql

CREATE TABLE pears (id SERIAL PRIMARY KEY, "name" STRING, apple_id INT REFERENCES apples (id));
INSERT INTO pears (name, apple_id) VALUES ('pear test', (SELECT id FROM apples LIMIT 1));

example.go

package main

import (
	"context"
	"fmt"
	"os"

	"github.com/jackc/pgx"
	"github.com/rs/zerolog"
	"github.com/rs/zerolog/log"
)

type PGXLogger struct {
	logger zerolog.Logger
}

func NewPGXLogger(logger zerolog.Logger) *PGXLogger {
	return &PGXLogger{
		logger: logger.With().Str("module", "pgx").Logger(),
	}
}

func (pl *PGXLogger) Log(level pgx.LogLevel, msg string, data map[string]interface{}) {
	var zlevel zerolog.Level
	switch level {
	case pgx.LogLevelNone:
		zlevel = zerolog.NoLevel
	case pgx.LogLevelError:
		zlevel = zerolog.ErrorLevel
	case pgx.LogLevelWarn:
		zlevel = zerolog.WarnLevel
	case pgx.LogLevelInfo:
		zlevel = zerolog.InfoLevel
	case pgx.LogLevelDebug:
		zlevel = zerolog.DebugLevel
	default:
		zlevel = zerolog.DebugLevel
	}

	fmt.Println("%#v", data)

	pgxlog := pl.logger.With().Fields(data).Logger()
	pgxlog.WithLevel(zlevel).Msg(msg)
}

func newTestDB(zlogger zerolog.Logger) (*pgx.ConnPool, error) {
	pgxLogger := NewPGXLogger(zlogger)
	pgxLogLevel := pgx.LogLevelDebug

	connPool, err := pgx.NewConnPool(pgx.ConnPoolConfig{
		MaxConnections: 5,
		AfterConnect:   nil,
		AcquireTimeout: 0,
		ConnConfig: pgx.ConnConfig{
			Host:     "localhost",
			Database: "testdb",
			Port:     26257,
			User:     "root",
			Logger:   pgxLogger,
			LogLevel: pgxLogLevel,
		},
	})
	if err != nil {
		return nil, err
	}

	return connPool, nil
}

type Store struct {
	pool *pgx.ConnPool
}

type Apple struct {
	ID   int64
	Name string
}

type Pear struct {
	ID      int64
	Name    string
	AppleID int64
}

func NewStore(pool *pgx.ConnPool) *Store {
	return &Store{
		pool: pool,
	}
}

func (s *Store) FindPearByName(ctx context.Context, pearName string) (*Pear, error) {
	var (
		id      int64
		name    string
		appleID int64
	)

	query := `SELECT id, name, apple_id FROM pears WHERE name = $1;`
	err := s.pool.QueryRowEx(ctx, query, nil, pearName).Scan(
		&id,
		&name,
		&appleID,
	)
	if err != nil {
		return nil, err
	}

	return &Pear{
		ID:      id,
		Name:    name,
		AppleID: appleID,
	}, nil
}

func (s *Store) FindPearByID(ctx context.Context, pearID int64) (*Pear, error) {
	var (
		id      int64
		name    string
		appleID int64
	)

	query := `SELECT id, name, apple_id FROM pears WHERE id = $1;`
	err := s.pool.QueryRowEx(ctx, query, nil, pearID).Scan(
		&id,
		&name,
		&appleID,
	)
	if err != nil {
		return nil, err
	}

	return &Pear{
		ID:      id,
		Name:    name,
		AppleID: appleID,
	}, nil
}

func main() {
	w := zerolog.ConsoleWriter{
		Out:     os.Stdout,
		NoColor: false,
	}

	zlogger := zerolog.New(w)

	log.Logger = zlogger

	pool, err := newTestDB(zlogger)
	if err != nil {
		panic(err)
	}

	store := NewStore(pool)

	p, err := store.FindPearByName(context.Background(), "pear test")
	if err != nil {
		panic(err)
	}

	p2, err := store.FindPearByID(context.Background(), p.ID)
	if err != nil {
		panic(err)
	}

	fmt.Println("p2 id:", p2.ID)
	fmt.Println("p2 name:", p2.Name)
	fmt.Println("p2 apple_id:", p2.AppleID)

	log.Debug().
		Int64("test_1", p2.AppleID).
		Str("test_2", fmt.Sprintf("%d", p2.AppleID)).
		Msg("this is the test")
}

output.txt

%#v map[network:tcp address:localhost:26257]
<nil> |INFO| Dialing PostgreSQL server address=localhost:26257 module=pgx network=tcp
%#v map[]
<nil> |INFO| connection established module=pgx
%#v map[time:6.872774ms rowCount:43 sql:select t.oid,
        case when nsp.nspname in ('pg_catalog', 'public') then t.typname
                else nsp.nspname||'.'||t.typname
        end
from pg_type t
left join pg_type base_type on t.typelem=base_type.oid
left join pg_namespace nsp on t.typnamespace=nsp.oid
where (
          t.typtype in('b', 'p', 'r', 'e')
          and (base_type.oid is null or base_type.typtype in('b', 'p', 'r'))
        ) args:[]]
6.872774 |INFO| Query args=[] module=pgx rowCount=43 sql="select t.oid,\n\tcase when nsp.nspname in ('pg_catalog', 'public') then t.typname\n\t\telse nsp.nspname||'.'||t.typname\n\tend\nfrom pg_type t\nleft join pg_type base_type on t.typelem=base_type.oid\nleft join pg_namespace nsp on t.typnamespace=nsp.oid\nwhere (\n\t  t.typtype in('b', 'p', 'r', 'e')\n\t  and (base_type.oid is null or base_type.typtype in('b', 'p', 'r'))\n\t)"
%#v map[sql:select t.oid, t.typname
from pg_type t
  join pg_type base_type on t.typelem=base_type.oid
where t.typtype = 'b'
  and base_type.typtype = 'e' args:[] time:4.589571ms rowCount:1]
4.589571 |INFO| Query args=[] module=pgx rowCount=1 sql="select t.oid, t.typname\nfrom pg_type t\n  join pg_type base_type on t.typelem=base_type.oid\nwhere t.typtype = 'b'\n  and base_type.typtype = 'e'"
%#v map[time:92.22566ms rowCount:1 sql:SELECT id, name, apple_id FROM pears WHERE name = $1; args:[pear test]]
92.22566 |INFO| Query args=[pear test] module=pgx rowCount=1 sql="SELECT id, name, apple_id FROM pears WHERE name = $1;"
%#v map[sql:SELECT id, name, apple_id FROM pears WHERE id = $1; args:[333543255570743297] time:1.858072ms rowCount:1]
1.858072 |INFO| Query args=[3.335432555707433e+17] module=pgx rowCount=1 sql="SELECT id, name, apple_id FROM pears WHERE id = $1;"
p2 id: 333543255570743297
p2 name: pear test
p2 apple_id: 333543199465996289
<nil> |DEBU| this is the test test_1=3.335431994659963e+17 test_2=333543199465996289