postgres

package
v1.30.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Dec 8, 2024 License: MIT Imports: 15 Imported by: 0

README

Backend Core Library - PostgreSQL Client

PostgreSQL Go Version GoDoc License

A production-ready PostgreSQL client for Go applications with built-in support for connection pooling, transactions, GORM integration, and comprehensive testing utilities.

Table of Contents

Features

Core Features
  • 🔄 Smart connection pooling with automatic management
  • 📊 Comprehensive transaction support with rollback
  • 🔌 Seamless GORM integration
  • 📈 Built-in instrumentation and monitoring
  • 🔁 Configurable retry mechanisms
  • ⏱️ Query timeout handling
  • 🧪 In-memory testing capabilities
  • 🔍 Detailed logging and debugging
  • 🛡️ Connection security options
Performance Features
  • Connection pool optimization
  • Automatic retry with exponential backoff
  • Query timeout management
  • Efficient resource cleanup
  • Statement caching
  • Connection lifecycle management

Installation

go get -u github.com/SolomonAIEngineering/backend-core-library/database/postgres

Quick Start

package main

import (
    "context"
    "time"
    "log"

    "github.com/SolomonAIEngineering/backend-core-library/database/postgres"
    "github.com/SolomonAIEngineering/backend-core-library/instrumentation"
    "go.uber.org/zap"
)

func main() {
    // Initialize client with comprehensive configuration
    client, err := postgres.New(
        postgres.WithQueryTimeout(30 * time.Second),
        postgres.WithMaxConnectionRetries(3),
        postgres.WithConnectionString("postgresql://user:pass@localhost:5432/mydb?sslmode=verify-full"),
        postgres.WithMaxIdleConnections(10),
        postgres.WithMaxOpenConnections(100),
        postgres.WithMaxConnectionLifetime(1 * time.Hour),
        postgres.WithLogger(zap.L()),
        postgres.WithInstrumentationClient(&instrumentation.Client{}),
    )
    if err != nil {
        log.Fatal(err)
    }
    defer client.Close()

    // Your application code here
}

Connection Management

Basic Configuration
type ConnectionConfig struct {
    // Connection parameters
    Host            string
    Port            int
    Database        string
    User            string
    Password        string
    SSLMode         string
    
    // Pool configuration
    MaxIdleConns    int
    MaxOpenConns    int
    ConnMaxLifetime time.Duration
    
    // Retry configuration
    MaxRetries      int
    RetryTimeout    time.Duration
    RetrySleep      time.Duration
}

// Example configuration
config := ConnectionConfig{
    Host:            "localhost",
    Port:            5432,
    Database:        "myapp",
    MaxIdleConns:    10,
    MaxOpenConns:    100,
    ConnMaxLifetime: time.Hour,
    MaxRetries:      3,
    RetryTimeout:    time.Minute,
    RetrySleep:      time.Second,
}
SSL/TLS Configuration
import "crypto/tls"

// Configure TLS
tlsConfig := &tls.Config{
    MinVersion: tls.VersionTLS12,
    ServerName: "your-db-host",
}

connStr := fmt.Sprintf(
    "host=%s port=%d user=%s password=%s dbname=%s sslmode=verify-full",
    config.Host, config.Port, config.User, config.Password, config.Database,
)

client, err := postgres.New(
    postgres.WithConnectionString(&connStr),
    postgres.WithTLSConfig(tlsConfig),
)

Transaction Handling

Simple Transactions
// Basic transaction
err := client.PerformTransaction(ctx, func(ctx context.Context, tx *gorm.DB) error {
    // Create a user
    user := User{Name: "John Doe", Email: "john@example.com"}
    if err := tx.Create(&user).Error; err != nil {
        return err
    }
    
    // Create an order for the user
    order := Order{UserID: user.ID, Amount: 100.00}
    if err := tx.Create(&order).Error; err != nil {
        return err
    }
    
    return nil
})
Complex Transactions
type TransactionResult struct {
    UserID  uint
    OrderID uint
}

// Transaction with return value
result, err := client.PerformComplexTransaction(ctx, func(ctx context.Context, tx *gorm.DB) (interface{}, error) {
    user := User{Name: "Jane Doe"}
    if err := tx.Create(&user).Error; err != nil {
        return nil, err
    }
    
    order := Order{UserID: user.ID, Amount: 200.00}
    if err := tx.Create(&order).Error; err != nil {
        return nil, err
    }
    
    return &TransactionResult{
        UserID:  user.ID,
        OrderID: order.ID,
    }, nil
})
Nested Transactions
err := client.PerformTransaction(ctx, func(ctx context.Context, tx *gorm.DB) error {
    // Outer transaction
    if err := tx.Create(&User{Name: "Outer"}).Error; err != nil {
        return err
    }
    
    // Nested transaction
    return tx.Transaction(func(tx2 *gorm.DB) error {
        return tx2.Create(&User{Name: "Inner"}).Error
    })
})

