squl

README

SQuL

Unlike all the other sql query builder tools that I have encountered out there in the golang ecosystem, this tool does not introduce a new custom designed fluent set of methods. Instead the goal is to provide golang structs which can be then built into custom queries. It's almost like writing the queries by hand except that you construct them using golang structs.

Installation

To get the latest version of the library simply run:

go get -u github.com/trivigy/squl

Usage

For a whole lot more examples please make sure to explore the unittest files.

INSERT

INSERT INTO contacts (last_name,first_name) SELECT last_name,first_name FROM customers WHERE customer_id > 4000

package main

import (
    "github.com/trivigy/squl"
)

func main() {
	query, args, err := squl.Build(&squl.Insert{
		Relation: &squl.RangeVar{
			Name: "contacts",
		},
		Columns: &squl.List{
			&squl.ResTarget{
				Value: &squl.ColumnRef{Fields: "last_name"},
			},
			&squl.ResTarget{
				Value: &squl.ColumnRef{Fields: "first_name"},
			},
		},
		Select: &squl.Select{
			Targets: &squl.List{
				&squl.ResTarget{
					Value: &squl.ColumnRef{Fields: "last_name"},
				},
				&squl.ResTarget{
					Value: &squl.ColumnRef{Fields: "first_name"},
				},
			},
			From: &squl.RangeVar{
				Name: "customers",
			},
			Where: &squl.Expr{
				Type: squl.ExprTypeOp,
				Name: ">",
				LHS:  &squl.ColumnRef{Fields: "customer_id"},
				RHS:  &squl.Const{Value: 4000},
			},
		},
	})
}

SELECT

SELECT a.id,first_name,last_name FROM customer AS a,laptops AS l INNER JOIN payment ON payment.id = a.id LEFT JOIN people ON people.id = a.id RIGHT JOIN cars ON cars.id = a.id

package main

import (
	"github.com/trivigy/squl"
)

func main() {
	query, args, err := squl.Build(&squl.Select{
        Targets: &squl.List{
            &squl.ResTarget{
                Value: &squl.ColumnRef{Fields: []string{"a", "id"}},
            },
            &squl.ResTarget{
                Value: &squl.ColumnRef{Fields: "first_name"},
            },
            &squl.ResTarget{
                Value: &squl.ColumnRef{Fields: "last_name"},
            },
        },
        From: &squl.List{
            &squl.RangeVar{
                Name:  "customer",
                Alias: "a",
            },
            &squl.JoinExpr{
                Type: squl.JoinTypeRight,
                LHS: &squl.JoinExpr{
                    Type: squl.JoinTypeLeft,
                    LHS: &squl.JoinExpr{
                        Type: squl.JoinTypeInner,
                        LHS: &squl.RangeVar{
                            Name:  "laptops",
                            Alias: "l",
                        },
                        RHS: &squl.RangeVar{Name: "payment"},
                        Qualifiers: &squl.Expr{
                            Type: squl.ExprTypeOp,
                            Name: "=",
                            LHS:  &squl.ColumnRef{Fields: []string{"payment", "id"}},
                            RHS:  &squl.ColumnRef{Fields: []string{"a", "id"}},
                        },
                    },
                    RHS: &squl.RangeVar{Name: "people"},
                    Qualifiers: &squl.Expr{
                        Type: squl.ExprTypeOp,
                        Name: "=",
                        LHS:  &squl.ColumnRef{Fields: []string{"people", "id"}},
                        RHS:  &squl.ColumnRef{Fields: []string{"a", "id"}},
                    },
                },
                RHS: &squl.RangeVar{Name: "cars"},
                Qualifiers: &squl.Expr{
                    Type: squl.ExprTypeOp,
                    Name: "=",
                    LHS:  &squl.ColumnRef{Fields: []string{"cars", "id"}},
                    RHS:  &squl.ColumnRef{Fields: []string{"a", "id"}},
                },
            },
        },
    })
}

UPDATE

UPDATE stock SET retail = stock_backup.retail FROM stock_backup WHERE stock.isbn = stock_backup.isbn

package main

import (
    "github.com/trivigy/squl"
)

