go / postgres

updated February 21, 2026

I use PostgreSQL with Go when I need a robust relational database with strong concurrency and full SQL support.

Setup

I use github.com/jackc/pgx/v5 as a database driver. It has better performance than lib/pq and is more actively maintained.

go mod init server
go get github.com/jackc/pgx/v5

Use pgxpool for connection pooling:

import (
	"context"
	"log"
	"net/http"

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

// DB wraps a PostgreSQL connection pool
type DB struct {
	*pgxpool.Pool
}

func NewDB(databaseURL string) (*DB, error) {
	pool, err := pgxpool.New(context.Background(), databaseURL)
	if err != nil {
		return nil, err
	}
	if err := pool.Ping(context.Background()); err != nil {
		pool.Close()
		return nil, err
	}
	return &DB{Pool: pool}, nil
}

type Server struct {
	db *DB
}

func (s *Server) health(w http.ResponseWriter, r *http.Request) {
	if err := s.db.Ping(r.Context()); err != nil {
		http.Error(w, "Database error", 500)
		return
	}
	w.Write([]byte("OK"))
}

func main() {
	db, err := NewDB("postgres://app:secret@localhost/app?sslmode=disable")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	server := &Server{db: db}
	http.HandleFunc("/health", server.health)
	log.Fatal(http.ListenAndServe(":8080", nil))
}

Key points:

• pgxpool: Built-in connection pooling with sensible defaults. Pool size is automatically configured based on max_connections.
• URL format: Use postgres://user:pass@host/dbname?sslmode=disable.
• Context everywhere: pgx methods take context.Context for cancellation.

Inserts with RETURNING

Use RETURNING to get generated values:

func createUser(ctx context.Context, db *DB, email string) (int64, error) {
	var id int64
	err := db.QueryRow(ctx,
		"INSERT INTO users (email) VALUES ($1) RETURNING id",
		email,
	).Scan(&id)
	return id, err
}

Use $1, $2, etc. for parameter placeholders (not ?).

Error handling

Extract structured error information from pgconn.PgError:

import (
	"errors"

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

func isUniqueViolation(err error) bool {
	var pgErr *pgconn.PgError
	return errors.As(err, &pgErr) && pgErr.Code == "23505"
}

func isForeignKeyViolation(err error) bool {
	var pgErr *pgconn.PgError
	return errors.As(err, &pgErr) && pgErr.Code == "23503"
}

Common error codes:

• 23505: unique_violation
• 23503: foreign_key_violation
• 23502: not_null_violation
• 23514: check_violation
• 40001: serialization_failure
• 40P01: deadlock_detected
• 55P03: lock_not_available

For retrying serialization failures and deadlocks, see retrytx.

User-facing errors

Translate database errors into user-friendly messages:

func createUser(ctx context.Context, db *DB, email string) error {
	_, err := db.Exec(ctx, "INSERT INTO users (email) VALUES ($1)", email)
	if err != nil {
		if isUniqueViolation(err) {
			return vizerror.WrapWithMessage(err,
				"An account with that email already exists")
		}
		return vizerror.WrapWithMessage(err,
			"Unable to create account. Please try again.")
	}
	return nil
}

See vizerror for the visible error pattern.

Testing

Use a test database with automatic cleanup:

import (
	"context"
	"net/http"
	"net/http/httptest"
	"testing"
)

func initTestDB(t *testing.T) *DB {
	t.Helper()

	db, err := NewDB("postgres://app:secret@localhost/app_test?sslmode=disable")
	if err != nil {
		t.Fatalf("Failed to connect: %v", err)
	}
	t.Cleanup(func() { db.Close() })

	// Reset test data
	_, err = db.Exec(context.Background(), "TRUNCATE users RESTART IDENTITY CASCADE")
	if err != nil {
		t.Fatalf("Failed to reset database: %v", err)
	}

	return db
}

func TestHealthCheck(t *testing.T) {
	db := initTestDB(t)
	server := &Server{db: db}

	req, err := http.NewRequest("GET", "/health", nil)
	if err != nil {
		t.Fatalf("Failed to create request: %v", err)
	}

	rr := httptest.NewRecorder()
	http.HandlerFunc(server.health).ServeHTTP(rr, req)

	if rr.Code != 200 {
		t.Errorf("Expected status 200, got %d", rr.Code)
	}

	if rr.Body.String() != "OK" {
		t.Errorf("Expected body 'OK', got '%s'", rr.Body.String())
	}
}

Key differences from SQLite testing:

• Use a dedicated test database, not :memory:
• Use TRUNCATE ... CASCADE to reset data between tests
• RESTART IDENTITY resets auto-increment sequences

When to use Postgres over SQLite

Use Postgres when you need:

• Multiple application instances connecting to one database
• Advanced SQL features (CTEs, window functions, JSONB, full-text search)
• Concurrent writes from many users
• Replication or high availability

Use SQLite when you want:

• Single binary deployment with embedded database
• Zero external dependencies
• Single-user or low-concurrency applications

← All articles