GORM Integration

Model Definition
type User struct {
    gorm.Model
    Name     string `gorm:"size:255;not null"`
    Email    string `gorm:"size:255;uniqueIndex"`
    Orders   []Order
}

type Order struct {
    gorm.Model
    UserID   uint
    Amount   float64
    Status   string
}

// Auto-migrate models
if err := client.DB().AutoMigrate(&User{}, &Order{}); err != nil {
    log.Fatal(err)
}
Advanced Queries
// Complex query with joins and conditions
var users []User
err := client.DB().
    Preload("Orders", "status = ?", "completed").
    Joins("LEFT JOIN orders ON orders.user_id = users.id").
    Where("orders.amount > ?", 1000).
    Group("users.id").
    Having("COUNT(orders.id) > ?", 5).
    Find(&users).Error

Error Handling

// Custom error types
var (
    ErrConnectionFailed = errors.New("failed to connect to postgresql")
    ErrQueryTimeout     = errors.New("query timeout")
    ErrDuplicateKey    = errors.New("duplicate key violation")
)

// Error handling example
if err := client.DB().Create(&user).Error; err != nil {
    switch {
    case errors.Is(err, gorm.ErrRecordNotFound):
        // Handle not found
    case errors.Is(err, gorm.ErrDuplicatedKey):
        // Handle duplicate key
    case errors.Is(err, context.DeadlineExceeded):
        // Handle timeout
    default:
        // Handle other errors
    }
    return err
}

Testing

In-Memory Database
func TestUserService(t *testing.T) {
    // Create test client
    client, err := postgres.NewInMemoryTestDbClient(
        &User{},
        &Order{},
    )
    if err != nil {
        t.Fatal(err)
    }
    defer client.Close()

    // Run tests
    t.Run("CreateUser", func(t *testing.T) {
        user := &User{Name: "Test User"}
        err := client.DB().Create(user).Error
        assert.NoError(t, err)
        assert.NotZero(t, user.ID)
    })
}
Transaction Testing
func TestOrderCreation(t *testing.T) {
    client, err := postgres.NewInMemoryTestDbClient(&Order{})
    if err != nil {
        t.Fatal(err)
    }
    
    handler := client.ConfigureNewTxCleanupHandlerForUnitTests()
    defer handler.savePointRollbackHandler(handler.Tx)
    
    t.Run("CreateOrder", func(t *testing.T) {
        // Your test code here
        order := &Order{Amount: 100}
        err := handler.Tx.Create(order).Error
        assert.NoError(t, err)
    })
}

Monitoring & Instrumentation

// Initialize with instrumentation
client, err := postgres.New(
    postgres.WithInstrumentationClient(&instrumentation.Client{
        ServiceName: "my-service",
        Environment: "production",
    }),
    postgres.WithMetricsEnabled(true),
)

// Access metrics
metrics := client.GetMetrics()
fmt.Printf("Total Queries: %d\n", metrics.TotalQueries)
fmt.Printf("Failed Queries: %d\n", metrics.FailedQueries)
fmt.Printf("Average Query Time: %v\n", metrics.AverageQueryTime)

Best Practices

Connection Management
// Initialize once at application startup
client, err := postgres.New(
    postgres.WithMaxIdleConnections(10),
    postgres.WithMaxOpenConnections(100),
    postgres.WithMaxConnectionLifetime(time.Hour),
)
if err != nil {
    log.Fatal(err)
}
defer client.Close()
Query Optimization
// Use indexes effectively
db.Exec(`CREATE INDEX idx_users_email ON users(email)`)

// Use appropriate batch sizes
const batchSize = 1000
for i := 0; i < len(records); i += batchSize {
    batch := records[i:min(i+batchSize, len(records))]
    client.DB().CreateInBatches(batch, batchSize)
}

API Reference

See our GoDoc for complete API documentation.

Contributing

We welcome contributions! Please see our Contributing Guide for details.

Development Setup
# Clone the repository
git clone https://github.com/SolomonAIEngineering/backend-core-library.git

# Install dependencies
go mod download

# Run tests
make test

# Run linting
make lint

# Generate coverage report
make coverage

License

This PostgreSQL client is released under the Apache License, Version 2.0. See LICENSE for details.

Support

Documentation

Overview

