dbrepo

package
v0.1.4 Latest Latest
Warning

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

 Go to latest Published: Apr 3, 2026 License: MIT Imports: 7 Imported by: 0

Documentation

Overview

Package dbrepo provides composable SQL fragment helpers for building queries.

Functions in this package use @Name placeholders (e.g., @ID, @Name) which rely on database/sql's sql.Named() for driver-level parameter translation. This is distinct from the chuck.Dialect.Placeholder() method which returns engine-specific positional syntax ($1, ?, @p1) for raw SQL composition.

The @Name convention works because database/sql drivers translate sql.NamedArg values into their native parameter syntax at execution time. This means dbrepo output is dialect-agnostic — the same query string works across all engines when paired with sql.Named() arguments.

For identifier quoting, use the Q-suffixed variants (ColumnsQ, SetClauseQ, InsertIntoQ) which accept a chuck.Dialect and quote table/column names via QuoteIdentifier.

Index

Examples

Constants

This section is empty.

Variables

View Source 
var NowFunc = time.Now

NowFunc is the function used to get the current time. Override this in tests to freeze or control time: 

dbrepo.NowFunc = func() time.Time { return fixedTime }

Functions

func BuildOrderByClause 

func BuildOrderByClause(sortStr string, columnMap map[string]string, defaultSort string) string

BuildOrderByClause builds an ORDER BY clause from a sort string. columnMap maps user-friendly column names to SQL column names. defaultSort is used when sortStr is empty or produces no valid columns.

Example 
package main

import (
	"fmt"

	"github.com/catgoose/chuck/dbrepo"
)

func main() {
	columnMap := map[string]string{
		"name":       "Name",
		"created_at": "CreatedAt",
	}

	fmt.Println(dbrepo.BuildOrderByClause("name:asc", columnMap, "ID ASC"))
	fmt.Println(dbrepo.BuildOrderByClause("", columnMap, "ID ASC"))
	fmt.Println(dbrepo.BuildOrderByClause("unknown:desc", columnMap, "ID ASC"))
}

Output:
ORDER BY Name ASC
ORDER BY ID ASC
ORDER BY ID ASC

func BuildSearchCondition 

func BuildSearchCondition(search, searchPattern string, fields ...string) string

BuildSearchCondition builds a search condition for WHERE clauses. Returns "(field1 LIKE @SearchPattern OR field2 LIKE @SearchPattern)".

func BuildSearchPattern 

func BuildSearchPattern(search string) string

BuildSearchPattern builds a LIKE search pattern from a search string. Returns "%search%" or empty string if search is empty.

func ClearArchive 

func ClearArchive(archivedAt *sql.NullTime)

ClearArchive sets a sql.NullTime to NULL (Valid = false) to unarchive.

func ClearExpiry 

func ClearExpiry(expiresAt *sql.NullTime)

ClearExpiry sets a sql.NullTime to NULL (Valid = false) to remove expiry.

func ClearReplacement 

func ClearReplacement(replacedByID *sql.NullInt64)

ClearReplacement sets a sql.NullInt64 to NULL (Valid = false).

func Columns 

func Columns(cols ...string) string

Columns joins column names into a comma-separated list. 

Columns("ID", "Name", "Email") => "ID, Name, Email"
Example 
package main

import (
	"fmt"

	"github.com/catgoose/chuck/dbrepo"
)

func main() {
	fmt.Println(dbrepo.Columns("ID", "Name", "Email"))
}

Output:
ID, Name, Email

func ColumnsQ 

func ColumnsQ(d chuck.Identifier, cols ...string) string

ColumnsQ joins column names into a comma-separated list with dialect quoting. 

ColumnsQ(d, "ID", "Name", "Email") => `"ID", "Name", "Email"` (Postgres/SQLite)
Example 
package main

import (
	"fmt"

	"github.com/catgoose/chuck"
	"github.com/catgoose/chuck/dbrepo"
)

func main() {
	d, _ := chuck.New(chuck.Postgres)
	fmt.Println(dbrepo.ColumnsQ(d, "ID", "Name", "Email"))
}

Output:
"ID", "Name", "Email"

func GetNow 

func GetNow() time.Time

GetNow returns the current time via NowFunc.

func IncrementVersion 

func IncrementVersion(version *int)

IncrementVersion increments Version by 1.

func InitVersion 

func InitVersion(version *int)

InitVersion sets Version to 1 for a new record.

Example 
package main

import (
	"fmt"

	"github.com/catgoose/chuck/dbrepo"
)