func main() {
	query, args, err := squl.Build(&squl.Update{
        Relation: &squl.RangeVar{
            Name: "stock",
        },
        Targets: &squl.ResTarget{
            Name:  &squl.ColumnRef{Fields: "retail"},
            Value: &squl.ColumnRef{Fields: []string{"stock_backup", "retail"}},
        },
        From: &squl.RangeVar{
            Name: "stock_backup",
        },
        Where: &squl.Expr{
            Type: squl.ExprTypeOp,
            Name: "=",
            LHS:  &squl.ColumnRef{Fields: []string{"stock", "isbn"}},
            RHS:  &squl.ColumnRef{Fields: []string{"stock_backup", "isbn"}},
        },
    })
}

DELETE

DELETE FROM products WHERE obsoletion_date = 'today' RETURNING *

package main

import (
    "github.com/trivigy/squl"
)

func main() {
	query, args, err := squl.Build(&squl.Delete{
        Relation: &squl.RangeVar{
            Name: "products",
        },
        Where: &squl.Expr{
            Type: squl.ExprTypeOp,
            Name: "=",
            LHS:  &squl.ColumnRef{Fields: "obsoletion_date"},
            RHS:  &squl.Const{Value: "today"},
        },
        Returning: &squl.ResTarget{
            Value: &squl.ColumnRef{Fields: "*"},
        },
    })
}

Documentation

Index

• Constants
• func Build(cmd Command) (string, []interface{}, error)
• type ArrayExpr
• type BoolExpr
• type BoolExprType
  • func NewBoolExprType(raw string) (BoolExprType, error)
  • func (r BoolExprType) MarshalJSON() ([]byte, error)
  • func (r BoolExprType) String() string
  • func (r *BoolExprType) UnmarshalJSON(rbytes []byte) error
• type ColumnRef
• type Command
• type Const
• type Default
• type Delete
• type Expr
• type ExprType
  • func NewExprType(raw string) (ExprType, error)
  • func (r ExprType) MarshalJSON() ([]byte, error)
  • func (r ExprType) String() string
  • func (r *ExprType) UnmarshalJSON(rbytes []byte) error
• type FuncCall
• type IndexElem
• type Infer
• type Insert
• type Into
• type JoinExpr
• type JoinType
  • func NewJoinType(raw string) (JoinType, error)
  • func (r JoinType) MarshalJSON() ([]byte, error)
  • func (r JoinType) String() string
  • func (r *JoinType) UnmarshalJSON(rbytes []byte) error
• type List
• type Node
• type Null
• type OnConflict
• type OnConflictAction
  • func NewOnConflictAction(raw string) (OnConflictAction, error)
  • func (r OnConflictAction) MarshalJSON() ([]byte, error)
  • func (r OnConflictAction) String() string
  • func (r *OnConflictAction) UnmarshalJSON(rbytes []byte) error
• type OrderBy
• type OrderByDirection
  • func NewOrderByDirection(raw string) (OrderByDirection, error)
  • func (r OrderByDirection) MarshalJSON() ([]byte, error)
  • func (r OrderByDirection) String() string
  • func (r *OrderByDirection) UnmarshalJSON(rbytes []byte) error
• type OrderByNulls
  • func NewOrderByNulls(raw string) (OrderByNulls, error)
  • func (r OrderByNulls) MarshalJSON() ([]byte, error)
  • func (r OrderByNulls) String() string
  • func (r *OrderByNulls) UnmarshalJSON(rbytes []byte) error
• type Persistence
  • func NewPersistence(raw string) (Persistence, error)
  • func (r Persistence) MarshalJSON() ([]byte, error)
  • func (r Persistence) String() string
  • func (r *Persistence) UnmarshalJSON(rbytes []byte) error
• type RangeVar
• type ResTarget
• type Select
• type TypeCast
• type Update
• type Var

Constants

const (
	// Unknown defines default public constant to use for "enum" like struct
	// comparisons when no value was defined.
	Unknown = iota
)

Functions

func Build

func Build(cmd Command) (string, []interface{}, error)

Build parses the provided go template and produces a query string and arguments slice consumable by db.Exec().

Types

type ArrayExpr

type ArrayExpr List

ArrayExpr - an ARRAY[] construct

type BoolExpr

type BoolExpr struct {
	Xpr  Node         `json:"xpr"`
	Type BoolExprType `json:"type"`
	Wrap bool         `json:"wrap"` /* indicate if expr should be wrapped with parentheses */
	Args []Node       `json:"args"` /* arguments to this expression */
}