Package database witholds database specific utilities for the core database libary This package mainly deals with interactions with the postgresql database.

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func DeleteCreatedEntitiesAfterTest 

func DeleteCreatedEntitiesAfterTest(db *gorm.DB) func()

DeleteCreatedEntitiesAfterTest sets up GORM `onCreate` hook and return a function that can be deferred to remove all the entities created after the hook was set up You can use it as 

func TestSomething(t *testing.T){
    db, _ := gorm.Open(...)

    cleaner := DeleteCreatedEntities(db)
    defer cleaner()

}

func GenerateRandomString 

func GenerateRandomString(n int) string

GenerateRandomString generates a random string based on the size specified by the client

Types

type Client 

type Client struct {
	// `Engine` is a field of the `Client` struct that holds a pointer to a `gorm.DB` object, which is a
	// database connection object provided by the GORM library. This field is used to store the connection
	// to the PostgreSQL database and is set when the `connect` method is called during the creation of a
	// new `Client` instance.
	Engine *gorm.DB
	// `QueryTimeout` is a field of the `Client` struct that holds a pointer to a `time.Duration` value
	// representing the maximum amount of time to wait for a query to complete before timing out. This
	// field can be set using options when creating a new `Client` instance and is used to set the query
	// timeout for the database connection. If a query takes longer than the specified timeout, an error
	// will be returned.
	QueryTimeout *time.Duration
	// `MaxConnectionRetries` is a field of the `Client` struct that holds a pointer to an integer value
	// representing the maximum number of times to retry connecting to the PostgreSQL database in case of a
	// connection failure. It is used in the `connect` method to set the maximum number of retries for the
	// retry mechanism.
	MaxConnectionRetries *int
	// `MaxConnectionRetryTimeout` is a field of the `Client` struct that holds a pointer to a
	// `time.Duration` value representing the maximum amount of time to wait for a successful connection to
	// the PostgreSQL database before giving up and returning an error. It is used in the `connect` method
	// to set the maximum timeout for the retry mechanism.
	MaxConnectionRetryTimeout *time.Duration
	// `RetrySleep` is a field of the `Client` struct that holds a pointer to a `time.Duration` value
	// representing the amount of time to wait between retries when attempting to connect to the PostgreSQL
	// database. It is used in the `connect` method to set the sleep time for the retry mechanism.
	RetrySleep *time.Duration
	// `ConnectionString` is a field of the `Client` struct that holds a pointer to a string value
	// representing the connection string used to connect to the PostgreSQL database. It is used in the
	// `connect` method to open a new connection to the database using the `gorm.Open` function provided by
	// the GORM library. The connection string typically includes information such as the database name,
	// host, port, username, and password required to establish a connection to the database.
	ConnectionString *string
	// `MaxIdleConnections` is a field of the `Client` struct that holds a pointer to an integer value
	// representing the maximum number of idle connections in the connection pool. It is used to set the
	// maximum number of idle connections that can be kept in the pool and is passed to the
	// `SetMaxIdleConns` function of the `sql.DB` object obtained from the `gorm.DB` object. This helps to
	// optimize the performance of the database connection by limiting the number of idle connections that
	// are kept open.
	MaxIdleConnections *int
	// `MaxOpenConnections` is a field of the `Client` struct that holds a pointer to an integer value
	// representing the maximum number of open connections in the connection pool. It is used to set the
	// maximum number of open connections that can be kept in the pool and is passed to the
	// `SetMaxOpenConns` function of the `sql.DB` object obtained from the `gorm.DB` object. This helps to
	// optimize the performance of the database connection by limiting the number of open connections that
	// are kept open.
	MaxOpenConnections *int
	// `MaxConnectionLifetime` is a field of the `Client` struct that holds a pointer to a `time.Duration`
	// value representing the maximum amount of time a connection can remain open before it is closed and
	// removed from the connection pool. It is used to set the maximum lifetime of a connection and is
	// passed to the `SetConnMaxLifetime` function of the `sql.DB` object obtained from the `gorm.DB`
	// object. This helps to optimize the performance of the database connection by limiting the amount of
	// time a connection can remain open and reducing the risk of resource leaks or other issues that can
	// arise from long-lived connections.
	MaxConnectionLifetime *time.Duration
	// `InstrumentationClient` is a field of the `Client` struct that holds a pointer to an instance of the
	// `instrumentation.Client` struct. This field is used to pass an instrumentation client to the
	// `Client` instance, which can be used to collect metrics and traces related to the database
	// operations performed by the client. The `instrumentation.Client` struct provides methods for
	// collecting metrics and traces, which can be used to monitor the performance and behavior of the
	// database connection.
	InstrumentationClient *instrumentation.Client
	// `Logger *zap.Logger` is a field of the `Client` struct that holds a pointer to an instance of the
	// `zap.Logger` struct. This field is used to pass a logger to the `Client` instance, which can be used
	// to log messages related to the database operations performed by the client. The `zap.Logger` struct
	// provides methods for logging messages at different levels of severity, which can be used to monitor
	// the behavior of the database connection and diagnose issues that may arise.
	Logger *zap.Logger
}

