qb

package module
v2.0.2+incompatible Latest Latest
Warning

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

 Go to latest Published: Apr 18, 2019 License: MIT Imports: 4 Imported by: 0

README

qb

Go Report Card GoDoc

Qb is a simple query builder.

Get the package

$ go get -u github.com/alexandergrom/qb

Examples

Select ...

// Set database grammar (default postgres)
// Not concurrency. You can set it once in the settings
qb.DefaultGrammar("postgres")

// ...

b := new(qb.WhereBuilder).
    Where("type", "=", "a").
    WhereOr("type", "=", "b")

q := qb.Query("SELECT id FROM table WHERE %s LIMIT %p OFFSET %p", b, 10, 0)

// SELECT id FROM table WHERE "type" = $1 OR "type" = $2 LIMIT $3 OFFSET $4
fmt.Println(q)

// ["a", "b", 10, 0]
fmt.Println(q.Params())

Update ...

b := new(qb.SetBuilder).
    Set("name", "Marty").
    Set("surname", "McFly")

q := qb.Query("UPDATE table SET %s WHERE id = %p", b, 10)

// UPDATE table SET "name" = $1, "surname" = $2 WHERE id = $3
fmt.Println(q)

// ["Marty", "McFly", 10]
fmt.Println(q.Params())

Insert ...

b := new(qb.ValuesBuilder).
    Values(1, "Marty", "McFly")

q := qb.Query("INSERT INTO table (id, name, surname) VALUES %s", b)

// INSERT INTO table (id, name, surname) VALUES ($1, $2, $3)
fmt.Println(q)

// [1, "Marty", "McFly"]
fmt.Println(q.Params())

Array ...

b := new(qb.ListBuilder).
    Append("one", "two").
    Append("three")

q := Query("SELECT id FROM table WHERE name ?| ARRAY[%s]", b)

// SELECT id FROM table WHERE name ?| ARRAY[$1, $2, $3]
fmt.Println(q)

// ["one", "two", "three"]
fmt.Println(q.Params())
A more complex example
type (
    CarFilter struct {
        Mark      string
        Model     string
        Color     []int
        Price     []int
        CreatedAt []string
        Limit     int
        Offset    int
    }

    Car struct {
        Mark      string
        Model     string
        Color     int
        Price     int
        CreatedAt time.Time
        UpdatedAt time.Time
    }

    CarRepository struct {
        db *sql.DB
    }
)

func (r *CarRepository) GetByFilter(filter CarFilter) (_ []Car, err error) {
    var builder = new(qb.WhereBuilder).WhereRaw("1=1")

    if len(filter.Mark) > 0 {
        builder.Where("mark", "=", filter.Mark)
    }

    if len(filter.Model) > 0 {
        builder.Where("model", "=", filter.Model)
    }

    if len(filter.Color) > 0 {
        var colors = make([]interface{}, len(filter.Color))
        for i, v := range filter.Color {
            colors[i] = v
        }
        builder.WhereIn("color", colors...)
    }

    if len(filter.Price) == 1 {
        builder.Where("price", "<=", filter.Price[0])
    } else if len(filter.Price) == 2 {
        builder.
            Where("price", ">=", filter.Price[0]).
            Where("price", "<=", filter.Price[1])
    }

    if len(filter.CreatedAt) == 1 {
        builder.Where("created_at::data", "=", filter.CreatedAt[0])
    } else if len(filter.CreatedAt) == 2 {
        builder.
            Where("created_at::data", ">=", filter.CreatedAt[0]).
            Where("created_at::data", "<", filter.CreatedAt[1])
    }

    var query = qb.Query(`
        SELECT
            mark, model, color, price, created_at, updated_at
        FROM cars
        WHERE %s
        LIMIT %p
        OFFSET %p
    `, builder, filter.Limit, filter.Offset)

    var rows *sql.Rows
    if rows, err = r.db.Query(query.String(), query.Params()...); err != nil {
        return nil, err
    }
    defer rows.Close()

    cars = make([]Car, 0, filter.Limit)
    for rows.Next() {
        var car = Car{}

        if err = rows.Scan(&car.Mark, &car.Model, &car.Color, &car.Price, &car.CreatedAt, &car.UpdatedAt); err != nil {
            return nil, err
        }

        cars = append(cars, car)
    }

    return cars, rows.Err()
}

