Executing Parameterized Queries with pgx

Why pgx?

In Go, you have several options for database access. The standard library provides the database/sql package, which offers a generic interface. However, for a project focused on PostgreSQL, using a specialized driver like pgx gives us significant advantages in performance and features. It provides direct support for many PostgreSQL-specific data types and functionalities that are not available through the standard interface.

The following short video gives a nice overview of the database access landscape in Go and highlights where pgx fits in.

Implementing the Repository Methods

Let's get hands-on and start implementing the methods for our PostgresStore that will fulfill the Storage interface. All the following code will go into your repository/storage file where you defined the PostgresStore struct.

We'll cover three fundamental operations that map to pgx's core functions:

1. Exec(): For executing commands that don't return rows (e.g., INSERT, UPDATE, DELETE).
2. QueryRow(): For fetching exactly one row (e.g., SELECT ... WHERE id = ?).
3. Query(): For fetching a list of rows (e.g., SELECT ... LIMIT ? OFFSET ?).

Pattern 1: Writing Data with Exec

Our first task is to create a new account. This involves an INSERT statement. Since this operation doesn't return a row, Exec is the perfect tool.

A critical concept here is parameterization. We use placeholders ($1, $2, etc.) in our SQL string, and pgx safely substitutes the arguments. This is non-negotiable for security.

Let's implement a CreateAccount method.

// In your repository file (e.g., storage.go)

import (
	"context"
	"fmt"
	// ... other imports
)

const createAccount = `-- name: CreateAccount :one
INSERT INTO accounts (
  owner,
  balance,
  currency
) VALUES (
  $1, $2, $3
) RETURNING id, owner, balance, currency, created_at
`

// You'll need a struct to hold the parameters for creating an account
type CreateAccountParams struct {
	Owner    string `json:"owner"`
	Balance  int64  `json:"balance"`
	Currency string `json:"currency"`
}

// And remember your Account struct model
type Account struct {
    ID        int64     `json:"id"`
    Owner     string    `json:"owner"`
    Balance   int64     `json:"balance"`
    Currency  string    `json:"currency"`
    CreatedAt time.Time `json:"created_at"`
}

func (s *PostgresStore) CreateAccount(ctx context.Context, arg CreateAccountParams) (Account, error) {
	var i Account
	// We use QueryRow because we added `RETURNING` to our SQL, which returns the newly created row.
	// If we didn't have `RETURNING`, we would use Exec.
	err := s.db.QueryRow(ctx, createAccount, arg.Owner, arg.Balance, arg.Currency).Scan(
		&i.ID,
		&i.Owner,
		&i.Balance,
		&i.Currency,
		&i.CreatedAt,
	)
	return i, err
}

Note: I've used QueryRow here because of the RETURNING clause, a common and useful pattern to get back the id and created_at timestamp generated by the database. If your INSERT statement did not have RETURNING, you would use Exec, as its primary purpose is for commands that don't return data.

Pattern 2: Reading a Single Row with QueryRow

Next, let's fetch a specific account by its ID. We expect this query to return exactly one row. QueryRow is designed for this.

A key behavior of QueryRow is that it doesn't return an error immediately. Any error from the query itself is deferred until you call the .Scan() method. If the query returns no rows, .Scan() will return a special error, pgx.ErrNoRows. Your code must handle this case.

// In your repository file (e.g., storage.go)

const getAccount = `-- name: GetAccount :one
SELECT id, owner, balance, currency, created_at FROM accounts
WHERE id = $1 LIMIT 1
`

func (s *PostgresStore) GetAccount(ctx context.Context, id int64) (Account, error) {
	var i Account
	err := s.db.QueryRow(ctx, getAccount, id).Scan(
		&i.ID,
		&i.Owner,
		&i.Balance,
		&i.Currency,
		&i.CreatedAt,
	)
	if err != nil {
		// Map pgx.ErrNoRows to a domain-specific error if you want.
		// For now, we'll just return it.
		return i, err
	}
	return i, nil
}

The manual .Scan() call requires you to list pointers to your struct fields in the exact order of the columns in your SELECT statement. This is a common source of bugs, which we'll discuss shortly.

Pattern 3: Reading Multiple Rows with Query

Finally, let's implement a function to list accounts, potentially with pagination. This query can return zero, one, or many rows. For this, we use Query.

