pan

package module
v0.4.0 Latest Latest
Warning

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

 Go to latest Published: Aug 2, 2023 License: MIT Imports: 9 Imported by: 7

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, PostgreSQL, and SQLite, 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 pkg.go.dev.

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 or SQLiteString
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.

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[Type SQLTableNamer](values ...Type) *Query

Insert returns a Query instance containing SQL that will insert the passed `values` into the database.

func New 

func New(query string) *Query

New returns a new Query instance, primed for use.

func (*Query) AppendToParent added in v0.4.0 

func (q *Query) AppendToParent() *Query

AppendToParent sets the entire Query as a single expression on its parent Query. It should only be called on Querys created by calling ComplexExpression; calling it on a Query that isn't a ComplexExpression will panic.

It returns the parent of the Query.

func (*Query) Args 

func (q *Query) Args() []any

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 any) *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 any) *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) ComplexExpression added in v0.4.0 

func (q *Query) ComplexExpression(query string) *Query

ComplexExpression starts a Query with a new buffer, so it can be flushed without affecting the outer Query's buffer of expressions.

Once a ComplexExpression has been flushed, it should have its AppendToParent method called, which sets the entire ComplexExpression as a single expression on its parent Query.

func (*Query) Expression 

func (q *Query) Expression(key string, values ...any) *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 ...any) *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) SQLiteString added in v0.3.0 

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

SQLiteString returns a SQL string that can be passed to SQLite 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.

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.