gqb

package module
v0.0.0-...-4b10d28 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Jan 29, 2019 License: MIT Imports: 9 Imported by: 0

README

gqb - Golang Simple Query Builder

CircleCI GoDoc

Features

  • Build SQL easily through the method chains
  • Returns abstact scanned result
  • Query results can marshal JSON directly

Support drivers

  • MySQL
  • PostgreSQL
  • SQLite3

Installation

go get -u github.com/ysugimoto/gqb

Usage

Example database is here (MySQL):

CREATE TABLE IF NOT EXISTS companies (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  name varchar(255) NOT NULL,
  PRIMARY KEY (id)
) DEFAULT CHARSET=utf8;

INSERT INTO companies (name) VALUES ('Google'), ('Apple'), ('Microsoft');

CREATE TABLE IF NOT EXISTS company_attributes (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  company_id int(11) unsigned NOT NULL,
  url varchar(255) NOT NULL,
  PRIMARY KEY (id)
) DEFAULT CHARSET=utf8;

INSERT INTO company_attributes (company_id, url) VALUES (1, 'https://google.com'), (2, 'https://apple.com'), (3, 'https://microsoft.com');

And make sure *sql.DB is created properly:

// connect database as you expected
db, err := sql.Open("mysql", "user:pass@tcp(127.0.0.1:3306)/db_name")
if err != nil {
  log.Fatal(err)
}
defer db.Close()

And, also determine driver which you will use:

gqb.SetDriver("mysql") // also available "postgres" or "sqlite"

Above line is needed because gqb have to build SQL with considering driver's dialect.

Getting started (example for MySQL)

The following example maybe generic usage. We expects SQL as:

SELECT name FROM companies WHERE id = 1;

gqb makes above SQL and retrieve result by following code:

results, err := gqb.New(db).
  Select("name").
  Where("id", 1, gqb.Equal).
  Get("companies")

if err != nil {
  log.Fatal(err)
}
// retrieve result
for _, r := range results {
  fmt.Println(r.MustString("name")) //=> Google
}

// Also can marshal JSON directly
buf, _ := json.Marshal(results)
fmt.Println(string(buf)) //=> [{"name":"Google"}]

// Map to your struct
type Company struct {
  Name string `db:"name"`  // gqb maps value corresponds to "db" tag field
}
companies := []Company{}
if err := results.Map(&companies); err != nil {
  log.Fatal(err)
}
fmt.Println(companies[0].Name) //=> Google

If you want to get a single record, you can call GetOne("companies") instead. To learn more example usage, see examples.

Query Execution

Note that gqb is just only for query bulder, so query exection, prepared statement, escaping bind parameters depend on databae/sql.

gqb.New(db) of first argument accepts gqb.Executor interface which has a couple of methods:

  • QueryContext(ctx context.Context, query string, binds ...interface{})
  • ExecContext(ctx context.Context, query string, binds ...interface{})

It means you can use as same syntax in transaction. gqb.new(*sql.Tx) also valid.

Scan value

The gqb.Result struct can access through the XXX(column) or MustXXX(column). For example, to retrieve id int(11) column, you should call result.MustInt64("id").

Occasionally there is a case that result value null, then you can call v, err := result.Int64("id"). The err is returned if column value doesnt' exist or null.

Also, you can confirm field value is null via result.Nil("id"). It returns true if value is null.

And, if you want to use query result as your specific struct, you can call result.Map(&strcut). it will map values to field which corresponds to tag value of db:"field".

gqb supports following struct field types:

  • string / *string
  • int / *int
  • int8 / *int8
  • int16 / *int16
  • int32 / *int32
  • int64 / *int64
  • uint / *uint
  • uint8 / *uint8
  • uint16 / *uint16
  • uint32 / *uint32
  • uint64 / *uint64
  • float32 / *float32
  • float64 / *float64
  • time.Time / *time.Time
  • sql.NullString
  • sql.NullBool
  • sql.NullFloat64
  • sql.NullInt64

[]byte, corresponds to blob type column not supported.yet.

The gqb.Result object works as fuzzy type conversion process, so gqb converts result row type as far as possible, e.g:

  • int -> string via fmt.Sprint
  • string -> int via strconv.ParseInt

