sqb

README

SQL Builder

The library bases on two ideas:

• an SQL statement expressed with this library should be similar to the statement itself;
• flexible combination of statement components.

Thus, using this library you get simple but, at the same time, powerful tool to build SQL statements in flexible and intuitive way.

package main

import (
	"fmt"
	"github.com/AlephTav/sqb"
	sql "github.com/AlephTav/sqb/postgresql"
)

func main() {
	st := sql.NewSelectStmt(NewStatementExecutor()).
		Select(sqb.Map(
			"", "u.id",
			"", "u.name",
			"company", "c.name",
			"unreadMessageCount", sql.NewSelectStmt(nil).
				Select("COUNT(*)").
				From("user_messages", "m").
				Where("m.user_id = u.id").
				AndWhere("m.read_at IS NULL"),
		)).
		From("users u").
		InnerJoin("companies c", "c.id = u.company_id").
		Where("u.deleted_at IS NULL").
		AndWhere(sql.NewCondExp().
			Where("u.roles", "IN", []any{"ADMIN", "RESELLER"}).
			OrWhere(
				sql.NewSelectStmt(nil).
					Select("COUNT(*)").
					From("user_contacts uc").
					Where("uc.user_id = u.id"),
				">",
				5,
			))

	// Outputs: 
	// SELECT
	//     u.id, u.name, c.name company,
	//     (SELECT COUNT(*) FROM user_messages m WHERE m.user_id = u.id AND m.read_at IS NULL) unreadMessageCount
	// FROM users u
	// INNER JOIN companies c ON c.id = u.company_id
	// WHERE
	//     u.deleted_at IS NULL AND (
	//         u.roles IN (:p1, :p2) OR 
	//         (SELECT COUNT(*) FROM user_contacts uc WHERE uc.user_id = u.id) > :p3
	//     )
	fmt.Println(st.String())

	// Outputs:
	// map[string]any{"p1": "ADMIN", "p2": "RESELLER", "p3": 5}
	fmt.Printf("%#v", st.Params())

	// Executes statement if StatementExecutor is defined, otherwise it panics
	rows, err := st.Rows()
}

Installation

go install github.com/AlephTav/sqb

Documentation

Index

• func CheckParams(t *testing.T, expected any, actual map[string]any)
• func CheckSql(t *testing.T, expected any, actual string)
• func Keys(m SliceMap) []any
• func MapKeys[K comparable, V any](m map[K]V) []K
• func MapValues[K comparable, V any](m map[K]V) []V
• func NextParameterName() string
• func ResetParameterIndex()
• func ToInt64(v any) (int64, error)
• func ToSliceMapSlice[K comparable, V any](values []map[K]V) []any
• func Values(m SliceMap) []any
• type ColumnsAwareStmt
• type Command
• type CommandStmt
• type Query
• type QueryStmt
• type SliceMap
  • func Map(kv ...any) SliceMap
  • func ToSliceMap[K comparable, V any](values map[K]V) SliceMap
• type Statement
• type StatementExecutor
• type StatementExecutorMock
  • func NewStatementExecutorMock() *StatementExecutorMock
  • func (m *StatementExecutorMock) Column(sql string, params map[string]any) ([]any, error)
  • func (m *StatementExecutorMock) Exec(sql string, params map[string]any) (int64, error)
  • func (m *StatementExecutorMock) Insert(sql string, params map[string]any, sequence string) (any, error)
  • func (m *StatementExecutorMock) MustColumn(sql string, params map[string]any) []any
  • func (m *StatementExecutorMock) MustExec(sql string, params map[string]any) int64
  • func (m *StatementExecutorMock) MustInsert(sql string, params map[string]any, sequence string) any
  • func (m *StatementExecutorMock) MustOne(sql string, params map[string]any) any
  • func (m *StatementExecutorMock) MustRow(sql string, params map[string]any) map[string]any
  • func (m *StatementExecutorMock) MustRows(sql string, params map[string]any) []map[string]any
  • func (m *StatementExecutorMock) One(sql string, params map[string]any) (any, error)
  • func (m *StatementExecutorMock) Row(sql string, params map[string]any) (map[string]any, error)
  • func (m *StatementExecutorMock) Rows(sql string, params map[string]any) ([]map[string]any, error)

Functions

func CheckParams

