Introduction

What are you using Go's RDB access library? I will introduce the go-pg/pg I found this time because it was messed up by myself.

This is the first Japanese article on go-pg/pg! !! (Self-examination)

What is go-pg/pg?

As you can guess from the name, it is a DB access library specialized for PostgreSQL. As described in the next section, it was the most feature-rich I've examined.

Document: https://pg.uptrace.dev/ Pkg: https://pkg.go.dev/github.com/go-pg/pg/v10 Git: https://github.com/go-pg/pg

function

before that

It all started when I investigated whether there was a DB library with the following functions.

I want to write SELECT in RawSQL (ORM is hard to read)
In RawSQL I want to bind by variable name
I want to do Insert, Update, Delete with ORM (redundant with SQL)
Insert, Update, Delete want to make Bulk query (multiple records can be processed with one query)

Gorm was also nominated as a rival horse of go-pg/pg due to its multi-functionality. Only go-pg/pg met all of the above conditions.

After that, I will introduce the typical functions and notes that may be a little clogged. If you are interested and want to check the functions comprehensively, please check Document.

Bulk processing

go-pg/pg can be BulkInsert, BulkUpdate, BulkDelete, all.
For example, the Bulk Update query looks like this: It is one query after uniquely identifying the record with PK.
By the way, gorm only supports Bulk Insert. As far as I know, there are no other libraries supported by Bulk Update.

UPDATE "table_objs" AS "table_obj"
SET "str1" = _data."str1", "time1" = _data."time1", "num1" = _data."num1", "created_at" = _data."created_at", "updated_at" = _data."updated_at"
FROM (VALUES
 ('r1k1'::text, 'r1k2'::text, 'testStr1'::text, NULL::timestamptz, '8.88'::numeric(4,2), '2021-01-01 16:36:29.869198+00:00:00'::timestamptz, '2021-01-01 16:36:31.877413+00:00:00'::timestamptz),
 ('r2k1'::text, 'r2k2'::text, 'testStr2'::text, NULL::timestamptz, '0.12'::numeric(4,2), '2021-01-01 16:36:29.869198+00:00:00'::timestamptz, '2021-01-01 16:36:31.877414+00:00:00'::timestamptz)
) AS _data("key1", "key2", "str1", "time1", "num1", "created_at", "updated_at") 
WHERE "table_obj"."key1" = _data."key1" AND "table_obj"."key2" = _data."key2"

Bind by variable name

It is possible by writing in the format of ? Variable name. The example of This Query method is easy to understand.

Retry timeout

Retry and query timeout at the time of query error can be set in Option to set when connecting to DB. The error to be retried is defined in Around this.

I understand that the ability to finely set retries and timeouts as options was made possible by narrowing down the RDBMS to one.

It also supports Context.

Tracing

I haven't tried it yet, but it seems that you can link with Open Telemetry with a query hook just by writing something like db.AddQueryHook (pgotel.TracingHook {}). It is described in Documents around here.

Stream processing

You can process SELECT results in a stream by using ForEach. The following is an excerpt from the README.

ForEach that calls a function for each row returned by the query without loading all rows into the memory.

The example sentence is described below.

err := pgdb.Model((*Book)(nil)).
	OrderExpr("id ASC").
	ForEach(func(b *Book) error {
		fmt.Println(b)
		return nil
	})
if err != nil {
	panic(err)
}
//Book<Id=1 Title="book 1">
//Book<Id=2 Title="book 2">
//Book<Id=3 Title="book 3">

There was a method called FindFatches in gorm, but FindFatches used OFFSET and LIMIT to throw multiple queries that could get a certain number of queries, so it's similar to For Each in go-pg/pg. It's not.

If you process in the loop of for rows.Next () {...}, you can also stream process with other DB access libraries.

Postgres specialization

I haven't checked it in detail, but it seems that it also supports the following Postgres-specific functions. array type, hstore type, ON CONFLICT clause, COPY FROM/TO syntax

important point

Tags attached to Struct

PK tags cannot be detected by " pk " and require , such as ", pk ".

I was quite addicted to it here. Other tag information is collected in here. It seems that the table name can also be specified with a tag.

type TableObj struct {
	Key1  string `pg:",pk"`
	Key2  string `pg:",pk"`
	Str1  string
	Time1 time.Time
	Num1  string `pg:"type:numeric(4,2)"`
}

As an aside, if you want to handle decimal values accurately, I think it is better to handle the go side as a string and the DB side as a numeric.

How to output a processing query

Execution query is output by putting the following pgdebug.DebugHook in AddQueryHook.

It seems to be supported from v10

import "github.com/go-pg/pg/extra/pgdebug"

//....
	db := pg.Connect(&pg.Options{...})
	defer db.Close()

	db.AddQueryHook(pgdebug.DebugHook{
		Verbose:   true,
		EmptyLine: true,
	})

If you set it to Verbose: false, it will change to output a query only when an error occurs. However, as far as I confirmed on 2021/1/1, the query output when an error occurred is not working well. I mentioned Issue, so I hope it will be improved. ..

If you set the following Query Hook instead, it will be output, so please refer to it.

type MyDebugHook struct {
	// pgdebug.Same content as Debug Hook
	Verbose   bool
	EmptyLine bool
}

func (h MyDebugHook) BeforeQuery(ctx context.Context, evt *pg.QueryEvent) (context.Context, error) {
	// pgdebug.Same content as Debug Hook
	q, err := evt.FormattedQuery()
	if err != nil {
		return nil, err
	}

	if evt.Err != nil {
		fmt.Printf("%s executing a query:\n%s\n", evt.Err, q)
	} else if h.Verbose {
		if h.EmptyLine {
			fmt.Println()
		}
		fmt.Println(string(q))
	}

	return ctx, nil
}

func (h MyDebugHook) AfterQuery(ctx context.Context, evt *pg.QueryEvent) error {
	//Only here pgdebug.The content is different from Debug Hook
	if evt.Err != nil {
		q, _ := evt.FormattedQuery()
		fmt.Printf("%s executing a query:\n%s\n", evt.Err, q)
	}
	return nil
}
/*How to use
	db.AddQueryHook(MyDebugHook{
		Verbose:   false,
		EmptyLine: true,
	})
*/

Tightening

I haven't put it into operation yet, so I don't know how it feels, but it looks pretty good. We would appreciate it if you could refer to it if you are considering new development with the combination of Postgres and Golang. I hope this will be an opportunity to increase the number of Japanese articles on go-pg/pg!

Introduction of Go's RDB access library (go-pg/pg)