sqlabble

README

sqlabble

SQL query builder with type support.

Features

• Type support:
  • Restrict method chain order.
  • Restrict expression that can be specified by the interface type.
• Flexible formatter:
  • Prefix and Indent.
  • Quote the alias according to the specification of each SQL server.
    • Standard: "
    • MySQL: `

Installation

go get -u github.com/sqlabble/sqlabble/...

Usage

Intro

import (
	"fmt"

	q "github.com/sqlabble/sqlabble"
	"github.com/sqlabble/sqlabble/builder"
)

func main() {
	stmt := q.Select(
		q.Column("person_id"),
		q.Column("fname"),
		q.Column("lname"),
		q.Column("birth_date"),
	).From(
		q.Table("person"),
	).Where(
		q.Column("lname").Eq(q.Param("Turner")),
	)

	query, values := builder.StandardIndented.Build(stmt)

	fmt.Println(query)
	// -> SELECT
	//      person_id
	//      , fname
	//      , lname
	//      , birth_date
	//    FROM
	//      person
	//    WHERE
	//      lname = ?

	fmt.Println(values)
	// -> [Turner]
}

If it is slightly redundant, there are short hands.

q.Select(
  q.C("person_id"),
  q.C("fname"),
  q.C("lname"),
  q.C("birth_date"),
).From(
  q.T("person"),
).Where(
  q.C("lname").Eq(q.P("Turner")),
)

If you do not want to write table names or column names many times with strings, try the code generation tool.

Insert

Select

q.Select(
  q.C("person_id").As("persion_id"),
  q.C("fname").As("persion_fname"),
  q.C("lname").As("persion_lname"),
  q.C("birth_date").As("persion_birth_date"),
).From(
  q.T("user"),
).Where(
  q.C("id").Eq(q.Param(3)),
),

Update

Delete

Sets

Subqueries

Code Generation Tool

If you write table names and column names many times with strings, you will mistype someday. It would be nonsense to spend time finding mistypes. There is a code generation tool that implements a method that returns a table or column to a struct. Is declarative coding is fun, right?

First, create a file named tables.go:

package tables

// +db:"persons"
type Person struct {
	PersonID             int
	FamilyName           string `db:"fname"`
	LastName             string `db:"lname"`
	BirthDate            time.Time
	SocialSecurityNumber string `db:"-"`
	password             string
}

And, call the following command at the terminal:

sqlabble tables.go

Then, a file named tables_sqlabble.go will be generated:

N/A

Finally, you will be able to construct queries using the added methods:

p := Person{}
q.Select(
    p.Columns()...,
  ).From(
    p.Table(),
  ).Where(
    p.ColumnLastName().Eq(q.P("Turner")),
  )

It's simple, and you never mistype table names or column names.

Processing Layers

                                   Format
                                     |
         Nodeizer   Tokenizer    Generator
            |           |            |
Statement --+-> Nodes --+-> Tokens --+-> Query
                        |
                        +--------------> Values

Supports

Clauses

• CREATE TABLE {TABLE}
• CREATE TABLE IF NOT EXISTS {TABLE}
• SELECT {COLUMN|FUNCTION|SUBQUERY}
• SELECT DISTINCT {COLUMN|FUNCTION|SUBQUERY}
• FROM {TABLE|SUBQUERY}
• WHERE {OPERATION}
• GROUP BY {COLUMN}
• HAVING
• ORDER BY {ORDER}
• LIMIT {COUNT}
• OFFSET {COUNT}
• INSERT INTO {TABLE} ({COLUMN})
• VALUES ({VALUE})
• DEFAULT VALUES
• UPDATE {TABLE}
• SET ({ASSIGNMENT})
• DELETE

Column Definition

• ({COLUMN} {DEFINITION})

Joins

• JOIN {TABLE|SUBQUERY}
• INNER JOIN {TABLE|SUBQUERY}
• LEFT JOIN {TABLE|SUBQUERY}
• RIGHT JOIN {TABLE|SUBQUERY}

Conditions

• ON {COLUMN} = {COLUMN}
• USING {COLUMN}

Orders

• {COLUMN} ASC
• {COLUMN} DESC

Aliases

• {TABLE} AS {ALIAS}
• {COLUMN} AS {ALIAS}

Assignment

• {COLUMN} = {VALUE|FUNCTION|SUBQUERY}

Sets

• ({STATEMENT}) UNION ({STATEMENT})
• ({STATEMENT}) UNION ALL ({STATEMENT})
• ({STATEMENT}) INTERSECT ({STATEMENT})
• ({STATEMENT}) INTERSECT ALL ({STATEMENT})
• ({STATEMENT}) EXCEPT ({STATEMENT})
• ({STATEMENT}) EXCEPT ALL ({STATEMENT})

Conditional Logics

• CASE {VALUE|COLUMN|FUNCTION|SUBQUERY} WHEN {VALUE} THEN {VALUE|COLUMN|FUNCTION|SUBQUERY} ELSE {VALUE|COLUMN|FUNCTION|SUBQUERY} END
• CASE WHEN {OPERATION} THEN {VALUE|COLUMN|FUNCTION|SUBQUERY} ELSE {VALUE|COLUMN|FUNCTION|SUBQUERY} END

Operators

Logical

• {OPERATION} AND {OPERATION}
• {OPERATION} OR {OPERATION}
• NOT ({OPERATION})

Comparison

Scalar

• {VALUE|COLUMN|FUNCTION|SUBQUERY} = {VALUE|COLUMN|FUNCTION|SUBQUERY}
• {VALUE|COLUMN|FUNCTION|SUBQUERY} != {VALUE|COLUMN|FUNCTION|SUBQUERY}
• {VALUE|COLUMN|FUNCTION|SUBQUERY} > {VALUE|COLUMN|FUNCTION|SUBQUERY}
• {VALUE|COLUMN|FUNCTION|SUBQUERY} >= {VALUE|COLUMN|FUNCTION|SUBQUERY}
• {VALUE|COLUMN|FUNCTION|SUBQUERY} < {VALUE|COLUMN|FUNCTION|SUBQUERY}
• {VALUE|COLUMN|FUNCTION|SUBQUERY} <= {VALUE|COLUMN|FUNCTION|SUBQUERY}
• {VALUE|COLUMN|FUNCTION|SUBQUERY} LIKE {VALUE|COLUMN|FUNCTION|SUBQUERY}
• {VALUE|COLUMN|FUNCTION|SUBQUERY} REGEXP {VALUE|COLUMN|FUNCTION|SUBQUERY}
• {VALUE|COLUMN|FUNCTION|SUBQUERY} BETWEEN {VALUE|COLUMN|FUNCTION|SUBQUERY} AND {VALUE|COLUMN|FUNCTION|SUBQUERY}
• {VALUE|COLUMN|FUNCTION|SUBQUERY} IN {VALUES|SUBQUERY}
• {VALUE|COLUMN|FUNCTION|SUBQUERY} NOT IN {VALUES|SUBQUERY}
• {COLUMN|SUBQUERY} IS NULL
• {COLUMN|SUBQUERY} IS NOT NULL

Nonscalar

• {VALUE|COLUMN|FUNCTION|SUBQUERY} = ALL {SUBQUERY}
• {VALUE|COLUMN|FUNCTION|SUBQUERY} != ALL {SUBQUERY}
• {VALUE|COLUMN|FUNCTION|SUBQUERY} > ALL {SUBQUERY}
• {VALUE|COLUMN|FUNCTION|SUBQUERY} >= ALL {SUBQUERY}
• {VALUE|COLUMN|FUNCTION|SUBQUERY} < ALL {SUBQUERY}
• {VALUE|COLUMN|FUNCTION|SUBQUERY} <= ALL {SUBQUERY}
• {VALUE|COLUMN|FUNCTION|SUBQUERY} = ANY {SUBQUERY}
• {VALUE|COLUMN|FUNCTION|SUBQUERY} != ANY {SUBQUERY}
• {VALUE|COLUMN|FUNCTION|SUBQUERY} > ANY {SUBQUERY}
• {VALUE|COLUMN|FUNCTION|SUBQUERY} >= ANY {SUBQUERY}
• {VALUE|COLUMN|FUNCTION|SUBQUERY} < ANY {SUBQUERY}
• {VALUE|COLUMN|FUNCTION|SUBQUERY} <= ANY {SUBQUERY}
• EXISTS {SUBQUERY}
• NOT EXISTS {SUBQUERY}

Functions

Control Flow

N/A

String

N/A

Numeric

N/A

Date and Time

• ADDDATE({VALUE|COLUMN|FUNCTION|SUBQUERY})
• ADDTIME({VALUE|COLUMN|FUNCTION|SUBQUERY})
• CONVERT_TZ({VALUE|COLUMN|FUNCTION|SUBQUERY})
• CURDATE({VALUE|COLUMN|FUNCTION|SUBQUERY})
• CURRENT_DATE({VALUE|COLUMN|FUNCTION|SUBQUERY})
• CURRENT_TIME({VALUE|COLUMN|FUNCTION|SUBQUERY})
• CURRENT_TIMESTAMP({VALUE|COLUMN|FUNCTION|SUBQUERY})
• CURTIME({VALUE|COLUMN|FUNCTION|SUBQUERY})
• DATE({VALUE|COLUMN|FUNCTION|SUBQUERY})
• ATE_AD({VALUE|COLUMN|FUNCTION|SUBQUERY})
• DATE_FORMAT({VALUE|COLUMN|FUNCTION|SUBQUERY})
• DATE_SUB({VALUE|COLUMN|FUNCTION|SUBQUERY})
• DATEDIFF({VALUE|COLUMN|FUNCTION|SUBQUERY})
• DAY({VALUE|COLUMN|FUNCTION|SUBQUERY})
• DAYNAME({VALUE|COLUMN|FUNCTION|SUBQUERY})
• DAYOFMONTH({VALUE|COLUMN|FUNCTION|SUBQUERY})
• DAYOFWEEK({VALUE|COLUMN|FUNCTION|SUBQUERY})
• DAYOFYEAR({VALUE|COLUMN|FUNCTION|SUBQUERY})
• EXTRACT({VALUE|COLUMN|FUNCTION|SUBQUERY})
• FROM_DAYS({VALUE|COLUMN|FUNCTION|SUBQUERY})
• FROM_UNIXTIME({VALUE|COLUMN|FUNCTION|SUBQUERY})
• GET_FORMAT({VALUE|COLUMN|FUNCTION|SUBQUERY})
• HOUR({VALUE|COLUMN|FUNCTION|SUBQUERY})
• LAST_DAY({VALUE|COLUMN|FUNCTION|SUBQUERY})
• LOCALTIME({VALUE|COLUMN|FUNCTION|SUBQUERY})
• LOCALTIMESTAMP({VALUE|COLUMN|FUNCTION|SUBQUERY})
• MAKEDATE({VALUE|COLUMN|FUNCTION|SUBQUERY})
• MAKETIME({VALUE|COLUMN|FUNCTION|SUBQUERY})
• MICROSECOND({VALUE|COLUMN|FUNCTION|SUBQUERY})
• MINUTE({VALUE|COLUMN|FUNCTION|SUBQUERY})
• MONTH({VALUE|COLUMN|FUNCTION|SUBQUERY})
• MONTHNAME({VALUE|COLUMN|FUNCTION|SUBQUERY})
• NOW({VALUE|COLUMN|FUNCTION|SUBQUERY})
• PERIOD_ADD({VALUE|COLUMN|FUNCTION|SUBQUERY})
• PERIOD_DIFF({VALUE|COLUMN|FUNCTION|SUBQUERY})
• QUARTER({VALUE|COLUMN|FUNCTION|SUBQUERY})
• SEC_TO_TIME({VALUE|COLUMN|FUNCTION|SUBQUERY})
• SECOND({VALUE|COLUMN|FUNCTION|SUBQUERY})
• STR_TO_DATE({VALUE|COLUMN|FUNCTION|SUBQUERY})
• SUBDATE({VALUE|COLUMN|FUNCTION|SUBQUERY})
• SUBTIME({VALUE|COLUMN|FUNCTION|SUBQUERY})
• SYSDATE({VALUE|COLUMN|FUNCTION|SUBQUERY})
• TIME({VALUE|COLUMN|FUNCTION|SUBQUERY})
• IME_FORMA({VALUE|COLUMN|FUNCTION|SUBQUERY})
• TIME_TO_SEC({VALUE|COLUMN|FUNCTION|SUBQUERY})
• TIMEDIFF({VALUE|COLUMN|FUNCTION|SUBQUERY})
• TIMESTAMP({VALUE|COLUMN|FUNCTION|SUBQUERY})
• TIMESTAMPADD({VALUE|COLUMN|FUNCTION|SUBQUERY})
• TIMESTAMPDIFF({VALUE|COLUMN|FUNCTION|SUBQUERY})
• TO_DAYS({VALUE|COLUMN|FUNCTION|SUBQUERY})
• TO_SECONDS({VALUE|COLUMN|FUNCTION|SUBQUERY})
• UNIX_TIMESTAMP({VALUE|COLUMN|FUNCTION|SUBQUERY})
• UTC_DATE({VALUE|COLUMN|FUNCTION|SUBQUERY})
• UTC_TIME({VALUE|COLUMN|FUNCTION|SUBQUERY})
• UTC_TIMESTAMP({VALUE|COLUMN|FUNCTION|SUBQUERY})
• WEEK({VALUE|COLUMN|FUNCTION|SUBQUERY})
• WEEKDAY({VALUE|COLUMN|FUNCTION|SUBQUERY})
• WEEKOFYEAR({VALUE|COLUMN|FUNCTION|SUBQUERY})
• YEAR({VALUE|COLUMN|FUNCTION|SUBQUERY})
• YEARWEEK({VALUE|COLUMN|FUNCTION|SUBQUERY})

Documentation

Index

• Variables
• func NewErrFoundMultipleRecords(table string) error
• func NewErrRecordNotFound(table string) error
• type DBInterface
• type ErrFoundMultipleRecords
  • func (err ErrFoundMultipleRecords) Error() string
• type ErrRecordNotFound
  • func (err ErrRecordNotFound) Error() string
• type Session
  • func NewSession(builderName string, db DBInterface) (*Session, error)

Variables

var (
	CreateTable            = stmt.NewCreateTable
	CreateTableIfNotExists = stmt.NewCreateTableIfNotExists
	Select                 = stmt.NewSelect
	SelectDistinct         = stmt.NewSelectDistinct
	InsertInto             = stmt.NewInsertInto
	Update                 = stmt.NewUpdate
	Delete                 = stmt.NewDelete

	SimpleCase   = stmt.NewSimpleCase
	SimpleWhen   = stmt.NewSimpleWhen
	SearchedCase = stmt.NewSearchedCase
	SearchedWhen = stmt.NewSearchedWhen

	Column   = stmt.NewColumn
	Table    = stmt.NewTable
	Val      = stmt.NewVal
	Vals     = stmt.NewVals
	Subquery = stmt.NewSubquery

	Assign    = stmt.NewAssign
	And       = stmt.NewAnd
	Or        = stmt.NewOr
	Not       = stmt.NewNot
	Exists    = stmt.NewExists
	NotExists = stmt.NewNotExists

	Union        = stmt.NewUnion
	UnionAll     = stmt.NewUnionAll
	Intersect    = stmt.NewIntersect
	IntersectAll = stmt.NewIntersectAll
	Except       = stmt.NewExcept
	ExceptAll    = stmt.NewExceptAll

	Wildcard = stmt.NewWildcard()

	Count = stmt.NewCount
	Max   = stmt.NewMax

	Add        = stmt.NewAdd
	Sub        = stmt.NewSub
	Mul        = stmt.NewMul
	Div        = stmt.NewDiv
	IntegerDiv = stmt.NewIntegerDiv
	Mod        = stmt.NewMod

	IFNULL = stmt.NewIFNULL
)

Methods exported to make statements.

Functions

func NewErrFoundMultipleRecords

func NewErrFoundMultipleRecords(table string) error

NewErrFoundMultipleRecords create an ErrFoundMultipleRecords instance - table: table name

func NewErrRecordNotFound

func NewErrRecordNotFound(table string) error

NewErrRecordNotFound create an ErrRecordNotFound instance - table: table name

Types

type DBInterface

type DBInterface interface {
	Query(query string, args ...interface{}) (*sql.Rows, error)
	QueryRow(query string, args ...interface{}) *sql.Row
	Exec(query string, args ...interface{}) (sql.Result, error)
}

DBInterface *sql.DB or *sql.Tx

type ErrFoundMultipleRecords

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

ErrFoundMultipleRecords some records found error

func (ErrFoundMultipleRecords) Error

func (err ErrFoundMultipleRecords) Error() string

type ErrRecordNotFound

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

ErrRecordNotFound record not found error

func (ErrRecordNotFound) Error

func (err ErrRecordNotFound) Error() string

type Session

type Session struct {
	DBInterface
	Builder *builder.Builder
}

Session wraps sql.DB

func NewSession

func NewSession(builderName string, db DBInterface) (*Session, error)

NewSession create a Session instance