scan

package module
v0.0.0-...-5c4fb0b Latest Latest
Warning

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

 Go to latest Published: Nov 10, 2022 License: MIT Imports: 8 Imported by: 0

README

Scan

GoDoc go test Coveralls github Report Card

Scan standard lib database rows directly to structs or slices. For the most comprehensive and up-to-date docs see the godoc

Examples

Multiple Rows
db, err := sql.Open("sqlite3", "database.sqlite")
rows, err := db.Query("SELECT * FROM persons")

var persons []Person
err := scan.Rows(&persons, rows)

fmt.Printf("%#v", persons)
// []Person{
//    {ID: 1, Name: "brett"},
//    {ID: 2, Name: "fred"},
//    {ID: 3, Name: "stacy"},
// }
Multiple rows of primitive type
rows, err := db.Query("SELECT name FROM persons")
var names []string
err := scan.Rows(&names, rows)

fmt.Printf("%#v", names)
// []string{
//    "brett",
//    "fred",
//    "stacy",
// }
Single row
rows, err := db.Query("SELECT * FROM persons where name = 'brett' LIMIT 1")
var person Person
err := scan.Row(&person, rows)

fmt.Printf("%#v", person)
// Person{ ID: 1, Name: "brett" }
Scalar value
rows, err := db.Query("SELECT age FROM persons where name = 'brett' LIMIT 1")
var age int8
err := scan.Row(&age, rows)

fmt.Printf("%d", age)
// 100
Nested Struct Fields (as of v2.0.0)
rows, err := db.Query(`
	SELECT person.id,person.name,company.name FROM person
	JOIN company on company.id = person.company_id
	LIMIT 1
`)

var person struct {
	ID      int    `db:"person.id"`
	Name    string `db:"person.name"`
	Company struct {
		Name string `db:"company.name"`
	}
}

err = scan.RowStrict(&person, rows)

err = json.NewEncoder(os.Stdout).Encode(&person)
// Output:
// {"ID":1,"Name":"brett","Company":{"Name":"costco"}}
Strict Scanning

Both Rows and Row have strict alternatives to allow scanning to structs strictly based on their db tag. To avoid unwanted behavior you can use RowsStrict or RowStrict to scan without using field names. Any fields not tagged with the db tag will be ignored even if columns are found that match the field names.

Columns

Columns scans a struct and returns a string slice of the assumed column names based on the db tag or the struct field name respectively. To avoid assumptions, use ColumnsStrict which will only return the fields tagged with the db tag. Both Columns and ColumnsStrict are variadic. They both accept a string slice of column names to exclude from the list. It is recommended that you cache this slice.

package main

type User struct {
        ID        int64
        Name      string
        Age       int
        BirthDate string `db:"bday"`
        Zipcode   string `db:"-"`
        Store     struct {
                ID int
                // ...
        }
}

var nobody = new(User)
var userInsertCols = scan.Columns(nobody, "ID")
// []string{ "Name", "Age", "bday" }

var userSelectCols = scan.Columns(nobody)
// []string{ "ID", "Name", "Age", "bday" }
Values

Values scans a struct and returns the values associated with the provided columns. Values uses a sync.Map to cache fields of structs to greatly improve the performance of scanning types. The first time a struct is scanned it's exported fields locations are cached. When later retrieving values from the same struct it should be much faster. See Benchmarks below.

user := &User{
        ID: 1,
        Name: "Brett",
        Age: 100,
}

vals := scan.Values([]string{"ID", "Name"}, user)
// []interface{}{ 1, "Brett" }

I find that the usefulness of both Values and Columns lies within using a library such as sq.

sq.Insert(userCols...).
        Into("users").
        Values(scan.Values(userCols, &user)...)

Configuration

AutoClose: Automatically call rows.Close() after scan completes (default true)

Why

While many other projects support similar features (i.e. sqlx) scan allows you to use any database lib such as the stdlib or squirrel to write fluent SQL statements and pass the resulting rows to scan for scanning.

Benchmarks

λ go test -bench=. -benchtime=10s ./...
goos: linux
goarch: amd64
pkg: github.com/blockloop/scan
BenchmarkColumnsLargeStruct-8           50000000               272 ns/op
BenchmarkValuesLargeStruct-8             2000000              8611 ns/op
BenchmarkScanRowOneField-8               2000000              8528 ns/op
BenchmarkScanRowFiveFields-8             1000000             12234 ns/op
BenchmarkScanTenRowsOneField-8           1000000             16802 ns/op
BenchmarkScanTenRowsTenFields-8           100000            104587 ns/op
PASS
ok      github.com/blockloop/scan       116.055s

