Postgres

with GORM

Working with Postgres in Golang is an important topic specially in the companies that have lots of data to model. Postgres support composite types with makes thing harder to work with. I prefer to use gorm but in case of these composite types you need to use pq.

To use arrays in the query:

db.Where("tags @> ?", pq.Array([]string{"t1"}))

The above query, check that all the tags in the given argument are exists in tags field.

Libraries

jackc/pgx: pgx is a pure Go driver and toolkit for PostgreSQL.
- The pgx driver is a low-level, high performance interface that exposes PostgreSQL-specific features such as LISTEN / NOTIFY and COPY. It also includes an adapter for the standard database/sql interface.
lib/pq: # A pure Go postgres driver for Go’s database/sql package
- This package is currently in maintenance mode, which means:
  1. It generally does not accept new features.
  2. It does accept bug fixes and version compatability changes provided by the community.
  3. Maintainers usually do not resolve reported issues.
  4. Community members are encouraged to help each other with reported issues.
- For users that require new features or reliable resolution of reported bugs, we recommend using pgx which is under active development.

pgx

github.com/jackc/pgx/v5/stdlib: Package stdlib is the compatibility layer from pgx to database/sql.
github.com/jackc/pgx/v5/pgxpool: Package pgxpool is a concurrency-safe connection pool for pgx. pgxpool implements a nearly identical interface to pgx connections.

Query arguments are passed directly to the underlying pgx conn so there is no need to implement driver. Valuer if pgx already understands the type.

_, err = db. ExecContext(ctx,
	`insert into t (data) values ($1)`,
	[]int32{1, 2, 3},
)
 
if err != nil {
	log. Fatal(err)
}

The primary way of establishing a connection is with pgxpool.Connect.

pool, err := pgxpool.Connect(context.Background(), os.Getenv("DATABASE_URL"))

The database connection string can be in URL or DSN format. PostgreSQL settings, pgx settings, and pool settings can be specified here. In addition, a config struct can be created by ParseConfig and modified before establishing the connection with ConnectConfig.

config, err := pgxpool.ParseConfig(os.Getenv("DATABASE_URL"))
if err != nil {
    // ...
}
config.AfterConnect = func(ctx context.Context, conn *pgx.Conn) error {
    // do something with every new connection
}
 
pool, err := pgxpool.ConnectConfig(context.Background(), config)

Acquire returns a connection (*Conn) from the Pool:

func (p *pgxpool.Pool) Acquire(ctx context.Context) (*pgxpool.Conn, error)

Hijack assumes ownership of the connection from the pool. Caller is responsible for closing the connection. Hijack will panic if called on an already released or hijacked connection.

func (c *Conn) Hijack() *pgx.Conn

Basics

pgx is connection oriented.
Basic interface mimics database/sql but with context by default (Query, Quer yRow, Exec).

func main () {
	ctx := context.Background()
 
	conn, err := pgx.Connect(ctx, os.Getenv("DATABASE_URL"))
	if err != nil {
		log.Fatal(err)
	}
	defer conn. Close(ctx)
 
	var n int32
 
	if err := conn.QueryRow(ctx, "select $1::int", 42).Scan(&n); err != nil {
		log.Fatal(err)
	}
 
	fmt.Println(n) // => 42

CollectRows is a generic function that takes a generic RowToFunc[T].

rows, _ := conn.Query(ctx, "select generate_series(1, 10)")
numbers, err := pgx.CollectRows(rows, func(row pgx.CollectableRow) (int32, error) {
	var n int32
	err := row.Scan&n)
	return n, err
})
 
if err != nil {
	log.Fatal(err)
}
 
fmt.Println(numbers) // => [1 2 3 4 5 6 7 8 9 10]

RowTo is a simple generic function as well.

rows, - := conn.Query(ctx, "select generate_series(1, 10)")
numbers, err := pgx.CollectRows(rows, pgx.RowTo[int32])
 
if err != nil {
	log. Fatal(err)
}
 
fmt.Println(numbers) // => [1 2 3 4 5 6 7 8 9 10]

struct mapping:

type User struct {
	ID    int32
	Name  string
	Email string
}
 
rows, _ := conn.Query(ctx, "select id, name, email from users")
users, err := pgx.CollectRows(rows, pgx.RowToStructByPos[User])
if err != nil {
	log.Fatal(err)
}
 
for _, user := range users {
	fmt.Println(user)
}
 
// Output:
//
// {1 Matthew matthew@example.com}
// {2 Mark mark@example.com}
// {3 Luke luke@example.com}
// {4 John john@example.com}
// {5 Mary mary@example.com}
// {6 Martha martha@example.com}

Nested Transactions

Postgres doesn’t have nested transactions, but pgx implements them through SAVEPOINT. But nested transactions can be especially useful in tests. It allows the test to be transactional (e.g. rolled back) while still testing transactional behavior.

Batched Queries

Batches are implicitly transactional. If any query in the batch fails the entire batch is rolled back.
There are limits on the number of queries that can be batched together.

🗒️ Parham Notes

Explorer