Connecting Go with PostgreSQL: queries, repositories and context

In Spring you have JPA, in Python SQLAlchemy. In Go, you write SQL. And honestly, for most backend services, that’s enough. You don’t need an ORM that generates magic queries, nor a DSL that abstracts the database until it’s unrecognizable. You need to execute queries, map results to structs and manage connections efficiently. Go gives you exactly that.

The philosophy is the same as in the rest of the language: explicit over implicit, simple over magical. You’ll write more lines than with JPA, but you’ll understand exactly which query executes, when a connection opens and what happens when something fails. And when at three in the morning your service returns 500 errors because the connection pool is exhausted, you’ll be grateful for having that control.

The key lies in something many projects ignore: the database shouldn’t leak throughout your entire application. Go lets you keep data access simple without turning it into bloated architecture. Neither JPA with its 47 annotations, nor SQL scattered across all the handlers. There’s a middle ground, and it’s quite comfortable.

If you’re not yet clear on the general structure of a Go project, check out project structure before continuing.

database/sql: Go’s standard interface

Go includes the database/sql package in its standard library. It’s not a driver, but an interface. It defines how to interact with relational databases, but doesn’t know how to speak to any specific database. For that you need a driver that implements that interface.

The model is similar to JDBC in Java, but lighter. database/sql gives you:

• An integrated and configurable connection pool.
• Prepared statements.
• Transactions.
• Scanning results into Go variables.

What it doesn’t give you:

• Any automatic mapping to structs.
• Any migrations.
• Any query generation.

A basic connection with database/sql looks like this:

package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/lib/pq" // PostgreSQL driver
)

func main() {
    connStr := "host=localhost port=5432 user=app password=secret dbname=mydb sslmode=disable"
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    if err := db.Ping(); err != nil {
        log.Fatal("cannot connect to PostgreSQL:", err)
    }

    fmt.Println("connected")
}

A detail that confuses many people: sql.Open doesn’t open any connection. It only validates the arguments and returns a *sql.DB, which is the pool. The first real connection is established when you execute the first query or call Ping(). Always do Ping() after Open() to verify that the database is accessible.

The import with _ (blank import) is necessary because the lib/pq driver registers automatically in database/sql through its init() function. You don’t use it directly in your code, but it has to be imported for the registration to happen.

That said, lib/pq is in maintenance mode. Its own README tells you to use pgx. Let’s follow that advice.

pgx: the PostgreSQL driver you should use

pgx is the most complete and active PostgreSQL driver for Go. It has two usage modes:

1. As a database/sql driver: you register it as a driver and use the standard interface.
2. With its native API: you skip database/sql and use pgx’s API directly, which is richer and more efficient.

My recommendation: use the native pgx API. The database/sql interface is generic and forces certain limitations on you (such as not being able to use native PostgreSQL types directly). The native pgx API gives you full access to PostgreSQL features without losing simplicity.

Install pgx:

go get github.com/jackc/pgx/v5

Basic connection with the native API:

package main

