namedParameterQuery

README

NamedParameterQuery

This fork add support for :arg and $n arg substitution for sql/database package

Provides support for named parameters in SQL queries used by Go / golang programs and libraries.

SQL query parameters in go are positional. This means that when writing a query, you'll need to do it like this:

SELECT * FROM table
WHERE col1 = ?
AND col2 IN(?, ?, ?)
AND col3 = ?

Where "?" is a parameter that you want to replace with an actual value at runtime. Your code would need to look like this:

sql.QueryRow(queryText, "foo", "bar", "baz", "woot", "bar")

As you can probably guess, this can lead to very unwieldy code in large queries. You wind up needing to keep track not only of how many parameters you have, but in what order the query expects them. Sometimes you want to reference the same variable in more than one place in your query, which requires you to specify it more than once in your code! Refactoring your queries even once can lead to disastrous and annoying results.

The answer to this is to use named parameters, which would look like this:

SELECT * FROM table
WHERE col1 = :userName
AND col2 IN(:firstName, :lastName, :middleName)
AND col3 = :firstname

You would then add parameters to your query by name. This means you won't need to worry about what order your parameters are specified, nor how many times they appear.

But golang doesn't support named parameters! That's what this library is for.

Why doesn't Go support this normally?

Go needs to support every kind of SQL server - and not all SQL servers support named parameters. The servers that do support them do it with a variety of quirks and "gotchas". But they all support positional parameters just fine.

I'm not sure why the Go authors didn't add this named parameter support on their own, but this polyfill works fine anyway.

It's possible that someone else already implemented this, but I sure couldn't find a pre-existing solution when I needed it.

Isn't there a better way?

In short, not across all databases, and not without complicating your query.

There are other ways to achieve the same effect on some databases. You can register stored procedures which take positional parameters, then call that procedure instead of writing a query. However that's a fairly specific use case - you don't always want to store your query permanently on the server; that means you have to worry about query versioning on the server, and complicates updates to queries during deployment, and precludes you from easily deploying new queries without damaging processes relying on the old ones. For most cases, sending the entire query every time you want to use it is the better option.

Or, if your database supports it, you could define user-local variables in your query. Usually this requires a change to your DB, connectionstring, and queries. The syntax also varies across databases in unpredictable ways - meaning you're going to write less portable queries.

Personally I don't find those options attractive. To me, a query ought to support named parameters without edits to your database. That's why this library exists.

How do I use this?

Probably best to check out the API docs

http://godoc.org/github.com/Knetic/go-namedParameterQuery

But here are some quick examples of the main use cases.

query := NewNamedParameterQuery("
	SELECT * FROM table
	WHERE col1 = :foo
	AND col2 IN(:firstName, :middleName, :lastName)
", "?")

query.SetValue("foo", "bar")
query.SetValue("firstName", "Alice")
query.SetValue("lastName", "Bob")
query.SetValue("middleName", "Eve")

connection, _ := sql.Open("mysql", "user:pass@tcp(localhost:3306)/db")
connection.QueryRow(query.GetParsedQuery(), (query.GetParsedParameters())...)

It doesn't matter what order you specify the parameters, or how many times they appear in the query, they're replaced as expected. The second argument is to tell witch syntax is expected for this query (in ?, $, :)

That looks a little tedious, and feels a lot like JDBC, where each parameter is given one line. But you can also add groups of parameters with a map:

query := NewNamedParameterQuery("
	SELECT * FROM table
	WHERE col1 = :foo
	AND col2 IN(:firstName, :middleName, :lastName)
", "?")

var parameterMap = map[string]interface{} {
	"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())...)

That example doesn't save any space because it defines the map immediately before using it, but if you already have a map of parameters available, this is easier.

But maybe you know the benefits of strong typing, and want to add entire structs as parameters. No problem.

type QueryValues struct {
	Foo string		`sqlParameterName:"foo"`
	FirstName string 	`sqlParameterName:"firstName"`
	MiddleName string `sqlParameterName:"middleName"`
	LastName string 	`sqlParameterName:"lirstName"`
}

query := NewNamedParameterQuery("
	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())...)

When defining your struct, you don't need to add the "sqlParameterName" tags. But if your query uses lowercase variable names (as mine did), your struct will need to have exportable field names (as above) you can translate between the two with a tag.

Activity

If this repository hasn't been updated in a while, it's probably because I don't have any outstanding issues to work on - it's not because I've abandoned the project. If you have questions, issues, or patches; I'm completely open to pull requests, issues opened on github, or emails from out of the blue.

License

This implementation of Go named parameter queries is licensed under the MIT general use license. You're free to integrate, fork, and play with this code as you feel fit without consulting the author, as long as you provide proper credit to the author in your works. If you have questions, issues, or patches, I'm completely open to pull requests, issues opened on github, or emails from out of the blue.

Documentation

Overview

Adaptation of package https://github.com/Knetic/go-namedParameterQuery/ to support $ and : arg syntaxes in query

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.
npq package translates SQL queries which use named parameters into queries which use positional parameters.
Example usage:
	query := NewNamedParameterQuery("
		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 npq exact format, including the variadic symbol "..."
Note that the example above uses "QueryRow", but named parameters used in npq fashion
work equally well for "Query" and "Exec".
It's also possible to pass in a map, instead of defining each parameter individually:
	query := NewNamedParameterQuery("
		SELECT * FROM table
		WHERE col1 = :foo
		AND col2 IN(:firstName, :middleName, :lastName)
	")
	var parameterMap = map[string]interface{} {
		"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 := NewNamedParameterQuery("
		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 NamedParameterQuery
  • func NewNamedParameterQuery(queryText string, argIndication string) *NamedParameterQuery
  • func (npq *NamedParameterQuery) GetParsedParameters() []interface{}
  • func (npq *NamedParameterQuery) GetParsedQuery() string
  • func (npq *NamedParameterQuery) SetValue(parameterName string, parameterValue interface{})
  • func (npq *NamedParameterQuery) SetValuesFromMap(parameters map[string]interface{})
  • func (npq *NamedParameterQuery) SetValuesFromStruct(parameters interface{}) error

Types

type NamedParameterQuery

type NamedParameterQuery 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 NewNamedParameterQuery

func NewNamedParameterQuery

func NewNamedParameterQuery(queryText string, argIndication string) *NamedParameterQuery

NewNamedParameterQuery 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 (*NamedParameterQuery) GetParsedParameters

func (npq *NamedParameterQuery) GetParsedParameters() []interface{}

GetParsedParameters returns an array of parameter objects that match the positional parameter list from GetParsedQuery

func (*NamedParameterQuery) GetParsedQuery

func (npq *NamedParameterQuery) GetParsedQuery() string

GetParsedQuery returns a version of the original query text whose named parameters have been replaced by positional parameters.

func (*NamedParameterQuery) SetValue

func (npq *NamedParameterQuery) SetValue(parameterName string, parameterValue interface{})

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], npq method does nothing.

func (*NamedParameterQuery) SetValuesFromMap

func (npq *NamedParameterQuery) SetValuesFromMap(parameters map[string]interface{})

SetValuesFromMap uses every key/value pair in the given [parameters] as a parameter replacement for npq query. npq 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 (*NamedParameterQuery) SetValuesFromStruct

func (npq *NamedParameterQuery) SetValuesFromStruct(parameters interface{}) error

SetValuesFromStruct uses reflection to find every public field of the given struct [parameters] and set their key/value as named parameters in npq query. If the given [parameters] is not a struct, npq 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"`
}