sqluct

package module
v0.1.1 Latest Latest
Warning

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

 Go to latest Published: Dec 10, 2020 License: MIT Imports: 8 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.

Example

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

Documentation

Overview

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

Index

Examples

Constants

This section is empty.

Variables

This section is empty.

Functions

func Columns 

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

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

func OrderDesc 

func OrderDesc(o *Options)

OrderDesc instructs mapper to use DESC order in Product func.

func SkipZeroValues 

func SkipZeroValues(o *Options)

SkipZeroValues instructs mapper to ignore fields with zero values.

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 Mapper 

type Mapper struct {
	ReflectMapper *reflectx.Mapper
}

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"`
		CreatedAt time.Time `db:"created_at"`
	}

	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) 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"`
		Amount int `db:"amount"`
		UserID int `db:"user_id"`
	}

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

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

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

	type Order struct {
		ID int `db:"order_id"`
		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"`
	}

	type Order struct {
		ID int `db:"order_id"`
		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"`
	}

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

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

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

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

}

Output:

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

type Options 

type Options struct {
	// SkipZeroValues instructs mapper to ignore fields with zero values.
	SkipZeroValues 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
}

Options defines mapping parameters.

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

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

	_, err := s.Exec(ctx, qb)
	if 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) 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 (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)

	err := s.Select(ctx, qb, &row)
	if 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.Mapper.WhereEq(MyIdentity{ID: 123}))

	_, err := s.Exec(ctx, qb)
	if 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.