sqliteutil

sqliteutil provides utility functions to work with the standard database/sql package in golang.

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 := sqliteutil.Builder()
	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
	cols := sqlutil.Cols(Car{}, "", "select")

	// build the query only using the "select" cols
	query := fmt.Sprintf("SELECT %s FROM car", cols)

	// execute the query
	cars, err := sqlutil.Query[Car](tx, "select", query)
	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