sqluct

package module
v0.2.4 Latest Latest
Warning

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

 Go to latest Published: Nov 26, 2024 License: MIT Imports: 14 Imported by: 18

README

Struct-based database access layer for Go

test-unit 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.

StorageOf[V any] typed query builder and scanner for specific table(s).

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

// Open DB connection.
s, _ := sqluct.Open(
    "postgres",
    "postgres://pqgotest:password@localhost/pqgotest?sslmode=disable",
)

// Or if you already have an *sql.DB or *sqlx.DB instances, you can use them:
// 	 db, _ := sql.Open("postgres", "postgres://pqgotest:password@localhost/pqgotest?sslmode=disable")
//   s := sqluct.NewStorage(sqlx.NewDb(db, "postgres"))

ctx := context.TODO()

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

// You can also use generic sqluct.Get and sqluct.List in go1.18 or later.
//
// SELECT id, title, created_at FROM products WHERE id != 3 AND created_at <= <now>
result, err = sqluct.List[Product](ctx,
	s,
    s.SelectStmt(tableName, row).
        Where(squirrel.NotEq(s.WhereEq(Product{ID: 3}, sqluct.SkipZeroValues))).
        Where(squirrel.LtOrEq{s.Col(&row, &row.CreatedAt): time.Now()}),
)
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]

Typed Storage

sqluct.Table[RowType](storageInstance, tableName) creates a type-safe storage accessor to a table with RowType. This accessor can help to retrieve or store data. Columns from multiple tables can be joined using field pointers.

Please check features overview in an example below.

var (
    st  = sqluct.NewStorage(sqlx.NewDb(sql.OpenDB(dumpConnector{}), "postgres"))
    ctx = context.Background()
)

st.IdentifierQuoter = sqluct.QuoteANSI

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

// Users repository.
ur := sqluct.Table[User](st, "users")

// Pointer to row, that can be used to reference columns via struct fields.
_ = ur.R

// Single user record can be inserted, last insert id (if available) and error are returned.
fmt.Println("Insert single user.")
_, _ = ur.InsertRow(ctx, User{Name: "John Doe", ID: 123})

// Multiple user records can be inserted with sql.Result and error returned.
fmt.Println("Insert two users.")
_, _ = ur.InsertRows(ctx, []User{{Name: "Jane Doe", ID: 124}, {Name: "Richard Roe", ID: 125}})

// Update statement for a single user with condition.
fmt.Println("Update a user with new name.")
_, _ = ur.UpdateStmt(User{Name: "John Doe, Jr.", ID: 123}).Where(ur.Eq(&ur.R.ID, 123)).ExecContext(ctx)

// Delete statement for a condition.
fmt.Println("Delete a user with id 123.")
_, _ = ur.DeleteStmt().Where(ur.Eq(&ur.R.ID, 123)).ExecContext(ctx)

fmt.Println("Get single user with id = 123.")
user, _ := ur.Get(ctx, ur.SelectStmt().Where(ur.Eq(&ur.R.ID, 123)))

// Squirrel expression can be formatted with %s reference(s) to column pointer.
fmt.Println("Get multiple users with names starting with 'John '.")
users, _ := ur.List(ctx, ur.SelectStmt().Where(ur.Fmt("%s LIKE ?", &ur.R.Name), "John %"))

// Squirrel expressions can be applied.
fmt.Println("Get multiple users with id != 123.")
users, _ = ur.List(ctx, ur.SelectStmt().Where(squirrel.NotEq(ur.Eq(&ur.R.ID, 123))))

fmt.Println("Get all users.")
users, _ = ur.List(ctx, ur.SelectStmt())

// More complex statements can be made with references to other tables.

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

// Roles repository.
rr := sqluct.Table[Role](st, "roles")

// To be able to resolve "roles" columns, we need to attach roles repo to users repo.
ur.AddTableAlias(rr.R, "roles")

fmt.Println("Get users with role 'admin'.")
users, _ = ur.List(ctx, ur.SelectStmt().
    LeftJoin(ur.Fmt("%s ON %s = %s", rr.R, &rr.R.ID, &ur.R.RoleID)).
    Where(ur.Fmt("%s = ?", &rr.R.Name), "admin"),
)

