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()
}