gosql

package module
v1.1.0 Latest Latest
Warning

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

 Go to latest Published: Mar 23, 2025 License: Apache-2.0 Imports: 5 Imported by: 0

README

GoSQL - A Type-Safe SQL Library for Go

GoSQL is a lightweight, type-safe SQL library for Go that provides a clean abstraction over the standard database/sql package. It offers a robust Data Access Object (DAO) pattern implementation with support for transactions, pagination, and entity relationship management.

Features

  • Type-safe queries with generics support
  • DAO pattern implementation for entity management
  • Transaction management with context-based propagation
  • Pagination support for large result sets
  • Entity versioning to prevent concurrent modification issues
  • Cascading operations for entity relationships
  • Prepared statement caching for improved performance
  • Comprehensive logging with structured logs via slog

Installation

go get github.com/iglin/go-sql

Core Concepts

Entity Interface

The library revolves around the Entity interface, which defines the contract for database entities:

type Entity interface {
	comparable
	GetID() uuid.UUID
	SetID(uuid.UUID)
	GetVersion() uuid.UUID
	SetVersion(uuid.UUID)
	Equals(another any) bool
}

A base implementation, GenericEntity, is provided for convenience:

type GenericEntity struct {
	ID      uuid.UUID `json:"id" yaml:"id"`
	Version uuid.UUID `json:"version" yaml:"version"`
}
Data Access Objects (DAOs)

The Dao interface provides CRUD operations for entities:

type Dao[T Entity] interface {
	Save(ctx context.Context, entities ...T) error
	FindById(ctx context.Context, id uuid.UUID) (T, error)
	FindOneByStmt(ctx context.Context, stmt *QueryOneStmt[T], args ...any) (T, error)
	ListByStmt(ctx context.Context, stmt *QueryStmt[T], args ...any) ([]T, error)
	ListAll(ctx context.Context) ([]T, error)
	ListPageByStmt(ctx context.Context, stmt *QueryPageStmt[T], paging Paging, args ...any) (Page[T], error)
	ListPage(ctx context.Context, paging Paging) (Page[T], error)
	Delete(ctx context.Context, entities ...T) error
	DeleteCascade(ctx context.Context, entities ...T) error
	DeleteByIds(ctx context.Context, ids ...uuid.UUID) error
	DeleteByIdsCascade(ctx context.Context, ids ...uuid.UUID) error
	Close(ctx context.Context) error
}
Statements

GoSQL provides several statement types for different query operations:

  • ExecStmt: For executing commands without returning rows
  • QueryValStmt<T>: For retrieving a single scalar value
  • QueryOneStmt<T>: For retrieving a single entity
  • QueryStmt<T>: For retrieving multiple entities
  • QueryPageStmt<T>: For retrieving paginated results

There are also DAO variants of these types (prefixed with Dao) that are used when building DAOs.

Pagination

The library includes built-in pagination support:

type Page[T any] struct {
	Items      []T `json:"items" yaml:"items"`
	TotalPages int `json:"totalPages" yaml:"totalPages"`
}

type Paging struct {
	PageNum  int `json:"pageNum" yaml:"pageNum"`
	PageSize int `json:"pageSize" yaml:"pageSize"`
}
Transaction Management

Transactions are managed through context propagation:

// Transaction options
var (
	RO = &sql.TxOptions{ReadOnly: true}
	RW = &sql.TxOptions{ReadOnly: false}
	TxKey = txKey{}
)

Usage Examples

Creating a DAO
// Define your entity
type User struct {
	gosql.GenericEntity
	Name  string
	Email string
}

// Implement Equals method required by Entity interface
func (u User) Equals(another any) bool {
    au, ok := another.(User)
    if !ok {
        return false
    }
    return u.ID == au.ID && u.Name == au.Name && u.Email == au.Email
}

// Create DAO statement helpers
createNewUser := func() User { return User{} }
receiveUser := func(u User) []any {
    return []any{&u.ID, &u.Version, &u.Name, &u.Email}
}