_ = user
_ = users

// Output:
// Insert single user.
// exec INSERT INTO "users" ("id","role_id","name") VALUES ($1,$2,$3) [123 0 John Doe]
// Insert two users.
// exec INSERT INTO "users" ("id","role_id","name") VALUES ($1,$2,$3),($4,$5,$6) [124 0 Jane Doe 125 0 Richard Roe]
// Update a user with new name.
// exec UPDATE "users" SET "id" = $1, "role_id" = $2, "name" = $3 WHERE "users"."id" = $4 [123 0 John Doe, Jr. 123]
// Delete a user with id 123.
// exec DELETE FROM "users" WHERE "users"."id" = $1 [123]
// Get single user with id = 123.
// query SELECT "users"."id", "users"."role_id", "users"."name" FROM "users" WHERE "users"."id" = $1 [123]
// Get multiple users with names starting with 'John '.
// query SELECT "users"."id", "users"."role_id", "users"."name" FROM "users" WHERE "users"."name" LIKE $1 [John %]
// Get multiple users with id != 123.
// query SELECT "users"."id", "users"."role_id", "users"."name" FROM "users" WHERE "users"."id" <> $1 [123]
// Get all users.
// query SELECT "users"."id", "users"."role_id", "users"."name" FROM "users" []
// Get users with role 'admin'.
// query SELECT "users"."id", "users"."role_id", "users"."name" FROM "users" LEFT JOIN "roles" ON "roles"."id" = "users"."role_id" WHERE "roles"."name" = $1 [admin]

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.

View Source 
const SerialID = "serialIdentity"

SerialID is the name of field tag to indicate integer serial (auto increment) ID of the table.

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 Get added in v0.1.11 

func Get[V any](ctx context.Context, s *Storage, qb ToSQL) (V, error)

Get retrieves a single row from database storage.

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 List added in v0.1.11 

func List[V any](ctx context.Context, s *Storage, qb ToSQL) ([]V, error)

List retrieves a collection of rows from database storage.

func NoTableAll added in v0.2.4 

func NoTableAll(ptrs ...interface{}) []interface{}

NoTableAll enables references without table prefix for all field pointers. It can be useful to prepare multiple variadic arguments. 

 r.Fmt("ON CONFLICT(%s) DO UPDATE SET %s = excluded.%s, %s = excluded.%s",
	sqluct.NoTableAll(&row.ID, &row.F1, &row.F1, &row.F2, &row.F3)...)

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 JSON added in v0.2.1 

type JSON[V any] struct {
	Val V
}

JSON is a generic container to a serialized db column.

func (JSON[V]) MarshalJSON added in v0.2.2 

func (s JSON[V]) MarshalJSON() ([]byte, error)

MarshalJSON encodes container value as JSON.

func (*JSON[V]) Scan added in v0.2.1 

func (s *JSON[V]) Scan(src any) error

Scan decodes json value from a db column.

func (*JSON[V]) UnmarshalJSON added in v0.2.2 

func (s *JSON[V]) UnmarshalJSON(bytes []byte) error

UnmarshalJSON decodes JSON into container.

func (JSON[V]) Value added in v0.2.1 

func (s JSON[V]) Value() (driver.Value, error)

Value encodes value as json for a db column.

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) 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 OR IGNORE for SQLite3,
	//  - INSERT ... ON CONFLICT DO NOTHING for Postgres.
	InsertIgnore bool
}

Options defines mapping and query building parameters.

type Plain added in v0.1.12 

type Plain = StringStatement

Plain is a plain string statement.

type Quoted added in v0.2.0 

type Quoted string

Quoted is a string that can be interpolated into an SQL statement as is.

type QuotedNoTable added in v0.2.4 

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

QuotedNoTable is a container of field pointer that should be referenced without table.

func NoTable added in v0.2.4 

func NoTable(ptr interface{}) QuotedNoTable

NoTable enables references without table prefix. So that `my_table`.`my_column` would be rendered as `my_column`. 

	r.Ref(sqluct.NoTable(&row.MyColumn))
 r.Fmt("%s = 1", sqluct.NoTable(&row.MyColumn))

