<repo_name>Toansmile/gos<file_sep>/gos.go
/*
Go ↔︎ SQL, tool for mapping between Go structs and SQL records. NOT AN ORM, and
should be used instead of an ORM. Expressly designed to let you WRITE PLAIN SQL.

Example usage:

	type External struct {
		Id    string `db:"id"`
		Name  string `db:"name"`
	}

	type Internal struct {
		Id   string `db:"id"`
		Name string `db:"name"`
	}

	// Step 1: generate query.

	var result []External

	query := fmt.Sprintf(`select %v from (
		select
			external.*,
			internal as internal
		from
			external
			cross join internal
	) as _
	`, gos.Cols(result))

	// Resulting query (formatted here for readability):
	// select
	// 	"id",
	// 	"name",
	// 	("internal")."id"   as "internal.id",
	// 	("internal")."name" as "internal.name"
	// from (
	// 	...
	// ) as _

	// Step 2: use query.

	err := gos.Query(ctx, conn, &result, query, nil)

	fmt.Printf("%#v\n", result)

Struct rules

When decoding a row into a struct, Gos observes the following rules.

1. Columns are matched to public struct fields whose `db` tag exactly matches
the column name. Private fields or fields without `db` are completely
ignored. Example:

	type Result struct {
		A string `db:"a"`
		B string // ignored: no `db` tag
		c string // ignored: private
	}

2. Fields of embedded structs are treated as part of the enclosing struct. For
example, these two definitions are completely equivalent:

	type Result struct {
		A string `db:"a"`
		Embedded
	}
	type Embedded struct {
		B string `db:"b"`
	}

	type Result struct {
		A string `db:"a"`
		B string `db:"b"`
	}

3. Fields of nested non-embedded structs are matched with columns whose aliases
look like `"outer_field.inner_field.innermost_field"` with arbitrary nesting.
Example:

	-- Query:
	select
		'one' as "outer_val",
		'two' as "inner.inner_val";

	// Go types:
	type Outer struct {
		OuterVal string `db:"outer_val"`
		Inner    Inner  `db:"inner"`
	}
	type Inner struct {
		InnerVal string `db:"inner_val"`
	}

4. If every column from a nested struct is null or missing, the entire nested
struct is considered null. If the field is not nilable (struct, not pointer
to struct), this will produce an error. Otherwise, the field is left nil and
not allocated. This convention is extremely useful for outer joins, where
nested records are often null. Example:

	-- Query:
	select
		'one' as "outer_val",
		null  as "inner.inner_val";

	// Go types:
	type Outer struct {
		OuterVal string `db:"outer_val"`
		Inner    *Inner `db:"inner"`
	}
	type Inner struct {
		InnerVal string `db:"inner_val"`
	}

	// Output:
	Outer{OuterVal: "one", Inner: nil}

Differences from sqlx

Gos is very similar to https://github.com/jmoiron/sqlx. Key differences:

	* Supports null records in outer joins, as nested struct pointers
	* Selects fields explicitly without relying on `select *`
	* Much simpler API, does not wrap `database/sql`
	* Explicit field-column mapping, no hidden renaming
	* No special utilities for named parameters
	* Depends only on the standard library (the `go.mod` dependencies are test-only)

Notes on array support

Gos doesn't specially support SQL arrays. Generally speaking, SQL arrays are
usable only for primitive types such as numbers or strings. Some databases, such
as Postgres, have their own implementations of multi-dimensional arrays, which
are non-standard and have so many quirks and limitations that it's more
practical to just use JSON. Arrays of primitives are already supported in
adapters such as "github.com/lib/pq", which are orthogonal to Gos and used in
combination with it.
*/
package gos

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"reflect"
	"strings"
	"time"
)

/*
Main API of this library. Scans columns into the destination, which may be one
of:

	* single scalar
	* slice of scalars
	* single struct
	* slice of structs

If the destination is a non-slice, there must be exactly one row. Less or more
will result in an error. If the destination is a struct, this will decode
columns into struct fields, following the rules outlined above in the package
overview.

The `select` part of the query must be generated by calling `Cols(dest)`. See
the example below.
*/
func Query(ctx context.Context, conn Queryer, dest interface{}, query string, args []interface{}) error {
	rval := reflect.ValueOf(dest)
	if !isNonNilPointer(rval) {
		return ErrInvalidDest.because(fmt.Errorf(`destination must be a non-nil pointer, received %#v`, dest))
	}

	rtype := derefRtype(rval.Type())

	if rtype.Kind() == reflect.Slice {
		elemRtype := derefRtype(rtype.Elem())
		if elemRtype.Kind() == reflect.Struct && !isScannableRtype(elemRtype) {
			return queryStructs(ctx, conn, rval, query, args)
		}
		return queryScalars(ctx, conn, rval, query, args)
	}

	if rtype.Kind() == reflect.Struct && !isScannableRtype(rtype) {
		return queryStruct(ctx, conn, rval, query, args)
	}
	return queryScalar(ctx, conn, dest, query, args)
}

/*
Takes a struct or struct slice and generates a string for inclusion into
`select`. Allowed inputs: struct, struct pointer, slice of structs, slice of
struct pointers, pointer to slice. Any other input causes a panic.

Should be used in conjunction with `Query()`. See the examples below.
*/
func Cols(dest interface{}) string {
	rtype := derefRtype(reflect.TypeOf(dest))
	if rtype.Kind() == reflect.Slice {
		rtype = derefRtype(rtype.Elem())
	}
	idents := structTypeSqlIdents(rtype)
	return sqlIdent{idents: idents}.selectString()
}

/*
Use these blank errors to detect error type:

	if errors.Is(err, gos.ErrNoRows) {
		// Handle specific error.
	}

Note that errors returned by Gos can't be compared via `==` because they may
include additional details about the circumstances. Use `errors.Is`.
*/
var (
	ErrNoRows       Err = Err{Code: ErrCodeNoRows, Cause: sql.ErrNoRows}
	ErrMultipleRows Err = Err{Code: ErrCodeMultipleRows, Cause: errors.New(`expected one row, got multiple`)}
	ErrInvalidDest  Err = Err{Code: ErrCodeInvalidDest, Cause: errors.New(`invalid destination type`)}
	ErrNoColDest    Err = Err{Code: ErrCodeNoColDest, Cause: errors.New(`column has no matching destination`)}
	ErrRedundantCol Err = Err{Code: ErrCodeRedundantCol, Cause: errors.New(`redundant column occurrence`)}
	ErrNull         Err = Err{Code: ErrCodeNull, Cause: errors.New(`null column for non-nilable field`)}
	ErrScan         Err = Err{Code: ErrCodeScan, Cause: errors.New(`error while scanning into field`)}
)

// Describes a Gos error.
type Err struct {
	Code  ErrCode
	While string
	Cause error
}

// Implement `error`.
func (self Err) Error() string {
	if self == (Err{}) {
		return ""
	}
	msg := `SQL error`
	if self.Code != ErrCodeUnknown {
		msg += fmt.Sprintf(` %s`, self.Code)
	}
	if self.While != "" {
		msg += fmt.Sprintf(` while %v`, self.While)
	}
	if self.Cause != nil {
		msg += `: ` + self.Cause.Error()
	}
	return msg
}

// Implement a hidden interface in "errors".
func (self Err) Is(other error) bool {
	if self.Cause != nil && errors.Is(self.Cause, other) {
		return true
	}
	err, ok := other.(Err)
	return ok && err.Code == self.Code
}

// Implement a hidden interface in "errors".
func (self Err) Unwrap() error {
	return self.Cause
}

func (self Err) while(while string) Err {
	self.While = while
	return self
}

func (self Err) because(cause error) Err {
	self.Cause = cause
	return self
}

/*
Describes the specific error type. You probably shouldn't use this directly;
instead, use `errors.Is` with the various `Err` variables.
*/
type ErrCode byte

const (
	ErrCodeUnknown ErrCode = iota
	ErrCodeNoRows
	ErrCodeMultipleRows
	ErrCodeInvalidDest
	ErrCodeNoColDest
	ErrCodeRedundantCol
	ErrCodeNull
	ErrCodeScan
)

// Implement `fmt.Stringer`.
func (self ErrCode) String() string {
	switch self {
	case ErrCodeNoRows:
		return "ErrNoRows"
	case ErrCodeMultipleRows:
		return "ErrMultipleRows"
	case ErrCodeInvalidDest:
		return "ErrInvalidDest"
	case ErrCodeNoColDest:
		return "ErrNoColDest"
	case ErrCodeRedundantCol:
		return "ErrRedundantCol"
	case ErrCodeNull:
		return "ErrNull"
	case ErrCodeScan:
		return "ErrScan"
	default:
		return ""
	}
}

/*
Database connection passed to `Query()`. Satisfied by `*sql.DB`, `*sql.Tx`, may
be satisfied by application-custom types.
*/
type Queryer interface {
	QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error)
}

/* Internal */

const expectedStructDepth = 8

type tDestSpec struct {
	colNames  []string
	colRtypes map[string]reflect.Type
	typeSpec  tTypeSpec
}

type tTypeSpec struct {
	rtype      reflect.Type
	fieldSpecs []tFieldSpec
}

type tFieldSpec struct {
	parentFieldSpec *tFieldSpec
	typeSpec        tTypeSpec
	fieldIndex      int
	fieldPath       []int // Relative to root struct.
	colName         string
	uniqColAlias    string
	colIndex        int // Must be initialized to -1.
	sfield          reflect.StructField
}

type tDecodeState struct {
	colPtrs []interface{}
}

/*
The destination must be a pointer to a non-scannable struct.
*/
func queryStruct(ctx context.Context, conn Queryer, rval reflect.Value, query string, args []interface{}) error {
	rows, err := conn.QueryContext(ctx, query, args...)
	if err != nil {
		return Err{While: `querying rows`, Cause: err}
	}
	defer rows.Close()

	spec, err := prepareDestSpec(rows, rval.Type())
	if err != nil {
		return err
	}

	state, err := prepareDecodeState(rows, spec)
	if err != nil {
		return err
	}

	if !rows.Next() {
		return ErrNoRows.while(`preparing row`)
	}

	err = rows.Scan(state.colPtrs...)
	if err != nil {
		return Err{While: `scanning row`, Cause: err}
	}

	err = traverseDecode(rval, spec, state, &spec.typeSpec, nil)
	if err != nil {
		return err
	}

	if rows.Next() {
		return ErrMultipleRows.while(`verifying row count`)
	}

	return nil
}

/*
The destination must be a pointer to a slice of non-scannable structs or
pointers to those structs.
*/
func queryStructs(ctx context.Context, conn Queryer, rval reflect.Value, query string, args []interface{}) error {
	elemRtype := derefRtype(rval.Type()).Elem()

	rows, err := conn.QueryContext(ctx, query, args...)
	if err != nil {
		return Err{While: `querying rows`, Cause: err}
	}
	defer rows.Close()

	spec, err := prepareDestSpec(rows, reflect.PtrTo(elemRtype))
	if err != nil {
		return err
	}

	sliceRval := derefAllocRval(rval)
	// When the slice is nil, this leaves it as nil.
	sliceRval.SetLen(0)

	for rows.Next() {
		state, err := prepareDecodeState(rows, spec)
		if err != nil {
			return err
		}

		err = rows.Scan(state.colPtrs...)
		if err != nil {
			return Err{While: `scanning row`, Cause: err}
		}

		elemPtrRval := reflect.New(elemRtype)

		err = traverseDecode(elemPtrRval, spec, state, &spec.typeSpec, nil)
		if err != nil {
			return err
		}

		sliceRval.Set(reflect.Append(sliceRval, elemPtrRval.Elem()))
	}

	return nil
}

func queryScalar(ctx context.Context, conn Queryer, dest interface{}, query string, args []interface{}) error {
	rows, err := conn.QueryContext(ctx, query, args...)
	if err != nil {
		return Err{While: `querying rows`, Cause: err}
	}
	defer rows.Close()

	if !rows.Next() {
		return ErrNoRows.while(`preparing row`)
	}

	err = rows.Scan(dest)
	if err != nil {
		return Err{While: `scanning row`, Cause: err}
	}

	if rows.Next() {
		return ErrMultipleRows.while(`verifying row count`)
	}

	return nil
}

/*
The destination must be a pointer to a slice of scannables or primitives.
*/
func queryScalars(ctx context.Context, conn Queryer, rval reflect.Value, query string, args []interface{}) error {
	elemRtype := derefRtype(rval.Type()).Elem()

	rows, err := conn.QueryContext(ctx, query, args...)
	if err != nil {
		return Err{While: `querying rows`, Cause: err}
	}
	defer rows.Close()

	sliceRval := derefAllocRval(rval)
	// When the slice is nil, this leaves it as nil.
	sliceRval.SetLen(0)

	for rows.Next() {
		elemPtrRval := reflect.New(elemRtype)

		err = rows.Scan(elemPtrRval.Interface())
		if err != nil {
			return Err{While: `scanning row`, Cause: err}
		}

		sliceRval.Set(reflect.Append(sliceRval, elemPtrRval.Elem()))
	}

	return nil
}

func prepareDestSpec(rows *sql.Rows, rtype reflect.Type) (*tDestSpec, error) {
	if rtype == nil || rtype.Kind() != reflect.Ptr || derefRtype(rtype).Kind() != reflect.Struct {
		return nil, Err{
			Code:  ErrCodeInvalidDest,
			While: `preparing destination spec`,
			Cause: fmt.Errorf(`expected destination type to be a struct pointer, got %q`, rtype),
		}
	}

	colNames, err := rows.Columns()
	if err != nil {
		return nil, Err{While: `getting columns`, Cause: err}
	}

	spec := &tDestSpec{
		typeSpec:  tTypeSpec{rtype: rtype},
		colNames:  colNames,
		colRtypes: map[string]reflect.Type{},
	}

	colPath := make([]string, 0, expectedStructDepth)
	fieldPath := make([]int, 0, expectedStructDepth)
	err = traverseMakeSpec(spec, &spec.typeSpec, nil, colPath, fieldPath)
	if err != nil {
		return nil, err
	}

	for _, colName := range colNames {
		if spec.colRtypes[colName] == nil {
			return nil, Err{
				Code:  ErrCodeNoColDest,
				While: `preparing destination spec`,
				Cause: fmt.Errorf(`column %q doesn't have a matching destination in type %q`, colName, rtype),
			}
		}
	}

	return spec, nil
}

func prepareDecodeState(rows *sql.Rows, spec *tDestSpec) (*tDecodeState, error) {
	colPtrs := make([]interface{}, 0, len(spec.colNames))
	for _, colName := range spec.colNames {
		if spec.colRtypes[colName] == nil {
			return nil, Err{
				Code:  ErrCodeNoColDest,
				While: `preparing decode state`,
				Cause: fmt.Errorf(`column %q doesn't have a matching destination in type %q`,
					colName, spec.typeSpec.rtype),
			}
		}
		colPtrs = append(colPtrs, reflect.New(reflect.PtrTo(spec.colRtypes[colName])).Interface())
	}
	return &tDecodeState{colPtrs: colPtrs}, nil
}

func traverseMakeSpec(spec *tDestSpec, typeSpec *tTypeSpec, parentFieldSpec *tFieldSpec, colPath []string, fieldPath []int) error {
	rtypeElem := derefRtype(typeSpec.rtype)
	typeSpec.fieldSpecs = make([]tFieldSpec, rtypeElem.NumField())

	for i := 0; i < rtypeElem.NumField(); i++ {
		sfield := rtypeElem.Field(i)
		fieldRtype := derefRtype(sfield.Type)
		fieldPath := append(fieldPath, i)

		fieldSpec := &typeSpec.fieldSpecs[i]
		*fieldSpec = tFieldSpec{
			parentFieldSpec: parentFieldSpec,
			typeSpec:        tTypeSpec{rtype: sfield.Type},
			fieldIndex:      i,
			fieldPath:       copyIntSlice(fieldPath),
			colIndex:        -1,
			sfield:          sfield,
		}

		if !isStructFieldPublic(sfield) {
			continue
		}

		if sfield.Anonymous && fieldRtype.Kind() == reflect.Struct {
			err := traverseMakeSpec(spec, &fieldSpec.typeSpec, fieldSpec, colPath, fieldPath)
			if err != nil {
				return err
			}
			continue
		}

		fieldSpec.colName = structFieldColumnName(sfield)
		if fieldSpec.colName == "" {
			continue
		}

		colPath := append(colPath, fieldSpec.colName)
		fieldSpec.uniqColAlias = strings.Join(colPath, ".")
		fieldSpec.colIndex = stringIndex(spec.colNames, fieldSpec.uniqColAlias)

		if spec.colRtypes[fieldSpec.uniqColAlias] != nil {
			return Err{
				Code:  ErrCodeRedundantCol,
				While: `preparing destination spec`,
				Cause: fmt.Errorf(`redundant occurrence of column %q`, fieldSpec.uniqColAlias),
			}
		}
		spec.colRtypes[fieldSpec.uniqColAlias] = sfield.Type

		if fieldRtype.Kind() == reflect.Struct && !isScannableRtype(fieldRtype) {
			err := traverseMakeSpec(spec, &fieldSpec.typeSpec, fieldSpec, colPath, fieldPath)
			if err != nil {
				return err
			}
		}
	}

	return nil
}

func traverseDecode(rootRval reflect.Value, spec *tDestSpec, state *tDecodeState, typeSpec *tTypeSpec, fieldSpec *tFieldSpec) error {
	everyColValueIsNil := true

	for i := range typeSpec.fieldSpecs {
		fieldSpec := &typeSpec.fieldSpecs[i]
		sfield := fieldSpec.sfield
		fieldRtype := derefRtype(sfield.Type)

		if !isStructFieldPublic(sfield) {
			continue
		}

		if sfield.Anonymous && fieldRtype.Kind() == reflect.Struct {
			err := traverseDecode(rootRval, spec, state, &fieldSpec.typeSpec, fieldSpec)
			if err != nil {
				return err
			}
			continue
		}

		if fieldSpec.colName == "" {
			continue
		}

		if fieldRtype.Kind() == reflect.Struct && !isScannableRtype(fieldRtype) {
			err := traverseDecode(rootRval, spec, state, &fieldSpec.typeSpec, fieldSpec)
			if err != nil {
				return err
			}
			continue
		}

		if !(fieldSpec.colIndex >= 0) {
			continue
		}

		colRval := reflect.ValueOf(state.colPtrs[fieldSpec.colIndex]).Elem()
		if !colRval.IsNil() {
			everyColValueIsNil = false
		}
	}

	isNested := fieldSpec != nil
	if everyColValueIsNil && isNested && isNilableOrHasNilableNonRootAncestor(fieldSpec) {
		return nil
	}

	for _, fieldSpec := range typeSpec.fieldSpecs {
		if !(fieldSpec.colIndex >= 0) {
			continue
		}

		sfield := fieldSpec.sfield
		colRval := reflect.ValueOf(state.colPtrs[fieldSpec.colIndex]).Elem()

		if colRval.IsNil() {
			if isNilableRkind(sfield.Type.Kind()) {
				continue
			}

			fieldRval := derefAllocStructRvalAt(rootRval, fieldSpec.fieldPath)
			scanner, ok := fieldRval.Addr().Interface().(sql.Scanner)
			if ok {
				err := scanner.Scan(nil)
				if err != nil {
					return Err{Code: ErrCodeScan, While: `scanning into field`, Cause: err}
				}
				continue
			}

			return Err{
				Code:  ErrCodeNull,
				While: `decoding into struct`,
				Cause: fmt.Errorf(`type %q at field %q of struct %q is not nilable, but corresponding column %q was null`,
					sfield.Type, sfield.Name, typeSpec.rtype, fieldSpec.uniqColAlias),
			}
		}

		fieldRval := derefAllocStructRvalAt(rootRval, fieldSpec.fieldPath)
		fieldRval.Set(colRval.Elem())
	}

	return nil
}

func stringIndex(strs []string, str string) int {
	for i := range strs {
		if strs[i] == str {
			return i
		}
	}
	return -1
}

var timeRtype = reflect.TypeOf(time.Time{})
var sqlScannerRtype = reflect.TypeOf((*sql.Scanner)(nil)).Elem()

func isScannableRtype(rtype reflect.Type) bool {
	return rtype != nil &&
		(rtype == timeRtype || reflect.PtrTo(rtype).Implements(sqlScannerRtype))
}

func isNonNilPointer(rval reflect.Value) bool {
	return rval.IsValid() && rval.Kind() == reflect.Ptr && !rval.IsNil()
}

func copyIntSlice(vals []int) []int {
	out := make([]int, len(vals), len(vals))
	copy(out, vals)
	return out
}

func isNilableOrHasNilableNonRootAncestor(fieldSpec *tFieldSpec) bool {
	for fieldSpec != nil {
		if isNilableRkind(fieldSpec.typeSpec.rtype.Kind()) {
			return true
		}
		fieldSpec = fieldSpec.parentFieldSpec
	}
	return false
}

func structTypeSqlIdents(structRtype reflect.Type) []sqlIdent {
	var idents []sqlIdent

	traverseStructFields(structRtype, func(sfield reflect.StructField) {
		colName := structFieldColumnName(sfield)
		if colName == "" {
			return
		}

		fieldRtype := derefRtype(sfield.Type)
		if fieldRtype.Kind() == reflect.Struct && !isScannableRtype(fieldRtype) {
			idents = append(idents, sqlIdent{
				name:   colName,
				idents: structTypeSqlIdents(fieldRtype),
			})
			return
		}

		idents = append(idents, sqlIdent{name: colName})
		return
	})

	return idents
}

type sqlIdent struct {
	name   string
	idents []sqlIdent
}

func (self sqlIdent) selectString() string {
	return bytesToString(self.appendSelect(nil, nil))
}

func (self sqlIdent) appendSelect(buf []byte, path []string) []byte {
	if len(self.idents) == 0 {
		if self.name == "" {
			return buf
		}
		if len(buf) > 0 {
			buf = append(buf, ", "...)
		}
		if len(path) == 0 {
			buf = appendIdentAlias(buf, path, self.name)
		} else {
			buf = appendIdentPath(buf, path, self.name)
			buf = append(buf, " as "...)
			buf = appendIdentAlias(buf, path, self.name)
		}
		return buf
	}

	if self.name != "" {
		path = append(path, self.name)
	}
	for _, ident := range self.idents {
		buf = ident.appendSelect(buf, path)
	}
	return buf
}

func appendIdentPath(buf []byte, path []string, ident string) []byte {
	for i, name := range path {
		if i == 0 {
			buf = append(buf, '(', '"')
			buf = append(buf, name...)
			buf = append(buf, '"', ')')
		} else {
			buf = append(buf, '"')
			buf = append(buf, name...)
			buf = append(buf, '"')
		}
		buf = append(buf, '.')
	}
	buf = append(buf, '"')
	buf = append(buf, ident...)
	buf = append(buf, '"')
	return buf
}

func appendIdentAlias(buf []byte, path []string, ident string) []byte {
	buf = append(buf, '"')
	for _, name := range path {
		buf = append(buf, name...)
		buf = append(buf, '.')
	}
	buf = append(buf, ident...)
	buf = append(buf, '"')
	return buf
}

func bytesToString(bytes []byte) string { return string(bytes) }

func stringToBytes(input string) []byte { return []byte(input) }

func traverseStructFields(rtype reflect.Type, fun func(sfield reflect.StructField)) {
	rtype = derefRtype(rtype)
	if rtype == nil || rtype.Kind() != reflect.Struct {
		panic(fmt.Errorf("expected a struct type, got a %q", rtype))
	}

	for i := 0; i < rtype.NumField(); i++ {
		sfield := rtype.Field(i)
		if !isStructFieldPublic(sfield) {
			continue
		}

		/**
		If this is an embedded struct, traverse its fields as if they're in the
		parent struct.
		*/
		if sfield.Anonymous && derefRtype(sfield.Type).Kind() == reflect.Struct {
			traverseStructFields(sfield.Type, fun)
			continue
		}

		fun(sfield)
	}
}

func derefRtype(rtype reflect.Type) reflect.Type {
	for rtype != nil && rtype.Kind() == reflect.Ptr {
		rtype = rtype.Elem()
	}
	return rtype
}

/*
Derefs the provided value until it's no longer a pointer, allocating as
necessary. Returns a non-pointer value. The input value must be settable or a
non-nil pointer, otherwise this causes a panic.
*/
func derefAllocRval(rval reflect.Value) reflect.Value {
	for rval.Kind() == reflect.Ptr {
		if rval.IsNil() {
			rval.Set(reflect.New(rval.Type().Elem()))
		}
		rval = rval.Elem()
	}
	return rval
}

/*
Finds or allocates an rval at the given struct field path, returning the
resulting reflect value. If the starting value is settable, the resulting value
should also be settable. Ditto if the starting value is a non-nil pointer and
the path is not empty.

Assumes that every type on the path, starting with the root, is a struct type or
an arbitrarily nested struct pointer type. Panics if the assumption doesn't
hold.
*/
func derefAllocStructRvalAt(rval reflect.Value, path []int) reflect.Value {
	for _, i := range path {
		rval = derefAllocRval(rval)
		rval = rval.Field(i)
	}
	return rval
}

func isStructFieldPublic(sfield reflect.StructField) bool {
	return sfield.PkgPath == ""
}

func structFieldColumnName(sfield reflect.StructField) string {
	tag := sfield.Tag.Get("db")
	if tag == "-" {
		return ""
	}
	return tag
}

func isNilableRkind(kind reflect.Kind) bool {
	switch kind {
	case reflect.Chan, reflect.Func, reflect.Interface, reflect.Map, reflect.Ptr, reflect.Slice:
		return true
	default:
		return false
	}
}
<file_sep>/gos_test.go
package gos

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"os"
	"os/user"
	"reflect"
	"strings"
	"testing"
	"time"
	"unsafe"

	_ "github.com/lib/pq"
)

var testCtx = context.Background()
var testTx *sql.Tx

const testDbName = `gos_test_db`

func TestMain(m *testing.M) {
	os.Exit(runTestMain(m))
}

// This is a separate function to allow `defer` before `os.Exit`.
func runTestMain(m *testing.M) int {
	connParams := []string{
		`host=localhost`,
		`sslmode=disable`,
		`dbname=gos_test_db`,
	}

	/**
	Try using the current OS user as the Postgres user. Works on MacOS when
	Postgres is installed via Homebrew. Might fail in other configurations.
	*/
	usr, err := user.Current()
	if err != nil {
		panic(err)
	}
	connParams = append(connParams, `user=`+usr.Username)

	/**
	Create a test database and drop it at the end. Note that two concurrent
	instances of this test would conflict; we could create databases with random
	names to allow multiple instances of the test, but that seems like decadent
	overkill.
	*/
	_ = dropDb(connParams, testDbName)
	err = createDb(connParams, testDbName)
	if err != nil {
		panic(err)
	}
	defer dropDb(connParams, testDbName)

	testConnParams := append(connParams, `dbname=`+testDbName)
	db, err := sql.Open("postgres", strings.Join(testConnParams, ` `))
	if err != nil {
		panic(err)
	}
	defer db.Close()

	/**
	Use a single DB transaction for testing, rolling it back at the end, to avoid
	storing test-generated garbage even if database deletion fails.
	*/
	testTx, err = db.Begin()
	if err != nil {
		panic(fmt.Errorf("failed to start DB transaction: %+v", err))
	}
	defer testTx.Rollback()

	return m.Run()
}

func TestScalarBasic(t *testing.T) {
	var result string
	query := `select 'blah'`
	err := Query(testCtx, testTx, &result, query, nil)
	if err != nil {
		t.Fatalf("%+v", err)
	}

	expected := "blah"
	if expected != result {
		t.Fatalf(`expected %q, got %q`, expected, result)
	}
}

func TestScalarNonNullable(t *testing.T) {
	var result string
	query := `select null`
	err := Query(testCtx, testTx, &result, query, nil)
	/**
	Why this doesn't inspect the error: the error comes from `database/sql`;
	there's no programmatic API to detect its type. We return an `ErrNull` in
	some other scenarios.
	*/
	if err == nil {
		t.Fatalf(`expected scanning null into non-nullable scalar to produce an error`)
	}
}

func TestScalarNullable(t *testing.T) {
	var result *string
	query := `select 'blah'`
	err := Query(testCtx, testTx, &result, query, nil)
	if err != nil {
		t.Fatalf("%+v", err)
	}

	expected := "blah"
	if expected != *result {
		t.Fatalf(`expected %q, got %q`, expected, *result)
	}

	query = `select null`
	err = Query(testCtx, testTx, &result, query, nil)
	if err != nil {
		t.Fatalf("%+v", err)
	}

	if result != nil {
		t.Fatalf(`expected selecting null to produce nil, got %q`, *result)
	}
}

func TestScalarsBasic(t *testing.T) {
	var results []string
	query := `select * from (values ('one'), ('two'), ('three')) as _`
	err := Query(testCtx, testTx, &results, query, nil)
	if err != nil {
		t.Fatalf("%+v", err)
	}

	expected := []string{"one", "two", "three"}
	if !reflect.DeepEqual(expected, results) {
		t.Fatalf(`expected %#v, got %#v`, expected, results)
	}
}

func TestScalarsNonNullable(t *testing.T) {
	var results []string
	query := `select * from (values ('one'), (null), ('three')) as _`
	err := Query(testCtx, testTx, &results, query, nil)
	/**
	Why this doesn't inspect the error: the error comes from `database/sql`;
	there's no programmatic API to detect its type. We return an `ErrNull` in
	some other scenarios.
	*/
	if err == nil {
		t.Fatalf(`expected scanning null into non-nullable scalar to produce an error`)
	}
}

func TestScalarsNullable(t *testing.T) {
	var results []*string
	query := `select * from (values ('one'), (null), ('three')) as _`
	err := Query(testCtx, testTx, &results, query, nil)
	if err != nil {
		t.Fatalf("%+v", err)
	}

	expected := []*string{strptr("one"), nil, strptr("three")}
	if !reflect.DeepEqual(expected, results) {
		t.Fatalf(`expected %#v, got %#v`, expected, results)
	}
}

// Verify that we treat `time.Time` as an atomic scannable rather than a struct.
func TestScalarTime(t *testing.T) {
	var result time.Time
	query := `select '0001-01-01'::timestamp`
	err := Query(testCtx, testTx, &result, query, nil)
	if err != nil {
		t.Fatalf("%+v", err)
	}

	expected := timeMustParse(`0001-01-01T00:00:00Z`)
	if expected.UnixNano() != result.UnixNano() {
		t.Fatalf(`expected %v, got %v`, expected, result)
	}
}

// Verify that we treat `[]time.Time` as atomic scannables rather than structs.
func TestScalarsTime(t *testing.T) {
	var results []time.Time
	query := `select * from (values ('0001-01-01'::timestamp), ('0002-01-01'::timestamp)) as _`
	err := Query(testCtx, testTx, &results, query, nil)
	if err != nil {
		t.Fatalf("%+v", err)
	}

	expected := []int64{
		timeMustParse(`0001-01-01T00:00:00Z`).UnixNano(),
		timeMustParse(`0002-01-01T00:00:00Z`).UnixNano(),
	}

	received := []int64{
		results[0].UnixNano(),
		results[1].UnixNano(),
	}

	if !reflect.DeepEqual(expected, received) {
		t.Fatalf(`expected %#v, got %#v`, expected, received)
	}
}

func TestScalarScannable(t *testing.T) {
	var result ScannableString
	query := `select 'blah'`
	err := Query(testCtx, testTx, &result, query, nil)
	if err != nil {
		t.Fatalf("%+v", err)
	}

	expected := "blah_scanned"
	received := string(result)
	if expected != received {
		t.Fatalf(`expected %q, got %q`, expected, received)
	}
}

func TestScalarsScannable(t *testing.T) {
	var results []ScannableString
	query := `select * from (values ('one'), ('two'), ('three')) as _`
	err := Query(testCtx, testTx, &results, query, nil)
	if err != nil {
		t.Fatalf("%+v", err)
	}

	expected := []string{"one_scanned", "two_scanned", "three_scanned"}
	received := *(*[]string)(unsafe.Pointer(&results))
	if !reflect.DeepEqual(expected, received) {
		t.Fatalf(`expected %#v, got %#v`, expected, received)
	}
}

func TestStructScannable(t *testing.T) {
	var result ScannableStruct
	query := `select 'blah'`
	err := Query(testCtx, testTx, &result, query, nil)
	if err != nil {
		t.Fatalf("%+v", err)
	}

	expected := ScannableStruct{Value: "blah_scanned"}
	if expected != result {
		t.Fatalf(`expected %q, got %q`, expected, result)
	}
}

func TestStructsScannable(t *testing.T) {
	var results []ScannableStruct
	query := `select * from (values ('one'), ('two'), ('three')) as _`
	err := Query(testCtx, testTx, &results, query, nil)
	if err != nil {
		t.Fatalf("%+v", err)
	}

	expected := []ScannableStruct{{"one_scanned"}, {"two_scanned"}, {"three_scanned"}}
	if !reflect.DeepEqual(expected, results) {
		t.Fatalf(`expected %#v, got %#v`, expected, results)
	}
}

func TestStructWithBasicTypes(t *testing.T) {
	var result struct {
		Int32   int32           `db:"int32"`
		Int64   int64           `db:"int64"`
		Float32 float32         `db:"float32"`
		Float64 float64         `db:"float64"`
		String  string          `db:"string"`
		Bool    bool            `db:"bool"`
		Time    time.Time       `db:"time"`
		Scan    ScannableString `db:"scan"`
	}

	query := `
	select
		1                 :: int4      as int32,
		2                 :: int8      as int64,
		3                 :: float4    as float32,
		4                 :: float8    as float64,
		'5'               :: text      as string,
		true              :: bool      as bool,
		current_timestamp :: timestamp as time,
		'scan'            :: text      as scan
	`

	err := Query(testCtx, testTx, &result, query, nil)
	if err != nil {
		t.Fatalf("%+v", err)
	}

	tFieldEq(t, "Int32", result.Int32, int32(1))
	tFieldEq(t, "Int64", result.Int64, int64(2))
	tFieldEq(t, "Float32", result.Float32, float32(3))
	tFieldEq(t, "Float64", result.Float64, float64(4))
	tFieldEq(t, "String", result.String, "5")
	tFieldEq(t, "Bool", result.Bool, true)
	if result.Time.IsZero() {
		t.Fatalf(`expected time to be non-zero`)
	}
	tFieldEq(t, "Scan", result.Scan, ScannableString("scan_scanned"))
}

func TestStructFieldNaming(t *testing.T) {
	var result struct {
		One   string   `db:"one"`
		Two   *string  `db:"six"`
		Three **string `db:"seven"`
		Four  string   `db:"-"`
		Five  string
	}

	two := "2"
	three := "3"
	three_ := &three

	result.One = "1"
	result.Two = &two
	result.Three = &three_
	result.Four = "4"
	result.Five = "5"

	query := `
	select
		'one'   as one,
		'two'   as six,
		'three' as seven
	`

	err := Query(testCtx, testTx, &result, query, nil)
	if err != nil {
		t.Fatalf("%+v", err)
	}

	tFieldEq(t, "One", result.One, "one")
	tFieldEq(t, "Two", *result.Two, "two")
	tFieldEq(t, "Three", **result.Three, "three")
	tFieldEq(t, "Four", result.Four, "4")
	tFieldEq(t, "Five", result.Five, "5")
}

func TestStructNoRows(t *testing.T) {
	var result struct{}
	query := `select where false`
	err := Query(testCtx, testTx, &result, query, nil)
	if !errors.Is(err, ErrNoRows) {
		t.Fatalf(`expected error ErrNoRows, got %+v`, err)
	}
}

func TestStructMultipleRows(t *testing.T) {
	var result struct {
		Val string `db:"val"`
	}
	query := `select * from (values ('one'), ('two')) as vals (val)`
	err := Query(testCtx, testTx, &result, query, nil)
	if !errors.Is(err, ErrMultipleRows) {
		t.Fatalf(`expected error ErrMultipleRows, got %+v`, err)
	}
}

func TestInvalidDest(t *testing.T) {
	err := Query(testCtx, testTx, nil, `select`, nil)
	if !errors.Is(err, ErrInvalidDest) {
		t.Fatalf(`expected error ErrInvalidDest, got %+v`, err)
	}
	err = Query(testCtx, testTx, "str", `select`, nil)
	if !errors.Is(err, ErrInvalidDest) {
		t.Fatalf(`expected error ErrInvalidDest, got %+v`, err)
	}
	err = Query(testCtx, testTx, struct{}{}, `select`, nil)
	if !errors.Is(err, ErrInvalidDest) {
		t.Fatalf(`expected error ErrInvalidDest, got %+v`, err)
	}
	err = Query(testCtx, testTx, []struct{}{}, `select`, nil)
	if !errors.Is(err, ErrInvalidDest) {
		t.Fatalf(`expected error ErrInvalidDest, got %+v`, err)
	}
}

func TestStructFieldNullability(t *testing.T) {
	type Result struct {
		NonNilable string  `db:"non_nilable"`
		Nilable    *string `db:"nilable"`
	}

	var result Result

	query := `
	select
		'one' as non_nilable,
		null  as nilable
	`

	err := Query(testCtx, testTx, &result, query, nil)
	if err != nil {
		t.Fatalf("%+v", err)
	}

	expected := Result{NonNilable: "one", Nilable: nil}
	if expected != result {
		t.Fatalf("expected %#v, got %#v", expected, result)
	}
}

func TestStructs(t *testing.T) {
	type Result struct {
		One string `db:"one"`
		Two int64  `db:"two"`
	}

	var results []Result
	query := `select * from (values ('one', 10), ('two', 20)) as vals (one, two)`
	err := Query(testCtx, testTx, &results, query, nil)
	if err != nil {
		t.Fatalf("%+v", err)
	}

	expected := []Result{{"one", 10}, {"two", 20}}
	if !reflect.DeepEqual(expected, results) {
		t.Fatalf(`expected %#v, got %#v`, expected, results)
	}
}

func TestStructMissingColDest(t *testing.T) {
	var result struct {
		One string `db:"one"`
	}

	{
		query := `select 'one' as one, 'two' as two`
		err := Query(testCtx, testTx, &result, query, nil)
		if !errors.Is(err, ErrNoColDest) {
			t.Fatalf(`expected error ErrNoColDest, got %+v`, err)
		}
	}

	{
		query := `select 'one' as one, null as two`
		err := Query(testCtx, testTx, &result, query, nil)
		if !errors.Is(err, ErrNoColDest) {
			t.Fatalf(`expected error ErrNoColDest, got %+v`, err)
		}
	}
}

func TestScalarsEmptyResult(t *testing.T) {
	results := []string{"one", "two", "three"}
	query := `select where false`
	err := Query(testCtx, testTx, &results, query, nil)
	if err != nil {
		t.Fatalf("%+v", err)
	}
	expected := []string{}
	if !reflect.DeepEqual(expected, results) {
		t.Fatalf(`expected %#v, got %#v`, expected, results)
	}
}

func TestStructsEmptyResult(t *testing.T) {
	results := []struct{}{{}, {}, {}}
	query := `select where false`
	err := Query(testCtx, testTx, &results, query, nil)
	if err != nil {
		t.Fatalf("%+v", err)
	}
	expected := []struct{}{}
	if !reflect.DeepEqual(expected, results) {
		t.Fatalf(`expected %#v, got %#v`, expected, results)
	}
}

func TestStructNestedNotNullNotNilable(t *testing.T) {
	type Nested struct {
		Val string `db:"val"`
	}
	type Nesting struct {
		Val    string `db:"val"`
		Nested Nested `db:"nested"`
	}

	var result Nesting
	query := `
	select
		'one' as "val",
		'two' as "nested.val"
	`
	err := Query(testCtx, testTx, &result, query, nil)
	if err != nil {
		t.Fatalf("%+v", err)
	}

	expected := Nesting{Val: "one", Nested: Nested{Val: "two"}}
	if !reflect.DeepEqual(expected, result) {
		t.Fatalf(`expected %#v, got %#v`, expected, result)
	}
}

func TestStructNestedNotNullNilableStruct(t *testing.T) {
	type Nested struct {
		Val string `db:"val"`
	}
	type Nesting struct {
		Val    string  `db:"val"`
		Nested *Nested `db:"nested"`
	}

	var result Nesting
	query := `
	select
		'one' as "val",
		'two' as "nested.val"
	`
	err := Query(testCtx, testTx, &result, query, nil)
	if err != nil {
		t.Fatalf("%+v", err)
	}

	expected := Nesting{Val: "one", Nested: &Nested{Val: "two"}}
	if !reflect.DeepEqual(expected, result) {
		t.Fatalf(`expected %#v, got %#v`, expected, result)
	}
}

func TestStructNestedNotNullNilableField(t *testing.T) {
	type Nested struct {
		Val *string `db:"val"`
	}
	type Nesting struct {
		Val    string `db:"val"`
		Nested Nested `db:"nested"`
	}

	var result Nesting
	query := `
	select
		'one' as "val",
		'two' as "nested.val"
	`
	err := Query(testCtx, testTx, &result, query, nil)
	if err != nil {
		t.Fatalf("%+v", err)
	}

	expected := Nesting{Val: "one", Nested: Nested{Val: strptr("two")}}
	if !reflect.DeepEqual(expected, result) {
		t.Fatalf(`expected %#v, got %#v`, expected, result)
	}
}

func TestStructNestedNotNullNilableBoth(t *testing.T) {
	type Nested struct {
		Val *string `db:"val"`
	}
	type Nesting struct {
		Val    string  `db:"val"`
		Nested *Nested `db:"nested"`
	}

	var result Nesting
	query := `
	select
		'one' as "val",
		'two' as "nested.val"
	`
	err := Query(testCtx, testTx, &result, query, nil)
	if err != nil {
		t.Fatalf("%+v", err)
	}

	expected := Nesting{Val: "one", Nested: &Nested{Val: strptr("two")}}
	if !reflect.DeepEqual(expected, result) {
		t.Fatalf(`expected %#v, got %#v`, expected, result)
	}
}

func TestStructNestedNullNotNilable(t *testing.T) {
	type Nested struct {
		Val string `db:"val"`
	}
	type Nesting struct {
		Val    string `db:"val"`
		Nested Nested `db:"nested"`
	}

	var result Nesting
	query := `
	select
		'one' as "val",
		null as "nested.val"
	`
	err := Query(testCtx, testTx, &result, query, nil)
	if !errors.Is(err, ErrNull) {
		t.Fatalf(`expected error ErrNull, got %+v`, err)
	}
}

// This also tests for on-demand allocation: if all fields of the inner struct
// are nil, the struct is not allocated.
func TestStructNestedNullNilableStruct(t *testing.T) {
	type Nested struct {
		Val string `db:"val"`
	}
	type Nesting struct {
		Val    string  `db:"val"`
		Nested *Nested `db:"nested"`
	}

	var result Nesting
	query := `
	select
		'one' as "val",
		null as "nested.val"
	`
	err := Query(testCtx, testTx, &result, query, nil)
	if err != nil {
		t.Fatalf("%+v", err)
	}

	expected := Nesting{Val: "one", Nested: nil}
	if !reflect.DeepEqual(expected, result) {
		t.Fatalf(`expected %#v, got %#v`, expected, result)
	}
}

func TestStructNestedNullNilableField(t *testing.T) {
	type Nested struct {
		Val *string `db:"val"`
	}
	type Nesting struct {
		Val    string `db:"val"`
		Nested Nested `db:"nested"`
	}

	var result Nesting
	query := `
	select
		'one' as "val",
		null as "nested.val"
	`
	err := Query(testCtx, testTx, &result, query, nil)
	if err != nil {
		t.Fatalf("%+v", err)
	}

	expected := Nesting{Val: "one", Nested: Nested{Val: nil}}
	if !reflect.DeepEqual(expected, result) {
		t.Fatalf(`expected %#v, got %#v`, expected, result)
	}
}

// This also tests for on-demand allocation: if all fields of the inner struct
// are nil, the struct is not allocated.
func TestStructNestedNullNilableBoth(t *testing.T) {
	type Nested struct {
		Val *string `db:"val"`
	}
	type Nesting struct {
		Val    string  `db:"val"`
		Nested *Nested `db:"nested"`
	}

	var result Nesting
	query := `
	select
		'one' as "val",
		null as "nested.val"
	`
	err := Query(testCtx, testTx, &result, query, nil)
	if err != nil {
		t.Fatalf("%+v", err)
	}

	expected := Nesting{Val: "one", Nested: nil}
	if !reflect.DeepEqual(expected, result) {
		t.Fatalf(`expected %#v, got %#v`, expected, result)
	}
}

func TestStructNestedPartiallyNull(t *testing.T) {
	type Nested struct {
		One *string `db:"one"`
		Two *string `db:"two"`
	}
	type Nesting struct {
		Nested *Nested `db:"nested"`
		Three  string  `db:"three"`
	}

	var result Nesting
	query := `
	select
		'one'   as "nested.one",
		'three' as "three"
	`
	err := Query(testCtx, testTx, &result, query, nil)
	if err != nil {
		t.Fatalf("%+v", err)
	}

	expected := Nesting{Nested: &Nested{One: strptr("one")}, Three: "three"}
	if !reflect.DeepEqual(expected, result) {
		t.Fatalf(`expected %#v, got %#v`, expected, result)
	}
}

/*
Fields without a matching source column must be left untouched. If they have
non-zero values, the existing values must be preserved.
*/
func TestStructMissingColSrc(t *testing.T) {
	type Result struct {
		One   string  `db:"one"`
		Two   string  `db:"two"`
		Three *string `db:"-"`
		Four  *Result
	}

	result := Result{Two: "two", Three: strptr("three"), Four: &Result{One: "four"}}
	query := `select 'one' as one`
	err := Query(testCtx, testTx, &result, query, nil)
	if err != nil {
		t.Fatalf("%+v", err)
	}

	expected := Result{One: "one", Two: "two", Three: strptr("three"), Four: &Result{One: "four"}}
	if !reflect.DeepEqual(expected, result) {
		t.Fatalf(`expected %#v, got %#v`, expected, result)
	}
}

func createDb(connParams []string, dbName string) error {
	return withPostgresDb(connParams, func(db *sql.DB) error {
		_, err := db.Exec(`create database ` + dbName)
		return err
	})
}

func dropDb(connParams []string, dbName string) error {
	return withPostgresDb(connParams, func(db *sql.DB) error {
		_, err := db.Exec(`drop database ` + dbName)
		return err
	})
}

func withPostgresDb(connParams []string, fun func(db *sql.DB) error) error {
	connParams = append(connParams, `dbname=postgres`)
	db, err := sql.Open("postgres", strings.Join(connParams, ` `))
	if err != nil {
		return err
	}
	err = fun(db)
	if err != nil {
		return err
	}
	return db.Close()
}

func tFieldEq(t *testing.T, fieldName string, left interface{}, right interface{}) {
	if !reflect.DeepEqual(left, right) {
		t.Fatalf(`mismatch in field %q: %#v vs. %#v`, fieldName, left, right)
	}
}

func tExec(t *testing.T, query string) {
	_, err := testTx.Exec(query)
	if err != nil {
		t.Fatalf("%+v", err)
	}
}

type ScannableString string

func (self *ScannableString) Scan(input interface{}) error {
	*self = ScannableString(input.(string) + "_scanned")
	return nil
}

func strptr(str string) *string { return &str }

func timeMustParse(str string) time.Time {
	out, err := time.Parse(time.RFC3339, str)
	if err != nil {
		panic(err)
	}
	return out
}

type ScannableStruct struct {
	Value string
}

func (self *ScannableStruct) Scan(input interface{}) error {
	switch input := input.(type) {
	case nil:
		return nil
	case string:
		self.Value = input + "_scanned"
		return nil
	case []byte:
		self.Value = string(input) + "_scanned"
		return nil
	default:
		return fmt.Errorf("unrecognized input for type %T: type %T, value %v", self, input, input)
	}
}
<file_sep>/go.mod
module github.com/mitranim/gos

go 1.13

// All dependencies are test-only.
require (
	github.com/davecgh/go-spew v1.1.1
	github.com/lib/pq v1.3.0
)
<file_sep>/readme.md
## Overview

**Go** ↔︎ **S**QL: tool for mapping between Go and SQL. Features:

* Decodes SQL records into Go structs
* Supports nested records/structs
* Supports nilable nested records/structs in outer joins
* Helps to generate queries from structs

**This is not an ORM**, and should be used **instead** of an ORM. This tool is expressly designed to let you **write plain SQL**.

See the full documentation at https://godoc.org/github.com/mitranim/gos.

## Differences from [jmoiron/sqlx](https://github.com/jmoiron/sqlx)

Gos is very similar to [jmoiron/sqlx](https://github.com/jmoiron/sqlx). Key differences:

* Supports null records in outer joins, as nested struct pointers
* Selects fields explicitly without relying on `select *`
* Much simpler API, does not wrap `database/sql`
* Explicit field-column mapping, no hidden renaming
* No special utilities for named parameters
* Depends only on the standard library (the `go.mod` dependencies are test-only)

## Usage Example

See the full documentation at https://godoc.org/github.com/mitranim/gos.

```go
type External struct {
  Id    string `db:"id"`
  Name  string `db:"name"`
}

type Internal struct {
  Id   string `db:"id"`
  Name string `db:"name"`
}

// Step 1: generate query.

var result []External

query := fmt.Sprintf(`select %v from (
  select
    external.*,
    internal as internal
  from
    external
    cross join internal
) as _
`, gos.Cols(result))

/*
Resulting query (formatted here for readability):

select
  "id",
  "name",
  ("internal")."id"   as "internal.id",
  ("internal")."name" as "internal.name"
from (
  ...
) as _
*/

// Step 2: use query.

err := gos.Query(ctx, conn, &result, query, nil)

fmt.Printf("%#v\n", result)
```

## Features Under Consideration

1. Utilities for insert and update queries.

The current API helps generate select queries from structs, but not insert or update queries. This needs to be rectified.

2. Short column aliases.

Like many similar libraries, when selecting fields for nested records, Gos relies on column aliases like `"column_name.column_name"`. With enough nesting, they can become too long. At the time of writing, Postgres 12 has an identifier length limit of 63 and will _silently truncate_ the remainder, causing queries to fail. One solution is shorter aliases, such as `"1.2.3"` from struct field indexes. We still want to support long alises for manually-written queries, which means the library would have to support _both_ alias types, which could potentially cause collisions. Unclear what's the best approach.

3. `select *`

Currently Gos requires you to generate queries with the help of `gos.Cols()`. It would be nice to just pass `select *` and have Gos automatically wrap the query into another `select`, listing the fields. Unfortunately not every query can be wrapped, but this can be provided as an additional function, alongside `Query()`.

4. Streaming API.

An API for scanning rows one-by-one, like `database/sql.Rows.Scan()` but for structs. This would allow streaming and might be useful when processing a very large amount of rows. Scanning into structs involves reflecting on the type and generating a spec, and this must be done only once; the API would have to be designed to make it seamless for the user. One option is to wrap `sql.Rows` into an object that provides `.Next()` and `.Scan()`, generates the output spec on the first call to `.Scan()` and stores it, and on subsequent calls ensures that the same destination type was provided.

## Contributing

Issues and pull requests are welcome! The development setup is simple:

```sh
git clone https://github.com/mitranim/gos
cd gos
go test
```

Tests currently require a local instance of Postgres on the default port. They create a separate "database", make no persistent changes, and drop it at the end.

## License

https://en.wikipedia.org/wiki/WTFPL

## Misc

I'm receptive to suggestions. If this library _almost_ satisfies you but needs changes, open an issue or chat me up. Contacts: https://mitranim.com/#contacts
