README

Importing pan

import "darlinggo.co/pan"

About pan

pan is an SQL query building and response unmarshalling library for Go. It is designed to be compatible with MySQL and PostgreSQL, but should be more or less agnostic. Please let us know if your favourite SQL flavour is not supported.

Pan is not designed to be an ORM, but it still eliminates much of the boilerplate code around writing queries and scanning over rows.

Pan’s design focuses on reducing repetition and hardcoded strings in your queries, but without limiting your ability to write any form of query you want. It is meant to be the smallest possible abstraction on top of SQL.

Docs can be found on GoDoc.org.

If you're using pan, we encourage you to join the pan mailing list, which will be our main mode of communication.

Using pan

Pan revolves around structs that fill the SQLTableNamer interface, by implementing the GetSQLTableName() string function, which just returns the name of the table that should store the data for that struct.

Let's say you have a Person in your code.

type Person struct {
    ID    int     `sql_column:"person_id"`
    FName string  `sql_column:"fname"`
    LName string  `sql_column:"lname"`
    Age   int
}

And you have a corresponding Person table:

+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| person_id | int         | NO   |     | NULL    |       |
| fname     | varchar(20) | NO   |     | ''      |       |
| lname     | varchar(20) | NO   |     | ''      |       |
| age       | int         | NO   |     | 0       |       |
+-----------+-------------+------+-----+---------+-------+

Note: Unless you're using sql.NullString or equivalent, it's not recommended to allow NULL in your data. It may cause you trouble when unmarshaling.

To use that Person type with pan, you need it to fill the SQLTableNamer interface, letting pan know to use the person table in your database:

func (p Person) GetSQLTableName() string {
    return "person"
}

Creating a query

// selects all rows
var p Person
query := pan.New(pan.MYSQL, "SELECT "+pan.Columns(p).String()+" FROM "+pan.Table(p))

or

// selects one row
var p Person
query := pan.New(pan.MYSQL, "SELECT "+pan.Columns(p).String()+" FROM "+pan.Table(p)).Where()
query.Comparison(p, "ID", "=", 1)
query.Flush(" ")

That Flush command is important: pan works by creating a buffer of strings, and then joining them by some separator character. Flush takes the separator character (in this case, a space) and uses it to join all the buffered strings (in this case, the WHERE statement and the person_id = ? statement), and then adds the result to its query.

It's safe to call Flush even if there are no buffered strings, so a good practice is to just call Flush after the entire query is built, just to make sure you don't leave anything buffered.

The pan.Columns() function returns the column names that a struct's properties correspond to. pan.Columns().String() joins them into a list of columns that can be passed right to the SELECT expression, making it easy to support reading only the columns you need, maintaining forward compatibility—your code will never choke on unexpected columns being added.

Executing the query and reading results

mysql, err := query.MySQLString() // could also be PostgreSQLString
if err != nil {
	// handle the error
}
rows, err := db.Query(mysql, query.Args...)
if err != nil {
	// handle the error
}
var people []Person
for rows.Next() {
	var p Person
        err := pan.Unmarshal(rows, &p) // put the results into the struct
        if err != nil {
        	// handle the error
        }
        people = append(people, p)
}

How struct properties map to columns

There are a couple rules about how struct properties map to column names. First, only exported struct properties are used; unexported properties are ignored.

By default, a struct property's name is snake-cased, and that is used as the column name. For example, Name would become name, and MyInt would become my_int.

If you want more control or want to make columns explicit, the sql_column struct tag can be used to override this behaviour.

Column flags

Sometimes, you need more than the base column name; you may need the full name (table.column) or you may be using special characters/need to quote the column name ("column" for Postgres, \column\ for MySQL). To support these use cases, the Column and Columns functions take a variable number of flags (including none):

Columns() // returns column format
Columns(FlagFull) // returns table.column format
Columns(FlagDoubleQuoted) // returns "column" format
Columns(FlagTicked) // returns `column` format
Columns(FlagFull, FlagDoubleQuoted) // returns "table"."column" format
Columns(FlagFull, FlagTicked) // returns `table`.`column` format

This behaviour is not exposed through the convenience functions built on top of Column and Columns; you'll need to use Expression to rebuild them by hand. Usually, this can be done simply; look at the source code for those convenience functions for examples.