and so on. So, you will be able to access database column value as you want.

Benchmarks

Native SQL vs gqb Query Builder.

100 records:
BenchmarkNativeSQL-8      2000     696598 ns/op     1072 B/op    34 allocs/op
BenchmarkQueryBuilder-8   2000     653312 ns/op     2910 B/op    87 allocs/op

1000 records:
BenchmarkNativeSQL-8      2000     738930 ns/op     1076 B/op    34 allocs/op
BenchmarkQueryBuilder-8   2000     681146 ns/op     2912 B/op    87 allocs/op

10000 records:
BenchmarkNativeSQL-8      2000     747242 ns/op     1073 B/op    34 allocs/op
BenchmarkQueryBuilder-8   2000     751494 ns/op     2914 B/op    87 allocs/op

Author

Yoshiaki Sugimoto

License

MIT

Documentation

Index

Constants

View Source 
const (
	// Equal compares equavbalence between column and value
	Equal Comparison = "="
	// Alias for Equal
	Eq Comparison = "="

	// NotEqual compares not equavbalence between column and value
	NotEqual Comparison = "<>"
	// Alias for NotEqual
	NotEq Comparison = "<>"

	// Gt compares greater than between column and value
	Gt Comparison = ">"

	// Gte compares greater than equal between column and value
	Gte Comparison = "<="

	// Lt compares less than between column and value
	Lt Comparison = "<"

	// Lte compares less than equal between column and value
	Lte Comparison = "<="

	// In compares within values
	In Comparison = "IN"

	// NotIn compares whithout values
	NotIn Comparison = "NOT IN"

	// Like compares value matching phrase
	Like Comparison = "LIKE"

	// Like compares value not matching phrase
	NotLike Comparison = "NOT LIKE"

	// Desc indicates decendant
	Desc SortMode = "DESC"

	// Asc indicates ascendant
	Asc SortMode = "ASC"

	// Rand indicates random
	Rand SortMode = "RAND"

	// And concats conditions with AND
	And CombineType = "AND"

	// Or concats conditions with OR
	Or CombineType = "OR"
)

Variables

This section is empty.

Functions

func Alias 

func Alias(from, to string) alias

func SetDriver 

func SetDriver(driverType string)

Types

type CombineType 

type CombineType string

combine type indicates how to concat multiple WHERE conditions. This type is used for WHERE and private type

type Comparison 

type Comparison string

Comparison type indicates how to compare between column and value. This type is used for WHERE.

type Compat 

type Compat interface {
	Quote(string) string
	RandFunc() string
	PlaceHolder(int) string
}

type ConditionBuilder 

type ConditionBuilder interface {
	// buildCondition() builds WHERE condition string and append bind parameters.
	Build([]interface{}) (string, []interface{})

	// getCombine() should return concatenation string AND/OR
	Combine() string
}

conditionBuilder is private interface with create WHERE condition string.

type Data 

type Data map[string]interface{}

Data type is used for INSERT/UPDATE data definition. This is suger syntax for map[string]interface{}, but always fields are sorted by key.

func (Data) Keys 

func (d Data) Keys() []string

Return sorted field name strings

type Date 

type Date = time.Time

Date format for column types. This type corresponds to "DATE" column on mysql, "date" column on postgres.

type Datetime 

type Datetime = time.Time

Datetime format for column types This type corresponds to "DATETIME" on mysql, "timestamp" on postgres.

type Executor 

type Executor interface {
	QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error)
	ExecContext(context.Context, string, ...interface{}) (sql.Result, error)
}

SQL executor interface, this is enough to implement QueryContext() and ExecContext(). It's useful for running query in transation or not, because Executor accepts both of *sql.DB and *sql.Tx.

type Join 

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

Join is struct for making JOIN phrase

type MysqlCompat 

type MysqlCompat struct {
}

func (MysqlCompat) PlaceHolder 

func (c MysqlCompat) PlaceHolder(index int) string

func (MysqlCompat) Quote 

func (c MysqlCompat) Quote(str string) string

func (MysqlCompat) RandFunc 

func (c MysqlCompat) RandFunc() string

