package module
Version: v0.2.0 Latest Latest

This package is not in the latest version of its module.

Go to latest
Published: May 29, 2017 License: MIT Imports: 9 Imported by: 5


Importing pan

import ""

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

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))


// 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.




This section is empty.


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")


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`.


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`.
	// FlagDoubleQuoted returns columns using double quotes to quote the column name, like "column".

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.

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL