sqluct

package main

import (
	"fmt"
	"time"

	"github.com/Masterminds/squirrel"
	"github.com/bool64/sqluct"
)

func main() {
	sm := sqluct.Mapper{}

	type Order struct {
		ID        int       `db:"order_id,omitempty"`
		CreatedAt time.Time `db:"created_at,omitempty"`
	}

	o := Order{
		ID: 123,
	}

	q := sm.
		Select(squirrel.Select(), o).
		From("orders").
		Where(squirrel.Eq{
			sm.Col(&o, &o.ID): o.ID, // Col returns "order_id" defined in field tag.
		})
	query, args, err := q.ToSql()
	fmt.Println(query, args, err)

}

Output:

SELECT order_id, created_at FROM orders WHERE order_id = ? [123] <nil>

package main

import (
	"fmt"

	"github.com/Masterminds/squirrel"
	"github.com/bool64/sqluct"
)

func main() {
	sm := sqluct.Mapper{}

	type Order struct {
		ID     int `db:"order_id,omitempty"`
		Amount int `db:"amount"`
		UserID int `db:"user_id"`
	}

	o := Order{}
	o.Amount = 100
	o.UserID = 123

	q := sm.Insert(squirrel.Insert("orders"), o)

	query, args, err := q.ToSql()
	fmt.Println(query, args, err)

}

Output:

INSERT INTO orders (amount,user_id) VALUES (?,?) [100 123] <nil>

package main

import (
	"fmt"

	"github.com/Masterminds/squirrel"
	"github.com/bool64/sqluct"
)

func main() {
	sm := sqluct.Mapper{}

	type OrderData struct {
		Amount int `db:"amount"`
		UserID int `db:"user_id,omitempty"`
	}

	type Order struct {
		ID int `db:"order_id,omitempty"`
		OrderData
	}

	o := Order{}
	o.ID = 321

	q := sm.
		Select(squirrel.Select(), o).
		Where(squirrel.Eq{sm.Col(&o, &o.ID): o.ID})

	query, args, err := q.ToSql()
	fmt.Println(query, args, err)

}

Output:

SELECT order_id, amount, user_id WHERE order_id = ? [321] <nil>

package main

import (
	"fmt"

	"github.com/Masterminds/squirrel"
	"github.com/bool64/sqluct"
)

func main() {
	sm := sqluct.Mapper{}

	type OrderData struct {
		Amount int `db:"amount"`
		UserID int `db:"user_id,omitempty"`
	}

	type Order struct {
		ID int `db:"order_id,omitempty"`
		OrderData
	}

	o := Order{}
	o.ID = 321
	o.Amount = 100
	o.UserID = 123

	q := sm.
		Update(squirrel.Update("orders"), o.OrderData).
		Where(squirrel.Eq{sm.Col(&o, &o.ID): o.ID})

	query, args, err := q.ToSql()
	fmt.Println(query, args, err)

}

Output:

UPDATE orders SET amount = ?, user_id = ? WHERE order_id = ? [100 123 321] <nil>

package main

import (
	"fmt"

	"github.com/Masterminds/squirrel"
	"github.com/bool64/sqluct"
)

func main() {
	sm := sqluct.Mapper{}

	type OrderData struct {
		Amount int `db:"amount"`
		UserID int `db:"user_id,omitempty"`
	}

	type Order struct {
		ID int `db:"order_id"`
		OrderData
	}

	o := Order{}
	o.Amount = 100
	o.UserID = 123

	q := sm.
		Select(squirrel.Select().From("orders"), o).
		Where(sm.WhereEq(o.OrderData))

	query, args, err := q.ToSql()
	fmt.Println(query, args, err)

}

Output:

SELECT order_id, amount, user_id FROM orders WHERE amount = ? AND user_id = ? [100 123] <nil>

package main

import (
	"fmt"

	"github.com/Masterminds/squirrel"
	"github.com/bool64/sqluct"
)

func main() {
	sm := sqluct.Mapper{}

	type OrderData struct {
		Amount int `db:"amount"`
		UserID int `db:"user_id,omitempty"`
	}

	type Order struct {
		ID int `db:"id"`
		OrderData
	}

	type User struct {
		ID   int    `db:"id"`
		Name string `db:"name"`
	}

	rf := sqluct.Referencer{}
	o := &Order{}
	u := &User{}

	rf.AddTableAlias(o, "orders")
	rf.AddTableAlias(u, "users")

	q := sm.
		Select(squirrel.Select().From(rf.Ref(o)), o, rf.ColumnsOf(o)).
		Join(rf.Fmt("%s ON %s = %s", u, &o.UserID, &u.ID)).
		Where(sm.WhereEq(OrderData{
			Amount: 100,
			UserID: 123,
		}, rf.ColumnsOf(o)))

	query, args, err := q.ToSql()
	fmt.Println(query, args, err)

}

Output:

SELECT orders.id, orders.amount, orders.user_id FROM orders JOIN users ON orders.user_id = users.id WHERE orders.amount = ? AND orders.user_id = ? [100 123] <nil>

package main

import (
	"fmt"
	"log"

	"github.com/Masterminds/squirrel"
	"github.com/bool64/sqluct"
)

func main() {
	type User struct {
		ID        int    `db:"id"`
		FirstName string `db:"first_name"`
		LastName  string `db:"last_name"`
	}

	type DirectReport struct {
		ManagerID  int `db:"manager_id"`
		EmployeeID int `db:"employee_id"`
	}

	rf := sqluct.Referencer{}

	manager := &User{}
	rf.AddTableAlias(manager, "manager")

	employee := &User{}
	rf.AddTableAlias(employee, "employee")

	dr := &DirectReport{}
	rf.AddTableAlias(dr, "dr")

	// Find direct reports that share same last name and manager is not named John.
	qb := squirrel.StatementBuilder.Select(rf.Fmt("%s, %s", &dr.ManagerID, &dr.EmployeeID)).
		From(rf.Fmt("%s AS %s", rf.Q("users"), manager)).
		InnerJoin(rf.Fmt("%s AS %s ON %s = %s AND %s = %s",
			rf.Q("direct_reports"), dr,
			&dr.ManagerID, &manager.ID,
			&dr.EmployeeID, &employee.ID)).
		Where(rf.Fmt("%s = %s", &manager.LastName, &employee.LastName)).
		Where(rf.Fmt("%s != ?", &manager.FirstName), "John")

	stmt, args, err := qb.ToSql()
	if err != nil {
		log.Fatal(err)
	}

	fmt.Println(stmt)
	fmt.Println(args)

}

Output:

SELECT dr.manager_id, dr.employee_id FROM users AS manager INNER JOIN direct_reports AS dr ON dr.manager_id = manager.id AND dr.employee_id = employee.id WHERE manager.last_name = employee.last_name AND manager.first_name != ?
[John]

package main

import (
	"context"
	"log"

	"github.com/bool64/sqluct"
)

func main() {
	var (
		s   sqluct.Storage
		ctx context.Context
	)

	err := s.InTx(ctx, func(_ context.Context) error {
		return nil
	})
	if err != nil {
		log.Fatal(err)
	}
}

Output:

package main

import (
	"context"
	"log"
	"time"

	"github.com/Masterminds/squirrel"
	"github.com/bool64/sqluct"
)

func main() {
	var (
		s   sqluct.Storage
		ctx context.Context
	)

	const tableName = "products"

	type Product struct {
		ID        int       `db:"id"`
		Title     string    `db:"title"`
		CreatedAt time.Time `db:"created_at"`
	}

	// INSERT INTO products (id, title, created_at) VALUES (1, 'Apples', <now>), (2, 'Oranges', <now>)
	_, err := s.Exec(ctx, s.InsertStmt(tableName, []Product{
		{
			ID:        1,
			Title:     "Apples",
			CreatedAt: time.Now(),
		}, {
			ID:        2,
			Title:     "Oranges",
			CreatedAt: time.Now(),
		},
	}))
	if err != nil {
		log.Fatal(err)
	}

	// UPDATE products SET title = 'Bananas' WHERE id = 2
	_, err = s.Exec(
		ctx,
		s.UpdateStmt(tableName, Product{Title: "Bananas"}, sqluct.SkipZeroValues).
			Where(s.WhereEq(Product{ID: 2}, sqluct.SkipZeroValues)),
	)
	if err != nil {
		log.Fatal(err)
	}

	var (
		result []Product
		row    Product
	)
	// SELECT id, title, created_at FROM products WHERE id != 3 AND created_at <= <now>
	err = s.Select(ctx,
		s.SelectStmt(tableName, row).
			Where(squirrel.NotEq(s.WhereEq(Product{ID: 3}, sqluct.SkipZeroValues))).
			Where(squirrel.LtOrEq{s.Col(&row, &row.CreatedAt): time.Now()}),
		&result,
	)
	if err != nil {
		log.Fatal(err)
	}

	// DELETE FROM products WHERE id = 2
	_, err = s.Exec(ctx, s.DeleteStmt(tableName).Where(Product{ID: 2}, sqluct.SkipZeroValues))
	if err != nil {
		log.Fatal(err)
	}
}