There are two main ways to process the results from Query: the classic manual loop and modern pgx helpers.

Method 1: Manual Iteration (The Classic Way)

This approach gives you full control but involves more boilerplate. It's important to understand the mechanics.

// In your repository file (e.g., storage.go)
const listAccounts = `-- name: ListAccounts :many
SELECT id, owner, balance, currency, created_at FROM accounts
ORDER BY id
LIMIT $1
OFFSET $2
`

func (s *PostgresStore) ListAccounts(ctx context.Context, limit, offset int) ([]Account, error) {
	rows, err := s.db.Query(ctx, listAccounts, limit, offset)
	if err != nil {
		return nil, err
	}
	// IMPORTANT: Always close the rows. `defer` is perfect for this.
	defer rows.Close()

	items := []Account{}
	for rows.Next() {
		var i Account
		if err := rows.Scan(&i.ID, &i.Owner, &i.Balance, &i.Currency, &i.CreatedAt); err != nil {
			return nil, err
		}
		items = append(items, i)
	}

	// After the loop, check for any error that occurred during iteration.
	if err := rows.Err(); err != nil {
		return nil, err
	}

	return items, nil
}

This pattern has several critical steps: Query, defer rows.Close(), loop with rows.Next(), rows.Scan() inside, and finally checking rows.Err().

Method 2: pgx Helpers (The Modern Way)

pgx v5 introduced generic helper functions that significantly reduce boilerplate and the risk of errors. pgx.CollectRows combined with pgx.RowToStructByName is a powerful and clean alternative.

import (
	// ...
	"github.com/jackc/pgx/v5"
)

// The SQL `listAccounts` constant is the same.

func (s *PostgresStore) ListAccountsModern(ctx context.Context, limit, offset int) ([]Account, error) {
	rows, err := s.db.Query(ctx, listAccounts, limit, offset)
	if err != nil {
		return nil, err
	}
	// No defer, no loop, no manual scan!
	// `CollectRows` handles closing rows, iteration, and error checking.
	accounts, err := pgx.CollectRows(rows, pgx.RowToStructByName[Account])
	if err != nil {
		return nil, fmt.Errorf("failed to collect rows: %w", err)
	}

	return accounts, nil
}

As you can see, this is much more concise and less error-prone. RowToStructByName automatically maps columns to struct fields by name (case-insensitive), removing the risk of a Scan argument mismatch. For a software engineer focused on building robust systems, this is the preferred approach.

For a deeper dive into these functions and other helpers, the pgx documentation is the ultimate source of truth.

The Problem with Boilerplate

You've now written the core functions for your data access layer. But even with pgx helpers, you might notice a pattern. You write SQL, then you write the Go function that executes it, scans the results, and handles errors.

For a project of any significant size, this manual process becomes tedious and a source of bugs. As someone with deep experience in system design, you know that eliminating repetitive, error-prone tasks is key to building reliable software. What if you could just write the SQL and have the type-safe Go code generated for you?

This is exactly what the tool sqlc does. It's not an ORM; it's a code generator that creates idiomatic Go code directly from your SQL queries. The following video is an excellent introduction to this concept. While we will continue with our manual implementation for now to master the fundamentals, understanding tools like sqlc is essential for advanced Go development and building large systems efficiently.

Conclusion

Excellent work. You've successfully written the Go code to create and retrieve data from your PostgreSQL database, covering the three most common query patterns. This is a huge step toward a functional API.

Key Takeaways:

• Parameterized queries (using $1, $2...) are essential for security.
• Exec is for commands that don't return rows (e.g., INSERT without RETURNING).
• QueryRow is for fetching a single row. You must call .Scan() to get the result and handle the potential pgx.ErrNoRows error.
• Query is for fetching multiple rows. The modern approach using pgx.CollectRows with helpers like pgx.RowToStructByName is safer and more concise than manual iteration.
• Tools like sqlc exist to automate the generation of this data access layer, a powerful concept for large-scale projects.

Next Up

Our current operations are all individual, or "atomic" in their own right. But what about a bank transfer? A transfer requires at least two database operations: debiting one account and crediting another. Both must succeed, or both must fail together. A single, indivisible unit of work like this is called a transaction.

In the next lesson, we will learn how to manage database transactions with commit and rollback semantics using pgx. This will allow us to implement the core money-transfer logic for our application safely.