Expand ▾ Collapse ▴

Documentation

Index

Constants

This section is empty.

Variables

View Source
var (
	// ErrNeedsFlush is returned when a Query is used while it has expressions left in its buffer
	// that haven’t been flushed using the Query’s Flush method.
	ErrNeedsFlush = errors.New("Query has dangling buffer, its Flush method needs to be called")
)

Functions

func Column

func Column(s SQLTableNamer, property string, flags ...Flag) string

    Column returns the name of the column that `property` maps to for `s`. `property` must be the exact name of a property on `s`, or Column will panic.

    func ColumnValues

    func ColumnValues(s SQLTableNamer) []interface{}

      ColumnValues returns the values in `s` for each column in `s`, in the same order `Columns` returns the names.

      func Placeholders

      func Placeholders(num int) string

        Placeholders returns a formatted string containing `num` placeholders. The placeholders will be comma-separated.

        func Table

        func Table(t SQLTableNamer) string

          Table is a convenient shorthand wrapper for the GetSQLTableName method on `t`.

          func Unmarshal

          func Unmarshal(s Scannable, dst interface{}, additional ...interface{}) error

            Unmarshal reads the Scannable `s` into the variable at `d`, and returns an error if it is unable to. If there are more values than `d` has properties associated with columns, `additional` can be supplied to catch the extra values. The variables in `additional` must be a compatible type with and be in the same order as the columns of `s`.

            Types

            type ColumnList

            type ColumnList []string

              ColumnList represents a set of columns.

              func Columns

              func Columns(s SQLTableNamer, flags ...Flag) ColumnList

                Columns returns a ColumnList containing the names of the columns in `s`.

                func (ColumnList) String

                func (c ColumnList) String() string

                  String returns the columns in the ColumnList, joined by ", ", often used to create an SQL-formatted list of column names.

                  type ErrWrongNumberArgs

                  type ErrWrongNumberArgs struct {
                  	NumExpected int
                  	NumFound    int
                  }

                    ErrWrongNumberArgs is returned when you’ve generated a Query with a certain number of placeholders, but supplied a different number of arguments. The NumExpected property holds the number of placeholders in the Query, and the NumFound property holds the number of arguments supplied.

                    func (ErrWrongNumberArgs) Error

                    func (e ErrWrongNumberArgs) Error() string

                      Error fills the error interface.

                      type Flag

                      type Flag int

                        Flag represents a modification to the returned values from our Column or Columns functions. See the constants defined in this package for valid values.

                        const (
                        	// FlagFull returns columns in their absolute table.column format.
                        	FlagFull Flag = iota
                        	// FlagTicked returns columns using ticks to quote the column name, like `column`.
                        	FlagTicked
                        	// FlagDoubleQuoted returns columns using double quotes to quote the column name, like "column".
                        	FlagDoubleQuoted
                        )

                        type Query

                        type Query struct {
                        	// contains filtered or unexported fields
                        }

                          Query represents an SQL query that is being built. It can be used from its empty value, or it can be instantiated with the New method.

                          Query instances are used to build SQL query string and argument lists, and consist of an SQL string and a buffer. The Flush method must be called before the Query is used, or you may leave expressions dangling in the buffer.

                          The Query type is not meant to be concurrency-safe; if you need to modify it from multiple goroutines, you need to coordinate that access yourself.

                          func Insert

                          func Insert(values ...SQLTableNamer) *Query

                            Insert returns a Query instance containing SQL that will insert the passed `values` into the database. All `values` will be inserted into the same table, so invalid SQL will be generated if all `values` are not the same type.

                            func New

                            func New(query string) *Query

                              New returns a new Query instance, primed for use.

                              func (*Query) Args

                              func (q *Query) Args() []interface{}

                                Args returns a slice of the arguments attached to the Query, which should be used when executing your SQL to fill the placeholders.

                                Note that Args returns its internal slice; you should copy the returned slice over before modifying it.

                                func (*Query) Assign

                                func (q *Query) Assign(obj SQLTableNamer, property string, value interface{}) *Query

                                  Assign adds an expression to the Query’s buffer in the form of "column = ?", and adds `value` to the arguments for this query. `obj` and `property` are used to determine the column. `property` must exactly match the name of a property on `obj`, or the call will panic.

                                  func (*Query) Comparison

                                  func (q *Query) Comparison(obj SQLTableNamer, property, operator string, value interface{}) *Query

                                    Comparison adds a comparison expression to the Query’s buffer. A comparison takes the form of `column operator ?`, with `value` added as an argument to the Query. Column is determined by finding the column name for the passed property on the passed SQLTableNamer. The passed property must be a string that matches, identically, the property name; if it does not, it will panic.

                                    func (*Query) Expression

                                    func (q *Query) Expression(key string, values ...interface{}) *Query

                                      Expression adds a raw string and optional values to the Query’s buffer.

                                      func (*Query) Flush

                                      func (q *Query) Flush(join string) *Query

                                        Flush flushes the expressions in the Query’s buffer, adding them to the SQL string being built. It must be called before a Query can be used. Any pending expressions (anything since the last Flush or since the Query was instantiated) are joined using `join`, then added onto the Query’s SQL string, with a space between the SQL string and the expressions.

                                        func (*Query) In

                                        func (q *Query) In(obj SQLTableNamer, property string, values ...interface{}) *Query

                                          In adds an expression to the Query’s buffer in the form of "column IN (value, value, value)". `values` are the variables to match against, and `obj` and `property` are used to determine the column. `property` must exactly match the name of a property on `obj`, or the call will panic.

                                          func (*Query) Limit

                                          func (q *Query) Limit(limit int64) *Query

                                            Limit adds an expression to the Query’s buffer in the form of "LIMIT ?", and adds `limit` as an argument to the Query.

                                            func (*Query) MySQLString

                                            func (q *Query) MySQLString() (string, error)

                                              MySQLString returns a SQL string that can be passed to MySQL to execute your query. If the number of placeholders do not match the number of arguments provided to your Query, an ErrWrongNumberArgs error will be returned. If there are still expressions left in the buffer (meaning the Flush method wasn't called) an ErrNeedsFlush error will be returned.

                                              func (*Query) Offset

                                              func (q *Query) Offset(offset int64) *Query

                                                Offset adds an expression to the Query’s buffer in the form of "OFFSET ?", and adds `offset` as an argument to the Query.

                                                func (*Query) OrderBy

                                                func (q *Query) OrderBy(column string) *Query

                                                  OrderBy adds an expression to the Query’s buffer in the form of "ORDER BY column".

                                                  func (*Query) OrderByDesc

                                                  func (q *Query) OrderByDesc(column string) *Query

                                                    OrderByDesc adds an expression to the Query’s buffer in the form of "ORDER BY column DESC".

                                                    func (*Query) PostgreSQLString

                                                    func (q *Query) PostgreSQLString() (string, error)

                                                      PostgreSQLString returns an SQL string that can be passed to PostgreSQL to execute your query. If the number of placeholders do not match the number of arguments provided to your Query, an ErrWrongNumberArgs error will be returned. If there are still expressions left in the buffer (meaning the Flush method wasn't called) an ErrNeedsFlush error will be returned.

                                                      func (*Query) String

                                                      func (q *Query) String() string

                                                        String returns a version of your Query with all the arguments in the place of their placeholders. It does not do any sanitization, and is vulnerable to SQL injection. It is meant as a debugging aid, not to be executed. The string will almost certainly not be valid SQL.

                                                        func (*Query) Where

                                                        func (q *Query) Where() *Query

                                                          Where adds a WHERE keyword to the Query’s buffer, then calls Flush on the Query, using a space as the join parameter.

                                                          Where can only be called once per Query; calling it multiple times on the same Query will be no-ops after the first.

                                                          type SQLTableNamer

                                                          type SQLTableNamer interface {
                                                          	GetSQLTableName() string
                                                          }

                                                            SQLTableNamer is used to represent a type that corresponds to an SQL table. It must define the GetSQLTableName method, returning the name of the SQL table to store data for that type in.

                                                            type Scannable

                                                            type Scannable interface {
                                                            	Scan(dst ...interface{}) error
                                                            	Columns() ([]string, error)
                                                            }

                                                              Scannable defines a type that can insert the results of a Query into the SQLTableNamer a Query was built from, and can list off the column names, in order, that those results represent.