Scan

Note

For all intents and purposes, this project is considered 'complete.' I do not plan on making any changes in the near future and have not written any code here since approximately 2021. To the best of my knowledge, everything functions as intended. If you are seeking a more comprehensive solution, please refer to https://github.com/stytchauth/sqx. That being said, this project operates effectively in its current state and requires no new code. Feel free to fork it and continue development if desired.

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

import "github.com/blockloop/scan/v2"

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"}}

Custom Column Mapping

By default, column names are mapped to and from database column names using basic title case conversion. You can override this behavior by setting ColumnsMapper and ScannerMapper to custom functions.

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
cpu: 11th Gen Intel(R) Core(TM) i7-1165G7 @ 2.80GHz
BenchmarkColumnsLargeStruct-8           41527964               288.0 ns/op
BenchmarkValuesLargeStruct-8             6816885              1807 ns/op
BenchmarkScanRowOneField-8               5686971              2074 ns/op
BenchmarkScanRowFiveFields-8             4962622              2381 ns/op
BenchmarkScanTenRowsOneField-8           1537761              8598 ns/op
BenchmarkScanTenRowsTenFields-8           322106             50431 ns/op
PASS
ok      github.com/blockloop/scan       92.374s