// Create a DAO for the User entity using the builder pattern
ctx := context.Background()
userDao, err := gosql.DaoBuilder[User]{
    DB: db,
    InsertStmt: &gosql.DaoExecStmt{
        Query: "INSERT INTO users (id, version, name, email) VALUES (?, ?, ?, ?)",
        Cache: true,
    },
    UpdateStmt: &gosql.DaoExecStmt{
        Query: "UPDATE users SET version = ?, name = ?, email = ? WHERE id = ? AND version = ?",
        Cache: true,
    },
    GetByIdStmt: &gosql.DaoQueryOneStmt[User]{
        Query: "SELECT id, version, name, email FROM users WHERE id = ?",
        Cache: true,
    },
    ListAllStmt: &gosql.DaoQueryStmt[User]{
        Query: "SELECT id, version, name, email FROM users",
        Cache: true,
    },
    ListAllPageStmt: &gosql.DaoQueryPageStmt[User]{
        CountStmt: &gosql.DaoQueryValStmt[int]{
            Query: "SELECT COUNT(*) FROM users",
            Cache: true,
        },
        QueryStmt: &gosql.DaoQueryStmt[User]{
            Query: "SELECT id, version, name, email FROM users LIMIT ? OFFSET ?",
            Cache: true,
        },
    },
    DeleteByIdStmt: &gosql.DaoExecStmt{
        Query: "DELETE FROM users WHERE id = ?",
        Cache: true,
    },
    NewReceiver: createNewUser,
    Receive: receiveUser,
    InsertArgs: func(u User) []any {
        return []any{u.ID, u.Version, u.Name, u.Email}
    },
    UpdateArgs: func(u User) []any {
        return []any{u.Version, u.Name, u.Email, u.ID, u.GetVersion()}
    },
    SaveChildren: func(ctx context.Context, tx *sql.Tx, e User) error { return nil },
    LoadChildren: func(ctx context.Context, tx *sql.Tx, e User) error { return nil },
    DeleteChildren: func(ctx context.Context, tx *sql.Tx, e User) error { return nil },
}.Build(ctx)

if err != nil {
    // Handle error
}
Saving Entities
user := User{
	Name:  "John Doe",
	Email: "john@example.com",
}

// Save a new user
err := userDao.Save(ctx, user)
Finding Entities
// Find by ID
user, err := userDao.FindById(ctx, userId)

// Custom query
stmt := &gosql.QueryOneStmt[User]{
	BaseStmt: gosql.BaseStmt{
		Query: "SELECT id, version, name, email FROM users WHERE email = ?",
		Cache: true,
	},
	NewReceiver: createNewUser,
	Receive: receiveUser,
}
user, err := userDao.FindOneByStmt(ctx, stmt, "john@example.com")
Listing Entities
// List all users
users, err := userDao.ListAll(ctx)

// Paginated list
paging := gosql.Paging{PageNum: 1, PageSize: 10}
page, err := userDao.ListPage(ctx, paging)

// Custom list query
stmt := &gosql.QueryStmt[User]{
    BaseStmt: gosql.BaseStmt{
        Query: "SELECT id, version, name, email FROM users WHERE name LIKE ?",
        Cache: true,
    },
    NewReceiver: createNewUser,
    Receive: receiveUser,
}
users, err := userDao.ListByStmt(ctx, stmt, "%John%")
Deleting Entities
// Delete by entity
err := userDao.Delete(ctx, user)

// Delete by ID
err := userDao.DeleteByIds(ctx, userId)

// Delete with cascade
err := userDao.DeleteCascade(ctx, user)
Working with Transactions
// Execute in a transaction
result, err := gosql.QueryWithTx(ctx, db, gosql.RW, func(ctx context.Context, tx *sql.Tx) (Result, error) {
    // Perform multiple operations in a transaction
    err := userDao.Save(ctx, user)
    if err != nil {
        return nil, err
    }
    
    // More operations...
    
    return result, nil
})

Best Practices

  1. Use context propagation for transaction management
  2. Enable statement caching for frequently used queries
  3. Implement proper entity versioning to prevent concurrent modification issues
  4. Define appropriate relationship handlers for cascading operations
  5. Use pagination for large result sets

Error Handling

The library provides predefined errors:

var (
	ErrNotFound = errors.New("gosql: entity not found")
	ErrVersionMismatch = errors.New("gosql: version mismatch - entity was modified")
)

Closing Resources

Always close DAOs when they're no longer needed:

err := userDao.Close(ctx)

Documentation

Index

Constants

This section is empty.

Variables

View Source 
var (
	// ErrNotFound is returned when an entity cannot be found
	ErrNotFound = errors.New("gosql: entity not found")
	// ErrVersionMismatch is returned when an entity's version doesn't match the expected version
	ErrVersionMismatch = errors.New("gosql: version mismatch - entity was modified")
)
View Source 
var (
	RO = &sql.TxOptions{ReadOnly: true}
	// RW represents read-write transaction options
	RW = &sql.TxOptions{ReadOnly: false}

	// TxKey is the context key used to store and retrieve transaction objects
	TxKey = txKey{}
)

RO represents read-only transaction options

Functions

func Exec 

func Exec(ctx context.Context, tx *sql.Tx, stmt *sql.Stmt, args ...any) error

Exec executes a SQL statement with the given arguments

func ExecWithTx 

func ExecWithTx(ctx context.Context, db *sql.DB, opts *sql.TxOptions, operation func(context.Context, *sql.Tx) error) error

ExecWithTx executes an operation within a transaction If a transaction already exists in the context, it will be reused

func IsNil 

func IsNil[T comparable](v T) bool

IsNil checks if a value is the zero value of its type

func Nil 

func Nil[T any]() T

Nil returns a zero value of type T

func Query 

func Query[T any](ctx context.Context, tx *sql.Tx, stmt *sql.Stmt, newReceiver func() T, dstFields func(T) []any, args ...any) ([]T, error)

Query executes a SQL query and returns a slice of results

func QueryOne 

func QueryOne[T any](ctx context.Context, tx *sql.Tx, stmt *sql.Stmt, newReceiver func() T, dstFields func(T) []any, args ...any) (T, error)

QueryOne executes a SQL query and returns a single result

func QueryVal 

func QueryVal[T any](ctx context.Context, tx *sql.Tx, stmt *sql.Stmt, args ...any) (T, error)

QueryVal executes a SQL query and returns a single scalar value

func QueryWithTx 

func QueryWithTx[T any](ctx context.Context, db *sql.DB, opts *sql.TxOptions, operation func(context.Context, *sql.Tx) (T, error)) (T, error)

QueryWithTx executes an operation that returns a result within a transaction If a transaction already exists in the context, it will be reused

func ToSliceOfAny 

func ToSliceOfAny[T any](slice ...T) []any

ToSliceOfAny converts a slice of values to a slice of interface{} values

Types

type BaseStmt 

type BaseStmt struct {
	Query string
	Cache bool
	// contains filtered or unexported fields
}

BaseStmt represents the base structure for all statement types

func (*BaseStmt) Close 

func (stmt *BaseStmt) Close(ctx context.Context) error

Close releases resources associated with the statement

type Dao 

type Dao[T Entity] interface {
	Save(ctx context.Context, entities ...T) error
	FindById(ctx context.Context, id uuid.UUID) (T, error)
	FindOneByStmt(ctx context.Context, stmt *QueryOneStmt[T], args ...any) (T, error)
	ListByStmt(ctx context.Context, stmt *QueryStmt[T], args ...any) ([]T, error)
	ListAll(ctx context.Context) ([]T, error)
	ListPageByStmt(ctx context.Context, stmt *QueryPageStmt[T], paging Paging, args ...any) (Page[T], error)
	ListPage(ctx context.Context, paging Paging) (Page[T], error)
	Delete(ctx context.Context, entities ...T) error
	DeleteCascade(ctx context.Context, entities ...T) error
	DeleteByIds(ctx context.Context, ids ...uuid.UUID) error
	DeleteByIdsCascade(ctx context.Context, ids ...uuid.UUID) error
	Close(ctx context.Context) error
}

Dao defines the interface for data access objects that manage entities

type DaoBuilder 

type DaoBuilder[T Entity] struct {
	//DB: SQL database connection to use for all operations
	DB *sql.DB
	//InsertStmt: Statement for inserting new entities
	InsertStmt *DaoExecStmt
	//UpdateStmt: Statement for updating existing entities
	UpdateStmt *DaoExecStmt
	//GetByIdStmt: Statement for retrieving a single entity by ID
	GetByIdStmt *DaoQueryOneStmt[T]
	//ListAllStmt: Statement for retrieving all entities
	ListAllStmt *DaoQueryStmt[T]
	//ListAllPageStmt: Statement for retrieving paginated results of all entities
	ListAllPageStmt *DaoQueryPageStmt[T]
	//DeleteByIdStmt: Statement for deleting entity by its ID
	DeleteByIdStmt *DaoExecStmt
	//NewReceiver: Function that returns a new instance of the entity
	NewReceiver func() T
	//Receive: Function that returns the arguments for the update statement for a given entity
	Receive func(T) []any
	//InsertArgs: Function that returns the arguments for the insert statement for a given entity
	InsertArgs func(T) []any
	//UpdateArgs: Function that returns the arguments for the update statement for a given entity
	UpdateArgs func(T) []any
	//SaveChildren: Function that saves child entities associated with the parent entity
	SaveChildren func(ctx context.Context, tx *sql.Tx, e T) error
	//LoadChildren: Function that loads child entities associated with the parent entity
	LoadChildren func(ctx context.Context, tx *sql.Tx, e T) error
	//DeleteChildren: Function that deletes child entities associated with the parent entity
	DeleteChildren func(ctx context.Context, tx *sql.Tx, e T) error
}

DaoBuilder builds new Dao[T] object with the provided parameters. All of the parameters are mandatory.

func (DaoBuilder[T]) Build 

func (b DaoBuilder[T]) Build(ctx context.Context) (Dao[T], error)

type DaoExecStmt added in v1.1.0 

type DaoExecStmt struct {
	Query string
	Cache bool
}

DaoExecStmt represents a statement that executes a command without returning rows

func (*DaoExecStmt) ToStmt added in v1.1.0 

func (s *DaoExecStmt) ToStmt() *ExecStmt

ToStmt converts a DaoExecStmt to an ExecStmt that can be used to execute an SQL command

type DaoQueryOneStmt added in v1.1.0 

type DaoQueryOneStmt[T any] struct {
	Query string
	Cache bool
}

DaoQueryOneStmt represents a statement that returns a single entity

func (*DaoQueryOneStmt[T]) ToStmt added in v1.1.0 

func (s *DaoQueryOneStmt[T]) ToStmt(newReceiver func() T, receive func(T) []any) *QueryOneStmt[T]

ToStmt converts a DaoQueryOneStmt to a QueryOneStmt that can be used to execute an SQL query

type DaoQueryPageStmt added in v1.1.0 

type DaoQueryPageStmt[T any] struct {
	CountStmt *DaoQueryValStmt[int]
	QueryStmt *DaoQueryStmt[T]
}

DaoQueryPageStmt represents a statement that returns a paginated result set

func (*DaoQueryPageStmt[T]) ToStmt added in v1.1.0 

func (s *DaoQueryPageStmt[T]) ToStmt(newReceiver func() T, receive func(T) []any) *QueryPageStmt[T]

ToStmt converts a DaoQueryPageStmt to a QueryPageStmt that can be used to execute an SQL query

type DaoQueryStmt added in v1.1.0 

type DaoQueryStmt[T any] struct {
	Query string
	Cache bool
}

DaoQueryStmt represents a statement that returns multiple entities

func (*DaoQueryStmt[T]) ToStmt added in v1.1.0 

func (s *DaoQueryStmt[T]) ToStmt(newReceiver func() T, receive func(T) []any) *QueryStmt[T]

ToStmt converts a DaoQueryStmt to a QueryStmt that can be used to execute an SQL query

type DaoQueryValStmt added in v1.1.0 

type DaoQueryValStmt[T any] struct {
	Query string
	Cache bool
}

DaoQueryValStmt represents a statement that returns a single scalar value

func (*DaoQueryValStmt[T]) ToStmt added in v1.1.0 

func (s *DaoQueryValStmt[T]) ToStmt() *QueryValStmt[T]

ToStmt converts a DaoQueryValStmt to a QueryValStmt that can be used to execute an SQL query

type Entity 

type Entity interface {
	comparable
	GetID() uuid.UUID
	SetID(uuid.UUID)
	GetVersion() uuid.UUID
	SetVersion(uuid.UUID)
	Equals(another any) bool
}

