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
Constants ¶
This section is empty.
Variables ¶
This section is empty.
Functions ¶
func HasRow ¶ added in v0.3.0
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 ¶
Return a proxy object that can be passed to functions like Scan or Query to serialize/deserialize v as a JSON string.
func MustAffectRow ¶ added in v0.3.0
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 ¶ added in v0.7.0
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 ¶ added in v0.3.0
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 ¶ added in v0.4.0
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 ¶
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 ¶
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 ¶
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 ¶ added in v0.3.0
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.
Source Files
¶
Directories
¶
| Path | Synopsis |
|---|---|
|
Helpers for building a database schema using migration files
|
Helpers for building a database schema using migration files |
|
Helpers for working with PostgreSQL errors
|
Helpers for working with PostgreSQL errors |
|
Helpers for working with PostgreSQL databases
|
Helpers for working with PostgreSQL databases |