sqlh

Documentation

Index

• Variables
• func BuildInsert(i interface{}) (string, string, []interface{})
• func BuildSelect(i interface{}) string
• func BuildUpdate(i interface{}) (string, []interface{})
• func Columns(i interface{}) string
• func Params(i interface{}) string
• func Set(i interface{}) string
• func Values(i interface{}) []interface{}
• func Where(i interface{}) string

Variables

var FormatChar = "?"

The format character for query parameter placeholders. See FormatCounter if you need the incrementing counter as is the case for PostgreSQL. Sqlite accepts both ? and $ MySQL accepts only ? PostgreSQL accepts only $ Using ?: (?, ?, ?) Using $: ($1, $2, $3)

var FormatCounter = false

Set FormatCounter to `true` if you need an incrementing counter on your parameter placeholders as is the case for PostgreSQL. ($1, $2, $3) vs ($, $, $)

var SkipFields = "ID"

TODO: Sort out who is using the global vs who takes an argument directly A comma delimited string of key names to skip on inserts. Spaces are stripped. Typically you don't want to insert your ID column, but may want to fetch it. You may also want to ignore date fields such as "CreatedAt" that are automatically set by your schema. Use the golang struct field name here, not the SQL column name.

sqlh.SkipFields = "ID, CreatedAt"

var UseSQLCase = true

Convert struct field names from golang case to SQL case. Setting a db tag on the field will override any name conversions.

type Person struct {
	ID        int
	FirstName string
	LastName  string
	Email     string `db:"eMail"`
}

Produces the string (id, first_name, last_name, eMail)

Functions

func BuildInsert

func BuildInsert(i interface{}) (string, string, []interface{})

BuildInsert is a helper function that returns all the variables needed for an INSERT query in one call. BuildInsert returns the column names, the parameter placeholder string, and the values. BuildInsert automatically passes SkipFields to the Columns() call.

columns, params, values := sqlh.BuildInsert(person)
query := fmt.Sprintf("INSERT INTO people (%s) VALUES (%s)",
	columns, params)
db.Exec(query, values)

`INSERT INTO people (first_name, last_name, eMail) values ($1, $2, $3)`

func BuildSelect

func BuildSelect(i interface{}) string

BuildSelect is a helper function that returns all the variables needed for a SELECT query in one call. BuildSelect returns the column names. BuildSelect automatically passes SkipFields to the Columns() call.

columns := sqlh.BuildSelect(person)
query := fmt.Sprintf("SELECT (%s) FROM people", columns)
row, err := db.Query(query)

`SELECT (first_name, last_name, eMail) FROM people`

func BuildUpdate

func BuildUpdate(i interface{}) (string, []interface{})

BuildUpdate is a helper function that returns all the variables needed for an UPDATE query in one call. BuildUPdate returns the column names and parameter placeholder strings spliced together in the field=$1 style, and the array of values. BuildUpdate automatically passes the SkipFields to the Columns() call.

columns, values := sqlh.BuildUpdate(person)
query := fmt.Sprintf("UPDATE people SET %s", columns)
db.Exec(query, values)

`UPDATE people SET first_name=$1, last_name=$2, eMail=$3`

func Columns

func Columns(i interface{}) string

Columns walks through a struct's fields and returns their names in a format suitable for an INSERT query. If any struct db tags are set, those names will be used for the SQL column name. If UseSQLCase is set, golang casing will be converted to SQL casing on the field names (see UseSQLCase for more details). If SkipFields is set, any golang struct fields are omitted (see SkipFields for more details).

func Params

func Params(i interface{}) string

Params walks through a struct's fields and creates a string of parameter placeholders suitable for an INSERT query. Params uses FormatChar to define the character used for parameter placeholders in your database and FormatCounter if your database requires numbered parameter placeholders. See FormatChar and FormatCounter for more details.

func Set

func Set(i interface{}) string

Set walks through a struct's fields and returns their names and parameter placeholders in a format suitable for a SET clause. Set calls out to Columns and Params and thus follows their same rules. Set and Where are similar functions, but with a suitable joiner for the specific clause.

fmt.Sprintf("SET %s", sqlh.Set(person, "ID"))
`SET first_name=$1, last_name=$2, eMail=$3`

func Values

func Values(i interface{}) []interface{}

Values walks through a struct's fields and returns their values in a format suitable for passing to database/sql, which does not believe structs exist. The values []interface{} returned by Values is suitable for passing directly in to database/sql's args ...interface{} parameter.

func Where

func Where(i interface{}) string

Where walks through a struct's fields and returns their names and parameter placeholders in a format suitable for a WHERE clause. Where calls out to Columns and Params and thus follows their same rules. Where only handles the very simple case of column=value and isn't suitable for more advanced comparisons. Set and Where are similar functions, but with a suitable joiner for the specific clause.

fmt.Sprintf("WHERE %s", sqlh.Where(person, "ID"))
`WHERE first_name=$1 AND last_name=$2 AND eMail=$3`