func main() {
	var version int
	dbrepo.InitVersion(&version)
	fmt.Println("Version:", version)

	dbrepo.IncrementVersion(&version)
	fmt.Println("After increment:", version)
}

Output:
Version: 1
After increment: 2

func InsertInto 

func InsertInto(table string, cols ...string) string

InsertInto builds a full INSERT INTO … VALUES … statement. 

InsertInto("Users", "Name", "Email") =>
  "INSERT INTO Users (Name, Email) VALUES (@Name, @Email)"
Example 
package main

import (
	"fmt"

	"github.com/catgoose/chuck/dbrepo"
)

func main() {
	fmt.Println(dbrepo.InsertInto("Users", "Name", "Email"))
}

Output:
INSERT INTO Users (Name, Email) VALUES (@Name, @Email)

func InsertIntoQ 

func InsertIntoQ(d chuck.Identifier, table string, cols ...string) string

InsertIntoQ builds a full INSERT INTO … VALUES … statement with dialect quoting. 

InsertIntoQ(d, "Users", "Name", "Email") =>
  `INSERT INTO "Users" ("Name", "Email") VALUES (@Name, @Email)` (Postgres/SQLite)
Example 
package main

import (
	"fmt"

	"github.com/catgoose/chuck"
	"github.com/catgoose/chuck/dbrepo"
)

func main() {
	d, _ := chuck.New(chuck.Postgres)
	fmt.Println(dbrepo.InsertIntoQ(d, "Users", "Name", "Email"))
}

Output:
INSERT INTO "Users" ("Name", "Email") VALUES (@Name, @Email)

func NamedArgs 

func NamedArgs(m map[string]any) []any

NamedArgs converts a map to a slice of sql.NamedArg values suitable for passing to database/sql query methods. Keys are sorted for deterministic output.

Example 
package main

import (
	"database/sql"
	"fmt"

	"github.com/catgoose/chuck/dbrepo"
)

func main() {
	args := dbrepo.NamedArgs(map[string]any{
		"Name":  "Alice",
		"Email": "alice@test.com",
	})
	// Keys are sorted for deterministic output
	for _, arg := range args {
		na := arg.(sql.NamedArg)
		fmt.Printf("%s=%v\n", na.Name, na.Value)
	}
}

Output:
Email=alice@test.com
Name=Alice

func Placeholders 

func Placeholders(cols ...string) string

Placeholders returns named placeholders for the given columns. 

Placeholders("ID", "Name", "Email") => "@ID, @Name, @Email"
Example 
package main

import (
	"fmt"

	"github.com/catgoose/chuck/dbrepo"
)

func main() {
	fmt.Println(dbrepo.Placeholders("ID", "Name", "Email"))
}

Output:
@ID, @Name, @Email

func SetArchive 

func SetArchive(archivedAt *time.Time)

SetArchive sets ArchivedAt to the current time.

func SetClause 

func SetClause(cols ...string) string

SetClause builds a SET fragment for UPDATE statements. 

SetClause("Name", "Email") => "Name = @Name, Email = @Email"
Example 
package main

import (
	"fmt"

	"github.com/catgoose/chuck/dbrepo"
)

func main() {
	fmt.Println(dbrepo.SetClause("Name", "Email"))
}

Output:
Name = @Name, Email = @Email

func SetClauseQ 

func SetClauseQ(d chuck.Identifier, cols ...string) string

SetClauseQ builds a SET fragment for UPDATE statements with dialect quoting. 

SetClauseQ(d, "Name", "Email") => `"Name" = @Name, "Email" = @Email` (Postgres/SQLite)
Example 
package main

import (
	"fmt"

	"github.com/catgoose/chuck"
	"github.com/catgoose/chuck/dbrepo"
)

func main() {
	d, _ := chuck.New(chuck.Postgres)
	fmt.Println(dbrepo.SetClauseQ(d, "Name", "Email"))
}

Output:
"Name" = @Name, "Email" = @Email

func SetCreateAudit 

func SetCreateAudit(createdBy, updatedBy *string, user string)

SetCreateAudit sets CreatedBy and UpdatedBy for a new record.

func SetCreateTimestamps 

func SetCreateTimestamps(createdAt, updatedAt *time.Time)

SetCreateTimestamps sets CreatedAt and UpdatedAt to current time.

Example 
package main

import (
	"fmt"
	"time"

	"github.com/catgoose/chuck/dbrepo"
)

