sqlx

package
v0.0.0-...-b173b6e Latest Latest
Warning

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

 Go to latest Published: Feb 25, 2018 License: MIT Imports: 8 Imported by: 0

README

Componenta / Sqlx - (SQL-query builder for golang)

Simple SQL-query builder for golang. The package supports: PostgreSql, MySql and Sqlite

package main

import (
    "database/sql"
    "github.com/AlexanderGrom/componenta/sqlx"
    _ "github.com/lib/pq"
    "log"
)

func main() {
    db, err := sql.Open("postgres", "UserName:UserPass@/DBName")

    if err != nil {
        log.Fatalln("DB Connecting:", err)
    }

    sqlx.Driver("postgres")

    // SELECT "id", "name" FROM "users" WHERE "age" > $1 ORDER BY "created_at" DESC LIMIT $2;
    query := sqlx.Table("users").
        Select("id", "name").
        Where("age", ">", 21).
        OrderBy("created_at", "DESC").
        Limit(10)

    rows, err := db.Query(query.Sql(), query.Data()...)

    // ...
    // ...

    db.Close()
}

Обращение к таблице по её имени

// SELECT * FROM "users"
sql := sqlx.Table("users").Sql()

Вложенный запрос в From

// SELECT * FROM (SELECT "group_id", MAX("created_at") as "lastdate" FROM "users" GROUP BY "group_id") as "users" ORDER BY "lastdate" DESC
sql := sqlx.Table(func(builder *sqlx.Builder) {
    builder.Select("group_id").From("users").GroupBy("group_id").Max("created_at", "lastdate")
}).OrderBy("lastdate", "DESC").Sql()

Вложенный запрос в From используя разные построители

// SELECT * FROM (SELECT "group_id", MAX("created_at") as "lastdate" FROM "users" GROUP BY "group_id") as "users" ORDER BY "lastdate" DESC
sub := sqlx.Table("users").
    Select("group_id").
    GroupBy("group_id").
    Max("created_at", "lastdate")
sql := sqlx.Table(sqlx.Raw("( "+subque.Sql()+" ) as users", subque.Data()...)).
    OrderBy("lastdate", "DESC").
    Sql()

Выборка Select

// SELECT "id", "name", "age" FROM "users"
sql := sqlx.Table("users").Select("id", "name", "age").Sql()

Комбинированный Select

// SELECT "id", "name", "age", COUNT(*) as count FROM "users"
sql := sqlx.Table("users").
    Select("id", "name").Select("age").
    SelectRaw("COUNT(*) as count").
    Sql()

Сырые выражения в Select

// SELECT "age", COUNT(*) as count FROM "users" GROUP BY "count"
sql := sqlx.Table("users").
    Select("age", sqlx.Raw("COUNT(*) as count")).
    GroupBy("count").
    Sql()

Вложенный запрос в Select

// SELECT "name", (SELECT age FROM ag WHERE id = $1 LIMIT 1) as age FROM "users"
sql := sqlx.Table("users").
    Select("name", sqlx.Raw("(SELECT age FROM ag WHERE id = ? LIMIT 1) as age", 1)).
    Sql()

Вложенный запрос в Select

// SELECT "name", (SELECT age FROM ag WHERE id = $1 LIMIT 1) as age FROM "users"
sql := sqlx.Table("users").
    Select("name").
    SelectRaw("(SELECT age FROM ag WHERE id = ? LIMIT 1) as age", 1)
    Sql()

Агрегатные функции Count(column, alias)
Sum(column, alias)
Min(column, alias)
Max(column, alias)
Avg(column, alias)

// SELECT "age", COUNT(*) as "count" FROM "users" GROUP BY "count"
sql := sqlx.Table("users").
    Select("age").
    GroupBy("count").
    Count("*", "count")
    Sql()

Условия (Where)

// SELECT * FROM "users" WHERE "id" = $1
sql := sqlx.Table("users").
    Where("id", "=", 1).
    Sql()

Комбинация условий (And)

// SELECT * FROM "users" WHERE "age" >= $1 AND "created_at" < $2
sql := sqlx.sqlx.Table("users").
    Where("age", ">=", 21).
    Where("created_at", "<", "2016-01-01").
    Sql().

Комбинация условий (Or)

// SELECT * FROM "users" WHERE "id" = $1 OR "id" = $2
sql := sqlx.Table("users").
    Where("id", "=", 1).
    OrWhere("id", "=", 2).
    Sql()

Груповые условия Where