import (
    "context"
    "fmt"
    "log"
    "os"

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

func main() {
    ctx := context.Background()

    pool, err := pgxpool.New(ctx, os.Getenv("DATABASE_URL"))
    if err != nil {
        log.Fatalf("cannot create pool: %v", err)
    }
    defer pool.Close()

    if err := pool.Ping(ctx); err != nil {
        log.Fatalf("cannot connect to PostgreSQL: %v", err)
    }

    fmt.Println("connected with pgx")
}

The DATABASE_URL follows the standard PostgreSQL format:

postgres://user:password@localhost:5432/mydb?sslmode=disable

pgx brings several advantages over database/sql with lib/pq:

• Performance: uses the PostgreSQL binary protocol, not the text one. Less parsing, fewer allocations.
• Native types: direct support for arrays, JSON, hstore, inet, UUID, and all PostgreSQL types without manual conversions.
• Batch queries: you can send multiple queries in a single round-trip.
• COPY protocol: for bulk data loading.
• LISTEN/NOTIFY: support for PostgreSQL notifications.
• pgxpool: a more configurable connection pool than the one in database/sql.

If you need to maintain compatibility with database/sql (for example, because you use a library that requires it), pgx works as a registerable driver:

import (
    "database/sql"

    _ "github.com/jackc/pgx/v5/stdlib"
)

func main() {
    db, err := sql.Open("pgx", "postgres://user:password@localhost:5432/mydb")
    // ...
}

But if you’re starting a new project and your database is PostgreSQL, the native API is the way to go.

Connection pool configuration

A poorly configured pool is one of the most common sources of problems in production. Exhausted connections, unexplained timeouts, silent deadlocks. pgxpool gives you granular control over all of this.

config, err := pgxpool.ParseConfig(os.Getenv("DATABASE_URL"))
if err != nil {
    log.Fatalf("error parsing config: %v", err)
}

config.MaxConns = 25
config.MinConns = 5
config.MaxConnLifetime = 30 * time.Minute
config.MaxConnIdleTime = 5 * time.Minute
config.HealthCheckPeriod = 1 * time.Minute

pool, err := pgxpool.NewWithConfig(ctx, config)

Each parameter matters:

• MaxConns: the maximum number of simultaneous connections. Don’t set it too high. PostgreSQL by default accepts 100 connections, and if you have 4 instances of your service with MaxConns=50 each, you’re already exceeding the limit. A good starting point is (server CPUs * 2) + 1 for the total PostgreSQL connections, distributed across instances.
• MinConns: connections kept open even when idle. Reduces latency on the first request after a period of inactivity.
• MaxConnLifetime: maximum time a connection can exist before being closed and recreated. Useful for DNS changes to propagate and to avoid zombie connections.
• MaxConnIdleTime: maximum time a connection can be idle before being closed. Frees resources when traffic drops.
• HealthCheckPeriod: how often idle connections are checked. If a connection has broken (network, PostgreSQL restart), the health check detects and removes it from the pool.

A classic mistake: not configuring MaxConnLifetime. Without it, a connection can live indefinitely. If you change the IP of your PostgreSQL server (due to failover, for example), old connections still point to the old IP and fail. With MaxConnLifetime, they’re recycled automatically.

For a typical service in production, something like this works well:

config.MaxConns = 20
config.MinConns = 3
config.MaxConnLifetime = 1 * time.Hour
config.MaxConnIdleTime = 10 * time.Minute
config.HealthCheckPeriod = 30 * time.Second

If you use database/sql instead of pgxpool, the equivalent configuration is:

db.SetMaxOpenConns(20)
db.SetMaxIdleConns(3)
db.SetConnMaxLifetime(1 * time.Hour)
db.SetConnMaxIdleTime(10 * time.Minute)

Basic CRUD operations with pgx

Let’s get to it. Consider a tasks table:

CREATE TABLE tasks (
    id          BIGSERIAL PRIMARY KEY,
    title       TEXT NOT NULL,
    description TEXT,
    status      TEXT NOT NULL DEFAULT 'pending',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

And its struct in Go:

type Task struct {
    ID          int64
    Title       string
    Description *string // nullable
    Status      string
    CreatedAt   time.Time
    UpdatedAt   time.Time
}

INSERT

func CreateTask(ctx context.Context, pool *pgxpool.Pool, title string, description *string) (int64, error) {
    var id int64
    err := pool.QueryRow(ctx,
        "INSERT INTO tasks (title, description) VALUES ($1, $2) RETURNING id",
        title, description,
    ).Scan(&id)
    if err != nil {
        return 0, fmt.Errorf("creating task: %w", err)
    }
    return id, nil
}

Note: RETURNING id avoids having to do a SELECT after the INSERT. PostgreSQL returns the generated value directly. Always take advantage of this.

SELECT (single row)

func GetTask(ctx context.Context, pool *pgxpool.Pool, id int64) (Task, error) {
    var t Task
    err := pool.QueryRow(ctx,
        "SELECT id, title, description, status, created_at, updated_at FROM tasks WHERE id = $1",
        id,
    ).Scan(&t.ID, &t.Title, &t.Description, &t.Status, &t.CreatedAt, &t.UpdatedAt)
    if err != nil {
        if errors.Is(err, pgx.ErrNoRows) {
            return Task{}, fmt.Errorf("task %d not found: %w", id, err)
        }
        return Task{}, fmt.Errorf("getting task %d: %w", id, err)
    }
    return t, nil
}

pgx.ErrNoRows is the equivalent of sql.ErrNoRows. Always check for it when you expect exactly one row. If you don’t, a SELECT that finds no results returns a generic, unhelpful error.

SELECT (multiple rows)

func ListTasks(ctx context.Context, pool *pgxpool.Pool, status string) ([]Task, error) {
    rows, err := pool.Query(ctx,
        "SELECT id, title, description, status, created_at, updated_at FROM tasks WHERE status = $1 ORDER BY created_at DESC",
        status,
    )
    if err != nil {
        return nil, fmt.Errorf("listing tasks: %w", err)
    }
    defer rows.Close()

    var tasks []Task
    for rows.Next() {
        var t Task
        if err := rows.Scan(&t.ID, &t.Title, &t.Description, &t.Status, &t.CreatedAt, &t.UpdatedAt); err != nil {
            return nil, fmt.Errorf("scanning task: %w", err)
        }
        tasks = append(tasks, t)
    }
    if err := rows.Err(); err != nil {
        return nil, fmt.Errorf("iterating tasks: %w", err)
    }
    return tasks, nil
}

Three important things here:

1. defer rows.Close(): always. If you don’t close the rows, the connection isn’t returned to the pool and you’ll eventually run out of connections.
2. rows.Err(): check it after the loop. rows.Next() can stop iterating due to a network error, not just because the rows ran out. If you don’t check rows.Err(), you silently lose that error.
3. pgx also offers pgx.CollectRows, a cleaner alternative:

func ListTasks(ctx context.Context, pool *pgxpool.Pool, status string) ([]Task, error) {
    rows, err := pool.Query(ctx,
        "SELECT id, title, description, status, created_at, updated_at FROM tasks WHERE status = $1 ORDER BY created_at DESC",
        status,
    )
    if err != nil {
        return nil, fmt.Errorf("listing tasks: %w", err)
    }
    return pgx.CollectRows(rows, pgx.RowToStructByName[Task])
}

pgx.RowToStructByName maps columns to struct fields by name. For it to work, the struct fields need db tags:

type Task struct {
    ID          int64     `db:"id"`
    Title       string    `db:"title"`
    Description *string   `db:"description"`
    Status      string    `db:"status"`
    CreatedAt   time.Time `db:"created_at"`
    UpdatedAt   time.Time `db:"updated_at"`
}

UPDATE

func UpdateTaskStatus(ctx context.Context, pool *pgxpool.Pool, id int64, status string) error {
    result, err := pool.Exec(ctx,
        "UPDATE tasks SET status = $1, updated_at = NOW() WHERE id = $2",
        status, id,
    )
    if err != nil {
        return fmt.Errorf("updating task %d: %w", id, err)
    }
    if result.RowsAffected() == 0 {
        return fmt.Errorf("task %d not found", id)
    }
    return nil
}

Exec returns a pgconn.CommandTag from which you can extract RowsAffected(). Always check it in UPDATEs and DELETEs to confirm the operation affected some row.

DELETE

func DeleteTask(ctx context.Context, pool *pgxpool.Pool, id int64) error {
    result, err := pool.Exec(ctx, "DELETE FROM tasks WHERE id = $1", id)
    if err != nil {
        return fmt.Errorf("deleting task %d: %w", id, err)
    }
    if result.RowsAffected() == 0 {
        return fmt.Errorf("task %d not found", id)
    }
    return nil
}

Context: timeouts and cancellation

Every pgx operation receives a context.Context as its first argument. It’s not optional, it’s not decorative. It’s your primary mechanism for controlling timeouts and cancellation.

If you come from other languages, you might think the timeout is configured in the pool or the driver. In Go, the timeout travels with the request through the context. This has a huge advantage: each operation can have its own timeout, and cancellation propagates automatically.

func GetTaskWithTimeout(pool *pgxpool.Pool, id int64) (Task, error) {
    ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
    defer cancel()

    var t Task
    err := pool.QueryRow(ctx,
        "SELECT id, title, description, status, created_at, updated_at FROM tasks WHERE id = $1",
        id,
    ).Scan(&t.ID, &t.Title, &t.Description, &t.Status, &t.CreatedAt, &t.UpdatedAt)
    return t, err
}

If the query takes more than 3 seconds, pgx cancels the query in PostgreSQL (sends a CancelRequest) and returns a context error. It doesn’t stay blocked waiting indefinitely.

In a REST API, the context normally comes from the HTTP request:

func handleGetTask(pool *pgxpool.Pool) http.HandlerFunc {
    return func(w http.ResponseWriter, r *http.Request) {
        // r.Context() is cancelled automatically if the client closes the connection
        task, err := GetTask(r.Context(), pool, taskID)
        if err != nil {
            // ...
        }
        // ...
    }
}

This means that if a client cancels their HTTP request, the query in PostgreSQL is also cancelled. You don’t waste resources executing queries whose result nobody is going to read.

To understand context in depth, check out context in Go.

A pattern I recommend: if your function doesn’t receive a context from outside, don’t use context.Background() directly. Always add a reasonable timeout:

func (r *TaskRepo) Cleanup() error {
    ctx, cancel := context.WithTimeout(context.Background(), 30*time.Second)
    defer cancel()

    _, err := r.pool.Exec(ctx, "DELETE FROM tasks WHERE status = 'deleted' AND updated_at < NOW() - INTERVAL '30 days'")
    return err
}

If that cleanup query gets stuck due to a lock, in 30 seconds it’s cancelled and your goroutine doesn’t hang forever.

The repository pattern: clean data access

Having SQL queries scattered across HTTP handlers is a direct path to chaos. The repository pattern encapsulates all data access behind a clear interface. It’s not “Enterprise Hexagonal Architecture” — it’s common sense: separating what data you need from how you get it.

// repository.go
type TaskRepository interface {
    Create(ctx context.Context, title string, description *string) (int64, error)
    GetByID(ctx context.Context, id int64) (Task, error)
    List(ctx context.Context, status string) ([]Task, error)
    UpdateStatus(ctx context.Context, id int64, status string) error
    Delete(ctx context.Context, id int64) error
}

The concrete implementation uses pgxpool:

// postgres_repository.go
type PostgresTaskRepository struct {
    pool *pgxpool.Pool
}

func NewPostgresTaskRepository(pool *pgxpool.Pool) *PostgresTaskRepository {
    return &PostgresTaskRepository{pool: pool}
}

func (r *PostgresTaskRepository) Create(ctx context.Context, title string, description *string) (int64, error) {
    var id int64
    err := r.pool.QueryRow(ctx,
        "INSERT INTO tasks (title, description) VALUES ($1, $2) RETURNING id",
        title, description,
    ).Scan(&id)
    if err != nil {
        return 0, fmt.Errorf("creating task: %w", err)
    }
    return id, nil
}

func (r *PostgresTaskRepository) GetByID(ctx context.Context, id int64) (Task, error) {
    var t Task
    err := r.pool.QueryRow(ctx,
        "SELECT id, title, description, status, created_at, updated_at FROM tasks WHERE id = $1",
        id,
    ).Scan(&t.ID, &t.Title, &t.Description, &t.Status, &t.CreatedAt, &t.UpdatedAt)
    if err != nil {
        if errors.Is(err, pgx.ErrNoRows) {
            return Task{}, ErrTaskNotFound
        }
        return Task{}, fmt.Errorf("getting task %d: %w", id, err)
    }
    return t, nil
}

// ... rest of methods

Handlers receive the interface, not the concrete implementation:

type TaskHandler struct {
    repo TaskRepository
}

func (h *TaskHandler) HandleGetTask(w http.ResponseWriter, r *http.Request) {
    task, err := h.repo.GetByID(r.Context(), taskID)
    if errors.Is(err, ErrTaskNotFound) {
        http.Error(w, "task not found", http.StatusNotFound)
        return
    }
    if err != nil {
        http.Error(w, "internal error", http.StatusInternalServerError)
        return
    }
    json.NewEncoder(w).Encode(task)
}

Concrete advantages:

1. Testing: you can mock the repository for handler tests without needing PostgreSQL. Unit tests run fast.
2. Domain errors: the repository translates pgx.ErrNoRows to ErrTaskNotFound. The handler knows nothing about pgx.
3. Changing driver: if one day you need to switch from pgx to something else (unlikely, but possible), you only change the repository implementation.

A mistake I often see: repositories that receive and return DTOs from the handler or API structs. Don’t do that. The repository works with domain models. The conversion between domain models and API DTOs is the responsibility of the handler or an intermediate layer.

For more context on how the repository fits within a complete REST service, check out REST API with Go.

Transactions: begin, commit, rollback

When you need several database operations to be atomic, you use transactions. pgx makes this clean:

func (r *PostgresTaskRepository) CreateWithSubtasks(ctx context.Context, title string, subtasks []string) (int64, error) {
    tx, err := r.pool.Begin(ctx)
    if err != nil {
        return 0, fmt.Errorf("starting transaction: %w", err)
    }
    defer tx.Rollback(ctx) // no-op if commit has already been done

    var taskID int64
    err = tx.QueryRow(ctx,
        "INSERT INTO tasks (title) VALUES ($1) RETURNING id",
        title,
    ).Scan(&taskID)
    if err != nil {
        return 0, fmt.Errorf("creating task: %w", err)
    }

    for _, st := range subtasks {
        _, err = tx.Exec(ctx,
            "INSERT INTO subtasks (task_id, title) VALUES ($1, $2)",
            taskID, st,
        )
        if err != nil {
            return 0, fmt.Errorf("creating subtask: %w", err)
        }
    }

    if err = tx.Commit(ctx); err != nil {
        return 0, fmt.Errorf("commit: %w", err)
    }
    return taskID, nil
}

The defer tx.Rollback(ctx) is a safety net. If the function returns with an error before reaching Commit, the rollback executes automatically. If commit has already been done, the rollback is a no-op. It’s an idiomatic Go pattern and you should always use it.

pgx.BeginTxFunc: transactions with a callback

pgx offers an alternative that reduces boilerplate:

func (r *PostgresTaskRepository) CreateWithSubtasks(ctx context.Context, title string, subtasks []string) (int64, error) {
    var taskID int64

    err := pgx.BeginTxFunc(ctx, r.pool, pgx.TxOptions{}, func(tx pgx.Tx) error {
        err := tx.QueryRow(ctx,
            "INSERT INTO tasks (title) VALUES ($1) RETURNING id",
            title,
        ).Scan(&taskID)
        if err != nil {
            return err
        }

        for _, st := range subtasks {
            _, err = tx.Exec(ctx,
                "INSERT INTO subtasks (task_id, title) VALUES ($1, $2)",
                taskID, st,
            )
            if err != nil {
                return err
            }
        }
        return nil
    })
    if err != nil {
        return 0, fmt.Errorf("creating task with subtasks: %w", err)
    }
    return taskID, nil
}

BeginTxFunc does Begin, executes your function, and does Commit or Rollback automatically depending on whether the function returns nil or an error. Less code, fewer opportunities to forget the rollback.

Passing transactions between repositories

In cases where you need to coordinate operations between different repositories within the same transaction, a pattern that works well is accepting an interface that implements both pgxpool.Pool and pgx.Tx:

// DBTX is the common interface between pool and transaction
type DBTX interface {
    Exec(ctx context.Context, sql string, arguments ...any) (pgconn.CommandTag, error)
    Query(ctx context.Context, sql string, args ...any) (pgx.Rows, error)
    QueryRow(ctx context.Context, sql string, args ...any) pgx.Row
}

type PostgresTaskRepository struct {
    db DBTX
}

This way the same repository works with a pool connection or inside a transaction:

// Normal usage
repo := NewPostgresTaskRepository(pool)

// Inside a transaction
tx, _ := pool.Begin(ctx)
txRepo := NewPostgresTaskRepository(tx)

Handling NULL values

SQL NULLs are one of the points where Go forces you to be explicit. You cannot scan a NULL into a string — Go will return an error. You have two main options.

Pointers

The most direct way: use pointers. A *string can be nil (NULL) or point to a value.

type Task struct {
    ID          int64
    Title       string
    Description *string // NULL -> nil
}

It’s simple and it works. The downside is that working with pointers is awkward: you have to check for nil before accessing the value, and serializing to JSON requires some thought.

if t.Description != nil {
    fmt.Println(*t.Description)
}

pgtype

pgx includes the pgtype package with types that represent nullable values more explicitly:

import "github.com/jackc/pgx/v5/pgtype"

type Task struct {
    ID          int64
    Title       string
    Description pgtype.Text // has Value and Valid fields
}

if t.Description.Valid {
    fmt.Println(t.Description.String)
}

pgtype.Text serializes correctly to JSON (null when Valid is false, the string when true). And it expresses the intention more clearly than a pointer.

My recommendation: use pointers for simple models where the semantics are obvious. Use pgtype when you need correct JSON serialization or when the model has many nullable fields and you want to avoid pointer proliferation.

There’s a third option worth mentioning: sql.NullString, sql.NullInt64, etc., from the database/sql package. They work, but their JSON serialization is horrible (an object with String and Valid as fields), so only use them if you’re working with database/sql and don’t care about serialization.

Migrations: goose and golang-migrate

Writing SQL by hand is fine. Managing your database schema manually is not. You need versioned and reproducible migrations.

The two most used tools in Go are goose and golang-migrate.

goose

go install github.com/pressly/goose/v3/cmd/goose@latest

Create a migration:

goose -dir migrations create add_tasks_table sql

That generates a file like 20260708120000_add_tasks_table.sql:

-- +goose Up
CREATE TABLE tasks (
    id          BIGSERIAL PRIMARY KEY,
    title       TEXT NOT NULL,
    description TEXT,
    status      TEXT NOT NULL DEFAULT 'pending',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_tasks_status ON tasks (status);

-- +goose Down
DROP TABLE IF EXISTS tasks;

Run migrations:

goose -dir migrations postgres "postgres://user:password@localhost:5432/mydb?sslmode=disable" up

goose also allows running migrations from Go code, which is useful for tests:

import "github.com/pressly/goose/v3"

func RunMigrations(db *sql.DB) error {
    goose.SetDialect("postgres")
    return goose.Up(db, "migrations")
}

golang-migrate

go install -tags 'postgres' github.com/golang-migrate/migrate/v4/cmd/migrate@latest

golang-migrate uses two files per migration (up and down):

migrate create -ext sql -dir migrations -seq add_tasks_table

Generates 000001_add_tasks_table.up.sql and 000001_add_tasks_table.down.sql.

The main difference: goose uses a single file with -- +goose Up and -- +goose Down markers. golang-migrate uses separate files. Both work well. I prefer goose for simplicity, but it’s a matter of taste.

What matters isn’t which one you choose, but that you choose one and always use it. No “I’ll change the schema directly in production and create the migration later”. That path ends in disaster.

Common errors and debugging

After working with Go and PostgreSQL for a while, there are errors that keep appearing. Here are the most common ones and how to resolve them.

”conn busy”

conn busy

You’re trying to use a connection that already has an operation in progress. Common cause: you didn’t close the rows from a previous query before executing another query on the same connection.

// BAD: rows are not closed before the next query
rows, _ := pool.Query(ctx, "SELECT ...")
for rows.Next() {
    // you make another query here using pool
    pool.QueryRow(ctx, "SELECT ...") // conn busy
}

Solution: close the rows before doing another operation, or use the pool (not an individual connection) for the second query.

”too many connections”

FATAL: too many clients already

Your application is opening more connections than PostgreSQL allows. Check MaxConns in your pool and make sure the total connections from all your instances don’t exceed PostgreSQL’s max_connections (default 100).

Scan to wrong type

can't scan into dest[3]: cannot assign NULL to *string

You’re trying to scan a NULL value into a non-nullable type. Use a pointer (*string) or pgtype.Text.

Context cancelled but query still running

If you cancel a context but the query keeps running in PostgreSQL, you may be using database/sql instead of native pgx. database/sql doesn’t always send the cancellation signal to PostgreSQL. Native pgx does it correctly.

Slow queries without explanation

Enable query logging in pgx to see exactly what executes:

config, _ := pgxpool.ParseConfig(os.Getenv("DATABASE_URL"))
config.ConnConfig.Tracer = &tracelog.TraceLog{
    Logger:   tracelog.LoggerFunc(myLogFunction),
    LogLevel: tracelog.LogLevelDebug,
}

Or, in PostgreSQL, enable log_min_duration_statement:

ALTER SYSTEM SET log_min_duration_statement = '100ms';
SELECT pg_reload_conf();

That logs any query that takes more than 100ms. Combined with EXPLAIN ANALYZE, it gives you a clear picture of where the bottleneck is.

SQL injection

pgx uses prepared statements with positional parameters ($1, $2…). As long as you use those placeholders, you’re protected against SQL injection. Never concatenate strings to build queries:

// BAD: SQL injection
query := "SELECT * FROM tasks WHERE status = '" + status + "'"

// GOOD: positional parameters
pool.Query(ctx, "SELECT * FROM tasks WHERE status = $1", status)

This seems obvious, but I keep seeing it in production code. Especially when building dynamic queries with optional filters. If you need dynamic queries, use a query builder like squirrel, or build the query by concatenating safe fragments and accumulate parameters in a slice:

query := "SELECT * FROM tasks WHERE 1=1"
args := []any{}
argPos := 1

if status != "" {
    query += fmt.Sprintf(" AND status = $%d", argPos)
    args = append(args, status)
    argPos++
}
if title != "" {
    query += fmt.Sprintf(" AND title ILIKE $%d", argPos)
    args = append(args, "%"+title+"%")
    argPos++
}

rows, err := pool.Query(ctx, query, args...)

Not pretty, but safe and explicit. And for most cases, sufficient.

When to consider an ORM (and why I usually don’t)

The question always comes up: “If I write so much SQL by hand, shouldn’t I use an ORM?”

Go has several options:

• GORM: the most popular ORM. Functional, but with an API that hides too much magic. The generated queries are not always what you expect, and debugging performance problems turns into debugging the ORM.
• sqlc: not an ORM. You write SQL, sqlc generates typesafe Go code. It’s my favourite option when hand-written SQL starts to become tedious.
• sqlx: extensions on database/sql that add struct scanning and named parameters. Minimal magic, maximum utility.
• Ent: an entity framework with code generation. More opinionated than GORM, better in consistency.

My position: for most backend services, pgx with direct SQL is sufficient. Add a repository, structure the queries, and that’s it. You don’t need an ORM.

When I consider sqlc:

• The project has many queries and manual mapping becomes tedious.
• I want type safety at compile time, not at runtime.
• I work with a team where not everyone is comfortable writing Go data access code.

When I consider an ORM (rarely):

• Quick prototype where query performance doesn’t matter yet.
• Pure CRUD with minimal business logic.

When I never use an ORM:

• Complex queries with JOINs, CTEs, window functions.
• When database performance is critical.
• When I need control over the exact queries that execute.

The reality is that in Go, writing SQL isn’t as painful as in Java. You don’t need XML mappers, you don’t need entity managers, you don’t need an IoC container to inject the SessionFactory. You have a pool, you have SQL, you have Scan. It’s direct. And if you use testing in Go correctly, testing your repositories against a real PostgreSQL instance (with testcontainers, for example) is trivial.

Control and clarity over magic

Working with PostgreSQL in Go comes down to a few decisions that make a difference. pgx as the native driver instead of database/sql, a well-configured pool with MaxConns and MaxConnLifetime, context with timeout on every query without exception, and repositories that encapsulate data access. Transactions with defer tx.Rollback(ctx) as a safety net, versioned migrations with goose or golang-migrate, and positional parameters always. No concatenating strings to build SQL, no manual changes in production.

Go doesn’t give you the comfort of JPA or the magic of ActiveRecord. It gives you control, clarity and excellent performance. For most backend services, that’s exactly what you need. Write SQL, understand your queries, and let the database do what it does best.