func main() {
	// Freeze time for deterministic output
	fixed := time.Date(2025, 6, 15, 12, 0, 0, 0, time.UTC)
	dbrepo.NowFunc = func() time.Time { return fixed }
	defer func() { dbrepo.NowFunc = time.Now }()

	type Task struct {
		CreatedAt time.Time
		UpdatedAt time.Time
	}

	var t Task
	dbrepo.SetCreateTimestamps(&t.CreatedAt, &t.UpdatedAt)

	fmt.Println("CreatedAt:", t.CreatedAt.Format(time.RFC3339))
	fmt.Println("UpdatedAt:", t.UpdatedAt.Format(time.RFC3339))
}

Output:
CreatedAt: 2025-06-15T12:00:00Z
UpdatedAt: 2025-06-15T12:00:00Z

func SetDeleteAudit 

func SetDeleteAudit(deletedAt *time.Time, deletedBy *string, user string)

SetDeleteAudit sets DeletedAt and DeletedBy for a soft-delete with audit trail.

func SetExpiry 

func SetExpiry(expiresAt *time.Time, t time.Time)

SetExpiry sets ExpiresAt to the given time.

func SetReplacement 

func SetReplacement(replacedByID *int64, id int64)

SetReplacement sets ReplacedByID to the given ID.

func SetSoftDelete 

func SetSoftDelete(deletedAt *time.Time)

SetSoftDelete sets DeletedAt to the current time for soft-delete.

func SetSortOrder 

func SetSortOrder(sortOrder *int, order int)

SetSortOrder sets the SortOrder value.

func SetStatus 

func SetStatus(status *string, value string)

SetStatus sets the Status value.

Example 
package main

import (
	"fmt"

	"github.com/catgoose/chuck/dbrepo"
)

func main() {
	var status string
	dbrepo.SetStatus(&status, "published")
	fmt.Println(status)
}

Output:
published

func SetUpdateAudit 

func SetUpdateAudit(updatedBy *string, user string)

SetUpdateAudit sets UpdatedBy for an updated record.

func SetUpdateTimestamp 

func SetUpdateTimestamp(updatedAt *time.Time)

SetUpdateTimestamp sets UpdatedAt to current time.

Types

type SelectBuilder 

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

SelectBuilder constructs composable SELECT queries with WHERE, ORDER BY, and pagination.

func NewSelect 

func NewSelect(table string, cols ...string) *SelectBuilder

NewSelect creates a new SelectBuilder for the given table and columns.

Example 
package main

import (
	"fmt"

	"github.com/catgoose/chuck/dbrepo"
)

func main() {
	query, _ := dbrepo.NewSelect("Tasks", "ID", "Title", "Status").
		Where(
			dbrepo.NewWhere().
				NotDeleted().
				HasStatus("active"),
		).
		OrderBy("Title ASC").
		Paginate(20, 0).
		Build()

	fmt.Println(query)
}

Output:
SELECT ID, Title, Status FROM Tasks WHERE DeletedAt IS NULL AND Status = @Status ORDER BY Title ASC LIMIT @Limit OFFSET @Offset

func (*SelectBuilder) Build 

func (s *SelectBuilder) Build() (query string, args []any)

Build returns the complete SQL query string and the collected arguments.

func (*SelectBuilder) CountQuery 

func (s *SelectBuilder) CountQuery() (query string, args []any)

CountQuery returns a COUNT(*) query using the same FROM and WHERE clauses.

Example 
package main

import (
	"fmt"

	"github.com/catgoose/chuck/dbrepo"
)

func main() {
	w := dbrepo.NewWhere().HasStatus("active")

	sb := dbrepo.NewSelect("Tasks", "ID", "Title").Where(w)
	countQuery, _ := sb.CountQuery()

	fmt.Println(countQuery)
}

Output:
SELECT COUNT(*) FROM Tasks WHERE Status = @Status

func (*SelectBuilder) OrderBy 

func (s *SelectBuilder) OrderBy(clause string) *SelectBuilder

OrderBy sets the ORDER BY clause (e.g., "Name ASC" or "CreatedAt DESC, Name ASC").

func (*SelectBuilder) OrderByMap 

func (s *SelectBuilder) OrderByMap(sortStr string, columnMap map[string]string, defaultSort string) *SelectBuilder

OrderByMap builds an ORDER BY clause from a sort string and column map, with a default fallback. The sortStr format is "column:direction" (e.g., "name:asc" or "created_at:desc"). Multiple sorts can be separated by commas: "name:asc,created_at:desc".

Example 
package main

import (
	"fmt"

	"github.com/catgoose/chuck/dbrepo"
)

func main() {
	columnMap := map[string]string{
		"title":      "Title",
		"created_at": "CreatedAt",
		"status":     "Status",
	}

	query, _ := dbrepo.NewSelect("Tasks", "ID", "Title", "Status").
		OrderByMap("title:asc,created_at:desc", columnMap, "ID ASC").
		Build()

	fmt.Println(query)
}