Such references may be useful for INSERT/UPDATE column expressions.

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) Col added in v0.2.0 

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

Col returns unescaped column name for field pointer that was previously added with AddTableAlias.

It panics if pointer is unknown. Might be used with Options.Columns.

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) Eq added in v0.2.0 

func (r *Referencer) Eq(ptr interface{}, val interface{}) squirrel.Eq

Eq is a shortcut for squirrel.Eq{r.Ref(ptr): val}.

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.

It panics if pointer is unknown or is not a Quoted string.

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

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.

func (*Referencer) Refs added in v0.2.4 

func (r *Referencer) Refs(ptrs ...interface{}) []string

Refs returns reference strings for multiple field pointers.

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 Open added in v0.1.11 

func Open(driverName, dataSourceName string) (*Storage, error)

Open opens a database specified by its database driver name and a driver-specific data source name, usually consisting of at least a database name and connection information.

Example 
package main

import (
	"context"
	"log"

	"github.com/bool64/sqluct"
)

func main() {
	// Open DB connection.
	st, err := sqluct.Open(
		"postgres",
		"postgres://pqgotest:password@localhost/pqgotest?sslmode=disable",
	)
	if err != nil {
		log.Fatal(err.Error())
	}

	// Use Storage.
	var foo []struct {
		Bar string `db:"bar"`
	}

	err = st.Select(context.TODO(), sqluct.StringStatement("SELECT bar FROM foo"), &foo)
	if err != nil {
		log.Fatal(err.Error())
	}
}

Output:

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(_ context.Context) error {
		return nil
	})
	if err != nil {
		log.Fatal(err)
	}
}

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

Output:

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

Output:

func (*Storage) MakeReferencer added in v0.2.0 

func (s *Storage) MakeReferencer() *Referencer

MakeReferencer creates Referencer for query builder.

func (*Storage) Query 

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

Query queries database and returns raw result.

You must close the rows after use to avoid resource leak. 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 (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.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>
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)
	}
}

Output:
Example (Slice) 
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:

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

Output:

func (*Storage) WhereEq 

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

WhereEq maps struct values as conditions to squirrel.Eq.

type StorageOf added in v0.2.0 

type StorageOf[V any] struct {
	*Referencer
	R *V
	// contains filtered or unexported fields
}

StorageOf is a type-safe facade to work with rows of specific type.

func Table added in v0.2.0 

func Table[V any](storage *Storage, tableName string) StorageOf[V]

Table configures and returns StorageOf in a table.

Example 
var (
	st  = sqluct.NewStorage(sqlx.NewDb(sql.OpenDB(dumpConnector{}), "postgres"))
	ctx = context.Background()
)

st.IdentifierQuoter = sqluct.QuoteANSI

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

// Users repository.
ur := sqluct.Table[User](st, "users")

// Pointer to row, that can be used to reference columns via struct fields.
_ = ur.R

// Single user record can be inserted, last insert id (if available) and error are returned.
fmt.Println("Insert single user.")
_, _ = ur.InsertRow(ctx, User{Name: "John Doe", ID: 123})

// Multiple user records can be inserted with sql.Result and error returned.
fmt.Println("Insert two users.")
_, _ = ur.InsertRows(ctx, []User{{Name: "Jane Doe", ID: 124}, {Name: "Richard Roe", ID: 125}})

// Update statement for a single user with condition.
fmt.Println("Update a user with new name.")
_, _ = ur.UpdateStmt(User{Name: "John Doe, Jr.", ID: 123}).Where(ur.Eq(&ur.R.ID, 123)).ExecContext(ctx)

// Delete statement for a condition.
fmt.Println("Delete a user with id 123.")
_, _ = ur.DeleteStmt().Where(ur.Eq(&ur.R.ID, 123)).ExecContext(ctx)

fmt.Println("Get single user with id = 123.")
user, _ := ur.Get(ctx, ur.SelectStmt().Where(ur.Eq(&ur.R.ID, 123)))

// Squirrel expression can be formatted with %s reference(s) to column pointer.
fmt.Println("Get multiple users with names starting with 'John '.")
users, _ := ur.List(ctx, ur.SelectStmt().Where(ur.Fmt("%s LIKE ?", &ur.R.Name), "John %"))

