SQL query builder with type support.
- 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:
`
- Standard:
go get -u github.com/sqlabble/sqlabble/...
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.
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)),
),
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.
Format
|
Nodeizer Tokenizer Generator
| | |
Statement --+-> Nodes --+-> Tokens --+-> Query
|
+--------------> Values
-
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})
-
JOIN {TABLE|SUBQUERY}
-
INNER JOIN {TABLE|SUBQUERY}
-
LEFT JOIN {TABLE|SUBQUERY}
-
RIGHT JOIN {TABLE|SUBQUERY}
-
ON {COLUMN} = {COLUMN}
-
USING {COLUMN}
-
{COLUMN} ASC
-
{COLUMN} DESC
-
{TABLE} AS {ALIAS}
-
{COLUMN} AS {ALIAS}
-
{COLUMN} = {VALUE|FUNCTION|SUBQUERY}
-
({STATEMENT}) UNION ({STATEMENT})
-
({STATEMENT}) UNION ALL ({STATEMENT})
-
({STATEMENT}) INTERSECT ({STATEMENT})
-
({STATEMENT}) INTERSECT ALL ({STATEMENT})
-
({STATEMENT}) EXCEPT ({STATEMENT})
-
({STATEMENT}) EXCEPT ALL ({STATEMENT})
-
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
-
{OPERATION} AND {OPERATION}
-
{OPERATION} OR {OPERATION}
-
NOT ({OPERATION})
-
{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
-
{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}
N/A
N/A
N/A
-
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})