dbutil

Documentation

Overview

Package dbutil contains useful utilities for working with a SQL database.

This package is intended to be used with PostgreSQL databases (using the github.com/lib/pq driver), but most functionality also works with SQLite (using github.com/mattn/go-sqlite3); see function documentation for details. Other SQL databases have not been tested but probably work to some extent.

Several functions in this package facilitate scanning, inserting, and updating structs. By default, all fields in a struct are expected to correspond to a SQL column with the same name. This behavior can be customized by the format string stored under the "sql" key in the struct field's tag. The format string specifies the name of the column, possibly followed by a comma-separated list of options. The name may be empty in order to specify options without overriding the default column name.

The "json" option specifies that the field should be marshaled and unmarshaled as a JSON string.

The "text" option specifies that the field should be marshaled and unmarshaled as a string using encoding.TextMarshaler and encoding.TextUnmarshaler.

The "binary" option specifies that the field should be marshaled and unmarshaled as a byte string using encoding.BinaryMarshaler and encoding.BinaryUnmarshaler.

The "embed" option specifies that the field is a struct whose fields should be treated as if they were fields of the parent struct.

The "noinsert" option specifies that the field should be excluded from INSERTs by the InsertStruct and InsertStructReturning functions.

If the field tag is "-", the field is ignored by this package.

Examples of struct fields and their meanings:

// Field corresponds to the SQL column named "Field"
Field string

// Field corresponds to the SQL column named "my_name"
Field string `sql:"my_name"`

// Field corresponds to the SQL column named "my_name" and is marshaled
// and unmarshaled as a JSON string
Field map[string]string `sql:"my_name,json"`

// Field corresponds to the SQL column named "Field" and is marshaled
// and unmarshaled as a JSON string
Field map[string]string `sql:",json"`

// Field is ignored by this package
Field string `sql:"-"`

Index

• func HasRow(ctx context.Context, db DB, query string, args ...interface{}) (bool, error)
• func InsertStruct(ctx context.Context, db DB, table Table, value interface{}, query string, ...) (sql.Result, error)
• func InsertStructReturning(ctx context.Context, db DB, table Table, value interface{}, query string, ...) (*sql.Rows, error)
• func JSON(v any) any
• func MustAffectRow(result sql.Result, err error) error
• func QueryAll(ctx context.Context, db DB, destSlicePtr interface{}, query string, ...) error
• func QueryOne(ctx context.Context, db DB, destPtr interface{}, query string, ...) error
• func QueryStruct(ctx context.Context, db DB, table Table, destPtr interface{}, query string, ...) error
• func QueryStructs(ctx context.Context, db DB, table Table, destSlicePtr interface{}, ...) error
• func QuoteIdentifier(name string) string
• func RegisterCustomScanner(makeScannerFunc interface{})
• func RowsAffected(result sql.Result, err error) (int64, error)
• func ScanAll(rows *sql.Rows, destSlicePtr interface{}) error
• func ScanCurrent(rows *sql.Rows, destPtr interface{}) error
• func ScanOne(rows *sql.Rows, destPtr interface{}) error
• func UpdateStruct(ctx context.Context, db DB, table Table, value interface{}, query string, ...) (sql.Result, error)
• func UpdateStructReturning(ctx context.Context, db DB, table Table, value interface{}, query string, ...) (*sql.Rows, error)
• func UpsertStruct(ctx context.Context, db DB, table Table, value interface{}) (sql.Result, error)
• type DB
• type Table

Functions

func HasRow

func HasRow(ctx context.Context, db DB, query string, args ...interface{}) (bool, error)

Execute a query and return whether the query returned at least one row

func InsertStruct

func InsertStruct(ctx context.Context, db DB, table Table, value interface{}, query string, args ...interface{}) (sql.Result, error)

Insert value (which must be a struct or a pointer to a struct) into table. query (with given args) is appended to the INSERT statement, and can be used to specify options like ON CONFLICT or left empty to do a plain INSERT.

func InsertStructReturning

func InsertStructReturning(ctx context.Context, db DB, table Table, value interface{}, query string, args ...interface{}) (*sql.Rows, error)

Insert value (which must be a struct or a pointer to a struct) into table. query (with given args) is appended to the INSERT statement, and must specify at least a RETURNING clause but can also specify options like ON CONFLICT.

func JSON

func JSON(v any) any

Return a proxy object that can be passed to functions like Scan or Query to serialize/deserialize v as a JSON string.

func MustAffectRow

func MustAffectRow(result sql.Result, err error) error

Return sql.ErrNoRows if the given result affected no rows.

Specifically:

• If err is non-nil: return err.
• If result.RowsAffected returns an error: return the error.
• If result.RowsAffected returns 0: return sql.ErrNoRows.
• Otherwise, return nil.

This function is intended to wrap calls to sql.DB.Exec, sql.DB.ExecContext, InsertStruct, etc. when you want to raise an error if no rows are affected.

func QueryAll

func QueryAll(ctx context.Context, db DB, destSlicePtr interface{}, query string, args ...interface{}) error

Execute a query and scan the results into destSlicePtr using ScanAll.

func QueryOne

func QueryOne(ctx context.Context, db DB, destPtr interface{}, query string, args ...interface{}) error

Execute a query that is expected to return one row and scan the result into destPtr using ScanOne. If the query returns no rows, then sql.ErrNoRows is returned.

func QueryStruct

func QueryStruct(ctx context.Context, db DB, table Table, destPtr interface{}, query string, args ...interface{}) error

Execute a SELECT query on table that is expected to return one row and scan the result into destPtr, which must be a pointer to a struct. The column names are taken from the struct type. query (with given args) is appended to the SELECT query and can be used to specify a WHERE clause. If the query returns no rows, then sql.ErrNoRows is returned.

func QueryStructs

func QueryStructs(ctx context.Context, db DB, table Table, destSlicePtr interface{}, query string, args ...interface{}) error

Execute a SELECT query on table and scan the results into destSlicePtr, which must be a pointer to a slice of structs or pointers-to-structs. The column names are taken from the struct type. query (with given args) is appended to the SELECT query and can be used to specify a WHERE clause.

func QuoteIdentifier

func QuoteIdentifier(name string) string

QuoteIdentifier quotes an "identifier" (e.g. a table or a column name) to be used as part of an SQL statement. For example:

tblname := "my_table"
data := "my_data"
quoted := dbutil.QuoteIdentifier(tblname)
err := db.Exec(fmt.Sprintf("INSERT INTO %s VALUES ($1)", quoted), data)

Any double quotes in name will be escaped. The quoted identifier will be case sensitive when used in a query. If the input string contains a zero byte, the result will be truncated immediately before it.

func RegisterCustomScanner

func RegisterCustomScanner(makeScannerFunc interface{})

Register a scanner for scanning custom types. makeScannerFunc must be a function with a signature that looks like:

func(out *T) sql.Scanner

Whenever this package needs to scan a database value into a *T, it will invoke makeScannerFunc to convert the *T into a sql.Scanner

This function is experimental.

func RowsAffected

func RowsAffected(result sql.Result, err error) (int64, error)

Return the number of rows affected by the result.

Specifically:

• If err is non-nil: return 0, err.
• If result.RowsAffected returns an error: return the error.
• Otherwise, return the result from result.RowsAffected.

This function is intended to wrap calls to sql.DB.Exec, sql.DB.ExecContext, InsertStruct, etc.

func ScanAll

func ScanAll(rows *sql.Rows, destSlicePtr interface{}) error

ScanAll scans all rows and appends them to destSlicePtr, which must be a pointer to a slice. After scanning the rows, rows is closed.

See ScanCurrent for a description of the scanning behavior. TODO: this should take a context, which we check at every iteration

func ScanCurrent

func ScanCurrent(rows *sql.Rows, destPtr interface{}) error

Scan the current row from rows into destPtr.

If destPtr points to a struct, then each column is scanned into the struct field with the same name as the column, per the rules described above (an error is returned if there is no matching struct field). If a struct field is a slice, the corresponding column must be a SQL array.

If destPtr points to a slice, then rows must have a single column which is a SQL array.

If destPtr points to any other type, then rows must have a single column which will be scanned as normal.

func ScanOne

func ScanOne(rows *sql.Rows, destPtr interface{}) error

ScanOne scans one row from rows into destPtr and then closes rows. If rows does not have any rows, then sql.ErrNoRows is returned.

See ScanCurrent for a description of the scanning behavior.

func UpdateStruct

func UpdateStruct(ctx context.Context, db DB, table Table, value interface{}, query string, args ...interface{}) (sql.Result, error)

Update table, taking column names and values from value, which must be a struct or a pointer to a struct. query (with given args) is appended to the UPDATE statement, and can be used to specify a WHERE clause (or left empty to update all rows of the table).

func UpdateStructReturning

func UpdateStructReturning(ctx context.Context, db DB, table Table, value interface{}, query string, args ...interface{}) (*sql.Rows, error)

Update table, taking column names and values from value, which must be a struct or a pointer to a struct. query (with given args) is appended to the UPDATE statement, and must specify at least a RETURNING clause, but can also specify other clauses like WHERE.

func UpsertStruct

func UpsertStruct(ctx context.Context, db DB, table Table, value interface{}) (sql.Result, error)

Upsert value (which must be a struct or a pointer to a struct) into table. The table's primary key index must be named TABLENAME_pkey.

This function is experimental and does not work with SQLite.

Types

type DB

type DB interface {
	ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
	QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)
	QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row
	PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
}

DB is an interface that is satisfied by *sql.DB, *sql.Tx, and *sql.Conn, allowing all three types to be used interchangeably for execing and querying.

type Table

type Table struct {
	Schema, Name string
}

A SQL table name, consisting of the name of the table and optionally the name of a schema.