Output:
SELECT ID, Title, Status FROM Tasks ORDER BY Title ASC, CreatedAt DESC

func (*SelectBuilder) Paginate 

func (s *SelectBuilder) Paginate(limit, offset int) *SelectBuilder

Paginate sets LIMIT and OFFSET for pagination.

func (*SelectBuilder) Where 

func (s *SelectBuilder) Where(w *WhereBuilder) *SelectBuilder

Where sets the WhereBuilder for filtering.

func (*SelectBuilder) WithDialect 

func (s *SelectBuilder) WithDialect(d chuck.Dialect) *SelectBuilder

WithDialect sets the dialect for pagination clause generation.

Example 
package main

import (
	"database/sql"
	"fmt"

	"github.com/catgoose/chuck"
	"github.com/catgoose/chuck/dbrepo"
)

func main() {
	d, _ := chuck.New(chuck.Postgres)

	query, _ := dbrepo.NewSelect("tasks", "id", "title").
		Where(dbrepo.NewWhere().And("id = @ID", sql.Named("ID", 1))).
		WithDialect(d).
		Build()

	fmt.Println(query)
}

Output:
SELECT id, title FROM "tasks" WHERE id = @ID

type SortField 

type SortField struct {
	Column    string
	Direction string
}

SortField represents a column and sort direction.

func ParseSortString 

func ParseSortString(sortStr string) []SortField

ParseSortString parses a sort string like "name:asc,created_at:desc" into column/direction pairs.

type WhereBuilder 

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

WhereBuilder constructs composable WHERE clauses with named parameters.

Example (DomainFilters) 
package main

import (
	"fmt"

	"github.com/catgoose/chuck/dbrepo"
)

func main() {
	w := dbrepo.NewWhere().
		NotDeleted().
		NotExpired().
		HasStatus("active").
		NotReplaced()

	fmt.Println(w.String())
}

Output:
WHERE DeletedAt IS NULL AND (ExpiresAt IS NULL OR ExpiresAt > CURRENT_TIMESTAMP) AND Status = @Status AND ReplacedByID IS NULL
Example (TreeQueries) 
package main

import (
	"fmt"

	"github.com/catgoose/chuck/dbrepo"
)

func main() {
	// Find root nodes
	roots := dbrepo.NewWhere().IsRoot()
	fmt.Println("Roots:", roots.String())

	// Find children of a specific node
	children := dbrepo.NewWhere().HasParent(42)
	fmt.Println("Children:", children.String())
}

Output:
Roots: WHERE ParentID IS NULL
Children: WHERE ParentID = @ParentID

func NewWhere 

func NewWhere() *WhereBuilder

NewWhere creates a new WhereBuilder.

Example 
package main

import (
	"database/sql"
	"fmt"

	"github.com/catgoose/chuck/dbrepo"
)

func main() {
	w := dbrepo.NewWhere().
		And("DepartmentID = @DeptID", sql.Named("DeptID", 5)).
		And("Active = @Active", sql.Named("Active", true))

	fmt.Println(w.String())
}

Output:
WHERE DepartmentID = @DeptID AND Active = @Active

func (*WhereBuilder) And 

func (w *WhereBuilder) And(condition string, args ...any) *WhereBuilder

And adds an AND condition with optional named args.

func (*WhereBuilder) AndIf 

func (w *WhereBuilder) AndIf(ok bool, condition string, args ...any) *WhereBuilder

AndIf adds an AND condition only when ok is true.

Example 
package main

import (
	"database/sql"
	"fmt"

	"github.com/catgoose/chuck/dbrepo"
)

func main() {
	// AndIf only adds the condition when the first argument is true.
	// This is useful for optional filters from user input.
	searchTerm := "alice"
	minAge := 0 // zero means no filter

	w := dbrepo.NewWhere().
		And("Active = 1").
		AndIf(searchTerm != "", "Name LIKE @Pattern", sql.Named("Pattern", "%"+searchTerm+"%")).
		AndIf(minAge > 0, "Age >= @MinAge", sql.Named("MinAge", minAge))

	fmt.Println(w.String())
}

Output:
WHERE Active = 1 AND Name LIKE @Pattern

func (*WhereBuilder) Args 

func (w *WhereBuilder) Args() []any

Args returns the collected named arguments.

func (*WhereBuilder) HasConditions 

func (w *WhereBuilder) HasConditions() bool

HasConditions reports whether any conditions have been added.

func (*WhereBuilder) HasParent 