Documentation

Overview

Package scan provides functionality for scanning database/sql rows into slices, structs, and primitive types dynamically

Index

Examples

Constants

This section is empty.

Variables

View Source 
var (
	// ErrNotAPointer is returned when a non-pointer is received
	// when a pointer is expected.
	ErrNotAPointer = errors.New("not a pointer")

	// ErrNotAStructPointer is returned when a non-struct pointer
	// is received but a struct pointer was expected
	ErrNotAStructPointer = errors.New("not a struct pointer")

	// ErrNotASlicePointer is returned when receiving an argument
	// that is expected to be a slice pointer, but it is not
	ErrNotASlicePointer = errors.New("not a slice pointer")

	// ErrStructFieldMissing is returned when trying to scan a value
	// to a column which does not match a struct. This means that
	// the struct does not have a field that matches the column
	// specified.
	ErrStructFieldMissing = errors.New("struct field missing")
)
View Source 
var (
	// ErrTooManyColumns indicates that a select query returned multiple columns and
	// attempted to bind to a slice of a primitive type. For example, trying to bind
	// `select col1, col2 from mutable` to []string
	ErrTooManyColumns = errors.New("too many columns returned for primitive slice")

	// ErrSliceForRow occurs when trying to use Row on a slice
	ErrSliceForRow = errors.New("cannot scan Row into slice")

	// AutoClose is true when scan should automatically close Scanner when the scan
	// is complete. If you set it to false, then you must defer rows.Close() manually
	AutoClose = true

	// OnAutoCloseError can be used to log errors which are returned from rows.Close()
	// By default this is a NOOP function
	OnAutoCloseError = func(error) {}
)

Functions

func Columns 

func Columns(v interface{}, excluded ...string) ([]string, error)

Columns scans a struct and returns a list of strings that represent the assumed column names based on the db struct tag, or the field name. Any field or struct tag that matches a string within the excluded list will be excluded from the result.

Example 
package main

import (
	"fmt"

	"github.com/ceebydith/scan"
)

func main() {
	var person struct {
		ID   int `db:"person_id"`
		Name string
	}

	cols, _ := scan.Columns(&person)
	fmt.Printf("%+v", cols)
}

Output:

[person_id Name]
Example (Exclude) 
package main

import (
	"fmt"

	"github.com/ceebydith/scan"
)

func main() {
	var person struct {
		ID   int    `db:"id"`
		Name string `db:"name"`
		Age  string `db:"-"`
	}

	cols, _ := scan.Columns(&person)
	fmt.Printf("%+v", cols)
}

Output:

[id name]

func ColumnsStrict 

func ColumnsStrict(v interface{}, excluded ...string) ([]string, error)

ColumnsStrict is identical to Columns, but it only searches struct tags and excludes fields not tagged with the db struct tag.

Example 
package main

import (
	"fmt"

	"github.com/ceebydith/scan"
)

func main() {
	var person struct {
		ID   int `db:"id"`
		Name string
		Age  string `db:"age"`
	}

	cols, _ := scan.ColumnsStrict(&person)
	fmt.Printf("%+v", cols)
}

Output:

[id age]

func Maps 

func Maps(v interface{}, excluded ...string) (map[string]interface{}, error)

func MapsStrict 

func MapsStrict(v interface{}, excluded ...string) (map[string]interface{}, error)

func Row 

func Row(v interface{}, r RowsScanner) error

Row scans a single row into a single variable. It requires that you use db.Query and not db.QueryRow, because QueryRow does not return column names. There is no performance impact in using one over the other. QueryRow only defers returning err until Scan is called, which is an unnecessary optimization for this library.

Example 
package main

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"math/rand"
	"os"

	"github.com/ceebydith/scan"
	_ "github.com/proullon/ramsql/driver"
)

func mustDB(name string, queries ...string) *sql.DB {
	db, err := sql.Open("ramsql", name)
	if err != nil {
		panic(err)
	}

	for _, s := range queries {
		_, err = db.Exec(s)
		if err != nil {
			panic(err)
		}
	}
	return db
}