BoolExpr describes the AND/OR expression combinations.

type BoolExprType

type BoolExprType int

BoolExprType describes the types of boolean expressions available.

const (
	// BoolExprTypeAnd describes the AND expression.
	BoolExprTypeAnd BoolExprType = iota + 1

	// BoolExprTypeOr describes the OR expression.
	BoolExprTypeOr
)

func NewBoolExprType

func NewBoolExprType(raw string) (BoolExprType, error)

NewBoolExprType creates a new instance of the enum from raw string.

func (BoolExprType) MarshalJSON

func (r BoolExprType) MarshalJSON() ([]byte, error)

MarshalJSON marshals the enum as a quoted json string.

func (BoolExprType) String

func (r BoolExprType) String() string

String returns the string representation of the enum type

func (*BoolExprType) UnmarshalJSON

func (r *BoolExprType) UnmarshalJSON(rbytes []byte) error

UnmarshalJSON unmarshals a quoted json string to enum type.

type ColumnRef

type ColumnRef struct {
	// Fields represents field names, `*`, or subscripts.
	Fields interface{} `json:"fields"`
}

ColumnRef describes a fully qualified column name, possibly with subscripts.

type Command

type Command interface {
	// contains filtered or unexported methods
}

Command describes top level commands like INSERT, SELECT, UPDATE, DELETE, etc.

type Const

type Const struct {
	Value interface{}
}

Const describes a constant primitive value.

type Default

type Default struct{}

Default describes a placeholder for DEFAULT constant value.

type Delete

type Delete struct {

	// Relation represents the relation to delete from.
	Relation *RangeVar `json:"relation"`

	// Using represents optional USING clause for more tables.
	Using Node `json:"using"`

	// Where represents WHERE qualification.
	Where Node `json:"where"`

	// Returning represents a list of expressions to return.
	Returning Node `json:"returning"`
}

Delete defines the struct for the DELETE command.

type Expr

type Expr struct {
	Type ExprType `json:"type"` /* see above */

	// Wrap indicates whether or not to wrap the expression in parentheses.
	Wrap bool

	// Name represents possibly-qualified name of operator.
	Name interface{} `json:"name"` /* possibly-qualified name of operator */

	LHS Node `json:"lhs"` /* left argument, or NULL if none */
	RHS Node `json:"rhs"` /* right argument, or NULL if none */
}

Expr describes the expression clause.

type ExprType

type ExprType int

ExprType defines possible types of expressions.

const (
	// ExprTypeOp describes normal operator
	ExprTypeOp ExprType = iota + 1

	// ExprTypeOpAny describes scalar op ANY (array)
	ExprTypeOpAny

	// ExprTypeOpAll describes scalar op ALL (array)
	ExprTypeOpAll

	// ExprTypeDistinct describes IS DISTINCT FROM - name must be "="
	ExprTypeDistinct

	// ExprTypeNotDistinct describes IS NOT DISTINCT FROM - name must be "="
	ExprTypeNotDistinct

	// ExprTypeNullIf describes NULLIF - name must be "="
	ExprTypeNullIf

	// ExprTypeOf describes IS [NOT] OF - name must be "=" or "<>"
	ExprTypeOf

	// ExprTypeIn describes [NOT] IN - name must be "=" or "<>"
	ExprTypeIn

	// ExprTypeLike describes [NOT] LIKE - name must be "~~" or "!~~"
	ExprTypeLike

	// ExprTypeILike describes [NOT] ILIKE - name must be "~~*" or "!~~*"
	ExprTypeILike

	// ExprTypeSimilar describes [NOT] SIMILAR - name must be "~" or "!~"
	ExprTypeSimilar

	// ExprTypeBetween indicates that the name must be "BETWEEN".
	ExprTypeBetween

	// ExprTypeNotBetween indicates that the name must be "NOT BETWEEN".
	ExprTypeNotBetween

	// ExprTypeBetweenSym indicates that the name must be "BETWEEN SYMMETRIC".
	ExprTypeBetweenSym

	// ExprTypeNotBetweenSym indicates that the name must be "NOT BETWEEN SYMMETRIC".
	ExprTypeNotBetweenSym

	// ExprTypeParen indicates nameless dummy node for parentheses.
	ExprTypeParen
)

