README
¶
squel
This library will provide a structured way of writing SQL statements. Which works well in combination with the github.com/jmoiron/sqlx package.
SELECT statement
Using the sqlx package
package main
import (
"github.com/jeppech/squel"
"github.com/jmoiron/sqlx"
// pgsql driver
_ "github.com/jackc/pgx/v4/stdlib"
)
var db *sqlx.DB
struct sqlData {
username string
avatar string
email string
name string
}
func main() {
var err error
db, err = sqlx.Connect("pgx", "")
if err != nil {
panic(err)
}
username := "jeppech"
stmt := squel.Table("api.users usr")
stmt.LeftJoin("api.tickets tck", "tck.user_id = usr.id")
stmt.Where("usr.username = %s", username)
stmt.And("usr.role = %s", "admin")
query_string, query_args := stmt.Select("usr.username, usr.avatar, usr.email, tck.name")
/**
query_string:
SELECT usr.username, usr.avatar, usr.email, tck.name
FROM api.users usr
LEFT JOIN api.tickets tck ON tck.user_id = usr.id
WHERE usr.username = $1
AND usr.role = $2
query_args:
[jeppech admin]
*/
if err := stmt.Ok(); err != nil {
panic(err)
}
data := make([]*sqlData, 0)
err = db.Select(&data, query_string, query_args...)
if err != nil {
panic(err)
}
fmt.Printf("%+v", data)
}
Insert statement
stmt := squel.Table("api.users")
stmt.Field("username", "jeppech")
stmt.Field("role", "admin")
// NilField are usefull, if the value could be nil.
// i.e if the value originates from an API request, that could have NULL properties
stmt.NilField("firstname", "Jeppe")
stmt.NilField("lastname", "Christiansen")
query_string, query_args := stmt.Insert()
/**
query_string:
INSERT INTO api.users (username,role,firstname,lastname)
VALUES ($1,$2,$3,$4)
query_args:
[jeppech admin Jeppe Christiansen]
*/
Update statement
stmt := squel.Table("api.users")
stmt.Field("email", "hello@example.com")
stmt.Field("role", "peasant")
stmt.Where("username = %s", "jeppech")
query_string, query_args := stmt.Update()
/**
query_string:
UPDATE api.users SET email = $1, role = $2
WHERE username = $3
query_args:
[hello@example.com peasant jeppech]
*/
Group clause
stmt := Table("api.users")
stmt.WhereGroup(func(s *Statement) {
s.Or("username = %s", "jeppech")
s.Or("email = %s", "hello@example.com")
})
stmt.And("pswd = %s", "VerySekritPassw0rt")
query_string, query_args := stmt.Select("id")
/**
query_string:
SELECT id FROM api.users
WHERE (
username = $1 OR
email = $2
) AND pswd = $3
query_args:
[jeppech hello@example.com VerySekritPassw0rt]
*/
Documentation
¶
Index ¶
- func SetDebug(value bool)
- type Condition
- type Statement
- func (stmt *Statement) And(clause string, args ...interface{}) *Statement
- func (stmt *Statement) AndGroup(c func(s *Statement)) *Statement
- func (stmt *Statement) Delete() (string, []interface{})
- func (stmt *Statement) Field(name string, value interface{}) *Statement
- func (stmt *Statement) GroupBy(group string) *Statement
- func (stmt *Statement) InnerJoin(table string, clause string, args ...interface{}) *Statement
- func (stmt *Statement) Insert() (string, []interface{})
- func (stmt *Statement) Join(table string, join string, clause string, args []interface{}) *Statement
- func (stmt *Statement) LeftJoin(table string, clause string, args ...interface{}) *Statement
- func (stmt *Statement) Limit(limit int) *Statement
- func (stmt *Statement) NamedArgs(v bool)
- func (stmt *Statement) NilField(field string, value interface{}) *Statement
- func (stmt *Statement) Offset(offset int) *Statement
- func (stmt *Statement) Ok() error
- func (stmt *Statement) Or(clause string, args ...interface{}) *Statement
- func (stmt *Statement) OrGroup(c func(s *Statement)) *Statement
- func (stmt *Statement) OrderBy(fields string, direction string) *Statement
- func (stmt *Statement) OuterJoin(table string, clause string, args ...interface{}) *Statement
- func (stmt *Statement) Returning(fields string) *Statement
- func (stmt *Statement) RightJoin(table string, clause string, args ...interface{}) *Statement
- func (stmt *Statement) Select(fields string) (string, []interface{})
- func (stmt *Statement) Update() (string, []interface{})
- func (stmt *Statement) Where(clause string, args ...interface{}) *Statement
- func (stmt *Statement) WhereGroup(c func(s *Statement)) *Statement
- type TableField
Constants ¶
This section is empty.
Variables ¶
This section is empty.
Functions ¶
Types ¶
type Statement ¶
type Statement struct {
// contains filtered or unexported fields
}
func (*Statement) And ¶
And will render an AND clause to the statement. This will render a WHERE clause, if it's called before any other condition-method.
func (*Statement) AndGroup ¶
AndGroup will render a group of conditions inside an AND clause to the statement. Will fallback to WHERE if it is the first condition of the statement
func (*Statement) NamedArgs ¶
NamedArgs will enable the use og named arguments in the SQL statement instead of index based.
func (*Statement) NilField ¶
NilField will OMIT adding the field to the SQL statement, if the passed value is nil.
func (*Statement) Or ¶
Or will render an OR clause to the statement. This will render a WHERE clause, if it's called before any other condition-method.
func (*Statement) OrGroup ¶
OrGroup will render a group of conditions inside an OR clause to the statement. Will fallback to WHERE if it is the first condition of the statement
func (*Statement) Where ¶
Where will render a WHERE clause to the statement. Subsequent calls to this method, for the same statement, will render an AND clause.
func (*Statement) WhereGroup ¶
WhereGroup will render a group of conditions inside a WHERE clause to the statement. Subsequent calls to this method, for the same statement, will render a grouped AND clause.
type TableField ¶
type TableField struct {
// contains filtered or unexported fields
}
Source Files