Documentation
¶
Overview ¶
Provides support for named parameters in SQL queries used by Go / golang programs and libraries.
Named parameters are not supported by all SQL query engines, and their standards are scattered. But positional parameters have wide adoption across all databases.
This package translates SQL queries which use named parameters into queries which use positional parameters.
Example usage:
query := NewQuery("
SELECT * FROM table
WHERE col1 = :foo
")
query.SetValue("foo", "bar")
connection, _ := sql.Open("mysql", "user:pass@tcp(localhost:3306)/db")
connection.QueryRow(query.GetParsedQuery(), (query.GetParsedParameters())...)
In the example above, note the format of "QueryRow". In order to use named parameter queries, you will need to use this exact format, including the variadic symbol "..."
Note that the example above uses "QueryRow", but named parameters used in this fashion work equally well for "Query" and "Exec".
It's also possible to pass in a map, instead of defining each parameter individually:
query := NewQuery("
SELECT * FROM table
WHERE col1 = :foo
AND col2 IN(:firstName, :middleName, :lastName)
")
var parameterMap = map[string]any {
"foo": "bar",
"firstName": "Alice",
"lastName": "Bob"
"middleName": "Eve",
}
query.SetValuesFromMap(parameterMap)
connection, _ := sql.Open("mysql", "user:pass@tcp(localhost:3306)/db")
connection.QueryRow(query.GetParsedQuery(), (query.GetParsedParameters())...)
But of course, sometimes you just want to pass in an entire struct. No problem:
type QueryValues struct {
Foo string `sqlParameterName:"foo"`
FirstName string `sqlParameterName:"firstName"`
MiddleName string `sqlParameterName:"middleName"`
LastName string `sqlParameterName:"lirstName"`
}
query := NewQuery("
SELECT * FROM table
WHERE col1 = :foo
AND col2 IN(:firstName, :middleName, :lastName)
")
parameter = new(QueryValues)
query.SetValuesFromStruct(parameter)
connection, _ := sql.Open("mysql", "user:pass@tcp(localhost:3306)/db")
connection.QueryRow(query.GetParsedQuery(), (query.GetParsedParameters())...)
Index ¶
- type ConnectionWrapper
- func (w *ConnectionWrapper) ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
- func (w *ConnectionWrapper) PrepareContext(ctx context.Context, query string) (*StatementWrapper, error)
- func (w *ConnectionWrapper) QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
- func (w *ConnectionWrapper) QueryRowContext(ctx context.Context, query string, args ...any) (*sql.Row, error)
- type DBObjectWrapper
- func (w *DBObjectWrapper) Exec(query string, args ...any) (sql.Result, error)
- func (w *DBObjectWrapper) ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
- func (w *DBObjectWrapper) Prepare(query string) (*StatementWrapper, error)
- func (w *DBObjectWrapper) PrepareContext(ctx context.Context, query string) (*StatementWrapper, error)
- func (w *DBObjectWrapper) Query(query string, args ...any) (*sql.Rows, error)
- func (w *DBObjectWrapper) QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
- func (w *DBObjectWrapper) QueryRow(query string, args ...any) (*sql.Row, error)
- func (w *DBObjectWrapper) QueryRowContext(ctx context.Context, query string, args ...any) (*sql.Row, error)
- type Query
- type StatementWrapper
- func (w *StatementWrapper) Exec(args ...any) (sql.Result, error)
- func (w *StatementWrapper) ExecContext(ctx context.Context, args ...any) (sql.Result, error)
- func (w *StatementWrapper) Query(args ...any) (*sql.Rows, error)
- func (w *StatementWrapper) QueryContext(ctx context.Context, args ...any) (*sql.Rows, error)
- func (w *StatementWrapper) QueryRow(args ...any) (*sql.Row, error)
- func (w *StatementWrapper) QueryRowContext(ctx context.Context, args ...any) (*sql.Row, error)
- type WrappableDBObject
Constants ¶
This section is empty.
Variables ¶
This section is empty.
Functions ¶
This section is empty.
Types ¶
type ConnectionWrapper ¶
type ConnectionWrapper struct {
// contains filtered or unexported fields
}
ConnectionWrapper wraps a sql.Conn and adds methods that can be used with parameterized queries.
func UsingConnection ¶
func UsingConnection(conn *sql.Conn) *ConnectionWrapper
UsingConnection wraps a *sql.Conn, in order to decorate it with parameterized methods.
func (*ConnectionWrapper) ExecContext ¶
func (w *ConnectionWrapper) ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
ExecContext executes a parameterized sql instruction using the expanded args to feed the parameter values and returns the results. This method expects the parameter arguments to either be a map[string]any, or to come in pairs, which are processed as key, value pairs, and in that case, the keys are expected to be strings. If the number of arguments is not even, or a key value is not a string, an error is returned.
func (*ConnectionWrapper) PrepareContext ¶
func (w *ConnectionWrapper) PrepareContext(ctx context.Context, query string) (*StatementWrapper, error)
PrepareContext prepares a statement from a parameterized query and returns a wrapper that can be used with named parameters.
func (*ConnectionWrapper) QueryContext ¶
func (w *ConnectionWrapper) QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
QueryContext performs a parameterized query using the expanded args to feed the parameter values. This method expects the parameter arguments to either be a map[string]any, or to come in pairs, which are processed as key, value pairs, and in that case, the keys are expected to be strings. If the number of arguments is not even, or a key value is not a string, an error is returned.
func (*ConnectionWrapper) QueryRowContext ¶
func (w *ConnectionWrapper) QueryRowContext(ctx context.Context, query string, args ...any) (*sql.Row, error)
QueryRowContext performs a parameterized query using the expanded args to feed the parameter values and returns one row. This method expects the parameter arguments to either be a map[string]any, or to come in pairs, which are processed as key, value pairs, and in that case, the keys are expected to be strings. If the number of arguments is not even, or a key value is not a string, an error is returned.
type DBObjectWrapper ¶
type DBObjectWrapper struct {
// contains filtered or unexported fields
}
DBObjectWrapper wraps either a sql.DB or a sql.Tx and adds methods that can be used with parameterized queries.
func Using ¶
func Using(dbObject WrappableDBObject) *DBObjectWrapper
Using wraps a *sql.DB or a *sql.Tx, in order to decorate them with parameterized methods.
func (*DBObjectWrapper) Exec ¶
Exec executes a parameterized sql instruction using the expanded args to feed the parameter values and returns the results. This method expects the parameter arguments to either be a map[string]any, or to come in pairs, which are processed as key, value pairs, and in that case, the keys are expected to be strings. If the number of arguments is not even, or a key value is not a string, an error is returned.
func (*DBObjectWrapper) ExecContext ¶
func (w *DBObjectWrapper) ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
ExecContext executes a parameterized sql instruction using the expanded args to feed the parameter values and returns the results. This method expects the parameter arguments to either be a map[string]any, or to come in pairs, which are processed as key, value pairs, and in that case, the keys are expected to be strings. If the number of arguments is not even, or a key value is not a string, an error is returned.
func (*DBObjectWrapper) Prepare ¶
func (w *DBObjectWrapper) Prepare(query string) (*StatementWrapper, error)
Prepare prepares a statement from a parameterized query and returns a wrapper that can be used with named parameters.
func (*DBObjectWrapper) PrepareContext ¶
func (w *DBObjectWrapper) PrepareContext(ctx context.Context, query string) (*StatementWrapper, error)
PrepareContext prepares a statement from a parameterized query and returns a wrapper that can be used with named parameters.
func (*DBObjectWrapper) Query ¶
Query performs a parameterized query using the expanded args to feed the parameter values. This method expects the parameter arguments to either be a map[string]any, or to come in pairs, which are processed as key, value pairs, and in that case, the keys are expected to be strings. If the number of arguments is not even, or a key value is not a string, an error is returned.
func (*DBObjectWrapper) QueryContext ¶
func (w *DBObjectWrapper) QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
QueryContext performs a parameterized query using the expanded args to feed the parameter values. This method expects the parameter arguments to either be a map[string]any, or to come in pairs, which are processed as key, value pairs, and in that case, the keys are expected to be strings. If the number of arguments is not even, or a key value is not a string, an error is returned.
func (*DBObjectWrapper) QueryRow ¶
QueryRow performs a parameterized query using the expanded args to feed the parameter values and returns one row. This method expects the parameter arguments to either be a map[string]any, or to come in pairs, which are processed as key, value pairs, and in that case, the keys are expected to be strings. If the number of arguments is not even, or a key value is not a string, an error is returned.
func (*DBObjectWrapper) QueryRowContext ¶
func (w *DBObjectWrapper) QueryRowContext(ctx context.Context, query string, args ...any) (*sql.Row, error)
QueryRowContext performs a parameterized query using the expanded args to feed the parameter values and returns one row. This method expects the parameter arguments to either be a map[string]any, or to come in pairs, which are processed as key, value pairs, and in that case, the keys are expected to be strings. If the number of arguments is not even, or a key value is not a string, an error is returned.
type Query ¶
type Query struct {
// contains filtered or unexported fields
}
NamedParameterQuery handles the translation of named parameters to positional parameters, for SQL statements. It is not recommended to create zero-valued NamedParameterQuery objects by yourself; instead use NewQuery
func NewQuery ¶
NewQuery creates a new named parameter query using the given [queryText] as a SQL query which contains named parameters. Named parameters are identified by starting with a ":" e.g., ":name" refers to the parameter "name", and ":foo" refers to the parameter "foo".
Except for their names, named parameters follow all the same rules as positional parameters; they cannot be inside quoted strings, and cannot inject statements into a query. They can only be used to insert values.
func (*Query) GetParsedParameters ¶
GetParsedParameters returns an array of parameter objects that match the positional parameter list from GetParsedQuery
func (*Query) GetParsedQuery ¶
GetParsedQuery returns a version of the original query text whose named parameters have been replaced by positional parameters.
func (*Query) SetValue ¶
SetValue sets the value of the given [parameterName] to the given [parameterValue]. If the parsed query does not have a placeholder for the given [parameterName], this method does nothing.
func (*Query) SetValuesFromMap ¶
SetValuesFromMap uses every key/value pair in the given [parameters] as a parameter replacement for this query. This is equivalent to calling SetValue for every key/value pair in the given [parameters] map. If there are any keys/values present in the map that aren't part of the query, they are ignored.
func (*Query) SetValuesFromStruct ¶
SetValuesFromStruct uses reflection to find every public field of the given struct [parameters] and set their key/value as named parameters in this query. If the given [parameters] is not a struct, this will return an error.
If you do not wish for a field in the struct to be added by its literal name, The struct may optionally specify the sqlParameterName as a tag on the field. e.g., a struct field may say something like:
type Test struct {
Foo string `sqlParameterName:"foobar"`
}
type StatementWrapper ¶
type StatementWrapper struct {
// contains filtered or unexported fields
}
StatementWrapper wraps a sql.Stmt and adds methods that can be used with parameterized queries.
func (*StatementWrapper) Exec ¶
func (w *StatementWrapper) Exec(args ...any) (sql.Result, error)
Exec executes a parameterized prepared sql instruction using the expanded args to feed the parameter values and returns the results. This method expects the parameter arguments to either be a map[string]any, or to come in pairs, which are processed as key, value pairs, and in that case, the keys are expected to be strings. If the number of arguments is not even, or a key value is not a string, an error is returned.
func (*StatementWrapper) ExecContext ¶
ExecContext executes a parameterized prepared sql instruction using the expanded args to feed the parameter values and returns the results. This method expects the parameter arguments to either be a map[string]any, or to come in pairs, which are processed as key, value pairs, and in that case, the keys are expected to be strings. If the number of arguments is not even, or a key value is not a string, an error is returned.
func (*StatementWrapper) Query ¶
func (w *StatementWrapper) Query(args ...any) (*sql.Rows, error)
Query performs a parameterized prepared query using the expanded args to feed the parameter values. This method expects the parameter arguments to either be a map[string]any, or to come in pairs, which are processed as key, value pairs, and in that case, the keys are expected to be strings. If the number of arguments is not even, or a key value is not a string, an error is returned.
func (*StatementWrapper) QueryContext ¶
QueryContext performs a parameterized prepared query using the expanded args to feed the parameter values. This method expects the parameter arguments to either be a map[string]any, or to come in pairs, which are processed as key, value pairs, and in that case, the keys are expected to be strings. If the number of arguments is not even, or a key value is not a string, an error is returned.
func (*StatementWrapper) QueryRow ¶
func (w *StatementWrapper) QueryRow(args ...any) (*sql.Row, error)
QueryRow performs a parameterized prepared query using the expanded args to feed the parameter values and returns one row. This method expects the parameter arguments to either be a map[string]any, or to come in pairs, which are processed as key, value pairs, and in that case, the keys are expected to be strings. If the number of arguments is not even, or a key value is not a string, an error is returned.
func (*StatementWrapper) QueryRowContext ¶
QueryRowContext performs a parameterized prepared query using the expanded args to feed the parameter values and returns one row. This method expects the parameter arguments to either be a map[string]any, or to come in pairs, which are processed as key, value pairs, and in that case, the keys are expected to be strings. If the number of arguments is not even, or a key value is not a string, an error is returned.
type WrappableDBObject ¶
type WrappableDBObject interface {
Exec(string, ...any) (sql.Result, error)
ExecContext(context.Context, string, ...any) (sql.Result, error)
Query(string, ...any) (*sql.Rows, error)
QueryContext(context.Context, string, ...any) (*sql.Rows, error)
QueryRow(string, ...any) *sql.Row
QueryRowContext(context.Context, string, ...any) *sql.Row
Prepare(string) (*sql.Stmt, error)
PrepareContext(context.Context, string) (*sql.Stmt, error)
}
WrappableDBObject is an interface that can cover both a sql.DB or a sql.Tx object, making parameterized queries usable for any of these without having to implement wrappers for each.
Source Files