Documentation ¶
Overview ¶
Package sx provides some simple extensions to the database/sql package to reduce the amount of boilerplate code.
Transactions and error handling ¶
Package sx provides a function called Do, which runs a callback function inside a transaction. The callback function is provided with a Tx object, which is an sql.Tx object that has been extended with some Must*** methods. When a Must*** method encounters an error, it panics, and the panic is caught by Do and returned to the caller as an error value.
Do automatically commits or rolls back the transaction based on whether or not the callback function completed successfuly.
Query helpers and struct matching ¶
Package sx provides functions to generate frequently-used queries, based on a simple matching between struct fields and database columns.
By default, every field in a struct corresponds to the database column whose name is the snake-cased version of the field name, i.e. the field HelloWorld corresponds to the "hello_world" column. Acronyms are treated as words, so HelloRPCWorld becomes "hello_rpc_world".
The column name can also be specified explicitly by tagging the field with the desired name, and fields can be excluded altogether by tagging with "-".
Fields that should be used for scanning but exluded for inserts and updates are additionally tagged "readonly".
Examples:
// Field is called "field" in the database. Field int // Field is called "hage" in the database. Field int `sx:"hage"` // Field is called "hage" in the database and should be skipped for inserts and updates. Field int `sx:"hage,readonly"` // Field is called "field" in the database and should be skipped for inserts and updates. Field int `sx:",readonly"` // Field should be ignored by sx. Field int `sx:"-"`
Example ¶
package main import ( "database/sql" "fmt" _ "github.com/mattn/go-sqlite3" sx "github.com/travelaudience/go-sx" ) func main() { db, err := sql.Open("sqlite3", ":memory:") if err != nil { fmt.Println(err) return } _, err = db.Exec("CREATE TABLE numbers (foo integer, bar string)") if err != nil { fmt.Println(err) return } // This is the default, but other examples set numbered placeholders to true, so // we need to make sure here that it's false. In practice, this would only be set // during initialization, and then only when $n-style placeholders are needed. sx.SetNumberedPlaceholders(false) type abc struct { Foo int32 Bar string } var data = []abc{ {Foo: 1, Bar: "one"}, {Foo: 2, Bar: "two"}, {Foo: 3, Bar: "three"}, } // Use Do to run a transaction. if err = sx.Do(db, func(tx *sx.Tx) { // Use MustPrepare with Do to insert rows into the table. query := sx.InsertQuery("numbers", &abc{}) tx.MustPrepare(query).Do(func(s *sx.Stmt) { for _, x := range data { s.MustExec(sx.Values(&x)...) } }) }); err != nil { // Any database-level error will be caught and printed here. fmt.Println(err) return } var dataRead []abc if err = sx.Do(db, func(tx *sx.Tx) { // Use MustQuery with Each to read the rows back in alphabetical order. query := sx.SelectQuery("numbers", &abc{}) + " ORDER BY bar" tx.MustQuery(query).Each(func(r *sx.Rows) { var x abc r.MustScans(&x) dataRead = append(dataRead, x) }) }); err != nil { fmt.Println(err) return } fmt.Println(dataRead) }
Output: [{1 one} {3 three} {2 two}]
Index ¶
- func Addrs(dest interface{}) []interface{}
- func ColumnOf(datatype interface{}, field string) (string, error)
- func Columns(datatype interface{}) []string
- func ColumnsWriteable(datatype interface{}) []string
- func Do(db *sql.DB, f func(*Tx), opts ...sql.TxOptions) error
- func DoContext(ctx context.Context, db *sql.DB, f func(*Tx), opts ...sql.TxOptions) (err error)
- func InsertQuery(table string, datatype interface{}) string
- func LimitOffset(limit, offset int64) string
- func SelectAliasQuery(table, alias string, datatype interface{}) string
- func SelectQuery(table string, datatype interface{}) string
- func SetNumberedPlaceholders(yes bool)
- func UpdateAllQuery(table string, data interface{}) string
- func UpdateFieldsQuery(table string, data interface{}, fields ...string) (string, []interface{})
- func UpdateQuery(table string, data interface{}, ph ...*Placeholder) (string, []interface{})
- func ValueOf(data interface{}, field string) interface{}
- func Values(data interface{}) []interface{}
- func Where(conditions ...string) string
- type Placeholder
- type Row
- type Rows
- type Stmt
- func (stmt *Stmt) Do(f func(*Stmt))
- func (stmt *Stmt) MustExec(args ...interface{}) sql.Result
- func (stmt *Stmt) MustExecContext(ctx context.Context, args ...interface{}) sql.Result
- func (stmt *Stmt) MustQuery(args ...interface{}) *Rows
- func (stmt *Stmt) MustQueryContext(ctx context.Context, args ...interface{}) *Rows
- func (stmt *Stmt) MustQueryRow(args ...interface{}) *Row
- func (stmt *Stmt) MustQueryRowContext(ctx context.Context, args ...interface{}) *Row
- type Tx
- func (tx *Tx) Fail(err error)
- func (tx *Tx) MustExec(query string, args ...interface{}) sql.Result
- func (tx *Tx) MustExecContext(ctx context.Context, query string, args ...interface{}) sql.Result
- func (tx *Tx) MustPrepare(query string) *Stmt
- func (tx *Tx) MustPrepareContext(ctx context.Context, query string) *Stmt
- func (tx *Tx) MustQuery(query string, args ...interface{}) *Rows
- func (tx *Tx) MustQueryContext(ctx context.Context, query string, args ...interface{}) *Rows
- func (tx *Tx) MustQueryRow(query string, args ...interface{}) *Row
- func (tx *Tx) MustQueryRowContext(ctx context.Context, query string, args ...interface{}) *Row
Examples ¶
Constants ¶
This section is empty.
Variables ¶
This section is empty.
Functions ¶
func Addrs ¶
func Addrs(dest interface{}) []interface{}
Addrs returns a slice of pointers to the fields of the struct pointed at by dest. Use for scanning rows from a SELECT query.
Panics if dest does not point at a struct.
func ColumnOf ¶
ColumnOf returns the name of the database column that corresponds to the specified field of the struct pointed at by datatype.
ColumnOf returns an error if the provided field name is missing from the struct.
func Columns ¶
func Columns(datatype interface{}) []string
Columns returns the names of the database columns that correspond to the fields in the struct pointed at by datatype. The order of returned fields matches the order of the struct.
func ColumnsWriteable ¶
func ColumnsWriteable(datatype interface{}) []string
ColumnsWriteable returns the names of the database columns that correspond to the fields in the struct pointed at by datatype, excluding those tagged "readonly". The order of returned fields matches the order of the struct.
func Do ¶
Do runs the function f in a transaction. Within f, if Fail() is invoked or if any Must*** method encounters an error, then the transaction is rolled back and Do returns the error. If f runs to completion, then the transaction is committed, and Do returns nil.
Internally, the Must*** methods panic on error, and Fail() always panics. The panic aborts execution of f. f should not attempt to recover from the panic. Instead, Do will catch the panic and return it as an error.
A TxOptions may be provided to specify isolation level and/or read-only status. If no TxOptions is provided, then the default oprtions are used. Extra TxOptions are ignored.
func DoContext ¶
DoContext runs the function f in a transaction. Within f, if Fail() is invoked or if any Must*** method encounters an error, then the transaction is rolled back and Do returns the error. If f runs to completion, then the transaction is committed, and DoContext returns nil.
Internally, the Must*** methods panic on error, and Fail() always panics. The panic aborts execution of f. f should not attempt to recover from the panic. Instead, Do will catch the panic and return it as an error.
A TxOptions may be provided to specify isolation level and/or read-only status. If no TxOptions is provided, then the default oprtions are used. Extra TxOptions are ignored.
func InsertQuery ¶
InsertQuery returns a query string of the form
INSERT INTO <table> (<columns>) VALUES (?,?,...) INSERT INTO <table> (<columns>) VALUES ($1,$2,...) (numbered placeholders)
where <table> is the table name given, and <columns> is the list of the columns defined by the struct pointed at by datatype. Struct fields tagged "readonly" are skipped.
Panics if all fields are tagged "readonly".
Example ¶
package main import ( "fmt" _ "github.com/mattn/go-sqlite3" sx "github.com/travelaudience/go-sx" ) func main() { sx.SetNumberedPlaceholders(true) type abc struct { Foo, Bar string Baz int64 `sx:",readonly"` } query := sx.InsertQuery("sometable", &abc{}) fmt.Println(query) }
Output: INSERT INTO sometable (foo,bar) VALUES ($1,$2)
func LimitOffset ¶
LimitOffset returns a string of the form
LIMIT <limit> OFFSET <offset>
with a leading space.
If either limit or offset are zero, then that part of the string is omitted. If both limit and offset are zero, then LimitOffset returns the empty string.
Example ¶
package main import ( "fmt" _ "github.com/mattn/go-sqlite3" sx "github.com/travelaudience/go-sx" ) func main() { query := "SELECT * FROM sometable" + sx.LimitOffset(100, 0) fmt.Println(query) }
Output: SELECT * FROM sometable LIMIT 100
func SelectAliasQuery ¶
SelectAliasQuery returns a query string like that of SelectQuery except that a table alias is included, e.g.
SELECT <alias>.<col0>, <alias>.<col1>, ..., <alias>.<coln> FROM <table> <alias>
Example ¶
package main import ( "fmt" _ "github.com/mattn/go-sqlite3" sx "github.com/travelaudience/go-sx" ) func main() { type abc struct { Foo, Bar string } query := sx.SelectAliasQuery("sometable", "s", &abc{}) fmt.Println(query) }
Output: SELECT s.foo,s.bar FROM sometable s
func SelectQuery ¶
SelectQuery returns a query string of the form
SELECT <columns> FROM <table>
where <columns> is the list of columns defined by the struct pointed at by datatype, and <table> is the table name given.
Example ¶
package main import ( "fmt" _ "github.com/mattn/go-sqlite3" sx "github.com/travelaudience/go-sx" ) func main() { type abc struct { Field1 int64 FieldTwo string Field3 bool `sx:"gigo"` } query := sx.SelectQuery("sometable", &abc{}) fmt.Println(query) }
Output: SELECT field1,field_two,gigo FROM sometable
func SetNumberedPlaceholders ¶
func SetNumberedPlaceholders(yes bool)
SetNumberedPlaceholders sets the style of placeholders to be used for generated queries. If yes is true, then postgres-style "$n" placeholders will be used for all future queries. If yes is false, then mysql-style "?" placeholders will be used. This setting may be changed at any time. Default is false.
func UpdateAllQuery ¶
UpdateAllQuery returns a query string of the form
UPDATE <table> SET <column>=?,<column>=?,... UPDATE <table> SET <column>=$2,<column>=$3,... (numbered placeholders)
where <table> is the table name given, and each <column> is a column name defined by the struct pointed at by data. All writeable fields (those not tagged "readonly") are included. Fields are in the order of the struct.
With numbered placeholders, numbering starts at $2. This allows $1 to be used in the WHERE clause.
Use with the Values function to write to all writeable feilds.
Example ¶
package main import ( "fmt" _ "github.com/mattn/go-sqlite3" sx "github.com/travelaudience/go-sx" ) func main() { sx.SetNumberedPlaceholders(true) type abc struct { Foo, Bar string Baz int64 `sx:",readonly"` } query := sx.UpdateAllQuery("sometable", &abc{}) + " WHERE id=$1" fmt.Println(query) }
Output: UPDATE sometable SET foo=$2,bar=$3 WHERE id=$1
func UpdateFieldsQuery ¶
UpdateFieldsQuery returns a query string and a list of values for the specified fields of the struct pointed at by data.
The query string is of the form
UPDATE <table> SET <column>=?,<column>=?,... UPDATE <table> SET <column>=$2,<column>=$3,... (numbered placeholders)
where <table> is the table name given, and each <column> is a column name defined by the struct pointed at by data.
The list of values contains values from the struct to match the placeholders. The order matches the the order of fields provided by the caller.
With numbered placeholders, numbering starts at $2. This allows $1 to be used in the WHERE clause.
UpdateFieldsQuery panics if no field names are provided or if any of the requested fields do not exist. If it is necessary to validate field names, use ColumnOf.
Example ¶
package main import ( "fmt" _ "github.com/mattn/go-sqlite3" sx "github.com/travelaudience/go-sx" ) func main() { sx.SetNumberedPlaceholders(true) type abc struct { Foo, Bar string Baz int64 } x := abc{Foo: "hello", Bar: "Goodbye", Baz: 42} query, values := sx.UpdateFieldsQuery("sometable", &x, "Bar", "Baz") query += " WHERE id=$1" fmt.Println(query) fmt.Println(values) }
Output: UPDATE sometable SET bar=$2,baz=$3 WHERE id=$1 [Goodbye 42]
func UpdateQuery ¶
func UpdateQuery(table string, data interface{}, ph ...*Placeholder) (string, []interface{})
UpdateQuery returns a query string and a list of values from the struct pointed at by data. This is the prefferred way to do updates, as it allows pointer fields in the struct and automatically skips zero values.
The query string of the form
UPDATE <table> SET <column>=?,<column>=?,... UPDATE <table> SET <column>=$2,<column>=$3,... (numbered placeholders)
where <table> is the table name given, and each <column> is a column name defined by the struct pointed at by data.
Note:
- placeholder could be passed as an optional parameter ( to control numbered placeholders )
- if not, numbering starts at $2 to allow $1 to be used in the WHERE clause ( ie. WHERE id = $1 ).
The list of values contains values from the struct to match the placeholders. For pointer fields, the values pointed at are used.
UpdateQuery takes all the writeable fields (not tagged "readonly") from the struct, looks up their values, and if it finds a zero value, the field is skipped. This allows the caller to set only those values that need updating. If it is necessary to update a field to a zero value, then a pointer field should be used. A pointer to a zero value will force an update, and a nil pointer will be skipped.
The struct used for UpdateQuery will normally be a different struct from that used for select or insert on the same table. This is okay.
If there are no applicable fields, Update returns ("", nil).
Example ¶
package main import ( "fmt" _ "github.com/mattn/go-sqlite3" sx "github.com/travelaudience/go-sx" ) func main() { sx.SetNumberedPlaceholders(true) type updateABC struct { Foo string // cannot update to "" Bar *string // can update to "" Baz int64 // cannot update to 0 Qux *int64 // can update to 0 } s1, i1 := "hello", int64(0) x := updateABC{Bar: &s1, Baz: 42, Qux: &i1} query, values := sx.UpdateQuery("sometable", &x) query += " WHERE id=$1" fmt.Println(query) fmt.Println(values) query, values = sx.UpdateQuery("sometable", &updateABC{}) fmt.Println(query == "", len(values)) }
Output: UPDATE sometable SET bar=$2,baz=$3,qux=$4 WHERE id=$1 [hello 42 0] true 0
func ValueOf ¶
func ValueOf(data interface{}, field string) interface{}
ValueOf returns the value of the specified field of the struct pointed at by data. Panics if data does not point at a struct, or if the requested field doesn't exist.
func Values ¶
func Values(data interface{}) []interface{}
Values returns a slice of values from the struct pointed at by data, excluding those from fields tagged "readonly". Use for providing values to an INSERT query.
Panics if data does not point at a struct.
func Where ¶
Where returns a string of the form
WHERE (<condition>) AND (<condition>) ...
with a leading space.
If no conditions are given, then Where returns the empty string.
Example ¶
package main import ( "fmt" _ "github.com/mattn/go-sqlite3" sx "github.com/travelaudience/go-sx" ) func main() { conditions := []string{ "ordered", "NOT sent", } query := "SELECT * FROM sometable" + sx.Where(conditions...) fmt.Println(query) }
Output: SELECT * FROM sometable WHERE (ordered) AND (NOT sent)
Types ¶
type Placeholder ¶
type Placeholder int
A Placeholder is a generator for the currently selected placeholder type. See SetNumberedPlaceholders().
func (*Placeholder) Next ¶
func (p *Placeholder) Next() string
Next increments the placeholder value and returns the string value of the next placeholder in sequence.
When using numbered placeholders, a zero-valued placeholder will return "$1" on its first call to Next(). When using ?-style placeholders, Next always returns "?".
func (Placeholder) String ¶
func (p Placeholder) String() string
String displays the current placeholder value in its chosen format (either "?" or "$n").
type Row ¶
Row is the result of calling MustQueryRow to select a single row. Row extends sql.Row with some useful scan methods.
type Rows ¶
Rows is the result of calling MustQuery to select a set of rows. Rows extends sql.Rows with some useful scan methods.
func (*Rows) Each ¶
Each iterates over all of the rows in a result set and runs a callback function on each row.
type Stmt ¶
Stmt extends sql.Stmt with some Must*** methods that panic instead of returning an error code. Stmt objects are used inside of transactions managed by Do. Panics are caught by Do and returned as errors.
func (*Stmt) Do ¶
Do runs a callback function f, providing f with the prepared statement, and then closing the prepared statement after f returns.
func (*Stmt) MustExec ¶
MustExec executes a prepared statement with the given arguments and returns an sql.Result summarizing the effect of the statement. In case of error, the transaction is aborted and Do returns the error code.
func (*Stmt) MustExecContext ¶
MustExecContext executes a prepared statement with the given arguments and returns an sql.Result summarizing the effect of the statement. In case of error, the transaction is aborted and Do returns the error code.
func (*Stmt) MustQuery ¶
MustQuery executes a prepared query statement with the given arguments and returns the query results as a *Rows. In case of error, the transaction is aborted and Do returns the error code.
func (*Stmt) MustQueryContext ¶
MustQueryContext executes a prepared query statement with the given arguments and returns the query results as a *Rows. In case of error, the transaction is aborted and Do returns the error code.
func (*Stmt) MustQueryRow ¶
MustQueryRow executes a prepared query that is expected to return at most one row. MustQueryRow always returns a non-nil value. Errors are deferred until one of the Row's scan methods is called.
func (*Stmt) MustQueryRowContext ¶
MustQueryRowContext executes a prepared query that is expected to return at most one row. MustQueryRowContext always returns a non-nil value. Errors are deferred until one of the Row's scan methods is called.
type Tx ¶
Tx extends sql.Tx with some Must*** methods that panic instead of returning an error code. Tx objects are used inside of transactions managed by Do. Panics are caught by Do and returned as errors.
func (*Tx) Fail ¶
Fail aborts and rolls back the transaction, returning the given error code to the caller of Do. Fail always rolls back the transaction, even if err is nil.
func (*Tx) MustExec ¶
MustExec executes a query without returning any rows. The args are for any placeholder parameters in the query. In case of error, the transaction is aborted and Do returns the error code.
func (*Tx) MustExecContext ¶
MustExecContext executes a query without returning any rows. The args are for any placeholder parameters in the query. In case of error, the transaction is aborted and Do returns the error code.
func (*Tx) MustPrepare ¶
MustPrepare creates a prepared statement for later queries or executions. Multiple queries or executions may be run concurrently from the returned statement. In case of error, the transaction is aborted and Do returns the error code.
The caller must call the statement's Close method when the statement is no longer needed.
func (*Tx) MustPrepareContext ¶
MustPrepareContext creates a prepared statement for later queries or executions. Multiple queries or executions may be run concurrently from the returned statement. In case of error, the transaction is aborted and Do returns the error code.
The caller must call the statement's Close method when the statement is no longer needed.
func (*Tx) MustQuery ¶
MustQuery executes a query that returns rows. The args are for any placeholder parameters in the query. In case of error, the transaction is aborted and Do returns the error code.
func (*Tx) MustQueryContext ¶
MustQueryContext executes a query that returns rows. The args are for any placeholder parameters in the query. In case of error, the transaction is aborted and Do returns the error code.
func (*Tx) MustQueryRow ¶
MustQueryRow executes a query that is expected to return at most one row. MustQueryRow always returns a non-nil value. Errors are deferred until one of the Row's scan methods is called.
func (*Tx) MustQueryRowContext ¶
MustQueryRowContext executes a query that is expected to return at most one row. MustQueryRow always returns a non-nil value. Errors are deferred until one of the Row's scan methods is called.