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