Output:

package main

import (
	"context"
	"log"

	"github.com/bool64/sqluct"
)

func main() {
	var (
		s   sqluct.Storage
		ctx context.Context
	)

	type MyEntity struct {
		Name string `db:"name"`
		Age  int    `db:"age"`
	}

	row := MyEntity{
		Name: "Jane",
		Age:  30,
	}

	qb := s.InsertStmt("my_table", row)

	if _, err := s.Exec(ctx, qb); err != nil {
		log.Fatal(err)
	}
}

Output:

package main

import (
	"fmt"

	"github.com/bool64/sqluct"
)

func main() {
	var s sqluct.Storage

	type OrderData struct {
		Amount int `db:"amount"`
		UserID int `db:"user_id,omitempty"`
	}

	type Order struct {
		ID int `db:"id"`
		OrderData
	}

	type User struct {
		ID   int    `db:"id"`
		Name string `db:"name"`
	}

	rf := s.MakeReferencer()
	o := &Order{}
	u := &User{}

	rf.AddTableAlias(o, "orders")
	rf.AddTableAlias(u, "users")

	q := s.SelectStmt(rf.Ref(o), o, rf.ColumnsOf(o)).
		Columns(rf.Ref(&u.Name)).
		Join(rf.Fmt("%s ON %s = %s", u, &o.UserID, &u.ID)).
		Where(s.WhereEq(OrderData{
			Amount: 100,
			UserID: 123,
		}, rf.ColumnsOf(o)))

	query, args, err := q.ToSql()
	fmt.Println(query, args, err)

}

Output:

SELECT orders.id, orders.amount, orders.user_id, users.name FROM orders JOIN users ON orders.user_id = users.id WHERE orders.amount = $1 AND orders.user_id = $2 [100 123] <nil>

package main

import (
	"context"
	"log"

	"github.com/bool64/sqluct"
)

func main() {
	var (
		s   sqluct.Storage
		ctx context.Context
	)

	type MyEntity struct {
		Name string `db:"name"`
		Age  int    `db:"age"`
	}

	var row MyEntity

	qb := s.SelectStmt("my_table", row)

	if err := s.Select(ctx, qb, &row); err != nil {
		log.Fatal(err)
	}
}

Output:

package main

import (
	"context"
	"fmt"
	"log"

	"github.com/bool64/sqluct"
)

func main() {
	var (
		s   sqluct.Storage
		ctx context.Context
	)

	// Define your entity as a struct with `db` field tags that correspond to column names in table.
	type MyEntity struct {
		Name string `db:"name"`
		Age  int    `db:"age"`
	}

	// Create destination for query result.
	rows := make([]MyEntity, 0, 100)

	// Create SELECT statement from fields of entity.
	qb := s.SelectStmt("my_table", MyEntity{}).
		Where(s.WhereEq(MyEntity{
			Name: "Jane",
		}, sqluct.SkipZeroValues)) // Add WHERE condition built from fields of entity.

	// Query statement would be
	// 	SELECT name, age FROM my_table WHERE name = $1
	// with argument 'Jane'.

	err := s.Select(ctx, qb, &rows)
	if err != nil {
		log.Fatal(err)
	}

	for _, row := range rows {
		fmt.Println(row)
	}
}

Output:

package main

import (
	"context"
	"log"

	"github.com/bool64/sqluct"
)

func main() {
	var (
		s   sqluct.Storage
		ctx context.Context
	)

	type MyIdentity struct {
		ID int `db:"id"`
	}

	type MyValue struct {
		Name string `db:"name"`
		Age  int    `db:"age"`
	}

	row := MyValue{
		Name: "Jane",
		Age:  30,
	}

	qb := s.UpdateStmt("my_table", row).
		Where(s.WhereEq(MyIdentity{ID: 123}))

	if _, err := s.Exec(ctx, qb); err != nil {
		log.Fatal(err)
	}
}

Output: