sqluct

package module
v0.1.10 Latest Latest
Warning

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

 Go to latest Published: Oct 11, 2022 License: MIT Imports: 12 Imported by: 23

README

Struct-based database access layer for Go

Build Status Coverage Status GoDevDoc Time Tracker Code lines Comments

This module integrates github.com/Masterminds/squirrel query builder and github.com/jmoiron/sqlx to allow seamless operation based on field tags of row structure.

All three libraries collaborate with standard database/sql and do not take away low level control from user.

This library helps to eliminate literal string column references (e.g. "created_at") and use field references instead (e.g. rf.Ref(&row.CreatedAt) and other mapping functions).

Field tags (db by default) act as a source of truth for column names to allow better maintainability and fewer errors.

Components

Storage is a high level service that provides query building, query executing and result fetching facilities as easy to use facades.

Mapper is a lower level tool that focuses on managing squirrel query builder with row structures.

Referencer helps to build complex statements by providing fully qualified and properly escaped names for participating columns.

Simple CRUD

var (
    s   sqluct.Storage
    ctx context.Context
)

const tableName = "products"

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

// 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"}).
        Where(s.WhereEq(Product{ID: 2})),
)
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)
}

Referencing Fields In Complex Statements

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"`
}

var s sqluct.Storage

rf := s.Ref()

// Add aliased tables as pointers to structs.
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)). // Quote literal name and alias it with registered struct pointer.
    InnerJoin(rf.Fmt("%s AS %s ON %s = %s AND %s = %s",
        rf.Q("direct_reports"), dr,
        &dr.ManagerID, &manager.ID, // Identifiers are resolved using row field pointers.
        &dr.EmployeeID, &employee.ID)).
    Where(rf.Fmt("%s = %s", &manager.LastName, &employee.LastName)).
    Where(rf.Fmt("%s != ?", &manager.FirstName), "John") // Regular binds work same way as in standard squirrel.

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

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

// 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]

Omitting Zero Values

When building WHERE conditions from row structure it is often needed skip empty fields from condition.

Behavior with empty fields (zero values) can be controlled via omitempty field tag flag and sqluct.IgnoreOmitEmpty, sqluct.SkipZeroValues options.

Please check example below to learn about behavior differences.

var s sqluct.Storage

type Product struct {
    ID    int    `db:"id,omitempty"`
    Name  string `db:"name,omitempty"`
    Price int    `db:"price"`
}

query, args, err := s.SelectStmt("products", Product{}).Where(s.WhereEq(Product{
    ID:    123,
    Price: 0,
})).ToSql()
fmt.Println(query, args, err)
// This query skips `name` in where condition for its zero value and `omitempty` flag.
//   SELECT id, name, price FROM products WHERE id = $1 AND price = $2 [123 0] <nil>

query, args, err = s.SelectStmt("products", Product{}).Where(s.WhereEq(Product{
    ID:    123,
    Price: 0,
}, sqluct.IgnoreOmitEmpty)).ToSql()
fmt.Println(query, args, err)
// This query adds `name` in where condition because IgnoreOmitEmpty is applied and `omitempty` flag is ignored.
//   SELECT id, name, price FROM products WHERE id = $1 AND name = $2 AND price = $3 [123  0] <nil>

query, args, err = s.SelectStmt("products", Product{}).Where(s.WhereEq(Product{
    ID:    123,
    Price: 0,
}, sqluct.SkipZeroValues)).ToSql()
fmt.Println(query, args, err)
// This query adds skips both price and name from where condition because SkipZeroValues option is applied.
//   SELECT id, name, price FROM products WHERE id = $1 [123] <nil>

Documentation

Overview

Package sqluct provides integration of sqlx and squirrel with Go structures.

Index

Examples

Constants

View Source 
const (
	DialectUnknown  = Dialect("")
	DialectMySQL    = Dialect("mysql")
	DialectPostgres = Dialect("postgres")
	DialectSQLite3  = Dialect("sqlite3")
)

Supported dialects.

Variables

This section is empty.

Functions

func Columns 

func Columns(columns ...string) func(o *Options)

Columns are used to control which columns from the structure should be used.

func IgnoreOmitEmpty added in v0.1.5 

func IgnoreOmitEmpty(o *Options)

IgnoreOmitEmpty instructs mapper to use zero values of fields with `omitempty`.

func InsertIgnore added in v0.1.10 

func InsertIgnore(o *Options)

InsertIgnore enables ignoring of row conflict during INSERT.

func OrderDesc 

func OrderDesc(o *Options)

OrderDesc instructs mapper to use DESC order in Product func.

func QuoteANSI added in v0.1.3 

func QuoteANSI(tableAndColumn ...string) string

QuoteANSI adds double quotes to symbols names.

Suitable for PostgreSQL, MySQL in ANSI SQL_MODE, SQLite statements.

func QuoteBackticks added in v0.1.3 

func QuoteBackticks(tableAndColumn ...string) string

QuoteBackticks quotes symbol names with backticks.

Suitable for MySQL, SQLite statements.

func QuoteNoop added in v0.1.3 

func QuoteNoop(tableAndColumn ...string) string

QuoteNoop does not add any quotes to symbol names.

Used in Referencer by default.

func SkipZeroValues 

func SkipZeroValues(o *Options)

SkipZeroValues instructs mapper to ignore fields with zero values.

Example 
package main

import (
	"fmt"

	"github.com/bool64/sqluct"
)

func main() {
	var s sqluct.Storage

	type Product struct {
		ID    int    `db:"id,omitempty"`
		Name  string `db:"name,omitempty"`
		Price int    `db:"price"`
	}

	query, args, err := s.SelectStmt("products", Product{}).Where(s.WhereEq(Product{
		ID:    123,
		Price: 0,
	})).ToSql()
	fmt.Println(query, args, err)
	// This query skips `name` in where condition for its zero value and `omitempty` flag.
	//   SELECT id, name, price FROM products WHERE id = $1 AND price = $2 [123 0] <nil>

	query, args, err = s.SelectStmt("products", Product{}).Where(s.WhereEq(Product{
		ID:    123,
		Price: 0,
	}, sqluct.IgnoreOmitEmpty)).ToSql()
	fmt.Println(query, args, err)
	// This query adds `name` in where condition because IgnoreOmitEmpty is applied and `omitempty` flag is ignored.
	//   SELECT id, name, price FROM products WHERE id = $1 AND name = $2 AND price = $3 [123  0] <nil>

	query, args, err = s.SelectStmt("products", Product{}).Where(s.WhereEq(Product{
		ID:    123,
		Price: 0,
	}, sqluct.SkipZeroValues)).ToSql()
	fmt.Println(query, args, err)
	// This query adds skips both price and name from where condition because SkipZeroValues option is applied.
	//   SELECT id, name, price FROM products WHERE id = $1 [123] <nil>

}

Output:

SELECT id, name, price FROM products WHERE id = $1 AND price = $2 [123 0] <nil>
SELECT id, name, price FROM products WHERE id = $1 AND name = $2 AND price = $3 [123  0] <nil>
SELECT id, name, price FROM products WHERE id = $1 [123] <nil>

func TxFromContext 

func TxFromContext(ctx context.Context) *sqlx.Tx

TxFromContext gets transaction or nil from context.

func TxToContext 

func TxToContext(ctx context.Context, tx *sqlx.Tx) context.Context

TxToContext adds transaction to context.

Types

type Dialect added in v0.1.10 

type Dialect string

Dialect defines SQL dialect.

type Mapper 

type Mapper struct {
	ReflectMapper *reflectx.Mapper
	Dialect       Dialect
	// contains filtered or unexported fields
}

Mapper prepares select, insert and update statements.

func (*Mapper) Col 

func (sm *Mapper) Col(structPtr, fieldPtr interface{}) string

Col will try to find column name and will panic on error.

Example 
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>

func (*Mapper) ColumnsValues added in v0.1.1 

func (sm *Mapper) ColumnsValues(v reflect.Value, options ...func(*Options)) ([]string, []interface{})

ColumnsValues extracts columns and values from provided struct value.

func (*Mapper) FindColumnName 

func (sm *Mapper) FindColumnName(structPtr, fieldPtr interface{}) (string, error)

FindColumnName returns column name of a database entity field.

Entity field is defined by pointer to owner structure and pointer to field in that structure. 

entity := MyEntity{}
name, found := sm.FindColumnName(&entity, &entity.UpdatedAt)

func (*Mapper) FindColumnNames added in v0.1.3 

func (sm *Mapper) FindColumnNames(structPtr interface{}) (map[interface{}]string, error)

FindColumnNames returns column names mapped by a pointer to a field.

func (*Mapper) Insert 

func (sm *Mapper) Insert(q squirrel.InsertBuilder, val interface{}, options ...func(*Options)) squirrel.InsertBuilder

Insert adds struct value or slice of struct values to squirrel.InsertBuilder.

Example 
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>

func (*Mapper) Order deprecated 

func (sm *Mapper) Order(columns interface{}, options ...func(*Options)) string

Order maps struct field tags as "ORDER BY".

Deprecated: use Col with DESC/ASC.

func (*Mapper) Select 

func (sm *Mapper) Select(q squirrel.SelectBuilder, columns interface{}, options ...func(*Options)) squirrel.SelectBuilder

Select maps struct field tags as columns to squirrel.SelectBuilder, slice of struct is also accepted.

Example 
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>

func (*Mapper) Update 

func (sm *Mapper) Update(q squirrel.UpdateBuilder, val interface{}, options ...func(*Options)) squirrel.UpdateBuilder

Update sets struct value to squirrel.UpdateBuilder.

Example 
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>

func (*Mapper) WhereEq 

func (sm *Mapper) WhereEq(conditions interface{}, options ...func(*Options)) squirrel.Eq

WhereEq maps struct values as conditions to squirrel.Eq.

Example 
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>
Example (ColumnsOf) 
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>

type Options 

type Options struct {
	// SkipZeroValues instructs mapper to ignore fields with zero values regardless of `omitempty` tag.
	SkipZeroValues bool

	// IgnoreOmitEmpty instructs mapper to use zero values of fields with `omitempty`.
	IgnoreOmitEmpty bool

	// Columns is used to control which columns from the structure should be used.
	Columns []string

	// OrderDesc instructs mapper to use DESC order in Product func.
	OrderDesc bool

	// PrepareColumn allows control of column quotation or aliasing.
	PrepareColumn func(col string) string

	// InsertIgnore enables ignoring of row conflict during INSERT.
	// Uses
	//  - INSERT IGNORE for MySQL,
	//  - INSERT ON IGNORE for SQLite3,
	//  - INSERT ... ON CONFLICT DO NOTHING for Postgres.
	InsertIgnore bool
}

Options defines mapping and query building parameters.

type Referencer added in v0.1.3 

type Referencer struct {
	Mapper *Mapper

	// IdentifierQuoter is formatter of column and table names.
	// Default QuoteNoop.
	IdentifierQuoter func(tableAndColumn ...string) string
	// contains filtered or unexported fields
}

Referencer maintains a list of string references to fields and table aliases.

func (*Referencer) AddTableAlias added in v0.1.3 

func (r *Referencer) AddTableAlias(rowStructPtr interface{}, alias string)

AddTableAlias creates string references for row pointer and all suitable field pointers in it.

Empty alias is not added to column reference.

func (*Referencer) Cols added in v0.1.9 

func (r *Referencer) Cols(ptr interface{}) []string

Cols returns column references of a row structure.

func (*Referencer) ColumnsOf added in v0.1.6 

func (r *Referencer) ColumnsOf(rowStructPtr interface{}) func(o *Options)

ColumnsOf makes a Mapper option to prefix columns with table alias.

Argument is either a structure pointer or string alias.

func (*Referencer) Fmt added in v0.1.3 

func (r *Referencer) Fmt(format string, ptrs ...interface{}) string

Fmt formats according to a format specified replacing ptrs with their reference strings where possible.

Values that are not available as reference string are passed to fmt.Sprintf as is.

Example 
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]

func (*Referencer) Q added in v0.1.3 

func (r *Referencer) Q(tableAndColumn ...string) string

Q quotes identifier.

func (*Referencer) Ref added in v0.1.3 

func (r *Referencer) Ref(ptr interface{}) string

Ref returns reference string for struct or field pointer that was previously added with AddTableAlias.

It panics if pointer is unknown.

type Storage 

