pgxhelper

README

pgxhelper

pgxhelper is a lightweight, opinionated wrapper around pgx/v5 and scany/v2 designed to simplify common database operations in Go applications. It provides a thin abstraction layer over pgxpool that reduces boilerplate for querying, scanning, and executing commands, including a straightforward way to handle transactions.

Features

• Simplified CRUD: Easy-to-use Get, Select, and Exec methods for common database interactions.
• Automatic Scanning: Leverages scany to automatically scan database rows into Go structs.
• Transaction Management: A simple WithinTransaction helper to run operations within a database transaction without manual boilerplate.
• Connection Pooling: Built on top of pgxpool for efficient database connection management.
• Context-Aware: All database operations are context.Context aware.

Installation

go get github.com/theprogrammer67/pgxhelper

Quick Start

Here is a complete example demonstrating how to connect to a database, create a table, insert data, and query it back.

1. Define Your Model

First, define a Go struct that corresponds to your database table schema.

// user.go
package main

type User struct {
    ID    int    `db:"id"`
    Name  string `db:"name"`
    Email string `db:"email"`
}

2. Use the DBHelper

// main.go
package main

import (
	"context"
	"fmt"
	"log"
	"time"

	"github.com/theprogrammer67/pgxhelper"
)

func main() {
	// Database connection string
	connStr := "postgres://user:password@localhost:5432/database?sslmode=disable"

	// 1. Initialize and connect
	db := pgxhelper.New()
	if err := db.Connect(connStr, 5*time.Second); err != nil {
		log.Fatalf("Failed to connect to database: %v", err)
	}
	defer db.Close()

	log.Println("Successfully connected to the database!")

	ctx := context.Background()

	// 2. Use a transaction to set up the schema and insert data
	err := db.WithinTransaction(ctx, func(txCtx context.Context) error {
		// Create table
		_, err := db.Exec(txCtx, `
			CREATE TABLE IF NOT EXISTS users (
				id SERIAL PRIMARY KEY,
				name TEXT NOT NULL,
				email TEXT NOT NULL UNIQUE
			)`)
		if err != nil {
			return fmt.Errorf("failed to create table: %w", err)
		}

		// Insert a new user
		_, err = db.Exec(txCtx,
			"INSERT INTO users (name, email) VALUES ($1, $2)",
			"John Doe", "john.doe@example.com",
		)
		if err != nil {
			return fmt.Errorf("failed to insert user: %w", err)
		}
		return nil
	})

	if err != nil {
		log.Fatalf("Transaction failed: %v", err)
	}

	log.Println("Table created and user inserted successfully.")

	// 3. Select multiple users
	var users []*User
	err = db.Select(ctx, &users, "SELECT id, name, email FROM users ORDER BY id")
	if err != nil {
		log.Fatalf("Failed to select users: %v", err)
	}
	fmt.Printf("All users: %+v\n", users)
	for _, u := range users {
		fmt.Printf("- ID: %d, Name: %s, Email: %s\n", u.ID, u.Name, u.Email)
	}

	// 4. Get a single user
	var singleUser User
	err = db.Get(ctx, &singleUser, "SELECT id, name, email FROM users WHERE name = $1", "John Doe")
	if err != nil {
		log.Fatalf("Failed to get user: %v", err)
	}
	fmt.Printf("Single user fetched: %+v\n", singleUser)
}

pgxhelper API Overview

pgxhelper.DBHelper

The main struct that holds the database connection pool.

• NewDBHelper(pool *pgxpool.Pool, opts ...Option) *DBHelper: Creates a new DBHelper with an existing pgxpool.Pool.
• Connect(connStr string, timeout time.Duration) error: A convenience method to create a pgxpool.Pool and connect to the database.
• Close(): Closes the underlying connection pool.

Operations

• Get(ctx context.Context, dest any, query string, args ...any) error: Queries for a single row and scans the result into dest. dest must be a pointer to a struct.
• Select(ctx context.Context, dest any, query string, args ...any) error: Queries for multiple rows and scans the results into dest. dest must be a pointer to a slice of structs.
• Exec(ctx context.Context, query string, args ...any) (int64, error): Executes a command (e.g., INSERT, UPDATE, DELETE) and returns the number of rows affected.
• WithinTransaction(ctx context.Context, fn func(ctx context.Context) error, opt ...pgx.TxOptions) error: Executes the function fn within a database transaction. The transaction is automatically committed if fn returns nil, or rolled back if it returns an error. The context passed to fn carries the transaction.
• Querier(ctx context.Context) Querier: Returns the underlying pgx.Tx if the context is transactional, otherwise returns the pgxpool.Pool. This is useful for interoperability with other libraries.