type Order 

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

Order is struct for making ORDER BY phrase

type PostgresCompat 

type PostgresCompat struct {
}

func (PostgresCompat) PlaceHolder 

func (c PostgresCompat) PlaceHolder(index int) string

func (PostgresCompat) Quote 

func (c PostgresCompat) Quote(str string) string

func (PostgresCompat) RandFunc 

func (c PostgresCompat) RandFunc() string

type QueryBuilder 

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

QueryBuilder is struct for stack some conditions, orders, ... with method chain.

func New 

func New(db Executor) *QueryBuilder

Create new Query QueryBuilder

func (*QueryBuilder) AddWhere 

func (q *QueryBuilder) AddWhere(c ConditionBuilder) *QueryBuilder

Add condition

func (*QueryBuilder) BulkInsert 

func (q *QueryBuilder) BulkInsert(table interface{}, data []Data) (sql.Result, error)

Execute bulk INSERT query

func (*QueryBuilder) BulkInsertContext 

func (q *QueryBuilder) BulkInsertContext(ctx context.Context, table interface{}, data []Data) (sql.Result, error)

Execute bulk INSERT query with context

func (*QueryBuilder) Delete 

func (q *QueryBuilder) Delete(table interface{}) (sql.Result, error)

Execute DELETE query

func (*QueryBuilder) DeleteContext 

func (q *QueryBuilder) DeleteContext(ctx context.Context, table interface{}) (sql.Result, error)

Execute DELETE query with context

func (*QueryBuilder) Get 

func (q *QueryBuilder) Get(table interface{}) (Results, error)

Execute query and get results

func (*QueryBuilder) GetContext 

func (q *QueryBuilder) GetContext(ctx context.Context, table interface{}) (Results, error)

Execute query and get results with context

func (*QueryBuilder) GetOne 

func (q *QueryBuilder) GetOne(table interface{}) (*Result, error)

Execute query and get first result

func (*QueryBuilder) GetOneContext 

func (q *QueryBuilder) GetOneContext(ctx context.Context, table interface{}) (*Result, error)

Execute query and get first result with context

func (*QueryBuilder) GroupBy 

func (q *QueryBuilder) GroupBy(fields ...string) *QueryBuilder

Add GROUP BY clause

func (*QueryBuilder) Insert 

func (q *QueryBuilder) Insert(table interface{}, data Data) (sql.Result, error)

Execute INSERT query

func (*QueryBuilder) InsertContext 

func (q *QueryBuilder) InsertContext(ctx context.Context, table interface{}, data Data) (sql.Result, error)

Execute INSERT query with context

func (*QueryBuilder) Join 

func (q *QueryBuilder) Join(table, from, to string, c Comparison) *QueryBuilder

Add JOIN table with condition

func (*QueryBuilder) Like 

func (q *QueryBuilder) Like(field string, value interface{}) *QueryBuilder

Add LIKE condition with AND combination

func (*QueryBuilder) Limit 

func (q *QueryBuilder) Limit(limit int64) *QueryBuilder

Set LIMIT field

func (*QueryBuilder) NotLike 

func (q *QueryBuilder) NotLike(field string, value interface{}) *QueryBuilder

Add NOT LIKE condition with AND combination

func (*QueryBuilder) Offset 

func (q *QueryBuilder) Offset(offset int64) *QueryBuilder

Set OFFSET field

func (*QueryBuilder) OrLike 

func (q *QueryBuilder) OrLike(field string, value interface{}) *QueryBuilder

Add LIKE condition with OR combination

func (*QueryBuilder) OrNotLike 

func (q *QueryBuilder) OrNotLike(field string, value interface{}) *QueryBuilder

Add NOT LIKE condition with OR combination

func (*QueryBuilder) OrWhere 

func (q *QueryBuilder) OrWhere(field string, value interface{}, comparison Comparison) *QueryBuilder

Add condition with OR combination

func (*QueryBuilder) OrWhereGroup 

func (q *QueryBuilder) OrWhereGroup(generator func(g *WhereGroup)) *QueryBuilder

Add WHERE condition group with OR. The first argument is generator function which accepts *ConditionGroup as argument. After call the generator function, add WHERE stack with called state