// SELECT * FROM "users" WHERE "group_id" = $1 OR ("age" = $2 OR "age" = $3)
sql := sqlx.Table("users").
    Where("id", "=", 1).
    OrWhereGroup(func(builder *sqlx.Builder) {
        builder.Where("age", "=", 18).OrWhere("age", "=", 21)
    }).Sql()

Сырые условия Where

// SELECT * FROM "users" WHERE "age" = $1 OR age = $2
sql := sqlx.Table("users").
    Where("age", "=", 18).
    OrWhereRaw("age = ?", 21).
    Sql()

Сырые условия Where с вложенным запросом

// SELECT * FROM "users" WHERE "age" = $1 OR "age" = (SELECT age FROM ag WHERE id = $2 OR id = $3 LIMIT 1)
sql := sqlx.Table("users").
    Where("age", "=", 18).
    OrWhere("age", "=", sqlx.Raw("(SELECT age FROM ag WHERE id = ? OR id = ? LIMIT 1)", 21, 27)).
    Sql()

Условия (Where Null и Where Not Null)

// SELECT * FROM "users" WHERE "country" IS NOT NULL
sql := sqlx.Table("users").WhereNotNull("country").Sql()

Условия (Where Between)

// SELECT * FROM "users" WHERE "create_at" BETWEEN $1 AND $2 AND "create_at" NOT BETWEEN $3 AND $4
sql := sqlx.Table("users").WhereBetween("create_at", "2007-01-01", "2007-12-31").Sql()

Условия (Where In)

// SELECT * FROM "users" WHERE "id" IN ($1, $2, $3, $4, $5, $6)
sql := sqlx.Table("users").WhereIn("id", sqlx.List{1, 2, 3, 4, 5, 6}).Sql()

Условия Where In c вложенным запросом

// SELECT * FROM "users" WHERE "id" IN (SELECT "user_id" FROM "orders" WHERE "city" = $1)
sql := sqlx.Table("users").
    WhereIn("id", func(builder *sqlx.Builder) {
        builder.Select("user_id").From("orders").Where("city", "=", "Moscow")
    }).Sql()

Группировка (Group By)

// SELECT "country", "city", COUNT(*) as "count" FROM "users" WHERE "age" > $1 GROUP BY "country", "city"
sql := sqlx.Table("users").
    Select("country", "city").
    Where("age", ">", 18).
    GroupBy("country", "city").
    Count("*", "count").
    Sql()

Сырые группировки

// SELECT to_char(created_at, 'YYYY-MM-DD') as date, COUNT(*) as "count" FROM "users" WHERE "age" > $1 GROUP BY to_char(crated_at, 'YYYY-MM-DD')
sql := sqlx.Table("users").
    SelectRaw("to_char(created_at, 'YYYY-MM-DD') as date").
    Where("age", ">", 18).
    GroupByRaw("to_char(created_at, 'YYYY-MM-DD')").
    Count("*", "count").
    Sql()

Группировка и условия после группировки (HAVING)

// SELECT "country", COUNT(*) as "count" FROM "users" WHERE "age" > $1 GROUP BY "country" HAVING "count" > $2
sql := sqlx.Table("users").
    Select("country").
    Where("age", ">", 18).
    GroupBy("country").
    Having("count", ">", 100).
    Count("*", "count").
    Sql()

Сортировка (ORDER BY)

// SELECT * FROM "users" WHERE "city" = $1 ORDER BY "id" DESC
sql := sqlx.Table("users").
    Where("city", "=", "Moscow").
    OrderBy("id", "DESC").
    Sql()

Лимиты (Limit и Offset)

// SELECT * FROM "users" WHERE "city" = $1 ORDER BY "age" ASC LIMIT $2 OFFSET $3
sql := sqlx.Table("users").
    Where("city", "=", "Moscow").
    OrderBy("age", "ASC").
    Limit(10).Offset(50).
    Sql()

Объединение (Join)

// SELECT * FROM "users" as "us" INNER JOIN "info" as "inf" ON ("us"."id" = "inf"."user_id" AND "us"."group" = $1)
sql := sqlx.Table("users as us").
    Join("info as inf", func(joiner *Joiner) {
        joiner.On("us.id", "=", "inf.user_id")
        joiner.Where("us.group", "=", "admin")
    }).Sql()

Объединение (Left Join)

// SELECT * FROM "users" as "us" LEFT JOIN "orders" as "ord" ON ("us"."id" = "ord"."user_id") WHERE "ord"."user_id" IS NOT NULL
sql := sqlx.Table("users as us").
    LeftJoin("orders as ord", func(joiner *Joiner) {
        joiner.On("us.id", "=", "ord.user_id")
    }).
    WhereNotNull("ord.user_id").
    Sql()

