scan

README

scan

scan provides the ability to use database/sql/rows to scan datasets 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, row)

fmt.Printf("%d", age)
// 100

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

Why

While many other awesome db project support similar features (i.e. sqlx) this provides the ability to use other projects like sq to write fluent sql statements and pass the resulting row to scan for simple scanning

Benchmarks

I created some benchmarks in bench_scanner_test.go to compare using scan against manually scanning directly to structs and/or appending to slices. The results aren't staggering as you can see. Roughly 850ns for one field structs and 4.6μs for five field structs.

> go test -benchtime=10s -bench=.
goos: darwin
goarch: amd64
pkg: github.com/blockloop/scan
BenchmarkValuesLargeStruct-8             2000000              8088 ns/op
BenchmarkValuesLargeStructCached-8      10000000              1564 ns/op
BenchmarkScanRowOneField-8               1000000             12496 ns/op
BenchmarkDirectScanOneField-8            2000000              9448 ns/op
BenchmarkScanRowFiveFields-8              500000             23949 ns/op
BenchmarkDirectScanFiveFields-8          1000000             16993 ns/op
BenchmarkScanRowsOneField-8              1000000             16872 ns/op
BenchmarkDirectScanManyOneField-8        1000000             14030 ns/op
PASS
ok      github.com/blockloop/scan       125.432s

Documentation

Overview

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

Index

• Variables
• func Columns(v interface{}, excluded ...string) []string
• func ColumnsStrict(v interface{}, excluded ...string) []string
• func Row(v interface{}, rows RowsScanner) error
• func Rows(v interface{}, rows RowsScanner) error
• func Values(cols []string, v interface{}) []interface{}
• type RowsScanner
• type Scanner

Examples

• Row
• Row (Scalar)
• Rows
• Rows (Primitive)

Variables

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 mytable` 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
)

Functions

func Columns

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

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

func ColumnsStrict

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

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

func Row

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

Row scans a single row into a single variable

Example

package main

import (
	"database/sql"
	"encoding/json"
	"os"

	"github.com/blockloop/scan"
	_ "github.com/mattn/go-sqlite3"
)

func openDB() *sql.DB {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		panic(err)
	}

	_, err = db.Exec(`CREATE TABLE persons (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		name VARCHAR(120) NOT NULL DEFAULT ''
	);

	INSERT INTO PERSONS (name)
	VALUES ('brett'), ('fred');`)
	if err != nil {
		panic(err)
	}

	return db
}

func main() {
	db := openDB()
	rows, err := db.Query("SELECT * FROM persons 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)
	}

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

Output:

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

Example (Scalar)

package main

import (
	"database/sql"
	"fmt"

	"github.com/blockloop/scan"
	_ "github.com/mattn/go-sqlite3"
)

func openDB() *sql.DB {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		panic(err)
	}

	_, err = db.Exec(`CREATE TABLE persons (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		name VARCHAR(120) NOT NULL DEFAULT ''
	);

	INSERT INTO PERSONS (name)
	VALUES ('brett'), ('fred');`)
	if err != nil {
		panic(err)
	}

	return db
}

func main() {
	db := openDB()
	rows, err := db.Query("SELECT name FROM persons 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 Rows

func Rows(v interface{}, rows RowsScanner) error

Rows scans sql rows into a slice (v)

Example

package main

import (
	"database/sql"
	"encoding/json"
	"os"

	"github.com/blockloop/scan"
	_ "github.com/mattn/go-sqlite3"
)

func openDB() *sql.DB {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		panic(err)
	}

	_, err = db.Exec(`CREATE TABLE persons (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		name VARCHAR(120) NOT NULL DEFAULT ''
	);

	INSERT INTO PERSONS (name)
	VALUES ('brett'), ('fred');`)
	if err != nil {
		panic(err)
	}

	return db
}

func main() {
	db := openDB()
	rows, err := db.Query("SELECT * FROM persons ORDER BY name")
	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"
	"os"

	"github.com/blockloop/scan"
	_ "github.com/mattn/go-sqlite3"
)

func openDB() *sql.DB {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		panic(err)
	}

	_, err = db.Exec(`CREATE TABLE persons (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		name VARCHAR(120) NOT NULL DEFAULT ''
	);

	INSERT INTO PERSONS (name)
	VALUES ('brett'), ('fred');`)
	if err != nil {
		panic(err)
	}

	return db
}

func main() {
	db := openDB()
	rows, err := db.Query("SELECT name FROM persons ORDER BY name")
	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 Values

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

Values scans a struct and returns the values associated with the columns provided.

Example:

var cols = scan.Columns(&models.User{})

func insertUser(u *models.User) {
    vals := scan.Values(cols, u)
    sq.Insert("users").
        Columns(cols).
        Values(vals).
        RunWith(db).ExecContext(ctx)
}

Types

type RowsScanner

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

RowsScanner is a database scanner for many rows. It is most commonly the result of *(database/sql).DB.Query(...) but can be mocked or stubbed

type Scanner

type Scanner interface {
	Scan(dest ...interface{}) error
}

Scanner is a single row scanner. It is most commonly the result of *(database/sql).DB.QueryRow(...) but can be mocked or stubbed