query

Documentation

Overview

Package query implements helpers around database/sql to execute various kinds of very common SQL queries.

Index

• func Count(ctx context.Context, tx *sql.Tx, table string, where string, args ...any) (int, error)
• func CountAll(ctx context.Context, tx *sql.Tx) (map[string]int, error)
• func DeleteObject(tx *sql.Tx, table string, id int64) (bool, error)
• func Dump(ctx context.Context, tx *sql.Tx, schemaOnly bool) (string, error)
• func InsertStrings(tx *sql.Tx, stmt string, values []string) error
• func IsRetriableError(err error) bool
• func Marshal(v any) (string, error)
• func Params(n int) string
• func Retry(f func() error) error
• func Scan(ctx context.Context, tx *sql.Tx, sql string, rowFunc Dest, inArgs ...any) error
• func SelectConfig(ctx context.Context, tx *sql.Tx, table string, where string, args ...any) (map[string]string, error)
• func SelectIntegers(ctx context.Context, tx *sql.Tx, query string, args ...any) ([]int, error)
• func SelectObjects(ctx context.Context, stmt *sql.Stmt, rowFunc Dest, args ...any) error
• func SelectStrings(ctx context.Context, tx *sql.Tx, query string, args ...any) ([]string, error)
• func Transaction(ctx context.Context, db *sql.DB, f func(context.Context, *sql.Tx) error) error
• func Unmarshal(data string, v any) error
• func UpdateConfig(tx *sql.Tx, table string, values map[string]string) error
• func UpsertObject(tx *sql.Tx, table string, columns []string, values []any) (int64, error)
• type Dest
• type Marshaler
• type Unmarshaler

Functions

func Count

func Count(ctx context.Context, tx *sql.Tx, table string, where string, args ...any) (int, error)

Count returns the number of rows in the given table.

func CountAll

func CountAll(ctx context.Context, tx *sql.Tx) (map[string]int, error)

CountAll returns a map associating each table name in the database with the total count of its rows.

func DeleteObject

func DeleteObject(tx *sql.Tx, table string, id int64) (bool, error)

DeleteObject removes the row identified by the given ID. The given table must have a primary key column called 'id'.

It returns a flag indicating if a matching row was actually found and deleted or not.

func Dump

func Dump(ctx context.Context, tx *sql.Tx, schemaOnly bool) (string, error)

Dump returns a SQL text dump of all rows across all tables, similar to sqlite3's dump feature.

func InsertStrings

func InsertStrings(tx *sql.Tx, stmt string, values []string) error

InsertStrings inserts a new row for each of the given strings, using the given insert statement template, which must define exactly one insertion column and one substitution placeholder for the values. For example: InsertStrings(tx, "INSERT INTO foo(name) VALUES %s", []string{"bar"}).

func IsRetriableError

func IsRetriableError(err error) bool

IsRetriableError returns true if the given error might be transient and the interaction can be safely retried.

func Marshal

func Marshal(v any) (string, error)

func Params

func Params(n int) string

Params returns a parameters expression with the given number of '?' placeholders. E.g. Params(2) -> "(?, ?)". Useful for IN and VALUES expressions.

func Retry

func Retry(f func() error) error

Retry wraps a function that interacts with the database, and retries it in case a transient error is hit.

This should by typically used to wrap transactions.

func Scan

func Scan(ctx context.Context, tx *sql.Tx, sql string, rowFunc Dest, inArgs ...any) error

Scan runs a query with inArgs and provides the rowFunc with the scan function for each row. It handles closing the rows and errors from the result set.

func SelectConfig

func SelectConfig(ctx context.Context, tx *sql.Tx, table string, where string, args ...any) (map[string]string, error)

SelectConfig executes a query statement against a "config" table, which must have 'key' and 'value' columns. By default this query returns all keys, but additional WHERE filters can be specified.

Returns a map of key names to their associated values.

func SelectIntegers

func SelectIntegers(ctx context.Context, tx *sql.Tx, query string, args ...any) ([]int, error)

SelectIntegers executes a statement which must yield rows with a single integer column. It returns the list of column values.

func SelectObjects

func SelectObjects(ctx context.Context, stmt *sql.Stmt, rowFunc Dest, args ...any) error

SelectObjects executes a statement which must yield rows with a specific columns schema. It invokes the given Dest hook for each yielded row.

func SelectStrings

func SelectStrings(ctx context.Context, tx *sql.Tx, query string, args ...any) ([]string, error)

SelectStrings executes a statement which must yield rows with a single string column. It returns the list of column values.

func Transaction

func Transaction(ctx context.Context, db *sql.DB, f func(context.Context, *sql.Tx) error) error

Transaction executes the given function within a database transaction with a 10s context timeout.

func Unmarshal

func Unmarshal(data string, v any) error

func UpdateConfig

func UpdateConfig(tx *sql.Tx, table string, values map[string]string) error

UpdateConfig updates the given keys in the given table. Config keys set to empty values will be deleted.

func UpsertObject

func UpsertObject(tx *sql.Tx, table string, columns []string, values []any) (int64, error)

UpsertObject inserts or replaces a new row with the given column values, to the given table using columns order. For example:

UpsertObject(tx, "cars", []string{"id", "brand"}, []any{1, "ferrari"})

The number of elements in 'columns' must match the one in 'values'.

Types

type Dest

type Dest func(scan func(dest ...any) error) error

Dest is a function that is expected to return the objects to pass to the 'dest' argument of sql.Rows.Scan(). It is invoked by SelectObjects once per yielded row, and it will be passed the index of the row being scanned.

type Marshaler

type Marshaler interface {
	MarshalDB() (string, error)
}

type Unmarshaler

type Unmarshaler interface {
	UnmarshalDB(string) error
}