Удаление (Delete)

// DELETE FROM "users" WHERE "id" = $1
sql := sqlx.Table("users").
    Where("id", "=", 15).
    Delete().
    Sql()

Изменение (Update)

// UPDATE "users" SET "city" = $1, "name" = $2 WHERE "id" = $3
sql := sqlx.Table("users").
    Where("id", "=", 15).
    Update(sqlx.Data{"name": "Ivan", "city": "Moscow"}).
    Sql()

Вставка (Insert)

// INSERT INTO "users" ("id", "name") VALUES ($1, $2)
sql := sqlx.Table("users").
    Insert(sqlx.Data{"id": 1, "name": "Jack"}).
    Sql()

Если необходимо вставить несколько записей

// INSERT INTO "users" ("id", "name") VALUES ($1, $2), ($3, $4)
sql := sqlx.Table("users").
    Insert(sqlx.Data{"id": 1, "name": "Jack"}, sqlx.Data{"id": 2, "name": "Mike"}).
    Sql()

Или так

// INSERT INTO "users" ("id", "name") VALUES ($1, $2), ($3, $4)
sql := sqlx.Table("users").
    Insert(sqlx.Data{"id": 1, "name": "Jack"}).
    Insert(sqlx.Data{"id": 2, "name": "Mike"}).
    Sql()

Вставка Insert + Returning id

// [Postrges Only]
// INSERT INTO "users" ("id", "name") VALUES ($1, $2) Returning "id"
sql := sqlx.Table("users").
    Insert(sqlx.Data{"id": 1, "name": "Jack"}).
    ReturnId()
    Sql()

Вставка Insert + Ignore

// INSERT INTO "users" ("id", "name") VALUES ($1, $2) ON CONFLICT DO NOTHING
sql := sqlx.Table("users").
    Insert(sqlx.Data{"id": 1, "name": "Jack"}).
    OrIgnore()
    Sql()

Выполнение запросов и сканирование результатов

package main

import (
    "database/sql"
    "fmt"
    "github.com/AlexanderGrom/componenta/sqlx"
    _ "github.com/go-sql-driver/mysql"
    "log"
)

type User struct {
    Id    int
    Name  string
}

func main() {
    var err error

    db, err := sql.Open("mysql", "UserName:UserPass@/DBName")

    if err != nil {
        log.Fatalln("DB Connecting:", err)
    }

    sqlx.Driver("mysql")
    dbx := sqlx.DataBase(db)

    var users []User

    query := sqlx.Table("users").OrderBy("id", "asc").Limit(10)
    err = dbx.Query(query).Scan(&users)

    if err != nil {
        log.Fatalln("DB Query:", err)
    }

    for _, user := range users {
        fmt.Printf("%d, %s\n", user.Id, user.Name)
    }

    db.Close()
}

Скан в структуру

package main

import (
    "database/sql"
    "fmt"
    "github.com/AlexanderGrom/componenta/sqlx"
    _ "github.com/go-sql-driver/mysql"
    "log"
)

type User struct {
    Id    int
    Name  string
}

func main() {
    var err error

    db, err := sql.Open("mysql", "UserName:UserPass@/DBName")

    if err != nil {
        log.Fatalln("DB Connecting:", err)
    }

    sqlx.Driver("mysql")
    dbx := sqlx.DataBase(db)

    var user User

    query := sqlx.Table("users").Where("id", "=", 2).Limit(1)
    err = dbx.Query(query).Scan(&user)

    if err != nil {
        log.Fatalln("DB Query:", err)
    }

    fmt.Printf("%d, %s\n", user.Id, user.Name)

    db.Close()
}

Скан в переменные

package main

import (
    "database/sql"
    "fmt"
    "github.com/AlexanderGrom/componenta/sqlx"
    _ "github.com/go-sql-driver/mysql"
    "log"
)

func main() {
    var err error

    db, err := sql.Open("mysql", "UserName:UserPass@/DBName")

    if err != nil {
        log.Fatalln("DB Connecting:", err)
    }

    sqlx.Driver("mysql")
    dbx := sqlx.DataBase(db)

    var id int
    var name string

    query := sqlx.Table("users").Select("id", "name").Where("id", "=", 2).Limit(1)
    err = dbx.Query(query).Scan(&id, &name)

    if err != nil {
        log.Fatalln("DB Query:", err)
    }

    fmt.Printf("%d, %s\n", id, name)

    db.Close()
}

Больший контроль над выборкой

Сканируем и обрабатываем результат по кускам

package main

import (
    "database/sql"
    "fmt"
    "github.com/AlexanderGrom/componenta/sqlx"
    _ "github.com/go-sql-driver/mysql"
    "log"
)

type User struct {
    Id    int
    Name  string
}

func main() {
    var err error

    db, err := sql.Open("mysql", "UserName:UserPass@/DBName")

    if err != nil {
        log.Fatalln("DB Connecting:", err)
    }

    defer db.Close()

    sqlx.Driver("mysql")
    dbx := sqlx.DataBase(db)

    query := sqlx.Table("users").OrderBy("id", "asc")

    err = dbx.Query(query).Chunk(100, func(users []User) {
        for _, user := range users {
            fmt.Printf("%d, %s\n", user.Id, user.Name)
        }
    })

    if err != nil {
        log.Fatalln("DB Query:", err)
    }

    db.Close()
}

Documentation

Index

Constants

This section is empty.

Variables

View Source 
var ErrNoRows = sql.ErrNoRows

Functions

func Driver 

func Driver(name string)

Драйвер грамматики, который будет использован для построения запроса Параметр name может принимать значения: mysql, postgres, sqlite3

Types

type Builder 

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

func NewBuilder 

func NewBuilder() *Builder

func Table 

func Table(table interface{}) *Builder

Helper для создания строителя

func (*Builder) Avg 

func (self *Builder) Avg(column interface{}, alias string) *Builder

func (*Builder) Count 

func (self *Builder) Count(column interface{}, alias string) *Builder

func (*Builder) Data 

func (self *Builder) Data() []interface{}

func (*Builder) Delete 

func (self *Builder) Delete() *Builder

func (*Builder) From 

func (self *Builder) From(table interface{}) *Builder

func (*Builder) GroupBy 

func (self *Builder) GroupBy(p ...interface{}) *Builder

func (*Builder) GroupByRaw 

func (self *Builder) GroupByRaw(exp string, bindings ...interface{}) *Builder

func (*Builder) Having 

func (self *Builder) Having(column string, operator string, value interface{}) *Builder

func (*Builder) HavingGroup 

func (self *Builder) HavingGroup(callback func(*Builder)) *Builder

func (*Builder) HavingRaw 

func (self *Builder) HavingRaw(exp string, bindings ...interface{}) *Builder

func (*Builder) Insert 

func (self *Builder) Insert(data ...Data) *Builder

func (*Builder) Join 

func (self *Builder) Join(table string, callback func(*Joiner)) *Builder

func (*Builder) LeftJoin 

func (self *Builder) LeftJoin(table string, callback func(*Joiner)) *Builder

func (*Builder) Limit 

func (self *Builder) Limit(number interface{}) *Builder

func (*Builder) Max 

func (self *Builder) Max(column interface{}, alias string) *Builder

func (*Builder) Min 

func (self *Builder) Min(column interface{}, alias string) *Builder

func (*Builder) Offset 

func (self *Builder) Offset(number interface{}) *Builder

func (*Builder) OrHaving 

func (self *Builder) OrHaving(column string, operator string, value interface{}) *Builder

func (*Builder) OrHavingGroup 

func (self *Builder) OrHavingGroup(callback func(*Builder)) *Builder

func (*Builder) OrHavingRaw 

func (self *Builder) OrHavingRaw(exp string, bindings ...interface{}) *Builder

func (*Builder) OrIgnore 

func (self *Builder) OrIgnore() *Builder

func (*Builder) OrWhere 

func (self *Builder) OrWhere(column string, operator string, value interface{}) *Builder

func (*Builder) OrWhereBetween 

func (self *Builder) OrWhereBetween(column string, min, max interface{}) *Builder

func (*Builder) OrWhereGroup 

func (self *Builder) OrWhereGroup(callback func(*Builder)) *Builder

func (*Builder) OrWhereIn 

func (self *Builder) OrWhereIn(column string, values interface{}) *Builder

func (*Builder) OrWhereNotBetween 

func (self *Builder) OrWhereNotBetween(column string, min, max interface{}) *Builder

func (*Builder) OrWhereNotIn 

func (self *Builder) OrWhereNotIn(column string, values interface{}) *Builder

func (*Builder) OrWhereNotNull 

func (self *Builder) OrWhereNotNull(column string) *Builder

func (*Builder) OrWhereNull 

func (self *Builder) OrWhereNull(column string) *Builder

func (*Builder) OrWhereRaw 

func (self *Builder) OrWhereRaw(exp string, bindings ...interface{}) *Builder

func (*Builder) OrderBy 

func (self *Builder) OrderBy(column string, direction string) *Builder

func (*Builder) ReturnId 

func (self *Builder) ReturnId() *Builder

func (*Builder) Select 

func (self *Builder) Select(p ...interface{}) *Builder

func (*Builder) SelectRaw 

func (self *Builder) SelectRaw(exp string, bindings ...interface{}) *Builder

func (*Builder) Sql 

func (self *Builder) Sql() string

func (*Builder) Sum 

func (self *Builder) Sum(column interface{}, alias string) *Builder

func (*Builder) Update 

func (self *Builder) Update(data Data) *Builder

func (*Builder) Where 

func (self *Builder) Where(column string, operator string, value interface{}) *Builder

func (*Builder) WhereBetween 

func (self *Builder) WhereBetween(column string, min, max interface{}) *Builder

func (*Builder) WhereGroup 

func (self *Builder) WhereGroup(callback func(*Builder)) *Builder

func (*Builder) WhereIn 

func (self *Builder) WhereIn(column string, values interface{}) *Builder

func (*Builder) WhereNotBetween 

func (self *Builder) WhereNotBetween(column string, min, max interface{}) *Builder

func (*Builder) WhereNotIn 

func (self *Builder) WhereNotIn(column string, values interface{}) *Builder

func (*Builder) WhereNotNull 

func (self *Builder) WhereNotNull(column string) *Builder

func (*Builder) WhereNull 

func (self *Builder) WhereNull(column string) *Builder

func (*Builder) WhereRaw 

func (self *Builder) WhereRaw(exp string, bindings ...interface{}) *Builder

type ChunkFunk 

type ChunkFunk interface{}

type Chunker 

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

func NewChunker 

func NewChunker(r *sql.Rows) *Chunker

func (*Chunker) Chunk 

func (self *Chunker) Chunk(n int, f ChunkFunk) error

type DB 

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

func DataBase 

func DataBase(db *sql.DB) *DB

Helper для добавления нового подключения

func (*DB) Begin 

func (self *DB) Begin() (*Tx, error)

Начать транзакцию

func (*DB) Origin 

func (self *DB) Origin() *sql.DB

func (*DB) Query 

func (self *DB) Query(builder *Builder) *Query

func (*DB) QueryRaw 

func (self *DB) QueryRaw(query string, data ...interface{}) *Query

type Data 

type Data map[string]interface{}

func (Data) Keys 

func (self Data) Keys() []string

func (Data) Values 

func (self Data) Values() []interface{}

type DataBaser 

type DataBaser interface {
	Query(string, ...interface{}) (*sql.Rows, error)
	Exec(string, ...interface{}) (sql.Result, error)
}

type Expression 

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

func Raw 

func Raw(exp string, params ...interface{}) Expression

func (Expression) Data 

func (self Expression) Data() []interface{}

func (Expression) String 

func (self Expression) String() string

type Joiner 

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

func (*Joiner) On 

func (self *Joiner) On(column1, operator, column2 string) *Joiner

func (*Joiner) OrOn 

func (self *Joiner) OrOn(column1, operator, column2 string) *Joiner

func (*Joiner) OrWhere 

func (self *Joiner) OrWhere(column string, operator string, value interface{}) *Joiner

func (*Joiner) Where 

func (self *Joiner) Where(column string, operator string, value interface{}) *Joiner

type List 

type List []interface{}

type Querier 

type Querier interface {
	Query(*Builder) *Query
}

type Query 

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

func (*Query) Chunk 

func (self *Query) Chunk(i int, f ChunkFunk) error

Сканировать в "чанки" и обрабатывать по кускам

func (*Query) Exec 

func (self *Query) Exec() (Result, error)

Выполнение запроса

func (*Query) Scan 

func (self *Query) Scan(a ...interface{}) error

Сканировать результаты

type Result 

type Result interface {
	LastInsertId() int64
	RowsAffected() int64
}

type Scanner 

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

func NewScanner 

func NewScanner(r *sql.Rows) *Scanner

func (*Scanner) Scan 

func (self *Scanner) Scan(a ...interface{}) error

Сканирование результатов запроса в переменные

type Stringer 

type Stringer interface {
	String() string
}

Интерерфейс Stringer

type Tx 

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

func (*Tx) Commit 

func (self *Tx) Commit() error

Зафиксировать транзакцию

func (*Tx) Origin 

func (self *Tx) Origin() *sql.Tx

func (*Tx) Query 

func (self *Tx) Query(builder *Builder) *Query

func (*Tx) QueryRaw 

func (self *Tx) QueryRaw(query string, data ...interface{}) *Query

func (*Tx) Rollback 

func (self *Tx) Rollback() error

Откатить транзакцию

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.