sqlstruct

README

sqlstruct

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

it is fully based on kisielks package and was expanded by me. I basically just add functionality I personally find convenient.

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

Goals of this fork

1. Use generics instead of interface{}: In my opinion, using generics improves readability and allows for additional functionality.
2. Keep Language Injections intact: Intellij IDEs offer language injections that, in this case, provide support for sql-queries if literals match sql query patterns. This was previously not possible, because for injecting columns dynamically with sqlstruct, a pattern like fmt.Sprintf("SELECT %s FROM ...", sqlstruct.Columns(mystruct{})) had to be used.
3. Improve the package for my use-cases: While I would love for someone else to find use in this package, one of its main goals is to allow for the removal of boilerplate and redundant code in my private project by integrating patterns I often deploy.

Usage

This package allows linking a struct and its database-counterpart, which means that SELECT-queries automatically reflect changes made to the datastructure by injecting the required columns into the query.

This works by extracting the exported fields of a struct, converting their names and inserting them into the given query. Just write the queries as normal using the autocomplete language injections provide and let your struct-definitions and sqlstruct take care of the columns.

Basics

var db *sql.DB

type User struct {
	ID       int
	UserName string
}

func GetAllUsers() (users []User, err error) {
	rows, err := db.Query(fmt.Sprintf("SELECT %s FROM users", sqlstruct.Columns[User]()))

	// ...
	return
}

The resulting query is: SELECT id, username FROM users. The column names can be modified using tags:

type User struct {
	ID       int
	UserName string `sql:"user_name"`
}

The query now is: SELECT id, user_name FROM users

Advanced

I've added the methods Query and QueryRow. They aim to remove boilerplate-code and keep goland language injections intact by working around the fmt.Sprintf.

The function GetAllUsers can now be simplified as follows:

func GetAllUsers() {
	sqlstruct.SetDatabase(db)

	users, err := sqlstruct.Query[User]("SELECT * FROM users")
	// ...
}

The * in the query is replaced by the structs' columns, just as using %s and sqlstruct.Columns(...) would. It can be changed by setting the exported variable sqlstruct.QueryReplace to any other character.

Query row does the same, except not a slice is returned, but a single object:

func GetUserByID(id int) {
    sqlstruct.SetDatabase(db)

    user, err := sqlstruct.QueryRow[User]("SELECT * FROM users WHERE id = ?", id)
    // ...
}

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[T any]() string
• func ColumnsAliased[T any](alias string) string
• func Query[T any](query string, args ...any) (slice []T, err error)
• func QueryInts(query string, args ...any) (results []int, err error)
• func QueryRow[T any](query string, args ...any) (stru T, err error)
• func Scan[T any](dest *T, rows Rows) error
• func ScanAliased[T any](dest *T, rows Rows, alias string) error
• func SetDatabase(sqldb *sql.DB)
• func SliceFromRows[T any](rows *sql.Rows) (slice []T, err error)
• func ToSnakeCase(src string) string
• type Rows
• type Scanner

Variables

var (
	// NameMapper is the function used to convert struct fields which do not have sql tags
	// into database column names.
	//
	// The default mapper converts field names to lower case. If instead you would prefer
	// field names converted to snake case, simply assign sqlstruct.ToSnakeCase to the variable:
	//
	//	sqlstruct.NameMapper = sqlstruct.ToSnakeCase
	//
	// Alternatively for a custom mapping, any func(string) string can be used instead.
	NameMapper = strings.ToLower

	// TagName is the name of the tag to use on struct fields
	TagName = "sql"

	QueryReplace = "*"
)

Functions

func Columns

func Columns[T any]() 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[T any](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 Query

func Query[T any](query string, args ...any) (slice []T, err error)

Query executes the given query using the global database handle and returns the resulting objects in a slice. SetDatabase must be called before using this function. The query should use the QueryReplace (* by default) string to indicate where the columns from the struct type T should be inserted.

For example for the following struct:

type User struct {
	ID   int
	Name string
}

and the following query

SELECT * FROM users WHERE id = ?

the query sent to the database will be

SELECT id, name FROM users WHERE id = ?

and a list of User objects will be returned.

func QueryInts

func QueryInts(query string, args ...any) (results []int, err error)

func QueryRow

func QueryRow[T any](query string, args ...any) (stru T, err error)

QueryRow works similar to Query except it returns only the first row from the result set. SetDatabase must be called before using this function. The query should use the QueryReplace (* by default) string to indicate where the columns from the struct type T should be inserted.

func Scan

func Scan[T any](dest *T, 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[T any](dest *T, 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 SetDatabase

func SetDatabase(sqldb *sql.DB)

SetDatabase sets the global database handle to be used by the Query function.

func SliceFromRows

func SliceFromRows[T any](rows *sql.Rows) (slice []T, err error)

SliceFromRows returns a slice of structs from the given rows by calling Scan on each row.

func ToSnakeCase

func ToSnakeCase(src string) string

ToSnakeCase converts a string to snake case, words separated with underscores. It's intended to be used with NameMapper to map struct field names to snake case database fields.

Types

type Rows

type Rows interface {
	Scan(...any) 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

type Scanner

type Scanner interface {
	Scan(src any) error
}

Scanner is an interface used by Scan.