func (w *WhereBuilder) HasParent(parentID int64, col ...string) *WhereBuilder

HasParent adds a "ParentID = @ParentID" condition. Pass an optional column name to override the default "ParentID".

func (*WhereBuilder) HasStatus 

func (w *WhereBuilder) HasStatus(status string, col ...string) *WhereBuilder

HasStatus adds a "Status = @Status" condition. Pass an optional column name to override the default "Status".

func (*WhereBuilder) HasVersion 

func (w *WhereBuilder) HasVersion(version int, col ...string) *WhereBuilder

HasVersion adds a "Version = @Version" condition for optimistic locking. Pass an optional column name to override the default "Version".

func (*WhereBuilder) IsRoot 

func (w *WhereBuilder) IsRoot(col ...string) *WhereBuilder

IsRoot adds a "ParentID IS NULL" condition for tree root nodes. Pass an optional column name to override the default "ParentID".

func (*WhereBuilder) NotArchived 

func (w *WhereBuilder) NotArchived(col ...string) *WhereBuilder

NotArchived adds an "ArchivedAt IS NULL" condition for archive filtering. Pass an optional column name to override the default "ArchivedAt".

func (*WhereBuilder) NotArchivedBool 

func (w *WhereBuilder) NotArchivedBool(col ...string) *WhereBuilder

NotArchivedBool adds a condition for boolean archived columns. Generates "NOT archived" for Postgres/SQLite, "archived = 0" for MSSQL. Pass an optional column name to override the default "archived".

func (*WhereBuilder) NotDeleted 

func (w *WhereBuilder) NotDeleted(col ...string) *WhereBuilder

NotDeleted adds a "DeletedAt IS NULL" condition for soft-delete filtering. Pass an optional column name to override the default "DeletedAt".

Example 
package main

import (
	"fmt"

	"github.com/catgoose/chuck/dbrepo"
)

func main() {
	w := dbrepo.NewWhere().NotDeleted()
	fmt.Println(w.String())

	// With a custom column name for snake_case schemas
	w2 := dbrepo.NewWhere().NotDeleted("deleted_at")
	fmt.Println(w2.String())
}

Output:
WHERE DeletedAt IS NULL
WHERE deleted_at IS NULL

func (*WhereBuilder) NotExpired 

func (w *WhereBuilder) NotExpired(col ...string) *WhereBuilder

NotExpired adds a condition that filters out expired records. Pass an optional column name to override the default "ExpiresAt".

func (*WhereBuilder) NotReplaced 

func (w *WhereBuilder) NotReplaced(col ...string) *WhereBuilder

NotReplaced adds a "ReplacedByID IS NULL" condition for current (non-replaced) records. Pass an optional column name to override the default "ReplacedByID".

func (*WhereBuilder) Or 

func (w *WhereBuilder) Or(condition string, args ...any) *WhereBuilder

Or adds an OR branch to the previous condition.

func (*WhereBuilder) OrIf 

func (w *WhereBuilder) OrIf(ok bool, condition string, args ...any) *WhereBuilder

OrIf adds an OR condition only when ok is true.

func (*WhereBuilder) ReplacedBy 

func (w *WhereBuilder) ReplacedBy(id int64, col ...string) *WhereBuilder

ReplacedBy adds a "ReplacedByID = @ReplacedByID" condition. Pass an optional column name to override the default "ReplacedByID".

func (*WhereBuilder) Search 

func (w *WhereBuilder) Search(search string, fields ...string) *WhereBuilder

Search adds a LIKE search condition across the given fields. When a dialect is set via WithDialect, Postgres uses ILIKE for case-insensitive matching. Field names are validated to prevent SQL injection — only alphanumeric characters, underscores, and dots (for qualified names) are allowed.

Example 
package main

import (
	"fmt"

	"github.com/catgoose/chuck"
	"github.com/catgoose/chuck/dbrepo"
)

func main() {
	d, _ := chuck.New(chuck.Postgres)

	w := dbrepo.NewWhere().WithDialect(d).
		NotDeleted("deleted_at").
		Search("chuck", "name", "bio")

	fmt.Println(w.String())
}

Output:
WHERE deleted_at IS NULL AND (name ILIKE @SearchPattern OR bio ILIKE @SearchPattern)

func (*WhereBuilder) String 

func (w *WhereBuilder) String() string

String returns the full WHERE clause or an empty string when no conditions exist.

func (*WhereBuilder) WithDialect 

func (w *WhereBuilder) WithDialect(d chuck.Dialect) *WhereBuilder

WithDialect sets the dialect for dialect-aware operations (e.g. ILIKE on Postgres).

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.