func exampleDB() *sql.DB {
	return mustDB(fmt.Sprintf("%d", rand.Uint64()), `CREATE TABLE person (
		id INTEGER NOT NULL,
		name VARCHAR(120)
	);`,
		`INSERT INTO person (id, name) VALUES (1, 'brett', 1);`,
		`INSERT INTO person (id, name) VALUES (2, 'fred', 1);`,
	)
}

func main() {
	db := exampleDB()
	defer db.Close()
	rows, err := db.Query("SELECT id,name FROM person LIMIT 1")
	if err != nil {
		panic(err)
	}

	var person struct {
		ID   int    `db:"id"`
		Name string `db:"name"`
	}

	err = scan.Row(&person, rows)
	if err != nil {
		panic(err)
	}

	err = json.NewEncoder(os.Stdout).Encode(&person)
	if err != nil {
		panic(err)
	}
}

Output:

{"ID":1,"Name":"brett"}
Example (Scalar) 
package main

import (
	"database/sql"
	"fmt"
	"math/rand"

	"github.com/ceebydith/scan"
	_ "github.com/proullon/ramsql/driver"
)

func mustDB(name string, queries ...string) *sql.DB {
	db, err := sql.Open("ramsql", name)
	if err != nil {
		panic(err)
	}

	for _, s := range queries {
		_, err = db.Exec(s)
		if err != nil {
			panic(err)
		}
	}
	return db
}

func exampleDB() *sql.DB {
	return mustDB(fmt.Sprintf("%d", rand.Uint64()), `CREATE TABLE person (
		id INTEGER NOT NULL,
		name VARCHAR(120)
	);`,
		`INSERT INTO person (id, name) VALUES (1, 'brett', 1);`,
		`INSERT INTO person (id, name) VALUES (2, 'fred', 1);`,
	)
}

func main() {
	db := exampleDB()
	defer db.Close()
	rows, err := db.Query("SELECT name FROM person LIMIT 1")
	if err != nil {
		panic(err)
	}

	var name string

	err = scan.Row(&name, rows)
	if err != nil {
		panic(err)
	}

	fmt.Printf("%q", name)
}

Output:

"brett"

func RowStrict 

func RowStrict(v interface{}, r RowsScanner) error

RowStrict scans a single row into a single variable. It is identical to Row, but it ignores fields that do not have a db tag

Example 
package main

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"math/rand"
	"os"

	"github.com/ceebydith/scan"
	_ "github.com/proullon/ramsql/driver"
)

func mustDB(name string, queries ...string) *sql.DB {
	db, err := sql.Open("ramsql", name)
	if err != nil {
		panic(err)
	}

	for _, s := range queries {
		_, err = db.Exec(s)
		if err != nil {
			panic(err)
		}
	}
	return db
}

func exampleDB() *sql.DB {
	return mustDB(fmt.Sprintf("%d", rand.Uint64()), `CREATE TABLE person (
		id INTEGER NOT NULL,
		name VARCHAR(120)
	);`,
		`INSERT INTO person (id, name) VALUES (1, 'brett', 1);`,
		`INSERT INTO person (id, name) VALUES (2, 'fred', 1);`,
	)
}

func main() {
	db := exampleDB()
	defer db.Close()
	rows, err := db.Query("SELECT id,name FROM person LIMIT 1")
	if err != nil {
		panic(err)
	}

	var person struct {
		ID   int
		Name string `db:"name"`
	}

	err = scan.RowStrict(&person, rows)
	if err != nil {
		panic(err)
	}

	json.NewEncoder(os.Stdout).Encode(&person)
}

Output:

{"ID":0,"Name":"brett"}

func Rows 

func Rows(v interface{}, r RowsScanner) (outerr error)

Rows scans sql rows into a slice (v)

Example 
package main

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"math/rand"
	"os"

	"github.com/ceebydith/scan"
	_ "github.com/proullon/ramsql/driver"
)

func mustDB(name string, queries ...string) *sql.DB {
	db, err := sql.Open("ramsql", name)
	if err != nil {
		panic(err)
	}

	for _, s := range queries {
		_, err = db.Exec(s)
		if err != nil {
			panic(err)
		}
	}
	return db
}

func exampleDB() *sql.DB {
	return mustDB(fmt.Sprintf("%d", rand.Uint64()), `CREATE TABLE person (
		id INTEGER NOT NULL,
		name VARCHAR(120)
	);`,
		`INSERT INTO person (id, name) VALUES (1, 'brett', 1);`,
		`INSERT INTO person (id, name) VALUES (2, 'fred', 1);`,
	)
}