func NewExprType

func NewExprType(raw string) (ExprType, error)

NewExprType creates a new instance of the enum from raw string.

func (ExprType) MarshalJSON

func (r ExprType) MarshalJSON() ([]byte, error)

MarshalJSON marshals the enum as a quoted json string.

func (ExprType) String

func (r ExprType) String() string

String returns the string representation of the enum type

func (*ExprType) UnmarshalJSON

func (r *ExprType) UnmarshalJSON(rbytes []byte) error

UnmarshalJSON unmarshals a quoted json string to enum type.

type FuncCall

type FuncCall struct {
	Name interface{}   `json:"name"` /* qualified name of function */
	Args []interface{} `json:"args"` /* the arguments (list of exprs) */

}

FuncCall describes the function class expression clause.

type IndexElem

type IndexElem struct {
	Name interface{} `json:"name"` /* name of attribute to index, or NULL */
	Expr Node        `json:"expr"` /* expression to index, or NULL */

}

IndexElem is used during used in CREATE INDEX, and in ON CONFLICT and represents an indexable column.

type Infer

type Infer struct {
	IndexElems  *List   `json:"indexElems"`  /* IndexElems to infer unique index */
	WhereClause Node    `json:"whereClause"` /* qualification (partial-index predicate) */
	Conname     *string `json:"conname"`     /* Constraint name, or NULL if unnamed */
	Location    int     `json:"location"`    /* token location, or -1 if unknown */
}

Infer represents the ON CONFLICT unique index inference clause.

type Insert

type Insert struct {

	// Relation represents the relation to insert into.
	Relation *RangeVar `json:"relation"`

	// Columns represents names of target columns (optional).
	Columns *List `json:"columns"`

	// Values represents the SELECT, VALUES, or NULL.
	Select *Select `json:"select"`

	// OnConflict represents the conflict resolution policy.
	OnConflict *OnConflict `json:"onConflict"`

	// Returning represents a list of expressions to return.
	Returning Node `json:"returning"`
}

Insert defines the struct for the INSERT command.

type Into

type Into struct {
	Relation *RangeVar `json:"relation"` /* target relation name */
	Columns  List      `json:"columns"`  /* column names to assign, or NIL */

}

Into describes the INTO clause of SELELCT INTO command.

type JoinExpr

type JoinExpr struct {
	Type    JoinType `json:"type"`    /* type of join */
	Natural bool     `json:"natural"` /* Natural join? Will need to shape table */
	LHS     Node     `json:"lhs"`     /* left subtree */
	RHS     Node     `json:"rhs"`     /* right subtree */
	// Using     List     `json:"using"`     /* USING clause, if any (list of String) */
	Qualifiers Node `json:"qualifiers"` /* qualifiers on join, if any */

}

JoinExpr defines the struct for generating JOIN clauses.

type JoinType

type JoinType int

JoinType describes the types of joins available.

const (
	// JoinTypeDefault indicates the usage of JOIN.
	JoinTypeDefault JoinType = iota + 1

	// JoinTypeInner indicates the usage of INNER JOIN.
	JoinTypeInner

	// JoinTypeLeft indicates the usage of LEFT JOIN.
	JoinTypeLeft

	// JoinTypeOuterLeft indicates the usage of LEFT OUTER JOIN.
	JoinTypeOuterLeft

	// JoinTypeRight indicates the usage of RIGHT JOIN.
	JoinTypeRight

	// JoinTypeOuterRight indicates the usage of RIGHT OUTER JOIN.
	JoinTypeOuterRight

	// JoinTypeFull indicates the usage of FULL JOIN.
	JoinTypeFull

	// JoinTypeOuterFull indicates the usage of FULL OUTER JOIN.
	JoinTypeOuterFull

	// JoinTypeCross indicates the usage of CROSS JOIN.
	JoinTypeCross
)

func NewJoinType

func NewJoinType(raw string) (JoinType, error)

NewJoinType creates a new instance of the enum from raw string.

func (JoinType) MarshalJSON

func (r JoinType) MarshalJSON() ([]byte, error)

MarshalJSON marshals the enum as a quoted json string.

func (JoinType) String