sqlsetpgxhelper

The sqlsetpgxhelper package provides a DBHelper, which is an alternative to the main pgxhelper.DBHelper. It is designed to work with the sqlset library, allowing you to manage your SQL queries in separate files instead of embedding them as strings in your Go code.

This is particularly useful for larger projects where separating SQL from application logic improves maintainability.

Documentation

Overview

Package pgxhelper provides a thin wrapper around pgxpool to simplify common database operations.

Index

• type DBHelper
  • func New(opts ...Option) *DBHelper
  • func (d *DBHelper) Close()
  • func (d *DBHelper) Connect(connStr string, timeout time.Duration) error
  • func (d *DBHelper) Exec(ctx context.Context, query string, args ...any) (int64, error)
  • func (d *DBHelper) Get(ctx context.Context, dest any, query string, args ...any) error
  • func (d *DBHelper) Ping(ctx context.Context) error
  • func (d *DBHelper) Querier(ctx context.Context) Querier
  • func (d *DBHelper) Select(ctx context.Context, dest any, query string, args ...any) error
  • func (d *DBHelper) WithinTransaction(ctx context.Context, fn func(ctx context.Context) error, opt ...pgx.TxOptions) error
• type Option
  • func WithScanAPI(scanAPI *pgxscan.API) Option
• type Querier

Types

type DBHelper

type DBHelper struct {
	// contains filtered or unexported fields
}

DBHelper is a wrapper around pgxpool.Pool to simplify common database operations.

func New

func New(opts ...Option) *DBHelper

New creates and returns a new DBHelper.

func (*DBHelper) Close

func (d *DBHelper) Close()

Close closes all connections in the pool and prevents further use.

func (*DBHelper) Connect

func (d *DBHelper) Connect(connStr string, timeout time.Duration) error

Connect establishes a connection to the database using the provided connection string and timeout. It creates a new pgxpool.Pool and pings the database to ensure the connection is live.

func (*DBHelper) Exec

func (d *DBHelper) Exec(ctx context.Context, query string, args ...any) (int64, error)

Exec executes a query that doesn't return rows, such as INSERT, UPDATE, or DELETE. It returns the number of rows affected.

func (*DBHelper) Get

func (d *DBHelper) Get(ctx context.Context, dest any, query string, args ...any) error

Get queries for a single row and scans it into dest.

func (*DBHelper) Ping

func (d *DBHelper) Ping(ctx context.Context) error

Ping checks the connection to the database.

func (*DBHelper) Querier

func (d *DBHelper) Querier(ctx context.Context) Querier

Querier returns the appropriate querier from the context. If the context contains a transaction, it returns the transaction. Otherwise, it returns the connection pool.

func (*DBHelper) Select

func (d *DBHelper) Select(ctx context.Context, dest any, query string, args ...any) error

Select queries for multiple rows and scans them into a slice.

func (*DBHelper) WithinTransaction

func (d *DBHelper) WithinTransaction(ctx context.Context, fn func(ctx context.Context) error, opt ...pgx.TxOptions) error

WithinTransaction runs the given function within a transactional context.

type Option

type Option func(*DBHelper)

Option is a functional option for configuring a DBHelper.

func WithScanAPI

func WithScanAPI(scanAPI *pgxscan.API) Option

WithScanAPI is a functional option to set the pgxscan.API for the DBHelper.

type Querier

type Querier interface {
	Exec(ctx context.Context, sql string, arguments ...interface{}) (pgconn.CommandTag, error)
	Query(ctx context.Context, sql string, args ...interface{}) (pgx.Rows, error)
	QueryRow(ctx context.Context, sql string, args ...interface{}) pgx.Row
	SendBatch(ctx context.Context, b *pgx.Batch) pgx.BatchResults
	CopyFrom(ctx context.Context, tableName pgx.Identifier, columnNames []string, rowSrc pgx.CopyFromSource) (int64, error)
}

Querier is an interface that abstracts database operations. It is implemented by *pgxpool.Pool, *pgx.Conn, and pgx.Tx. This allows functions to accept any of these types as a querier.