Go: pgx

Environment

export DATABASE_URL="postgres://user:password@host:6432/db?sslmode=require"
export DATABASE_DIRECT_URL="postgres://user:password@host:5432/db?sslmode=require"

pgxpool

package main

import (
	"context"
	"os"

	"github.com/jackc/pgx/v5/pgxpool"
)

func main() {
	config, err := pgxpool.ParseConfig(os.Getenv("DATABASE_URL"))
	if err != nil {
		panic(err)
	}
	config.MaxConns = 10

	pool, err := pgxpool.NewWithConfig(context.Background(), config)
	if err != nil {
		panic(err)
	}
	defer pool.Close()

	var now string
	if err := pool.QueryRow(context.Background(), "SELECT now()::text").Scan(&now); err != nil {
		panic(err)
	}
}

Pooled URL and statement caching

pgx caches prepared statements by default, which breaks behind transaction pooling. On the pooled URL, switch to simple protocol:

config.ConnConfig.DefaultQueryExecMode = pgx.QueryExecModeSimpleProtocol

Or add default_query_exec_mode=simple_protocol to the URL. On the direct URL the default cache mode is fine.

Migrations

golang-migrate, goose, atlas, and friends should use DATABASE_DIRECT_URL — they rely on advisory locks and DDL transactions.

Pitfalls

prepared statement "stmtcache_..." already exists — you are using the default exec mode through the pooler; see above.
MaxConns per instance: with multiple replicas, total client connections = replicas × MaxConns. Keep that under the plan budget or stay on the pooled URL.
LISTEN/NOTIFY requires the direct URL.