func (r JoinType) String() string

String returns the string representation of the enum type

func (*JoinType) UnmarshalJSON

func (r *JoinType) UnmarshalJSON(rbytes []byte) error

UnmarshalJSON unmarshals a quoted json string to enum type.

type List

type List []Node

List describes a collection of nodes aggregated together.

type Node

type Node interface {
	// contains filtered or unexported methods
}

Node describes the abstract element which can be built.

type Null

type Null struct{}

Null defines a placehold for specifying NULL constant value.

type OnConflict

type OnConflict struct {
	Action      OnConflictAction `json:"action"`      /* DO NOTHING or UPDATE? */
	Infer       *Infer           `json:"infer"`       /* Optional index inference clause */
	TargetList  *List            `json:"targetList"`  /* the target list (of ResTarget) */
	WhereClause Node             `json:"whereClause"` /* qualifications */
	Location    int              `json:"location"`    /* token location, or -1 if unknown */
}

OnConflict representation of ON CONFLICT clause.

type OnConflictAction

type OnConflictAction int

OnConflictAction represents the action an ON CONFLICT clause should take.

const (
	// OnConflictNothing indicates that the conflict resolution policy should
	// do nothing to resolve conflict.
	OnConflictNothing OnConflictAction = iota + 1

	// OnConflictUpdate indicates that the conflict resolution policy should
	// update the existing value.
	OnConflictUpdate
)

func NewOnConflictAction

func NewOnConflictAction(raw string) (OnConflictAction, error)

NewOnConflictAction creates a new instance of the enum from raw string.

func (OnConflictAction) MarshalJSON

func (r OnConflictAction) MarshalJSON() ([]byte, error)

MarshalJSON marshals the enum as a quoted json string.

func (OnConflictAction) String

func (r OnConflictAction) String() string

String returns the string representation of the enum type

func (*OnConflictAction) UnmarshalJSON

func (r *OnConflictAction) UnmarshalJSON(rbytes []byte) error

UnmarshalJSON unmarshals a quoted json string to enum type.

type OrderBy

type OrderBy struct {
	// Value represents expresiion to order by.
	Value Node `json:"value"`

	// Direction represents sort direction ASC/DESC/USING/default.
	Direction OrderByDirection `json:"direction"`

	// UsingOp represents name of op to use with OrderByDirectionUsing.
	UsingOp string `json:"UsingOp"`

	// Nulls represents NULLS sort order FIRST/LAST.
	Nulls OrderByNulls `json:"nulls"`
}

OrderBy defines the ORDER BY clause.

type OrderByDirection

type OrderByDirection int

OrderByDirection describes the ORDER BY direction ASC/DESC/USING.

const (
	// OrderByDirectionAsc indicates the sorting direction is ascending.
	OrderByDirectionAsc OrderByDirection = iota + 1

	// OrderByDirectionDesc indicates the sorting direction is descending.
	OrderByDirectionDesc

	// OrderByDirectionUsing indicates the usage of custom sorting direction operator.
	OrderByDirectionUsing
)

func NewOrderByDirection

func NewOrderByDirection(raw string) (OrderByDirection, error)

NewOrderByDirection creates a new instance of the enum from raw string.

func (OrderByDirection) MarshalJSON

func (r OrderByDirection) MarshalJSON() ([]byte, error)

MarshalJSON marshals the enum as a quoted json string.

func (OrderByDirection) String

func (r OrderByDirection) String() string

String returns the string representation of the enum type

func (*OrderByDirection) UnmarshalJSON

func (r *OrderByDirection) UnmarshalJSON(rbytes []byte) error

UnmarshalJSON unmarshals a quoted json string to enum type.

type OrderByNulls

type OrderByNulls int

OrderByNulls describes NULLS sorting order.

const (
	// OrderByNullsFirst indicates that sorting should list nulls first.
	OrderByNullsFirst OrderByNulls = iota + 1

	// OrderByNullsLast indicates that sorting should list nulls last.
	OrderByNullsLast
)

func NewOrderByNulls

func NewOrderByNulls(raw string) (OrderByNulls, error)

NewOrderByNulls creates a new instance of the enum from raw string.

func (OrderByNulls) MarshalJSON

func (r OrderByNulls) MarshalJSON() ([]byte, error)