Client is defining a new struct type called `Client` which will be used to create instances of a client for connecting to a PostgreSQL database. The struct contains various fields that can be set using options, such as the database engine, query timeout, connection string, and instrumentation client. The struct also has a `connect` method that attempts to connect to the database using retries.

func New 

func New(options ...Option) (*Client, error)

The New function creates a new client with optional configuration options.

func NewInMemoryTestDbClient 

func NewInMemoryTestDbClient(models ...any) (*Client, error)

NewInMemoryTestDbClient creates a new in memory test db client This is useful only for unit tests

func (*Client) Close 

func (c *Client) Close() error

Close closes the database connection

func (*Client) ConfigureNewTxCleanupHandlerForUnitTests 

func (c *Client) ConfigureNewTxCleanupHandlerForUnitTests() *TestTxCleanupHandlerForUnitTests

ConfigureNewTxCleanupHandlerForUnitTests creates a new transaction with a save point and returns a handler that can be used to rollback to the save point. This is useful for unit tests that need to rollback a transaction to a save point.

func (*Client) PerformComplexTransaction 

func (db *Client) PerformComplexTransaction(ctx context.Context, transaction CmplxTx) (interface{}, error)

PerformComplexTransaction takes as input an anonymous function witholding logic to perform within a transaction returning an abstract type. This function is then invoked within a transaction and depending on the occurrence of any specific errors, the transaction is either committed to the database or completely rolled back. This returns the result obtained from the invocation of the anonymous function as well as any error occuring throughout the transaction lifecycle.

func (*Client) PerformTransaction 

func (db *Client) PerformTransaction(ctx context.Context, transaction Tx) error

PerformTransaction takes as input an anonymous function witholding logic to perform within a transaction. This function is then invoked within a transaction. if unsuccessful or any error is raised throughout the transaction, then, the transaction is rolled back. Returned is any error occuring throughout the transaction lifecycle

func (*Client) Validate 

func (c *Client) Validate() error

Validate validates the client

type CmplxTx 

type CmplxTx func(ctx context.Context, tx *gorm.DB) (interface{}, error)

CmplxTx is a type serving as a function decorator for complex database transactions

type Option 

type Option func(*Client)

Option is a function that sets a parameter for the client

func WithConnectionString 

func WithConnectionString(connectionString *string) Option

WithConnectionString sets the connection string

func WithInstrumentationClient 

func WithInstrumentationClient(instrumentationClient *instrumentation.Client) Option

WithInstrumentationClient sets the instrumentation client

func WithLogger 

func WithLogger(logger *zap.Logger) Option

WithLogger sets the logger

func WithMaxConnectionLifetime 

func WithMaxConnectionLifetime(maxConnectionLifetime *time.Duration) Option

WithMaxConnectionLifetime sets the max connection lifetime

func WithMaxConnectionRetries 

func WithMaxConnectionRetries(retries *int) Option

WithMaxConnectionRetries sets the max connection retries

func WithMaxConnectionRetryTimeout 

func WithMaxConnectionRetryTimeout(timeout *time.Duration) Option

WithMaxConnectionRetryTimeout sets the max connection retry timeout

func WithMaxIdleConnections 

func WithMaxIdleConnections(maxIdleConnections *int) Option

WithMaxIdleConnections sets the max idle connections

func WithMaxOpenConnections 

func WithMaxOpenConnections(maxOpenConnections *int) Option

WithMaxOpenConnections sets the max open connections

func WithQueryTimeout 

func WithQueryTimeout(timeout *time.Duration) Option

WithQueryTimeout sets the query timeout

func WithRetrySleep 

func WithRetrySleep(sleep *time.Duration) Option

WithRetrySleep sets the retry sleep

type TestTxCleanupHandlerForUnitTests 

type TestTxCleanupHandlerForUnitTests struct {
	Tx *gorm.DB
	// contains filtered or unexported fields
}

TestTxCleanupHandlerForUnitTests is a handler that can be used to rollback a transaction to a save point. This is useful for unit tests that need to rollback a transaction to a save point.

type Tx 

type Tx func(ctx context.Context, tx *gorm.DB) error

Tx is a type serving as a function decorator for common database transactions

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.