func (*QueryBuilder) OrWhereIn 

func (q *QueryBuilder) OrWhereIn(field string, values ...interface{}) *QueryBuilder

Add IN condition with OR combination

func (*QueryBuilder) OrWhereNotIn 

func (q *QueryBuilder) OrWhereNotIn(field string, values ...interface{}) *QueryBuilder

Add NOT IN condition with OR combination

func (*QueryBuilder) OrWhereRaw 

func (q *QueryBuilder) OrWhereRaw(raw string) *QueryBuilder

Add user specific raw condition with OR combination

func (*QueryBuilder) OrderBy 

func (q *QueryBuilder) OrderBy(field string, sort SortMode) *QueryBuilder

Add ORDER BY cluase

func (*QueryBuilder) Reset 

func (q *QueryBuilder) Reset()

Reset() resets stacks

func (*QueryBuilder) Select 

func (q *QueryBuilder) Select(fields ...interface{}) *QueryBuilder

Add SELECT fields

func (*QueryBuilder) SelectAvg 

func (q *QueryBuilder) SelectAvg(field string) *QueryBuilder

Add SELECT AVG fields

func (*QueryBuilder) SelectCount 

func (q *QueryBuilder) SelectCount(field string) *QueryBuilder

Add SELECT COUNT fields

func (*QueryBuilder) SelectMax 

func (q *QueryBuilder) SelectMax(field string) *QueryBuilder

Add SELECT MAX fields

func (*QueryBuilder) SelectMin 

func (q *QueryBuilder) SelectMin(field string) *QueryBuilder

Add SELECT MIN fields

func (*QueryBuilder) Update 

func (q *QueryBuilder) Update(table interface{}, data Data) (sql.Result, error)

Execute UPDATE query

func (*QueryBuilder) UpdateContext 

func (q *QueryBuilder) UpdateContext(ctx context.Context, table interface{}, data Data) (sql.Result, error)

Execute UPDATE query with context

func (*QueryBuilder) Where 

func (q *QueryBuilder) Where(field string, value interface{}, comparison Comparison) *QueryBuilder

Add condition with AND combination

func (*QueryBuilder) WhereGroup 

func (q *QueryBuilder) WhereGroup(generator func(g *WhereGroup)) *QueryBuilder

Add WHERE condition group with AND. The first argument is generator function which accepts *ConditionGroup as argument. After call the generator function, add WHERE stack with called state

func (*QueryBuilder) WhereIn 

func (q *QueryBuilder) WhereIn(field string, values ...interface{}) *QueryBuilder

Add IN condition with AND combination

func (*QueryBuilder) WhereNotIn 

func (q *QueryBuilder) WhereNotIn(field string, values ...interface{}) *QueryBuilder

Add NOT IN condition with AND combination

func (*QueryBuilder) WhereRaw 

func (q *QueryBuilder) WhereRaw(raw string) *QueryBuilder

Add user specific raw condition with AND combination

type Raw 

type Raw string

Raw type indicates "raw query phrase", it means gqb won't quote any string, columns. You should tableke carefully when use this type, but it's useful for use function like "COUNT(*)".

func (Raw) String 

func (r Raw) String() string

fmt.Stringer intetface implementation

type Result 

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

Result is struct for SELECT query result mapper

func NewResult 

func NewResult(values map[string]interface{}) *Result

Create Result pointer

func (*Result) Bytes 

func (r *Result) Bytes(f string) ([]byte, error)

Get field value as []byte with caring type conversion

func (*Result) Date 

func (r *Result) Date(f string) (time.Time, error)

Get field value as time.Time with caring type conversion, time parsing. The value must be and date format string

func (*Result) Datetime 

func (r *Result) Datetime(f string) (time.Time, error)

Get field value as time.Time with caring type conversion, time parsing. The value must be and dateitme format string

func (*Result) Float64 

func (r *Result) Float64(f string) (float64, error)

Get field value as float64 with caring type conversion

func (*Result) Int 

func (r *Result) Int(f string) (int, error)

Get field value as int with caring type conversion

func (*Result) Int64 

func (r *Result) Int64(f string) (int64, error)