func CheckParams(t *testing.T, expected any, actual map[string]any)

func CheckSql

func CheckSql(t *testing.T, expected any, actual string)

func Keys

func Keys(m SliceMap) []any

func MapKeys

func MapKeys[K comparable, V any](m map[K]V) []K

func MapValues

func MapValues[K comparable, V any](m map[K]V) []V

func NextParameterName

func NextParameterName() string

func ResetParameterIndex

func ResetParameterIndex()

func ToInt64

func ToInt64(v any) (int64, error)

func ToSliceMapSlice

func ToSliceMapSlice[K comparable, V any](values []map[K]V) []any

func Values

func Values(m SliceMap) []any

Types

type ColumnsAwareStmt

type ColumnsAwareStmt[T any] interface {
	Statement[T]
	Columns(columns any) T
}

type Command

type Command interface {
	ItIsCommand()
	String() string
	Params() map[string]any
}

type CommandStmt

type CommandStmt[T any] interface {
	Statement[T]
	ItIsCommand()
}

type Query

type Query interface {
	ItIsQuery()
	String() string
	Params() map[string]any
}

type QueryStmt

type QueryStmt[T any] interface {
	Statement[T]
	ItIsQuery()
}

type SliceMap

type SliceMap []any

func Map

func Map(kv ...any) SliceMap

func ToSliceMap

func ToSliceMap[K comparable, V any](values map[K]V) SliceMap

type Statement

type Statement[T any] interface {
	IsEmpty() bool
	IsNotEmpty() bool
	String() string
	Params() map[string]any
	Executor() StatementExecutor
	AddParams(params map[string]any)
	AddSql(sql string)
	IsBuilt() bool
	Built() T
	Dirty() T
	Build() T
	Clean() T
	Copy() T
}

type StatementExecutor

type StatementExecutor interface {
	Exec(sql string, params map[string]any) (int64, error)
	MustExec(sql string, params map[string]any) int64

	Insert(sql string, params map[string]any, sequence string) (any, error)
	MustInsert(sql string, params map[string]any, sequence string) any

	Rows(sql string, params map[string]any) ([]map[string]any, error)
	MustRows(sql string, params map[string]any) []map[string]any

	Row(sql string, params map[string]any) (map[string]any, error)
	MustRow(sql string, params map[string]any) map[string]any

	Column(sql string, params map[string]any) ([]any, error)
	MustColumn(sql string, params map[string]any) []any

	One(sql string, params map[string]any) (any, error)
	MustOne(sql string, params map[string]any) any
}

type StatementExecutorMock

type StatementExecutorMock struct{}

func NewStatementExecutorMock

func NewStatementExecutorMock() *StatementExecutorMock

func (*StatementExecutorMock) Column

func (m *StatementExecutorMock) Column(sql string, params map[string]any) ([]any, error)

func (*StatementExecutorMock) Exec

func (m *StatementExecutorMock) Exec(sql string, params map[string]any) (int64, error)

func (*StatementExecutorMock) Insert

func (m *StatementExecutorMock) Insert(sql string, params map[string]any, sequence string) (any, error)

func (*StatementExecutorMock) MustColumn

func (m *StatementExecutorMock) MustColumn(sql string, params map[string]any) []any

func (*StatementExecutorMock) MustExec

func (m *StatementExecutorMock) MustExec(sql string, params map[string]any) int64

func (*StatementExecutorMock) MustInsert

func (m *StatementExecutorMock) MustInsert(sql string, params map[string]any, sequence string) any

func (*StatementExecutorMock) MustOne

func (m *StatementExecutorMock) MustOne(sql string, params map[string]any) any

func (*StatementExecutorMock) MustRow

func (m *StatementExecutorMock) MustRow(sql string, params map[string]any) map[string]any

func (*StatementExecutorMock) MustRows

func (m *StatementExecutorMock) MustRows(sql string, params map[string]any) []map[string]any

func (*StatementExecutorMock) One

func (m *StatementExecutorMock) One(sql string, params map[string]any) (any, error)

func (*StatementExecutorMock) Row

func (m *StatementExecutorMock) Row(sql string, params map[string]any) (map[string]any, error)

func (*StatementExecutorMock) Rows

func (m *StatementExecutorMock) Rows(sql string, params map[string]any) ([]map[string]any, error)