sqlutil

sqliteutil

sqliteutil provides utility functions to work with the standard database/sql package in golang. It is based on Cols and Vals, two functions to get field names and scan targets from struct based on struct tags.

It additionally provides the Builder, that allows to generate parameterized Queries like it was fmt.Sprintf.

Example

type Car struct {
	ID    int64  `db:"id,select"`
	Make  string `db:"make,select,insert,update"`
	Model string `db:"model,select,insert,update"`
	Year  int64  `db:"year,select,insert,update"`
	Color string `db:"color,select,insert,update"`
}

func InsertCar(tx *sql.Tx, c *Car) (int64, error) {
	// extract all columns with the "insert" tag
	cols := sqlutil.Cols(c, "", "insert")
	vals := sqlutil.Vals(c, "insert")

	// create a Builder for sqlite (using the ? placeholder)
	// and write the query. It will end up looking like
	// INSERT INTO car (make, model, year, color) VALUES (?, ?, ?, ?)
	b := sqlutil.Builder{Dialect: sqlutil.SQLite}
	b.Printf("INSERT INTO car (%s) VALUES (%s)", cols, vals)

	// execute the query using the (matching) number of arguments
	res, err := tx.Exec(b.String(), b.Args...)
	if err != nil {
		return 0, err
	}
	return res.LastInsertId()
}

func SelectCars(tx *sql.Tx) ([]Car, error) {
	// extract all columns with the "select" tag
	var c Car
	cols := sqlutil.Cols(&c, "", "select")
	vals := sqlutil.Vals(&c, "select")

	// build the query only using the "select" cols
	b := sqlutil.Builder{Dialect: sqlutil.SQLite}
	b.Printf("SELECT %v FROM car", cols)

	// execute the query
	// See sqlutil.Slice and sqlutil.MappedSlice for other scan targets
	// that support straight forward scanning into slices.
	var cars []Car
	err := b.ScanRows(tx, &sqlutil.ScanFunc{
		Dest: vals,
		Add: func() {
			cars = append(cars, c)
		},
	})
	return cars, err
}

func UpdateCar(tx *sql.Tx, c *Car) error {
	// extract all columns with the "update" tag
	cols := sqlutil.Cols(c, "", "update")
	vals := sqlutil.Vals(c, "update")

	// create a Builder for sqlite (using the ? placeholder)
	// and use it to format the update statement. FieldList
	// is used to create the col = ...  part.
	b := sqliteutil.Builder()
	fields := sqlutil.FieldList{
		Cols: cols,
		Vals: vals,
		OP:   "=",
	}
	b.Printf("UPDATE car SET %v WHERE id = %v", fields, c.ID)
	_, err := tx.Exec(b.String(), b.Args...)
	return err
}

Struct Tags

The function Cols and Vals work using struct tags. Each field that should be retrievable must contain a struct tag named db.

The tag value consists of a field name, and optional "tags". The empty tag is implicitly given to all struct fields.

Licensing

sqlutil is released under the MPL v2.0 license