MarshalJSON marshals the enum as a quoted json string.

func (OrderByNulls) String

func (r OrderByNulls) String() string

String returns the string representation of the enum type

func (*OrderByNulls) UnmarshalJSON

func (r *OrderByNulls) UnmarshalJSON(rbytes []byte) error

UnmarshalJSON unmarshals a quoted json string to enum type.

type Persistence

type Persistence int

Persistence describes the SELECT INTO persistence type.

const (
	// PersistencePermanent indicates permanent table persistence.
	PersistencePermanent Persistence = iota + 1

	// PersistenceUnlogged indicates unlogged table persistence
	PersistenceUnlogged

	// PersistenceTemporary indicates temporary table persistence.
	PersistenceTemporary
)

func NewPersistence

func NewPersistence(raw string) (Persistence, error)

NewPersistence creates a new instance of the enum from raw string.

func (Persistence) MarshalJSON

func (r Persistence) MarshalJSON() ([]byte, error)

MarshalJSON marshals the enum as a quoted json string.

func (Persistence) String

func (r Persistence) String() string

String returns the string representation of the enum type

func (*Persistence) UnmarshalJSON

func (r *Persistence) UnmarshalJSON(rbytes []byte) error

UnmarshalJSON unmarshals a quoted json string to enum type.

type RangeVar

type RangeVar struct {
	Schema string `json:"schema"`
	Name   string `json:"name"`
	// Inheritance bool        `json:"inheritance"`
	// Persistence Persistence `json:"persistence"`
	Alias string `json:"alias"`
}

RangeVar describes a schema qualified target table.

type ResTarget

type ResTarget struct {
	// Name represents a column name or nil.
	Name interface{} `json:"name"`

	// Value represents the value expression to compute or assign
	Value Node `json:"value"`

	// Alias represents column name or NULL
	Alias string `json:"alias"`
}

ResTarget defines the struct for generating target clauses like column_name.

type Select

type Select struct {

	// Distinct represents NULL, list of DISTINCT ON exprs, or * lcons(NIL,NIL) for all (SELECT DISTINCT)
	Distinct Node `json:"distinct"`

	// Into represents target for SELECT INTO command.
	Into *Into `json:"into"`

	// Targets represents the target list to select.
	Targets Node `json:"targets"`

	// From represents the FROM clause.
	From Node `json:"from"`

	// Where represents WHERE qualification.
	Where Node `json:"where"`

	// Group represents GROUP BY clauses.
	Group Node `json:"group"`

	// Having represents HAVING conditional-expression.
	Having Node `json:"having"`

	// Window represents WINDOW window_name AS (...), ...
	Window Node `json:"window"`

	// Values represents a VALUES list. If a "leaf" node representing a VALUES
	// list, the above fields are all null, and instead this field is set. Note
	// that the elements of the sublists are just expressions, without ResTarget
	// decoration. Also note that a list element can be DEFAULT (represented as
	// a SetToDefault node), regardless of the context of the VALUES list. It's
	// up to parse analysis to reject that where not valid.
	Values []Node `json:"values"`

	// Sort represents a list of ORDER BY.
	OrderBy Node `json:"orderBy"`

	// Offset represents the number of rows to skip before returning rows.
	Offset Node `json:"offset"`

	// Limit represents the maximum number of rows to returnl
	Limit Node `json:"limit"`
}

Select defines the struct for the SELECT/SELECT INTO commands.

type TypeCast

type TypeCast struct {
	Arg  Node   `json:"arg"`  /* the expression being casted */
	Type string `json:"type"` /* the target type */
}

TypeCast describes the type casting expression.

type Update

type Update struct {
	Relation *RangeVar `json:"relation"` /* relation to update */

	// Targets represents the target list to select.
	Targets Node `json:"targets"`

	// From represents the FROM clause.
	From Node `json:"from"`

	// Where represents WHERE qualification.
	Where Node `json:"where"`

	// Returning represents a list of expressions to return.
	Returning Node `json:"returning"`
}

Update defines the struct for the UPDATE command.

type Var

type Var struct {
	Value interface{}
}

Var defines an object for marking parameters in the query. Values which are placed inside of Var are ultimately replaced with `$%d` enumerated number and the argument is returned in the appropriate position when the quory is done building.