bunutils

A collection of powerful helper functions and utilities for Bun ORM, designed to streamline database operations and reduce boilerplate code in Go applications.

Features

🔍 Query Selectors: Composable query builders with type-safe WHERE conditions
🔄 Transaction Management: Context-aware transaction handling with nested transaction support
📊 Filtering Utilities: Pre-built Where struct for common filtering patterns
🏗️ Querier Interface: Context-aware query builders that automatically use transactions from context
❌ Error Handling: Specialized error checkers for common database errors

Database Compatibility

This library works with all databases supported by Bun (PostgreSQL, MySQL, SQLite, MSSQL). However, some features are PostgreSQL-specific:

JSONB Selectors: WhereJsonbEqual, WhereJsonbPathEqual, WhereJsonbObjectsArrayKeyValueEqual, WhereJsonbPathObjectsArrayKeyValueEqual - require PostgreSQL's JSONB support
Case-insensitive string matching: WhereContains, WhereBegins, WhereEnds - use PostgreSQL's ILIKE operator
DISTINCT ON: WhereDistinctOn - uses PostgreSQL's DISTINCT ON clause

All other features (transactions, basic selectors, querier interface, error handling, etc.) are database-agnostic and work across all supported databases.

TODO

Check versions less then 1.25 (1.23+, 1.24+)

Installation

go get github.com/nesymno/bunutils

Quick Start

package main

import (
    "context"
    "database/sql"
    
    "github.com/nesymno/bunutils"
    "github.com/uptrace/bun"
    "github.com/uptrace/bun/dialect/pgdialect"
    "github.com/uptrace/bun/driver/pgdriver"
)

func main() {
    // Setup Bun
    sqldb := sql.OpenDB(pgdriver.NewConnector(pgdriver.WithDSN("postgres://...")))
    db := bun.NewDB(sqldb, pgdialect.New())
    defer db.Close()
    
    ctx := context.Background()
    
    // Use selectors
    var users []User
    err := db.NewSelect().
        Model(&users).
        Apply(bunutils.WhereEqual("status", "active")).
        Apply(bunutils.WhereContains("email", "example.com")).
        Scan(ctx)
}

Documentation

1. Query Selectors

Build complex queries using composable selector functions:

Basic Selectors

// Equality checks
query.Apply(bunutils.WhereEqual("status", "active"))
query.Apply(bunutils.WhereNotEqual("role", "guest"))

// NULL checks
query.Apply(bunutils.WhereNull("deleted_at"))
query.Apply(bunutils.WhereNotNull("verified_at"))

// IN clauses
query.Apply(bunutils.WhereIn("id", []string{"1", "2", "3"}))
query.Apply(bunutils.WhereNotIn("status", []string{"banned", "suspended"}))

// String matching (case-insensitive, PostgreSQL ILIKE operator)
query.Apply(bunutils.WhereContains("name", "john"))  // ILIKE '%john%'
query.Apply(bunutils.WhereBegins("email", "admin")) // ILIKE 'admin%'
query.Apply(bunutils.WhereEnds("domain", ".com"))   // ILIKE '%.com'

// Time-based queries
query.Apply(bunutils.WhereBefore("created_at", time.Now()))
query.Apply(bunutils.WhereAfter("updated_at", lastWeek))

JSONB Selectors

Note: PostgreSQL only

Work with PostgreSQL JSONB columns safely:

// Simple JSONB field equality
query.Apply(bunutils.WhereJsonbEqual("metadata", "status", "verified"))
// Generates: metadata->>'status' = 'verified'

// Nested JSONB path equality
query.Apply(bunutils.WhereJsonbPathEqual("data", []string{"user", "profile", "name"}, "John"))
// Generates: data->'user'->'profile'->>'name' = 'John'

// JSONB array of objects - find object with matching field
query.Apply(bunutils.WhereJsonbObjectsArrayKeyValueEqual("tags", "items", "id", "123"))