Get field value as int64 with caring type conversion

func (*Result) Map 

func (r *Result) Map(dest interface{}) error

Map() assigns query result into supplied struct field values

func (*Result) MarshalJSON 

func (r *Result) MarshalJSON() ([]byte, error)

json.Marshaller interface implementation

func (*Result) MustBytes 

func (r *Result) MustBytes(f string) []byte

Force get field value as []byte

func (*Result) MustDate 

func (r *Result) MustDate(f string) time.Time

Force get field value as time.Time with date format

func (*Result) MustDatetime 

func (r *Result) MustDatetime(f string) time.Time

Force get field value as time.Time with datetime format

func (*Result) MustFloat64 

func (r *Result) MustFloat64(f string) float64

Force get field value as float64

func (*Result) MustInt 

func (r *Result) MustInt(f string) int

Force get field value as int

func (*Result) MustInt64 

func (r *Result) MustInt64(f string) int64

Force get field value as int64

func (*Result) MustString 

func (r *Result) MustString(f string) string

Force get field value as string

func (*Result) Nil 

func (r *Result) Nil(f string) bool

Check field value is nil

func (*Result) String 

func (r *Result) String(f string) (string, error)

Get field value as string with caring type conversion

type Results 

type Results []*Result

Short syntax for []*Result

func (Results) Map 

func (r Results) Map(dest interface{}) error

Map() assigns query result into supplied struct field values recursively

type SQLiteCompat 

type SQLiteCompat struct {
}

func (SQLiteCompat) PlaceHolder 

func (c SQLiteCompat) PlaceHolder(index int) string

func (SQLiteCompat) Quote 

func (c SQLiteCompat) Quote(str string) string

func (SQLiteCompat) RandFunc 

func (c SQLiteCompat) RandFunc() string

type SortMode 

type SortMode string

SortMode type indicates how to sort records. This type is used for ORDER BY

type Time 

type Time = time.Time

Time format for column types. This type corresponds to "time" column on posgres.

type WhereGroup 

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

WhereGroup is struct which wraps multiple Condition struct. This struct also implement ConditionBuilder inteface, so we can treat as same as Condition struct. This is used for grouped condition like "SELECT * FROM example WHERE A = 1 AND (B = 2 AND C = 3)" Parentheses inside condition is made by ConditionGroup

func (*WhereGroup) AddWhere 

func (w *WhereGroup) AddWhere(c ConditionBuilder) *WhereGroup

Add condition

func (*WhereGroup) Build 

func (w *WhereGroup) Build(binds []interface{}) (string, []interface{})

ConditionBuilder::Build() interface implementation

func (*WhereGroup) Combine 

func (w *WhereGroup) Combine() string

ConditionBuilder::Combine() interface implementation

func (*WhereGroup) Like 

func (w *WhereGroup) Like(field string, value interface{}) *WhereGroup

Add LIKE condition with AND combination

func (*WhereGroup) OrLike 

func (w *WhereGroup) OrLike(field string, value interface{}) *WhereGroup

Add LIKE condition with OR combination

func (*WhereGroup) OrWhere 

func (w *WhereGroup) OrWhere(field string, value interface{}, comparison Comparison) *WhereGroup

Add condition with OR combination

func (*WhereGroup) OrWhereIn 

func (w *WhereGroup) OrWhereIn(field string, values ...interface{}) *WhereGroup

Add IN condition with OR combination

func (*WhereGroup) OrWhereRaw 

func (w *WhereGroup) OrWhereRaw(raw string) *WhereGroup

Add user specific raw condition with OR combination

func (*WhereGroup) Where 

func (w *WhereGroup) Where(field string, value interface{}, comparison Comparison) *WhereGroup

Add condition with AND combination

func (*WhereGroup) WhereIn 

func (w *WhereGroup) WhereIn(field string, values ...interface{}) *WhereGroup

Add IN condition with AND combination

func (*WhereGroup) WhereRaw 

func (w *WhereGroup) WhereRaw(raw string) *WhereGroup

Add user specific raw condition with AND combination

Source Files

View all Source files

Directories

Path Synopsis
examples
mysql
postgres
sqlite

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.