sqlh

README

sqlh aka SQL Helper.

sqlh.Scanner

sqlh.Scanner is a powerful database result set scanner.

• Similar to jmoiron/sqlx but supports nested Go structs.
• Should work with any database/sql compatible driver.

model.Models

model.Models supports INSERT|UPDATE on Go structs registered as database models, where a model is a language type mapped to a database table.

• Supports Postgres.
• Supports grammars that use ? for parameters and have a RETURNING clause.
  • Benchmarked with Sqlite 3.35 -- your mileage may vary.

sqlh Design Philosphy

Hand Crafted  |                                         |  Can I Have
   Artisinal  | ======================================= |  My Database
         SQL  |     ^                                   |  Back, Please?
                    |
                    +-- sqlh is here.

sqlh is easy to use because it lives very close to database/sql. The primary goal of sqlh is to work with and facilitate using database/sql without replacing or hijacking it. When using sqlh you manage your *sql.DB or create *sql.Tx as you normally would and pass those as arguments to functions in sqlh when scanning or persisting models; sqlh then works within the confines of what you gave it.

When accepting arguments that work directly with the database (*sql.DB or *sql.Tx) sqlh accepts them as interfaces. This means sqlh may work with other database packages that define their own types as long as they kept a method set similar to database/sql.

The implementation for sqlh is fairly straight forward. Primarily this is because all the heavy reflect work is offloaded to set, which is another of my packages @ https://www.github.com/nofeaturesonlybugs/set

set exports a flexible set.Mapper for mapping Go structs to string names such as database columns. A lot of the power and flexibility exposed by sqlh is really derived from set. I think this gives sqlh an advantage over similar database packages because it's very configurable, performs well, and alleviates sqlh from getting bogged down in the complexities of reflect.

Here are some sqlh.Scanner examples:

type MyStruct struct {
    Message string
    Number  int
}
//
db, err := examples.Connect(examples.ExSimpleMapper)
if err != nil {
    fmt.Println(err.Error())
}
//
scanner := &sqlh.Scanner{
    // Mapper is pure defaults.  Uses exported struct names as column names.
    Mapper: &set.Mapper{},
}
var rv []MyStruct // []*MyStruct also acceptable
err = scanner.Select(db, &rv, "select * from mytable")
if err != nil {
    fmt.Println(err.Error())
}

type Common struct {
    Id       int       `json:"id"`
    Created  time.Time `json:"created"`
    Modified time.Time `json:"modified"`
}
type Person struct {
    Common
    First string `json:"first"`
    Last  string `json:"last"`
}
// Note here the natural mapping of SQL columns to nested structs.
type Sale struct {
    Common
    // customer_first and customer_last map to Customer.
    Customer Person `json:"customer"`
    // contact_first and contact_last map to Contact.
    Contact Person `json:"contact"`
}
db, err := examples.Connect(examples.ExNestedTwice)
if err != nil {
    fmt.Println(err.Error())
}
//
scanner := &sqlh.Scanner{
    Mapper: &set.Mapper{
      // Mapper elevates Common to same level as other fields.
      Elevated: set.NewTypeList(Common{}),
      // Nested struct fields joined with _
      Join:     "_",
      // Mapper uses struct tag db or json, db higher priority.
      Tags:     []string{"db", "json"},
    },
}
var rv []Sale // []*Sale also acceptable
query := `
        select
            s.id, s.created, s.modified,
            s.customer_id, c.first as customer_first, c.last as customer_last,
            s.vendor_id as contact_id, v.first as contact_first, v.last as contact_last
        from sales s
        inner join customers c on s.customer_id = c.id
        inner join vendors v on s.vendor_id = v.id
    `
err = scanner.Select(db, &rv, query)
if err != nil {
    fmt.Println(err.Error())
}

Roadmap

The development of sqlh is essentially following my specific pain points when using database/sql:

• ✓ Row scanning provided by sqlh.Scanner
• ✓ High level Save() method provided by model.Models
• ✓ Specific Insert(), Update(), and Upsert() logic provided by model.Models
  • Upsert() currently supports conflict from primary key; conflicts on arbitrary unique indexes not supported.
• ⭴ DELETE CRUD statements : to be covered by model.Models.
• ⭴ UPSERT type operations using index information : to be covered by model.Models.
• ⭴ Find() or Filter() for advanced WHERE clauses and model selection.
• ⭴ Performance enhancements if possible.
• ⭴ Relationship management -- maybe.

Personally I find SELECT|INSERT|UPDATE to be the most painful and tedious with large queries or tables so those are the features I've addressed first.

set.Mapper Tips

When you want set.Mapper to treat a nested struct as a single field rather than a struct itself add it to the TreatAsScalar member:

• TreatAsScalar : set.NewTypeList( sql.NullBool{}, sql.NullString{} )

When you use a common nested struct to represent fields present in many of your types consider using the Elevated member:

type CommonDB struct {
    Id int
    CreatedAt time.Time
    ModifiedAt time.Time
}
type Something struct {
    CommonDB
    Name string
}

Without Elevated the set.Mapper will generate names like:

CommonDBId
CommonDBCreatedAt
CommonDBModifiedAt
Name

To prevent CommonDB from being part of the name add CommonDB{} to the Elevated member of the mapper, which elevates the nested fields as if they were defined directly in the parent struct:

Elevated : set.NewTypeList( CommonDB{} )

Then the generated names will be:

Id
CreatedAt
ModifiedAt
Name

You can further customize generated names with struct tags:

type CommonDB struct {
    Id int `json:"id"`
    CreatedAt time.Time `json:"created"`
    ModifiedAt time.Time `json:"modified"`
}
type Something struct {
    CommonDB // No tag necessary since this field is Elevated.
    Name string `json:"name"`
}

Specify the tag name to use in the Tags member, which is a []string:

Tags : []string{"json"}

Now generated names will be:

id
created
modified
name

If you want to use different names for some fields in your database versus your JSON encoding you can specify multiple Tags, with tags listed first taking higher priority:

Tags : []string{"db", "json"} // Uses either db or json, db has higher priority.

With the above Tags, if CommonDB is defined as the following:

type CommonDB struct {
    Id int `json:"id" db:"pk"`
    CreatedAt time.Time `json:"created" db:"created_tmz"`
    ModifiedAt time.Time `json:"modified" db:"modified_tmz"`
}

Then the mapped names are:

pk
created_tmz
modified_tmz
name

Benchmarks

See my sibling package sqlhbenchmarks for my methodology, goals, and interpretation of results.

API Consistency and Breaking Changes

I am making a very concerted effort to break the API as little as possible while adding features or fixing bugs. However this software is currently in a pre-1.0.0 version and breaking changes are allowed under standard semver. As the API approaches a stable 1.0.0 release I will list any such breaking changes here and they will always be signaled by a bump in minor version.

• 0.4.0 ⭢ 0.5.0
  • model.Models methods allow []T or []*T when performing INSERT|UPDATE|UPSERT on slices of models.
  • model.QueryBinding is no longer an interface.
  • model.Model pruned:
    • Removed fields V, VSlice and BoundMapping
    • Removed methods NewInstance and NewSlice
    • BindQuery() signature changed to require a *set.Mapper
  • Upgrade set dependency to v0.5.1 for performance enhancements.
• 0.3.0 ⭢ 0.4.0
  • Transact(fn) was correctly rolling the transaction back if fn returned err != nil; however the error from fn and any potential error from the rollback were not returned from Transact(). This is fixed in 0.4.0 and while technically a bug fix it also changes the behavior of Transact() to (correctly) return errors as it should have been doing. As this is a potentially breaking change in behavior I have bumped the minor version for this patch.