Entity defines the interface for database entities that can be managed by the DAO

type ExecStmt 

type ExecStmt struct {
	BaseStmt
}

ExecStmt represents a statement that executes a command without returning rows

func (*ExecStmt) Exec 

func (stmt *ExecStmt) Exec(ctx context.Context, tx *sql.Tx, args ...any) error

Exec executes a gosql statement with the given arguments

type GenericEntity 

type GenericEntity struct {
	ID      uuid.UUID `json:"id" yaml:"id"`
	Version uuid.UUID `json:"version" yaml:"version"`
}

GenericEntity is a base implementation of the Entity interface

func (*GenericEntity) GetID 

func (e *GenericEntity) GetID() uuid.UUID

GetID returns the entity's ID

func (*GenericEntity) GetVersion 

func (e *GenericEntity) GetVersion() uuid.UUID

GetVersion returns the entity's version

func (*GenericEntity) SetID 

func (e *GenericEntity) SetID(id uuid.UUID)

SetID sets the entity's ID

func (*GenericEntity) SetVersion 

func (e *GenericEntity) SetVersion(version uuid.UUID)

SetVersion sets the entity's version

type Page 

type Page[T any] struct {
	Items      []T `json:"items" yaml:"items"`
	TotalPages int `json:"totalPages" yaml:"totalPages"`
}

Page represents a paginated result set of items

func QueryPage 

func QueryPage[T any](ctx context.Context, tx *sql.Tx, countStmt, stmt *sql.Stmt, paging Paging, newReceiver func() T, dstFields func(T) []any, args ...any) (Page[T], error)

QueryPage executes a SQL query with pagination and returns a Page of results

type Paging 

type Paging struct {
	PageNum  int `json:"pageNum" yaml:"pageNum"`
	PageSize int `json:"pageSize" yaml:"pageSize"`
}

Paging represents pagination parameters

func (Paging) GetLimit 

func (p Paging) GetLimit() int

GetLimit returns the page size as a limit for SQL queries

func (Paging) GetOffset 

func (p Paging) GetOffset() int

GetOffset calculates the offset for SQL queries based on page number and size

func (Paging) GetTotalPages 

func (p Paging) GetTotalPages(totalRows int) int

GetTotalPages calculates the total number of pages based on total rows and page size

func (*Paging) Normalize 

func (p *Paging) Normalize()

Normalize ensures that pagination parameters have valid values

type QueryOneStmt 

type QueryOneStmt[T any] struct {
	BaseStmt
	NewReceiver func() T
	Receive     func(T) []any
}

QueryOneStmt represents a statement that returns a single entity

func (*QueryOneStmt[T]) Query 

func (stmt *QueryOneStmt[T]) Query(ctx context.Context, tx *sql.Tx, args ...any) (T, error)

Query executes a SQL query and returns a single entity

type QueryPageStmt 

type QueryPageStmt[T any] struct {
	CountStmt *QueryValStmt[int]
	QueryStmt *QueryStmt[T]
}

QueryPageStmt represents a statement that returns a paginated result set

func (*QueryPageStmt[T]) Close 

func (stmt *QueryPageStmt[T]) Close(ctx context.Context) error

Close releases resources associated with the paginated query statement

func (*QueryPageStmt[T]) QueryPage 

func (stmt *QueryPageStmt[T]) QueryPage(ctx context.Context, tx *sql.Tx, paging Paging, args ...any) (Page[T], error)

QueryPage executes a gosql query with pagination and returns a Page of results

type QueryStmt 

type QueryStmt[T any] struct {
	BaseStmt
	NewReceiver func() T
	Receive     func(T) []any
}

QueryStmt represents a statement that returns multiple entities

func (*QueryStmt[T]) Query 

func (stmt *QueryStmt[T]) Query(ctx context.Context, tx *sql.Tx, args ...any) ([]T, error)

Query executes a SQL query and returns multiple entities

type QueryValStmt 

type QueryValStmt[T any] struct {
	BaseStmt
}

QueryValStmt represents a statement that returns a single scalar value

func (*QueryValStmt[T]) Query 

func (stmt *QueryValStmt[T]) Query(ctx context.Context, tx *sql.Tx, args ...any) (T, error)

Query executes a SQL query and returns a single scalar value

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.