Documentation

Overview

Package qb is a simple query builder. 

var b = new(qb.WhereBuilder).
  Where("name", "=", "Marty").
  Where("surname", "=", "McFly")

var q = qb.Query("SELECT id FROM table WHERE %s LIMIT %p", b, 1)
_ = b.String() // SELECT id FROM table WHERE "name" = $1 AND "surname" = $2 LIMIT $3
_ = b.Params() // ["Marty", "McFly", 1]

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func DefaultGrammar 

func DefaultGrammar(name string)

DefaultGrammar sets a default grammar

func RegisterGrammar 

func RegisterGrammar(name string, grammar func() Grammar)

RegisterGrammar registers a new grammar

Types

type Builder 

type Builder interface {
	String() string
	Params() []interface{}
	Grammar(Grammar) Builder
}

Builder interface

func Query 

func Query(query string, params ...interface{}) Builder

Query formats according to a format specifier and returns the sql query string 

var q = qb.Query("SELECT id FROM table WHERE name = %p LIMIT %p OFFSET %p", "Tom", 10, 0)
_ = b.String() // SELECT id FROM table WHERE name = $1 LIMIT $2 OFFSET $3
_ = b.Params() // ["Tom", 10, 0]

type Grammar 

type Grammar interface {
	Wrap(s string) string
	Placeholder(n int) string
}

Grammar interface

func MysqlGrammar 

func MysqlGrammar() Grammar

MysqlGrammar returns a specific grammar for mysql

func PgsqlGrammar 

func PgsqlGrammar() Grammar

PgsqlGrammar returns a specific grammar for postgresql

func SQLiteGrammar 

func SQLiteGrammar() Grammar

SQLiteGrammar returns a specific grammar for sqlite

type ListBuilder 

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

ListBuilder builds list of placeholders

func (*ListBuilder) Append 

func (b *ListBuilder) Append(values ...interface{}) *ListBuilder

Append appends new values to the list 

var b = new(qb.ListBuilder).Append("one", "two", "three").
_ = b.String() // $1, $2, $3
_ = b.Params() // ["one", "two", "three"]

func (*ListBuilder) Grammar 

func (b *ListBuilder) Grammar(grammar Grammar) Builder

Grammar sets a Grammar

func (*ListBuilder) Params 

func (b *ListBuilder) Params() []interface{}

Params returns parameters for query

func (*ListBuilder) String 

func (b *ListBuilder) String() string

String implementations Stringer interface

type SetBuilder 

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

SetBuilder builds SET expressions

func (*SetBuilder) Grammar 

func (b *SetBuilder) Grammar(grammar Grammar) Builder

Grammar sets a Grammar

func (*SetBuilder) Params 

func (b *SetBuilder) Params() []interface{}

Params returns parameters for query

func (*SetBuilder) Set 

func (b *SetBuilder) Set(field string, value interface{}) *SetBuilder

Set adds a new SET expression 

var b = new(qb.SetBuilder).Set("name", "Tom").Set("surname", "Johnson")
_ = b.String() // "name" = $1, "surname" = $2
_ = b.Params() // ["Tom", "Johnson"]

func (*SetBuilder) SetRaw 

func (b *SetBuilder) SetRaw(query string, params ...interface{}) *SetBuilder

SetRaw adds a new SET expression 

var b = new(qb.SetBuilder).SetRaw("jsondata->'name' = %p", "Tom")
_ = b.String() // jsondata->'name' = $1
_ = b.Params() // ["Tom"]

func (*SetBuilder) String 

func (b *SetBuilder) String() string

String implementations Stringer interface

type ValuesBuilder 

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

ValuesBuilder builds VALUES expressions

func (*ValuesBuilder) Grammar 

func (b *ValuesBuilder) Grammar(grammar Grammar) Builder

Grammar sets a Grammar

func (*ValuesBuilder) Params 

func (b *ValuesBuilder) Params() []interface{}

Params returns parameters for query

func (*ValuesBuilder) String 

func (b *ValuesBuilder) String() string

String implementations Stringer interface

func (*ValuesBuilder) Values 

func (b *ValuesBuilder) Values(values ...interface{}) *ValuesBuilder

Values sets values and adds a new VALUES expression 

var b = new(qb.ValuesBuilder).
  Values(1, "Marty", "McFly").
  Values(2, "Emmett", "Brown")
_ = b.String() // ($1, $2, $3), ($4, $5, $6)
_ = b.Params() // [1, "Marty", "McFly", 2, "Emmett", "Brown"]

type WhereBuilder 

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

WhereBuilder builds WHERE expressions.

func (*WhereBuilder) Grammar 

func (b *WhereBuilder) Grammar(grammar Grammar) Builder

Grammar sets a Grammar

func (*WhereBuilder) Params 

func (b *WhereBuilder) Params() []interface{}

Params returns parameters for query

func (*WhereBuilder) String 

func (b *WhereBuilder) String() string

String implementations Stringer interface

func (*WhereBuilder) Where 

func (b *WhereBuilder) Where(field, operator string, value interface{}) *WhereBuilder

Where adds an expression to the group 

var b = new(qb.WhereBuilder).Where("name", "=", "Tom")
_ = b.String() // "name" = $1
_ = b.Params() // ["Tom"]

func (*WhereBuilder) WhereBuilder 

func (b *WhereBuilder) WhereBuilder(group *WhereBuilder) *WhereBuilder

WhereBuilder adds an expression to the group 

var g = new(qb.WhereBuilder).Where("id", "=", 1).WhereOr("id", "=", 2)
var b = new(qb.WhereBuilder).Where("name", "=", "Tom").WhereBuilder(g)
_ = b.String() // "name" = $1 AND ("id" = $2 OR "id" = $3)
_ = b.Params() // ["Tom", 1, 2]

func (*WhereBuilder) WhereBuilderOr 

func (b *WhereBuilder) WhereBuilderOr(group *WhereBuilder) *WhereBuilder

WhereBuilderOr adds an expression to the group 

var g = new(qb.WhereBuilder).Where("id", "=", 1).WhereOr("id", "=", 2)
var b = new(qb.WhereBuilder).Where("name", "=", "Tom").WhereBuilderOr(g)
_ = b.String() // "name" = $1 AND ("id" = $2 OR "id" = $3)
_ = b.Params() // ["Tom", 1, 2]

func (*WhereBuilder) WhereIn 

func (b *WhereBuilder) WhereIn(field string, params ...interface{}) *WhereBuilder

WhereIn adds an expression to the group 

var b = new(qb.WhereBuilder).WhereIn("id", 1, 2, 3)
_ = b.String() // "id" IN ($1, $2, $3)
_ = b.Params() // [1, 2, 3]

func (*WhereBuilder) WhereInOr 

func (b *WhereBuilder) WhereInOr(field string, params ...interface{}) *WhereBuilder

WhereInOr adds an expression to the group 

var b = new(qb.WhereBuilder).WhereInOr("id", 1, 2, 3)
_ = b.String() // "id" IN ($1, $2, $3)
_ = b.Params() // [1, 2, 3]

func (*WhereBuilder) WhereInSub 

func (b *WhereBuilder) WhereInSub(field string, query Builder) *WhereBuilder

WhereInSub adds an expression to the group 

var b = new(qb.WhereBuilder).WhereInSub("id", qb.Query(`SELECT id FROM table name = %p`, "Tom"))
_ = b.String() // "id" IN (SELECT id FROM table name = $1)
_ = b.Params() // ["Tom"]

func (*WhereBuilder) WhereInSubOr 

func (b *WhereBuilder) WhereInSubOr(field string, query Builder) *WhereBuilder

WhereInSubOr adds an expression to the group 

var b = new(qb.WhereBuilder).WhereInSubOr("id", qb.Query(`SELECT id FROM table name = %p`, "Tom"))
_ = b.String() // "id" IN (SELECT id FROM table name = $1)
_ = b.Params() // ["Tom"]

func (*WhereBuilder) WhereNotIn 

func (b *WhereBuilder) WhereNotIn(field string, params ...interface{}) *WhereBuilder

WhereNotIn adds an expression to the group 

var b = new(qb.WhereBuilder).WhereNotIn("id", 1, 2, 3)
_ = b.String() // "id" NOT IN ($1, $2, $3)
_ = b.Params() // [1, 2, 3]

func (*WhereBuilder) WhereNotInOr 

func (b *WhereBuilder) WhereNotInOr(field string, params ...interface{}) *WhereBuilder

WhereNotInOr adds an expression to the group 

var b = new(qb.WhereBuilder).WhereNotInOr("id", 1, 2, 3)
_ = b.String() // "id" NOT IN ($1, $2, $3)
_ = b.Params() // [1, 2, 3]

func (*WhereBuilder) WhereNotInSub 

func (b *WhereBuilder) WhereNotInSub(field string, query Builder) *WhereBuilder

WhereNotInSub adds an expression to the group 

var b = new(qb.WhereBuilder).WhereNotInSub("id", qb.Query(`SELECT id FROM table name = %p`, "Tom"))
_ = b.String() // "id" NOT IN (SELECT id FROM table name = $1)
_ = b.Params() // ["Tom"]

func (*WhereBuilder) WhereNotInSubOr 

func (b *WhereBuilder) WhereNotInSubOr(field string, query Builder) *WhereBuilder

WhereNotInSubOr adds an expression to the group 

var b = new(qb.WhereBuilder).WhereNotInSubOr("id", qb.Query(`SELECT id FROM table name = %p`, "Tom"))
_ = b.String() // "id" NOT IN (SELECT id FROM table name = $1)
_ = b.Params() // ["Tom"]

func (*WhereBuilder) WhereNotNull 

func (b *WhereBuilder) WhereNotNull(field string) *WhereBuilder

WhereNotNull adds an expression to the group 

var b = new(qb.WhereBuilder).WhereNotNull("data")
_ = b.String() // "data" IS NOT NULL
_ = b.Params() // []

func (*WhereBuilder) WhereNotNullOr 

func (b *WhereBuilder) WhereNotNullOr(field string) *WhereBuilder

WhereNotNullOr adds an expression to the group 

var b = new(qb.WhereBuilder).WhereNotNullOr("data")
_ = b.String() // "data" IS NOT NULL
_ = b.Params() // []

func (*WhereBuilder) WhereNull 

func (b *WhereBuilder) WhereNull(field string) *WhereBuilder

WhereNull adds an expression to the group 

var b = new(qb.WhereBuilder).WhereNull("data")
_ = b.String() // "data" IS NULL
_ = b.Params() // []

func (*WhereBuilder) WhereNullOr 

func (b *WhereBuilder) WhereNullOr(field string) *WhereBuilder

WhereNullOr adds an expression to the group 

var b = new(qb.WhereBuilder).WhereNullOr("data")
_ = b.String() // "data" IS NULL
_ = b.Params() // []

func (*WhereBuilder) WhereOr 

func (b *WhereBuilder) WhereOr(field, operator string, value interface{}) *WhereBuilder

WhereOr adds an expression to the group 

var b = new(qb.WhereBuilder).WhereOr("id", "=", "1").WhereOr("id", "=", "2")
_ = b.String() // "id" = $1 OR "id" = $2
_ = b.Params() // [1, 2]

func (*WhereBuilder) WhereRaw 

func (b *WhereBuilder) WhereRaw(query string, params ...interface{}) *WhereBuilder

WhereRaw adds an expression to the group 

var b = new(qb.WhereBuilder).WhereRaw("jsondata->%p = %p", "name", "Tom")
_ = b.String() // jsondata->$1 = $2
_ = b.Params() // ["name", "Tom"]

func (*WhereBuilder) WhereRawOr 

func (b *WhereBuilder) WhereRawOr(query string, params ...interface{}) *WhereBuilder

WhereRawOr adds an expression to the group 

var b = new(qb.WhereBuilder).WhereRawOr("jsondata->%p = %p", "name", "Tom")
_ = b.String() // jsondata->$1 = $2
_ = b.Params() // ["name", "Tom"]

Source Files

View all Source files

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.