• 0.2.0 ⭢ 0.3.0
  • grammar.Default renamed to grammar.Sqlite -- generated SQL is same as previous version.
  • grammar.Grammar is now an interface where methods now return (*statements.Query, error) where previously only (*statements.Query) was returned.
  • Package grammar no longer has any panics; errors are returned instead (see previous note).
  • Prior to this release model.Models only ran queries that had followup targets for Scan() and panicked when such targets did not exist. This release allows for queries that do not have any Scan() targets and will switch to calling Exec() instead of Query() or QueryRow() when necessary. An implication of this change is that Models.Insert() and Models.Update() no longer panic in the absence of Scan() targets.

Documentation

Overview

Package sqlh provides some simple utility for database/sql.

Refer to examples under Scanner.Select for row scanning.

Refer to subdirectory model for the model abstraction layer.

sqlh and associated packages use reflect but nearly all of the heavy lifting is offloaded to set @ https://www.github.com/nofeaturesonlybugs/set

Both sqlh.Scanner and model.Models use set.Mapper; the examples typically demonstrate instantiating a set.Mapper. If you design your Go destinations and models well then ideally your app will only need a single set.Mapper, a single sqlh.Scanner, and a single model.Models. Since all of these can be instantiated without a database connection you may wish to define them as globals and register models as part of an init() function.

set.Mapper supports some additional flexibility not shown in the examples for this package; if your project has extra convoluted Go structs in your database layer you may want to consult the package documentation for package set.

Index

• func Transact(Q IQueries, fn func(Q IQueries) error) error
• func TransactRollback(B IBegins, fn func(Q IQueries) error) error
• type IBegins
• type IIterates
• type IPrepares
• type IQueries
• type Scanner
  • func (me *Scanner) ScanRows(R IIterates, dest interface{}) error
  • func (me *Scanner) Select(Q IQueries, dest interface{}, query string, args ...interface{}) error

Examples

• Scanner.Select (NestedStruct)
• Scanner.Select (NestedTwice)
• Scanner.Select (Scalar)
• Scanner.Select (ScalarSlice)
• Scanner.Select (ScalarSliceOfPointers)
• Scanner.Select (Struct)
• Scanner.Select (StructNotFound)
• Scanner.Select (StructSlice)
• Scanner.Select (StructSliceOfPointers)
• Scanner.Select (Tags)

Functions

func Transact

func Transact(Q IQueries, fn func(Q IQueries) error) error

Transact runs fn inside a transaction if Q supports transactions; otherwise it just calls fn(Q). If a transaction is started and fn returns a non-nil error then the transaction is rolled back.

func TransactRollback

func TransactRollback(B IBegins, fn func(Q IQueries) error) error

TransactRollback is similar to Transact except the created transaction will always be rolled back; consider using this during tests when you do not want to persist changes to the database.

Unlike Transact the database object passed to this function must be of type IBegins so the caller is guaranteed fn occurs under a transaction that will be rolled back.

Types

type IBegins

type IBegins interface {
	Begin() (*sql.Tx, error)
}

IBegins defines the method(s) required to open a transaction.

type IIterates

type IIterates interface {
	Close() error
	Columns() ([]string, error)
	Err() error
	Next() bool
	Scan(dest ...interface{}) error
}

IIterates defines the methods required for iterating a query result set.

type IPrepares

type IPrepares interface {
	Prepare(query string) (*sql.Stmt, error)
}

IPrepares defines the methods required to run prepared statements.

type IQueries

type IQueries interface {
	Exec(query string, args ...interface{}) (sql.Result, error)
	Query(query string, args ...interface{}) (*sql.Rows, error)
	QueryRow(query string, args ...interface{}) *sql.Row
}

IQueries defines the methods common to types that can run queries.

type Scanner

type Scanner struct {
	*set.Mapper
}

Scanner facilitates scanning query results into destinations.

func (*Scanner) ScanRows

func (me *Scanner) ScanRows(R IIterates, dest interface{}) error

ScanRows scans rows from R into dest.

func (*Scanner) Select

func (me *Scanner) Select(Q IQueries, dest interface{}, query string, args ...interface{}) error

Select uses Q to run the query string with args and scans results into dest.

Example (NestedStruct)

package main

import (
	"fmt"
	"time"

	"github.com/nofeaturesonlybugs/set"
	"github.com/nofeaturesonlybugs/sqlh"
	"github.com/nofeaturesonlybugs/sqlh/examples"
)

func main() {
	type Common struct {
		Id       int       `json:"id"`
		Created  time.Time `json:"created"`
		Modified time.Time `json:"modified"`
	}
	type MyStruct struct {
		// Structs can share the structure in Common.
		// This would work just as well if the embed was not a pointer.
		// Note how set.Mapper.Elevated is set!
		*Common
		Message string `json:"message"`
		Number  int    `json:"value" db:"num"`
	}
	db, err := examples.Connect(examples.ExNestedStruct)
	if err != nil {
		fmt.Println(err.Error())
	}
	//
	scanner := &sqlh.Scanner{
		//
		Mapper: &set.Mapper{
			Elevated: set.NewTypeList(Common{}),
			Tags:     []string{"db", "json"},
		},
	}
	var rv []*MyStruct
	err = scanner.Select(db, &rv, "select id, created, modified, message, num from mytable")
	if err != nil {
		fmt.Println(err.Error())
	}
	for _, row := range rv {
		fmt.Printf("%v %v %v\n", row.Id, row.Message, row.Number)
	}

}

Output:

1 Hello, World! 42
2 So long! 100

Example (NestedTwice)

package main

import (
	"fmt"
	"time"

	"github.com/nofeaturesonlybugs/set"
	"github.com/nofeaturesonlybugs/sqlh"
	"github.com/nofeaturesonlybugs/sqlh/examples"
)

func main() {
	type Common struct {
		Id       int       `json:"id"`
		Created  time.Time `json:"created"`
		Modified time.Time `json:"modified"`
	}
	type Person struct {
		Common
		First string `json:"first"`
		Last  string `json:"last"`
	}
	// Note here the natural mapping of SQL columns to nested structs.
	type Sale struct {
		Common
		// customer_first and customer_last map to Customer.
		Customer Person `json:"customer"`
		// contact_first and contact_last map to Contact.
		Contact Person `json:"contact"`
	}
	db, err := examples.Connect(examples.ExNestedTwice)
	if err != nil {
		fmt.Println(err.Error())
	}
	//
	scanner := &sqlh.Scanner{
		// Mapper uses struct tag db or json, db higher priority.
		// Mapper elevates Common to same level as other fields.
		Mapper: &set.Mapper{
			Elevated: set.NewTypeList(Common{}),
			Join:     "_",
			Tags:     []string{"db", "json"},
		},
	}
	var rv []*Sale
	query := `
		select
			s.id, s.created, s.modified,
			s.customer_id, c.first as customer_first, c.last as customer_last,
			s.vendor_id as contact_id, v.first as contact_first, v.last as contact_last
		from sales s
		inner join customers c on s.customer_id = c.id
		inner join vendors v on s.vendor_id = v.id
	`
	err = scanner.Select(db, &rv, query)
	if err != nil {
		fmt.Println(err.Error())
	}
	for _, row := range rv {
		fmt.Printf("%v %v.%v %v %v.%v %v\n", row.Id, row.Customer.Id, row.Customer.First, row.Customer.Last, row.Contact.Id, row.Contact.First, row.Contact.Last)
	}

}

Output:

1 10.Bob Smith 100.Sally Johnson
2 20.Fred Jones 200.Betty Walker

Example (Scalar)

package main

import (
	"fmt"

	"github.com/nofeaturesonlybugs/set"
	"github.com/nofeaturesonlybugs/sqlh"
	"github.com/nofeaturesonlybugs/sqlh/examples"
)

func main() {
	db, err := examples.Connect(examples.ExScalar)
	if err != nil {
		fmt.Println(err.Error())
	}
	scanner := sqlh.Scanner{
		Mapper: &set.Mapper{},
	}
	var n int
	err = scanner.Select(db, &n, "select count(*) as n from thetable")
	if err != nil {
		fmt.Println(err.Error())
	}
	fmt.Println(n)
}

Output:

64

Example (ScalarSlice)

package main

import (
	"fmt"

	"github.com/nofeaturesonlybugs/set"
	"github.com/nofeaturesonlybugs/sqlh"
	"github.com/nofeaturesonlybugs/sqlh/examples"
)

func main() {
	db, err := examples.Connect(examples.ExScalarSlice)
	if err != nil {
		fmt.Println(err.Error())
	}
	scanner := sqlh.Scanner{
		Mapper: &set.Mapper{},
	}
	fmt.Println("Dest is slice of scalar:")
	var ids []int
	err = scanner.Select(db, &ids, "select id from thetable where col = ?", "some value")
	if err != nil {
		fmt.Println(err.Error())
	}
	fmt.Println(ids)
}

Output:

Dest is slice of scalar:
[1 2 3]

Example (ScalarSliceOfPointers)

package main

import (
	"fmt"

	"github.com/nofeaturesonlybugs/set"
	"github.com/nofeaturesonlybugs/sqlh"
	"github.com/nofeaturesonlybugs/sqlh/examples"
)

func main() {
	db, err := examples.Connect(examples.ExScalarSlice)
	if err != nil {
		fmt.Println(err.Error())
	}
	scanner := sqlh.Scanner{
		Mapper: &set.Mapper{},
	}
	fmt.Println("Dest is slice of pointer-to-scalar:")
	var ptrs []*int
	err = scanner.Select(db, &ptrs, "select id from thetable where col = ?", "some value")
	if err != nil {
		fmt.Println(err.Error())
	}
	var ids []int
	for _, ptr := range ptrs {
		ids = append(ids, *ptr)
	}
	fmt.Println(ids)
}

Output:

Dest is slice of pointer-to-scalar:
[1 2 3]

Example (Struct)

package main

import (
	"fmt"
	"time"

	"github.com/nofeaturesonlybugs/set"
	"github.com/nofeaturesonlybugs/sqlh"
	"github.com/nofeaturesonlybugs/sqlh/examples"
)

func main() {
	db, err := examples.Connect(examples.ExStruct)
	if err != nil {
		fmt.Println(err.Error())
	}
	scanner := sqlh.Scanner{
		Mapper: &set.Mapper{
			Tags: []string{"db", "json"},
		},
	}
	type Temp struct {
		Min time.Time `json:"min"`
		Max time.Time `json:"max"`
	}
	var dest *Temp
	err = scanner.Select(db, &dest, "select min(col) as min, max(col) as max from thetable")
	if err != nil {
		fmt.Println(err.Error())
	}
	fmt.Println(dest.Min.Format(time.RFC3339), dest.Max.Format(time.RFC3339))
}

Output:

1970-01-01T00:00:00Z 2012-01-01T00:00:00Z

Example (StructNotFound)

package main

import (
	"fmt"
	"time"

	"github.com/nofeaturesonlybugs/set"
	"github.com/nofeaturesonlybugs/sqlh"
	"github.com/nofeaturesonlybugs/sqlh/examples"
)

func main() {
	type Common struct {
		Id       int       `json:"id"`
		Created  time.Time `json:"created"`
		Modified time.Time `json:"modified"`
	}
	type Person struct {
		Common
		First string `json:"first"`
		Last  string `json:"last"`
	}
	// Note here the natural mapping of SQL columns to nested structs.
	type Sale struct {
		Common
		// customer_first and customer_last map to Customer.
		Customer Person `json:"customer"`
		// contact_first and contact_last map to Contact.
		Contact Person `json:"contact"`
	}
	//
	db, err := examples.Connect(examples.ExStructNotFound)
	if err != nil {
		fmt.Println(err.Error())
	}
	scanner := sqlh.Scanner{
		Mapper: &set.Mapper{
			Elevated: set.NewTypeList(Common{}),
			Tags:     []string{"db", "json"},
			Join:     "_",
		},
	}
	query := `
		select
			s.id, s.created, s.modified,
			s.customer_id, c.first as customer_first, c.last as customer_last,
			s.vendor_id as contact_id, v.first as contact_first, v.last as contact_last
		from sales s
		inner join customers c on s.customer_id = c.id
		inner join vendors v on s.vendor_id = v.id
	`
	// When destination is a pointer to struct and no rows are found then the dest pointer
	// remains nil and no error is returned.
	var dest *Sale
	err = scanner.Select(db, &dest, query)
	if err != nil {
		fmt.Println(err.Error())
	}
	fmt.Printf("Is nil: %v\n", dest == nil)

}

Output:

Is nil: true

Example (StructSlice)

package main

import (
	"fmt"

	"github.com/nofeaturesonlybugs/set"
	"github.com/nofeaturesonlybugs/sqlh"
	"github.com/nofeaturesonlybugs/sqlh/examples"
)

func main() {
	type MyStruct struct {
		Message string
		Number  int
	}
	db, err := examples.Connect(examples.ExSimpleMapper)
	if err != nil {
		fmt.Println(err.Error())
	}
	//
	scanner := &sqlh.Scanner{
		// Mapper is pure defaults.  Uses exported struct names as column names.
		Mapper: &set.Mapper{},
	}

	fmt.Println("Dest is slice struct:")
	var rv []MyStruct
	err = scanner.Select(db, &rv, "select * from mytable")
	if err != nil {
		fmt.Println(err.Error())
	}
	for _, row := range rv {
		fmt.Printf("%v %v\n", row.Message, row.Number)
	}

}

Output:

Dest is slice struct:
Hello, World! 42
So long! 100

Example (StructSliceOfPointers)

package main

import (
	"fmt"

	"github.com/nofeaturesonlybugs/set"
	"github.com/nofeaturesonlybugs/sqlh"
	"github.com/nofeaturesonlybugs/sqlh/examples"
)

func main() {
	type MyStruct struct {
		Message string
		Number  int
	}
	db, err := examples.Connect(examples.ExSimpleMapper)
	if err != nil {
		fmt.Println(err.Error())
	}
	//
	scanner := &sqlh.Scanner{
		// Mapper is pure defaults.  Uses exported struct names as column names.
		Mapper: &set.Mapper{},
	}

	fmt.Println("Dest is slice of pointers:")
	var rv []*MyStruct
	err = scanner.Select(db, &rv, "select * from mytable")
	if err != nil {
		fmt.Println(err.Error())
	}
	for _, row := range rv {
		fmt.Printf("%v %v\n", row.Message, row.Number)
	}

}

Output:

Dest is slice of pointers:
Hello, World! 42
So long! 100

Example (Tags)

package main

import (
	"fmt"

	"github.com/nofeaturesonlybugs/set"
	"github.com/nofeaturesonlybugs/sqlh"
	"github.com/nofeaturesonlybugs/sqlh/examples"
)

func main() {
	type MyStruct struct {
		Message string `json:"message"`
		Number  int    `json:"value" db:"num"`
	}
	db, err := examples.Connect(examples.ExTags)
	if err != nil {
		fmt.Println(err.Error())
	}
	//
	scanner := &sqlh.Scanner{
		// Mapper uses struct tag db or json, db higher priority
		Mapper: &set.Mapper{
			Tags: []string{"db", "json"},
		},
	}
	var rv []*MyStruct
	err = scanner.Select(db, &rv, "select message, num from mytable")
	if err != nil {
		fmt.Println(err.Error())
	}
	for _, row := range rv {
		fmt.Printf("%v %v\n", row.Message, row.Number)
	}

}

Output:

Hello, World! 42
So long! 100