gos

README

Overview

Go ↔︎ SQL: tool for decoding results into Go structs. Supports streaming.

Not an ORM, and should be used instead of an ORM, in combination with a simple query builder (see below).

Key features:

• Decodes SQL records into Go structs.
• Supports nested records/structs.
• Supports nilable nested records/structs in outer joins.
• Supports streaming.

See the full documentation at https://pkg.go.dev/github.com/mitranim/gos.

See the sibling library https://pkg.go.dev/github.com/mitranim/sqlb: a simple query builder that supports scanning structs into named arguments.

Differences from jmoiron/sqlx

Gos is somewhat similar to jmoiron/sqlx. Key differences:

• Supports null records in outer joins, as nested struct pointers.
• Selects fields explicitly, by reflecting on the output struct. This allows you to write select *, but if the struct is lacking some of the fields, the DB will optimize them out of the query.
• Simpler API, does not wrap database/sql.
• Explicit field-column mapping, no hidden renaming.
• Has only one tiny dependency (most deps in go.mod are test-only).
• ... probably more

Features Under Consideration

• 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, or worse. 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.

Changelog

0.1.10

Improved how Query and Scanner handle previously-existing values in the output, especially in regards to pointers.

When a row contains null, the corresponding Go value is now zeroed rather than ignored. The old non-zeroing behavior was aligned with encoding/json. The new behavior diverges from it.

When a row contains non-null and the corresponding Go value is a non-nil pointer, the value is written to the pointer's target, without replacing the pointer.

0.1.9

Query allows nil output, using conn.ExecContext to discard the result.

0.1.8

Support streaming via QueryScanner and Scanner.

0.1.7

Dependency update.

0.1.6

Breaking: moved query generation utils into https://pkg.go.dev/github.com/mitranim/sqlb.

0.1.5

Fixed an oversight in queryStruct and queryScalar that could lead to shadowing DB errors with ErrNoRows in some edge cases.

0.1.4

Added SqlQuery.QueryAppend.

0.1.3

Changed the license to Unlicense.

0.1.2

Breaking changes in named args utils for symmetry with database/sql.

• Added Named().
• Renamed SqlArg -> NamedArg.
• Renamed SqlArgs -> NamedArgs.
• Renamed StructSqlArgs -> StructNamedArgs.

Also moved some reflection-related utils to a tiny dependency.

0.1.1

First tagged release. Added SqlArgs and SqlQuery for query building.

Contributing

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

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://unlicense.org

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

Documentation

Overview

Go SQL, tool for decoding results into Go structs. Supports streaming.

NOT AN ORM, and should be used instead of an ORM, in combination with a simple query builder (see below).

See the sibling library "github.com/mitranim/sqlb": a simple query builder that supports converting structs into named arguments.

Key Features

• Decodes SQL records into Go structs. See `Query()`.

• Supports nested records/structs.

• Supports nilable nested records/structs in outer joins.

• Supports streaming. See `QueryScanner()`.

Struct Decoding 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, the following two definitions are completely equivalent.

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

Same as:

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 somewhat similar to https://github.com/jmoiron/sqlx. Key differences:

• Supports null records in outer joins, as nested struct pointers.

• Selects fields explicitly, by reflecting on the output struct. This allows YOU to write `select *`, but if the struct is lacking some of the fields, the DB will optimize them out of the query.

• Simpler API, does not wrap `database/sql`.

• Explicit field-column mapping, no hidden renaming.

• Has only one tiny dependency (most deps in `go.mod` are test-only).

• ... probably more

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.

Index

• func Query(ctx context.Context, conn QueryExecer, dest interface{}, query string, ...) error
• type Err
  • func (self Err) Error() string
  • func (self Err) Is(other error) bool
  • func (self Err) Unwrap() error
• type ErrCode
• type Execer
• type QueryExecer
• type Queryer
• type Scanner
  • func QueryScanner(ctx context.Context, conn Queryer, query string, args []interface{}) (Scanner, error)

Examples

• Query

Functions

func Query

func Query(ctx context.Context, conn QueryExecer, dest interface{}, query string, args []interface{}) error

Shortcut for scanning columns into the destination, which may be one of:

• Nil interface{}.
• Nil pointer.
• Pointer to single scalar.
• Pointer to slice of scalars.
• Pointer to single struct.
• Pointer to slice of structs.

When the output is nil interface{} or nil pointer, this calls `conn.ExecContext`, discarding the result.

When the output is a slice, the query should use a small `limit`. When processing a large data set, prefer `QueryScanner()` to scan rows one-by-one without buffering the result.

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 should follow the common convention for selecting nested fields, see below.

type Inner struct {
	InnerValue string `db:"inner_value"`
}
type OuterValue struct {
	Inner      Inner `db:"inner"`
	OuterValue string `db:"outer_value"`
}

The query should have:

select
	outer_value         as "outer_value",
	(inner).inner_value as "inner.inner_value"

The easiest way to generate the query correctly is by calling `sqlb.Cols(dest)`, using the sibling package "github.com/mitranim/sqlb".

Example

package main

import (
	"context"
	"database/sql"
	"fmt"

	"github.com/mitranim/gos"
	"github.com/mitranim/sqlb"
)

func main() {
	type Internal struct {
		Id   string `db:"id"`
		Name string `db:"name"`
	}

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

	// Step 1: generate query.

	var result []External

	query := fmt.Sprintf(`
select %v from (
	select
		external.*,
		internal as internal
	from
		external
		cross join internal
) as _
`, sqlb.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.

	var ctx context.Context
	var conn *sql.Tx
	err := gos.Query(ctx, conn, &result, query, nil)
	if err != nil {
		panic(err)
	}
}

Types

type Err

type Err struct {
	Code  ErrCode
	While string
	Cause error
}

Describes a Gos error.

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`)}
	ErrInvalidInput Err = Err{Code: ErrCodeInvalidInput, Cause: errors.New(`invalid input`)}
	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 row`)}
)

Use blank error variables to detect error types:

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. When compared by `errors.Is`, they compare `.Cause` and fall back on `.Code`.

func (Err) Error

func (self Err) Error() string

Implement `error`.

func (Err) Is

func (self Err) Is(other error) bool

Implement a hidden interface in "errors".

func (Err) Unwrap

func (self Err) Unwrap() error

Implement a hidden interface in "errors".

type ErrCode

type ErrCode string

Error codes. You probably shouldn't use this directly; instead, use the `Err` variables with `errors.Is`.

const (
	ErrCodeUnknown      ErrCode = ""
	ErrCodeNoRows       ErrCode = "ErrNoRows"
	ErrCodeMultipleRows ErrCode = "ErrMultipleRows"
	ErrCodeInvalidDest  ErrCode = "ErrInvalidDest"
	ErrCodeInvalidInput ErrCode = "ErrInvalidInput"
	ErrCodeNoColDest    ErrCode = "ErrNoColDest"
	ErrCodeRedundantCol ErrCode = "ErrRedundantCol"
	ErrCodeNull         ErrCode = "ErrNull"
	ErrCodeScan         ErrCode = "ErrScan"
)

type Execer

type Execer interface {
	ExecContext(context.Context, string, ...interface{}) (sql.Result, error)
}

Subset of `QueryExecer`. Satisfied by `*sql.DB`, `*sql.Tx`, may be satisfied by other types.

type QueryExecer

type QueryExecer interface {
	Queryer
	Execer
}

Database connection required by `Query`. Satisfied by `*sql.DB`, `*sql.Tx`, may be satisfied by other types.

type Queryer

type Queryer interface {
	QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error)
}

Database connection required by `QueryScanner`. Satisfied by `*sql.DB`, `*sql.Tx`, may be satisfied by other types.

type Scanner

type Scanner interface {
	// Same as `(*sql.Rows).Close`. MUST be called at the end.
	io.Closer

	// Same as `(*sql.Rows).Next`.
	Next() bool

	// Same as `(*sql.Rows).Err`.
	Err() error

	// Decodes the current row into the output. For technical reasons, the output
	// type is cached on the first call and must be the same for every call.
	Scan(interface{}) error
}

Decodes individual SQL rows in a streaming fashion. Returned by `QueryScanner()`.

func QueryScanner

func QueryScanner(ctx context.Context, conn Queryer, query string, args []interface{}) (Scanner, error)

Executes an SQL query and prepares a `Scanner` that can decode individual rows into structs or scalars. A `Scanner` is used similarly to `*sql.Rows`, but automatically maps columns to struct fields. Just like `*sql.Rows`, this avoids buffering all results in memory, which is especially useful for large sets.

The returned scanner MUST be closed after finishing.

Example:

scan, err := QueryScanner(ctx, conn, query, args)
panic(err)
defer scan.Close()

for scan.Next() {
	var result ResultType
	err := scan.Scan(&result)
	panic(err)
}