type Storage struct {
	Mapper *Mapper

	// Format is a placeholder format, default squirrel.Dollar.
	// Other values are squirrel.Question, squirrel.AtP and squirrel.Colon.
	Format squirrel.PlaceholderFormat

	// IdentifierQuoter is formatter of column and table names.
	// Default QuoteNoop.
	IdentifierQuoter func(tableAndColumn ...string) string

	// OnError is called when error is encountered, could be useful for logging.
	OnError func(ctx context.Context, err error)

	// Trace wraps a call to database.
	// It takes statement as arguments and returns
	// instrumented context with callback to call after db call is finished.
	Trace func(ctx context.Context, stmt string, args []interface{}) (newCtx context.Context, onFinish func(error))
	// contains filtered or unexported fields
}

Storage creates and executes database statements.

func NewStorage 

func NewStorage(db *sqlx.DB) *Storage

NewStorage creates an instance of Storage.

func (*Storage) Col 

func (s *Storage) Col(structPtr, fieldPtr interface{}) string

Col will try to find column name and will panic on error.

func (*Storage) DB added in v0.1.8 

func (s *Storage) DB() *sqlx.DB

DB returns database instance.

func (*Storage) DeleteStmt 

func (s *Storage) DeleteStmt(tableName string) squirrel.DeleteBuilder

DeleteStmt makes a delete query builder.

func (*Storage) Exec 

func (s *Storage) Exec(ctx context.Context, qb ToSQL) (res sql.Result, err error)

Exec executes query according to query builder.

func (*Storage) InTx 

func (s *Storage) InTx(ctx context.Context, fn func(context.Context) error) (err error)

InTx runs callback in a transaction.

If transaction already exists, it will reuse that. Otherwise it starts a new transaction and commit or rollback (in case of error) at the end.

Example 
package main

import (
	"context"
	"log"

	"github.com/bool64/sqluct"
)

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

	err := s.InTx(ctx, func(ctx context.Context) error {
		return nil
	})
	if err != nil {
		log.Fatal(err)
	}
}
Example (Full) 
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)
	}
}

func (*Storage) InsertStmt 

func (s *Storage) InsertStmt(tableName string, val interface{}, options ...func(*Options)) squirrel.InsertBuilder

InsertStmt makes an insert query builder.

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

func (*Storage) Query 

func (s *Storage) Query(ctx context.Context, qb ToSQL) (*sqlx.Rows, error)

Query queries database and returns raw result.

Select is recommended to use instead of Query.

func (*Storage) QueryBuilder 

func (s *Storage) QueryBuilder() squirrel.StatementBuilderType

QueryBuilder returns query builder with placeholder format.

func (*Storage) Ref added in v0.1.3 

func (s *Storage) Ref() *Referencer

Ref creates Referencer for query builder.

func (*Storage) Select 

func (s *Storage) Select(ctx context.Context, qb ToSQL, dest interface{}) (err error)

Select queries statement of query builder and scans result into destination.

Destination can be a pointer to struct or slice, e.g. `*row` or `*[]row`.

Example (Join) 
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.Ref()
	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>
Example (OneRow) 
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)
	}
}
Example (Slice) 
package main

import (
	"context"
	"fmt"
	"log"

	"github.com/bool64/sqluct"
	"github.com/jmoiron/sqlx"
)

func main() {
	var (
		db  *sqlx.DB // Setup db connection.
		ctx context.Context
	)

	s := sqluct.NewStorage(db)

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

func (*Storage) SelectStmt 

func (s *Storage) SelectStmt(tableName string, columns interface{}, options ...func(*Options)) squirrel.SelectBuilder

SelectStmt makes a select query builder.

func (*Storage) UpdateStmt 

func (s *Storage) UpdateStmt(tableName string, val interface{}, options ...func(*Options)) squirrel.UpdateBuilder

UpdateStmt makes an update query builder.

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

func (*Storage) WhereEq 

func (s *Storage) WhereEq(conditions interface{}, options ...func(*Options)) squirrel.Eq

WhereEq maps struct values as conditions to squirrel.Eq.

type StringStatement 

type StringStatement string

StringStatement is a plain string statement.

func (StringStatement) ToSql 

func (s StringStatement) ToSql() (string, []interface{}, error)

ToSql implements query builder result.

type ToSQL 

type ToSQL interface {
	ToSql() (string, []interface{}, error)
}

ToSQL defines query builder.

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.