// Nested array search
query.Apply(bunutils.WhereJsonbPathObjectsArrayKeyValueEqual(
    "metadata", 
    []string{"users", "preferences"}, 
    "theme", 
    "dark",
))

Combining Selectors

// Apply multiple conditions
query.Apply(
    bunutils.WhereEqual("status", "active"),
    bunutils.WhereNotNull("verified_at"),
)

// Conditional application
isAdmin := true
query.Apply(bunutils.ApplyIf(isAdmin, 
    bunutils.WhereEqual("role", "admin"),
))

// OR groups
query.Apply(bunutils.OrGroup(
    bunutils.WhereEqual("status", "active"),
    bunutils.WhereEqual("status", "pending"),
))

// AND groups
query.Apply(bunutils.AndGroup(
    bunutils.WhereEqual("verified", true),
    bunutils.WhereNotNull("email"),
))

// Complex OR conditions
query.Apply(bunutils.Or(
    bunutils.WhereEqual("role", "admin"),
    bunutils.WhereEqual("role", "moderator"),
    bunutils.WhereEqual("role", "editor"),
))

2. Transaction Management

Simple Transactions with InTx

The InTx function handles transaction lifecycle automatically:

err := bunutils.InTx(ctx, db, func(ctx context.Context) error {
    // Create user
    _, err := db.NewInsert().
        Model(&user).
        Exec(ctx)  // Automatically uses transaction
    if err != nil {
        return err // Transaction will rollback
    }
    
    // Create profile
    _, err = db.NewInsert().
        Model(&profile).
        Exec(ctx)
    if err != nil {
        return err // Transaction will rollback
    }
    
    return nil // Transaction will commit
})

Nested Transactions

InTx supports nested calls - only the outermost call creates the transaction:

func CreateUser(ctx context.Context, db *bun.DB, user *User) error {
    return bunutils.InTx(ctx, db, func(ctx context.Context) error {
        // This might create a new transaction OR use existing one
        _, err := db.NewInsert().Model(user).Exec(ctx)
        return err
    })
}

func CreateUserWithProfile(ctx context.Context, db *bun.DB, user *User, profile *Profile) error {
    return bunutils.InTx(ctx, db, func(ctx context.Context) error {
        // Outer transaction
        if err := CreateUser(ctx, db, user); err != nil {
            return err // Nested call uses same transaction
        }
        
        _, err := db.NewInsert().Model(profile).Exec(ctx)
        return err
    })
}

Manual Transaction Management

For more control, use TxToContext and TxFromContext:

tx, err := db.BeginTx(ctx, nil)
if err != nil {
    return err
}
defer tx.Rollback()

// Store transaction in context
ctx = bunutils.TxToContext(ctx, &tx)

// Pass context to functions
err = createUser(ctx, db)
if err != nil {
    return err
}

return tx.Commit()

3. Querier Interface

The Querier interface provides context-aware query builders:

type UserRepository struct {
    querier bunutils.Querier
}

func NewUserRepository(db *bun.DB) *UserRepository {
    return &UserRepository{
        querier: bunutils.NewQuerier(db),
    }
}

func (r *UserRepository) GetByID(ctx context.Context, id string) (*User, error) {
    user := new(User)
    
    // Automatically uses transaction from context if available
    err := r.querier.NewSelectQuery(ctx).
        Model(user).
        Where("id = ?", id).
        Scan(ctx)
    
    if bunutils.IsNotFoundError(err) {
        return nil, fmt.Errorf("user not found")
    }
    
    return user, err
}

func (r *UserRepository) Create(ctx context.Context, user *User) error {
    _, err := r.querier.NewInsertQuery(ctx).
        Model(user).
        Exec(ctx)
    
    if bunutils.IsConstraintError(err) {
        return fmt.Errorf("user already exists")
    }
    
    return err
}

4. Where Struct - Advanced Filtering

Use the pre-built Where struct for common filtering patterns:

// Build complex filters
where := &bunutils.Where{
    IDs: []string{"1", "2", "3"},
    NotInIDs: []string{"99"},
    OnlyDeleted: false,
    WithDeleted: false,
    Limit: ToPtr(10),
    Offset: ToPtr(0),
    CreatedAfter: ToPtr(time.Now().Add(-24*time.Hour).UnixMilli()),
    SelectColumns: []string{"id", "name", "email"},
    SortBy: 1,
    SortDesc: true,
}

// Define sort mapping
where.Order = bunutils.Order{
    1: "created_at",
    2: "updated_at",
    3: "name",
}

// Apply to query
query := db.NewSelect().Model(&users)
query = where.Where(query)   // Apply WHERE conditions
query = where.Select(query)  // Apply SELECT, LIMIT, ORDER BY

Bitwise Flag Filtering

const (
    FlagActive   = 1 << 0  // 1
    FlagVerified = 1 << 1  // 2
    FlagPremium  = 1 << 2  // 4
)

where := &bunutils.Where{
    HasFlags: []int{FlagActive, FlagVerified},    // Must have both flags
    HasNotFlags: []int{FlagPremium},              // Must not have flag
}

query := db.NewSelect().Model(&users)
query = where.Where(query)

Use as Selector

where := &bunutils.Where{IDs: []string{"1", "2"}}

query := db.NewSelect().
    Model(&users).
    Apply(bunutils.UseWhere(where)).
    Apply(bunutils.WhereEqual("status", "active"))

5. Error Handling

Specialized error handlers for common database scenarios:

user := new(User)
err := db.NewSelect().Model(user).Where("id = ?", id).Scan(ctx)

if bunutils.IsNotFoundError(err) {
    return fmt.Errorf("user not found")
}

// Check constraint violations
_, err = db.NewInsert().Model(user).Exec(ctx)

if bunutils.IsConstraintError(err) {
    return fmt.Errorf("user with this email already exists")
}

Complete Example

package main

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

    "github.com/nesymno/bunutils"
    "github.com/uptrace/bun"
    "github.com/uptrace/bun/dialect/pgdialect"
    "github.com/uptrace/bun/driver/pgdriver"
)

type User struct {
    bun.BaseModel `bun:"table:users"`
    
    ID        string    `bun:"id,pk"`
    Email     string    `bun:"email,notnull,unique"`
    Name      string    `bun:"name"`
    Status    string    `bun:"status"`
    Role      string    `bun:"role"`
    Flags     int       `bun:"flags"`
    CreatedAt time.Time `bun:"created_at,nullzero,notnull,default:current_timestamp"`
}

// Custom Where struct that embeds bunutils.Where
// This allows you to add domain-specific filtering while reusing common filters
type UserWhere struct {
    bunutils.Where                          // Embed base Where for common filters
    
    Status        string   `json:"status,omitempty" form:"status"`
    Roles         []string `json:"roles,omitempty" form:"roles"`
    EmailContains string   `json:"email_contains,omitempty" form:"email_contains"`
    IsVerified    *bool    `json:"is_verified,omitempty" form:"is_verified"`
}

// Apply custom WHERE conditions, then delegate to base Where
func (w *UserWhere) ApplyFilters(q *bun.SelectQuery) *bun.SelectQuery {
    if w == nil {
        return q
    }
    
    // Apply custom domain-specific filters
    if w.Status != "" {
        q = q.Where("?TableAlias.status = ?", w.Status)
    }
    if len(w.Roles) > 0 {
        q = q.Where("?TableAlias.role IN (?)", bun.In(w.Roles))
    }
    if w.EmailContains != "" {
        q = q.Where("?TableAlias.email ILIKE ?", "%"+w.EmailContains+"%")
    }
    if w.IsVerified != nil {
        if *w.IsVerified {
            q = q.WhereGroup(" AND ", func(q *bun.SelectQuery) *bun.SelectQuery {
                return q.Where("?TableAlias.flags & ? = ?", 1, 1)
            })
        } else {
            q = q.Where("?TableAlias.flags & ? = 0", 1)
        }
    }
    
    // Apply base filters (IDs, flags, dates, soft deletes, etc.)
    q = w.Where.Where(q)
    
    return q
}

type UserRepository struct {
    querier bunutils.Querier
}

func NewUserRepository(db *bun.DB) *UserRepository {
    return &UserRepository{
        querier: bunutils.NewQuerier(db),
    }
}

func (r *UserRepository) Find(ctx context.Context, where *UserWhere) ([]*User, error) {
    var users []*User
    
    q := r.querier.NewSelectQuery(ctx).Model(&users)
    
    // Apply custom filters
    q = where.ApplyFilters(q)
    
    // Apply SELECT, LIMIT, OFFSET, ORDER BY from base Where
    q = where.Select(q)
    
    err := q.Scan(ctx)
    return users, err
}

func (r *UserRepository) Create(ctx context.Context, user *User) error {
    _, err := r.querier.NewInsertQuery(ctx).
        Model(user).
        Exec(ctx)
    
    if bunutils.IsConstraintError(err) {
        return fmt.Errorf("user already exists")
    }
    
    return err
}

func ToPtr[T any](v T) *T {
    return &v
}

func main() {
    // Setup database
    dsn := "postgres://user:pass@localhost:5432/dbname?sslmode=disable"
    sqldb := sql.OpenDB(pgdriver.NewConnector(pgdriver.WithDSN(dsn)))
    db := bun.NewDB(sqldb, pgdialect.New())
    defer db.Close()
    
    repo := NewUserRepository(db)
    ctx := context.Background()
    
    // Example 1: Using custom UserWhere with both custom and base filters
    where := &UserWhere{
        // Custom filters
        Status:        "active",
        Roles:         []string{"admin", "moderator"},
        EmailContains: "example.com",
        IsVerified:    ToPtr(true),
        
        // Base filters from bunutils.Where
        Where: bunutils.Where{
            IDs:          []string{"1", "2", "3"},
            CreatedAfter: ToPtr(time.Now().Add(-7*24*time.Hour).UnixMilli()),
            Limit:        ToPtr(10),
            Offset:       ToPtr(0),
            SortBy:       1,
            SortDesc:     true,
            Order: bunutils.Order{
                1: "created_at",
                2: "email",
                3: "name",
            },
        },
    }
    
    users, err := repo.Find(ctx, where)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("Found %d users matching filters\n", len(users))
    
    // Example 2: Transaction with InTx
    err = bunutils.InTx(ctx, db, func(ctx context.Context) error {
        user := &User{
            ID:     "123",
            Email:  "test@example.com",
            Name:   "Test User",
            Status: "active",
            Role:   "user",
        }
        
        return repo.Create(ctx, user)
    })
    
    if err != nil {
        log.Fatal(err)
    }
    
    // Example 3: Using base bunutils.Where directly
    baseWhere := &bunutils.Where{
        IDs:   []string{"1", "2", "3"},
        Limit: ToPtr(10),
        Order: bunutils.Order{1: "created_at"},
        SortBy:   1,
        SortDesc: true,
    }
    
    var moreUsers []*User
    query := db.NewSelect().Model(&moreUsers)
    query = baseWhere.Where(query)
    query = baseWhere.Select(query)
    
    if err := query.Scan(ctx); err != nil {
        log.Fatal(err)
    }
    
    fmt.Printf("Found %d users using base Where\n", len(moreUsers))
}

API Reference

Selectors

Apply(selectors ...Selector) Selector - Combine multiple selectors
ApplyIf(cond bool, selectors ...Selector) Selector - Conditionally apply selectors
OrGroup(selectors ...Selector) Selector - Create OR group
AndGroup(selectors ...Selector) Selector - Create AND group
Or(selectors ...Selector) Selector - Separate conditions with OR
UseWhere(where *Where) Selector - Use Where struct as selector
WhereEqual(col string, value any) Selector
WhereNotEqual(col string, value any) Selector
WhereNull(col string) Selector
WhereNotNull(col string) Selector
WhereIn(col string, values any) Selector
WhereNotIn(col string, values any) Selector
WhereContains(col string, substr string) Selector (PostgreSQL ILIKE)
WhereBegins(col string, substr string) Selector (PostgreSQL ILIKE)
WhereEnds(col string, substr string) Selector (PostgreSQL ILIKE)
WhereBefore(col string, t time.Time) Selector
WhereAfter(col string, t time.Time) Selector
WhereDistinctOn(col string) Selector (PostgreSQL only)
WhereJsonbEqual(col string, field string, value any) Selector (PostgreSQL only)
WhereJsonbPathEqual(col string, path []string, value any) Selector (PostgreSQL only)
WhereJsonbObjectsArrayKeyValueEqual(col string, key, field string, value any) Selector (PostgreSQL only)
WhereJsonbPathObjectsArrayKeyValueEqual(col string, path []string, field string, value any) Selector (PostgreSQL only)

Transaction Context

InTx(ctx context.Context, client *bun.DB, fn func(ctx context.Context) error) error - Execute function in transaction
TxToContext(ctx context.Context, tx *bun.Tx) context.Context - Store transaction in context
TxFromContext(ctx context.Context) *bun.Tx - Retrieve transaction from context

Error Handling

IsNotFoundError(err error) bool - Check if error is sql.ErrNoRows
IsConstraintError(err error) bool - Check for unique constraint violations

Querier Interface

NewQuerier(c *bun.DB) Querier - Create new querier
NewSelectQuery(ctx context.Context) *bun.SelectQuery - Get context-aware SELECT query
NewInsertQuery(ctx context.Context) *bun.InsertQuery - Get context-aware INSERT query
NewUpdateQuery(ctx context.Context) *bun.UpdateQuery - Get context-aware UPDATE query
NewDeleteQuery(ctx context.Context) *bun.DeleteQuery - Get context-aware DELETE query

Utilities

OrderAsc(col string) string - Create ascending order expression
OrderDesc(col string) string - Create descending order expression

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

Changeset Workflow

This project uses a changeset-based workflow for versioning and releases, similar to npm changesets.

Adding a Changeset

When you make changes that should be released, add a changeset:

just changeset

This will:

Prompt you for the type of change (major/minor/patch)
Ask for a description of your changes
Create a changeset file in .changeset/

Include this changeset file in your Pull Request.

Changeset Types

patch - Bug fixes and minor changes (0.0.X)
minor - New features, backwards compatible (0.X.0)
major - Breaking changes (X.0.0)

Checking Next Version

To see what version will be released based on current changesets:

just version

Creating a Release

When ready to release, run:

just release

This will:

Calculate the next version based on changesets
Update CHANGELOG.md with changes
Remove processed changeset files
Commit and push changes
Create and push a version tag
Trigger the GitHub Actions release workflow

License

This project is licensed under the MIT License - see the LICENSE file for details.

Acknowledgments

Built on top of the excellent Bun ORM
Ukrainian Golang Community @uagolang
Kuberly.io - your devs should work on product, not on infra

Name		Name	Last commit message	Last commit date
.changeset		.changeset
.github/workflows		.github/workflows
scripts		scripts
.gitignore		.gitignore
.goreleaser.yaml		.goreleaser.yaml
CHANGELOG.md		CHANGELOG.md
LICENSE		LICENSE
README.md		README.md
RELEASE.md		RELEASE.md
errors.go		errors.go
errors_test.go		errors_test.go
go.mod		go.mod
go.sum		go.sum
justfile		justfile
querier.go		querier.go
querier_test.go		querier_test.go
selectors.go		selectors.go
selectors_test.go		selectors_test.go
testing.go		testing.go
tx.go		tx.go
tx_test.go		tx_test.go
utils.go		utils.go
where.go		where.go
where_test.go		where_test.go

Latest commit

History

Folders and files

Repository files navigation