func main() {
	db := exampleDB()
	defer db.Close()
	rows, err := db.Query("SELECT id,name FROM person ORDER BY id ASC")
	if err != nil {
		panic(err)
	}

	var persons []struct {
		ID   int    `db:"id"`
		Name string `db:"name"`
	}

	err = scan.Rows(&persons, rows)
	if err != nil {
		panic(err)
	}

	json.NewEncoder(os.Stdout).Encode(&persons)
}

Output:

[{"ID":1,"Name":"brett"},{"ID":2,"Name":"fred"}]
Example (Primitive) 
package main

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"math/rand"
	"os"

	"github.com/ceebydith/scan"
	_ "github.com/proullon/ramsql/driver"
)

func mustDB(name string, queries ...string) *sql.DB {
	db, err := sql.Open("ramsql", name)
	if err != nil {
		panic(err)
	}

	for _, s := range queries {
		_, err = db.Exec(s)
		if err != nil {
			panic(err)
		}
	}
	return db
}

func exampleDB() *sql.DB {
	return mustDB(fmt.Sprintf("%d", rand.Uint64()), `CREATE TABLE person (
		id INTEGER NOT NULL,
		name VARCHAR(120)
	);`,
		`INSERT INTO person (id, name) VALUES (1, 'brett', 1);`,
		`INSERT INTO person (id, name) VALUES (2, 'fred', 1);`,
	)
}

func main() {
	db := exampleDB()
	defer db.Close()
	rows, err := db.Query("SELECT name FROM person ORDER BY id ASC")
	if err != nil {
		panic(err)
	}

	var names []string
	err = scan.Rows(&names, rows)
	if err != nil {
		panic(err)
	}

	json.NewEncoder(os.Stdout).Encode(&names)
}

Output:

["brett","fred"]

func RowsStrict 

func RowsStrict(v interface{}, r RowsScanner) (outerr error)

RowsStrict scans sql rows into a slice (v) only using db tags

Example 
package main

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"math/rand"
	"os"

	"github.com/ceebydith/scan"
	_ "github.com/proullon/ramsql/driver"
)

func mustDB(name string, queries ...string) *sql.DB {
	db, err := sql.Open("ramsql", name)
	if err != nil {
		panic(err)
	}

	for _, s := range queries {
		_, err = db.Exec(s)
		if err != nil {
			panic(err)
		}
	}
	return db
}

func exampleDB() *sql.DB {
	return mustDB(fmt.Sprintf("%d", rand.Uint64()), `CREATE TABLE person (
		id INTEGER NOT NULL,
		name VARCHAR(120)
	);`,
		`INSERT INTO person (id, name) VALUES (1, 'brett', 1);`,
		`INSERT INTO person (id, name) VALUES (2, 'fred', 1);`,
	)
}

func main() {
	db := exampleDB()
	defer db.Close()
	rows, err := db.Query("SELECT id,name FROM person ORDER BY id ASC")
	if err != nil {
		panic(err)
	}

	var persons []struct {
		ID   int
		Name string `db:"name"`
	}

	err = scan.Rows(&persons, rows)
	if err != nil {
		panic(err)
	}

	json.NewEncoder(os.Stdout).Encode(&persons)
}

Output:

[{"ID":0,"Name":"brett"},{"ID":0,"Name":"fred"}]

func Values 

func Values(cols []string, v interface{}) ([]interface{}, error)

Values scans a struct and returns the values associated with the columns provided. Only simple value types are supported (i.e. Bool, Ints, Uints, Floats, Interface, String)

Example 
package main

import (
	"fmt"

	"github.com/ceebydith/scan"
)

func main() {
	person := struct {
		ID   int    `db:"id"`
		Name string `db:"name"`
	}{
		ID:   1,
		Name: "Brett",
	}

	cols := []string{"id", "name"}
	vals, _ := scan.Values(cols, &person)
	fmt.Printf("%+v", vals)
}

Output:

[1 Brett]

Types

type RowsScanner 

type RowsScanner interface {
	Close() error
	Scan(dest ...interface{}) error
	Columns() ([]string, error)
	ColumnTypes() ([]*sql.ColumnType, error)
	Err() error
	Next() bool
}

RowsScanner is a database scanner for many rows. It is most commonly the result of *sql.DB Query(...).

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.