// Squirrel expressions can be applied.
fmt.Println("Get multiple users with id != 123.")
users, _ = ur.List(ctx, ur.SelectStmt().Where(squirrel.NotEq(ur.Eq(&ur.R.ID, 123))))

fmt.Println("Get all users.")
users, _ = ur.List(ctx, ur.SelectStmt())

// More complex statements can be made with references to other tables.

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

// Roles repository.
rr := sqluct.Table[Role](st, "roles")

// To be able to resolve "roles" columns, we need to attach roles repo to users repo.
ur.AddTableAlias(rr.R, "roles")

fmt.Println("Get users with role 'admin'.")
users, _ = ur.List(ctx, ur.SelectStmt().
	LeftJoin(ur.Fmt("%s ON %s = %s", rr.R, &rr.R.ID, &ur.R.RoleID)).
	Where(ur.Fmt("%s = ?", &rr.R.Name), "admin"),
)

_ = user
_ = users

Output:

Insert single user.
exec INSERT INTO "users" ("id","role_id","name") VALUES ($1,$2,$3) [123 0 John Doe]
Insert two users.
exec INSERT INTO "users" ("id","role_id","name") VALUES ($1,$2,$3),($4,$5,$6) [124 0 Jane Doe 125 0 Richard Roe]
Update a user with new name.
exec UPDATE "users" SET "id" = $1, "role_id" = $2, "name" = $3 WHERE "users"."id" = $4 [123 0 John Doe, Jr. 123]
Delete a user with id 123.
exec DELETE FROM "users" WHERE "users"."id" = $1 [123]
Get single user with id = 123.
query SELECT "users"."id", "users"."role_id", "users"."name" FROM "users" WHERE "users"."id" = $1 [123]
Get multiple users with names starting with 'John '.
query SELECT "users"."id", "users"."role_id", "users"."name" FROM "users" WHERE "users"."name" LIKE $1 [John %]
Get multiple users with id != 123.
query SELECT "users"."id", "users"."role_id", "users"."name" FROM "users" WHERE "users"."id" <> $1 [123]
Get all users.
query SELECT "users"."id", "users"."role_id", "users"."name" FROM "users" []
Get users with role 'admin'.
query SELECT "users"."id", "users"."role_id", "users"."name" FROM "users" LEFT JOIN "roles" ON "roles"."id" = "users"."role_id" WHERE "roles"."name" = $1 [admin]

func (*StorageOf[V]) DeleteStmt added in v0.2.0 

func (s *StorageOf[V]) DeleteStmt() squirrel.DeleteBuilder

DeleteStmt creates delete statement with table name.

func (*StorageOf[V]) Get added in v0.2.0 

func (s *StorageOf[V]) Get(ctx context.Context, qb ToSQL) (V, error)

Get retrieves a single row from database storage.

func (*StorageOf[V]) InsertRow added in v0.2.0 

func (s *StorageOf[V]) InsertRow(ctx context.Context, row V, options ...func(o *Options)) (int64, error)

InsertRow inserts single row database table.

func (*StorageOf[V]) InsertRows added in v0.2.0 

func (s *StorageOf[V]) InsertRows(ctx context.Context, rows []V, options ...func(o *Options)) (sql.Result, error)

InsertRows inserts multiple rows in database table.

func (*StorageOf[V]) List added in v0.2.0 

func (s *StorageOf[V]) List(ctx context.Context, qb ToSQL) ([]V, error)

List retrieves a collection of rows from database storage.

func (*StorageOf[V]) SelectStmt added in v0.2.0 

func (s *StorageOf[V]) SelectStmt(options ...func(*Options)) squirrel.SelectBuilder

SelectStmt creates query statement with table name and row columns.

func (*StorageOf[V]) UpdateStmt added in v0.2.0 

func (s *StorageOf[V]) UpdateStmt(value any, options ...func(*Options)) squirrel.UpdateBuilder

UpdateStmt creates update statement with table name and updated value (can be nil).

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.

func Stmt added in v0.1.12 

func Stmt(query string, args ...interface{}) ToSQL

Stmt is a statement with placeholder arguments.

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.