sqlstruct

README

sqlstruct

sqlstruct provides some convenience functions for using structs with go's database/sql package

Documentation can be found at http://godoc.org/github.com/kisielk/sqlstruct

Documentation

Overview

Package sqlstruct provides some convenience functions for using structs with the Go standard library's database/sql package.

The package matches struct field names to SQL query column names. A field can also specify a matching column with "sql" tag, if it's different from field name. Unexported fields or fields marked with `sql:"-"` are ignored, just like with "encoding/json" package.

For example:

type T struct {
    F1 string
    F2 string `sql:"field2"`
    F3 string `sql:"-"`
}

rows, err := db.Query(fmt.Sprintf("SELECT %s FROM tablename", sqlstruct.Columns(T{})))
...

for rows.Next() {
	var t T
    err = sqlstruct.Scan(&t, rows)
    ...
}

err = rows.Err() // get any errors encountered during iteration

Aliased tables in a SQL statement may be scanned into a specific structure identified by the same alias, using the ColumnsAliased and ScanAliased functions:

type User struct {
    Id int `sql:"id"`
    Username string `sql:"username"`
    Email string `sql:"address"`
    Name string `sql:"name"`
    HomeAddress *Address `sql:"-"`
}

type Address struct {
    Id int `sql:"id"`
    City string `sql:"city"`
    Street string `sql:"address"`
}

...

var user User
var address Address
sql := `

SELECT %s, %s FROM users AS u INNER JOIN address AS a ON a.id = u.address_id WHERE u.username = ? `

sql = fmt.Sprintf(sql, sqlstruct.ColumnsAliased(*user, "u"), sqlstruct.ColumnsAliased(*address, "a"))
rows, err := db.Query(sql, "gedi")
if err != nil {
    log.Fatal(err)
}
defer rows.Close()
if rows.Next() {
    err = sqlstruct.ScanAliased(&user, rows, "u")
    if err != nil {
        log.Fatal(err)
    }
    err = sqlstruct.ScanAliased(&address, rows, "a")
    if err != nil {
        log.Fatal(err)
    }
    user.HomeAddress = address
}
fmt.Printf("%+v", *user)
// output: "{Id:1 Username:gedi Email:gediminas.morkevicius@gmail.com Name:Gedas HomeAddress:0xc21001f570}"
fmt.Printf("%+v", *user.HomeAddress)
// output: "{Id:2 City:Vilnius Street:Plento 34}"

Index

• Variables
• func Columns(s interface{}) string
• func ColumnsAliased(s interface{}, alias string) string
• func Scan(dest interface{}, rows Rows) error
• func ScanAliased(dest interface{}, rows Rows, alias string) error
• func ToSnakeCase(src string) string
• type Rows

Variables

var NameMapper = strings.ToLower

NameMapper is used to convert struct fields which do not have sql tags into database column names. The default mapper is a no-op.

Example override usage:

 FirstName => first_name
		sqlstruct.NameMapper = sqlstruct.ToSnakeCase

 Any func(string)string can be used

Functions

func Columns

func Columns(s interface{}) string

Columns returns a string containing a sorted, comma-separated list of column names as defined by the type s. s must be a struct that has exported fields tagged with the "sql" tag.

func ColumnsAliased

func ColumnsAliased(s interface{}, alias string) string

ColumnsAliased works like Columns except it prefixes the resulting column name with the given alias.

For each field in the given struct it will generate a statement like:

alias.field AS alias_field

It is intended to be used in conjunction with the ScanAliased function.

func Scan

func Scan(dest interface{}, rows Rows) error

Scan scans the next row from rows in to a struct pointed to by dest. The struct type should have exported fields tagged with the "sql" tag. Columns from row which are not mapped to any struct fields are ignored. Struct fields which have no matching column in the result set are left unchanged.

func ScanAliased

func ScanAliased(dest interface{}, rows Rows, alias string) error

ScanAliased works like scan, except that it expects the results in the query to be prefixed by the given alias.

For example, if scanning to a field named "name" with an alias of "user" it will expect to find the result in a column named "user_name".

See ColumnAliased for a convenient way to generate these queries.

func ToSnakeCase

func ToSnakeCase(src string) string

Types

type Rows

type Rows interface {
	Scan(...interface{}) error
	Columns() ([]string, error)
}

Rows defines the interface of types that are scannable with the Scan function. It is implemented by the sql.Rows type from the standard library